Wiring up additional properties for LINQ to SQL

  • Thread starter Thread starter moo
  • Start date Start date
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?
 
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.
 
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>
 
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?
 
Back
Top