Wiring up additional properties for LINQ to SQL

M

moo

I'm a newbie to LINQ who's trying to use/learn it on a project, though
my use-case seems different from normal Google queries on LINQ (LINQG,
I'm sure it'll be out in 2011).

I have a data model where a Person has an M:N relationship with Phone
(many people from the same office may share a number, one person may
have cell, office, home, etc) in my database.

Person
+personID
firstName
lastName
email

PersonPhone
personID
phoneID

Phone
phoneID
phoneNumber

I've generated a view in the back-end that selects columns from
various tables in order to simplify processing insertions from an
input Excel file. This file has columns that correspond to home phone
and office phone.

I'm not sure whether I should try to modify the view within the DB
structure to try to get another phone number column, or modify the
LINQ dbml by adding an property to the generated View class in the
code. The modified class seems easiest (though then the LINQ doesn't
directly respond to the underlying data model), where I could create
an property phoneNumber2 who's SQL does an insert into the phone
table. I'm not sure how to wire that up though. Any suggestions?
 
M

MooMaster

I'm a newbie to LINQ who's trying to use/learn it on a project, though
my use-case seems different from normal Google queries on LINQ (LINQG,
I'm sure it'll be out in 2011).

I have a data model where a Person has an M:N relationship with Phone
(many people from the same office may share a number, one person may
have cell, office, home, etc) in my database.

Person
+personID
firstName
lastName
email

PersonPhone
personID
phoneID

Phone
phoneID
phoneNumber

I've generated a view in the back-end that selects columns from
various tables in order to simplify processing insertions from an
input Excel file. This file has columns that correspond to home phone
and office phone.

I'm not sure whether I should try to modify the view within the DB
structure to try to get another phone number column, or modify the
LINQ dbml by adding an property to the generated View class in the
code. The modified class seems easiest (though then the LINQ doesn't
directly respond to the underlying data model), where I could create
an property phoneNumber2 who's SQL does an insert into the phone
table. I'm not sure how to wire that up though. Any suggestions?
Click to expand...

Hmmm, looks like what I'm really asking is easiest to do via the
snippet shown in figure 9
http://aspalliance.com/1414_LINQ_to_SQL_Part_4__Updating_our_Database.all#Page7

in which case I get away from adding a new property and worrying about
that mess. Nevermind.
 
M

MooMaster

Hmmm, looks like what I'm really asking is easiest to do via the
snippet shown in figure 9http://aspalliance.com/1414_LINQ_to_SQL_Part_4__Updating_our_Database...

in which case I get away from adding a new property and worrying about
that mess. Nevermind.- Hide quoted text -

- Show quoted text -
Click to expand...

I tried replicating the steps shown in the figure on my code, but no
joy. When debugging I see that all of the LINQ objects are populated
successfully from a Dataset, and the SubmitChanges() method resolves
and no exceptions are thrown...but the database does not have the data
afterward. I'm at a loss as to why. My code is show below:

<code>
/// <summary>
/// insertBCEIntoDatabase -- inserts the data in the given table
into the db using
/// LINQ
/// </summary>
/// <param name="loadedTable">A DataTable populated from an Excel
file using an OleDBAdapter.
/// The data is successfully populated and has been verified on a
GridView </param>
private void insertBCEIntoDatabase(DataTable loadedTable)
{
foreach (DataRow aRow in loadedTable.Rows)
{
//see if these entities exist in the database. If not,
insert them
Address tempAddress = new Address
{
streetAddress = aRow[BCESchema[3]].ToString(),
city = aRow[BCESchema[4]].ToString(),
state = aRow[BCESchema[5]].ToString(),
zip = aRow[BCESchema[6]].ToString()
};
Phone tempPhone = new Phone
{
phoneNumber = aRow[BCESchema[7]].ToString()
};
Lead tempLead = new Lead
{
firstName = aRow[BCESchema[2]].ToString().Split(' ')
[0],
lastName = aRow[BCESchema[2]].ToString().Split(' ')
[1],
email = aRow[BCESchema[11]].ToString()
};
LeadAddress ledo = new LeadAddress
{
Lead = tempLead,
Address = tempAddress
};
LeadPhone leadPhone = new LeadPhone
{
Lead = tempLead,
Phone = tempPhone
};
//do I need to do this? Not sure since I created the
entities
tempLead.LeadAddresses.Add(ledo);
tempLead.LeadPhones.Add(leadPhone);
bool forParse = false;
Order tempOrder = new Order
{
invoiceID = aRow[BCESchema[0]].ToString(),
orderDate = aRow[BCESchema[1]].ToString(),
Lead = tempLead,
orderAmount = (decimal?)Decimal.Parse(aRow[BCESchema
[8]].ToString()),
division = aRow[BCESchema[9]].ToString(),
reload = (bool?)Boolean.TryParse(aRow[BCESchema
[10]].ToString(),out forParse)

};
//again, necessary?
tempLead.Orders.Add(tempOrder);
aContext.SubmitChanges();
}//end of foreach for DataRows
}//end of helper method to insert BCE data into DB
</code>
 
M

MooMaster

Hmmm, looks like what I'm really asking is easiest to do via the
snippet shown in figure 9http://aspalliance.com/1414_LINQ_to_SQL_Part_4__Updating_our_Database...
Click to expand...
in which case I get away from adding a new property and worrying about
that mess. Nevermind.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

I tried replicating the steps shown in the figure on my code, but no
joy. When debugging I see that all of the LINQ objects are populated
successfully from a Dataset, and the SubmitChanges() method resolves
and no exceptions are thrown...but the database does not have the data
afterward. I'm at a loss as to why. My code is show below:

<code>
/// <summary>
    /// insertBCEIntoDatabase -- inserts the data in the given table
into the db using
    /// LINQ
    /// </summary>
    /// <param name="loadedTable">A DataTable populated from an Excel
file using an OleDBAdapter.
    /// The data is successfully populated and has been verified on a
GridView </param>
    private void insertBCEIntoDatabase(DataTable loadedTable)
    {
        foreach (DataRow aRow in loadedTable.Rows)
        {
            //see if these entities exist in the database. Ifnot,
insert them
            Address tempAddress = new Address
            {
                streetAddress = aRow[BCESchema[3]].ToString(),
                city = aRow[BCESchema[4]].ToString(),
                state = aRow[BCESchema[5]].ToString(),
                zip = aRow[BCESchema[6]].ToString()
            };
            Phone tempPhone = new Phone
            {
                phoneNumber = aRow[BCESchema[7]].ToString()
            };
            Lead tempLead = new Lead
            {
                firstName = aRow[BCESchema[2]].ToString().Split(' ')
[0],
                lastName = aRow[BCESchema[2]].ToString().Split(' ')
[1],
                email = aRow[BCESchema[11]].ToString()
            };
            LeadAddress ledo = new LeadAddress
            {
                Lead = tempLead,
                Address = tempAddress
            };
            LeadPhone leadPhone = new LeadPhone
            {
                Lead = tempLead,
                Phone = tempPhone
            };
            //do I need to do this? Not sure since I created the
entities
            tempLead.LeadAddresses.Add(ledo);
            tempLead.LeadPhones.Add(leadPhone);
            bool forParse = false;
            Order tempOrder = new Order
            {
                invoiceID = aRow[BCESchema[0]].ToString(),
                orderDate = aRow[BCESchema[1]].ToString(),
                Lead = tempLead,
                orderAmount = (decimal?)Decimal.Parse(aRow[BCESchema
[8]].ToString()),
                division = aRow[BCESchema[9]].ToString(),
                reload = (bool?)Boolean.TryParse(aRow[BCESchema
[10]].ToString(),out forParse)

            };
            //again, necessary?
            tempLead.Orders.Add(tempOrder);
            aContext.SubmitChanges();
        }//end of foreach for DataRows
    }//end of helper method to insert BCE data into DB
</code>- Hide quoted text -

- Show quoted text -
Click to expand...

Found a solution, but it makes no sense.
http://msdn.microsoft.com/en-us/library/bb399339.aspx
http://www.hookedonlinq.com/LINQtoSQL5MinuteOverview.ashx?Code=1
http://aspalliance.com/1414_LINQ_to_SQL_Part_4__Updating_our_Database.all#Page7

all suggest that SubmitChanges() alone does the job. However,
http://www.develop-one.net/blog/2008/11/30/LINQToSQLSubmitChanges.aspx

shows an InsertOnSubmit() method. I added
<code>
aContext.Leads.InsertOnSubmit(tempLead);
</code>
above the SubmitChanges() call and now I get data in the database.
But if spreadsheets contain duplicate entites, I violate primary key
constraints (since it's always trying to insert). I thought LINQ took
care of this by analyzing the data model and only generating SQL for
changes...am I mistaken?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Question about LinQ (LinQ to Sql) 4
Specifying the Linq to SQL dbml connection at runtime 2
Linq To SQL and multiple foreign keys naming issue 1
linq to sql for stored proc with user defined table as parameter 6
Critique my LINQ to SQL strategy using stored procs, please 5
Linq to SQL: insert subclassed row 4
Full compliance with Code Analysis still possible when using LINQ? 1
Linq 2 sql, enum, no supported translation to SQL 1

Top