In my project I retrieve all contacts (around 1300) using LinqToSql. In the database the contacts table (tblContacts) is associated with the tblContactsTypes and also with tblContactsChildren.
The associations are:
tblContact.IDContact > tblContactsTypes.IDContact
tblContact.IDContact > tblContactsChildren.IDCont
Getting only the contacts works great and fast. Getting the contacts and also the contact types OR the contact children also works great and fast. But when I try to get all the contacts and contact types AND contact children at the same time things become really crippled and slow.
In the example below I have set the code up to get all the information i want, but this example doesn't work. If I remove either the contact types or the contact children code, it works.
I have figured out that when I use both contact types and contact children, the contact children is retrieved from the sql server once for every contact. If I switch place of the contact types and contact children in the code, it's still the contact children that is requested from the sql server each time.
Why is this happening?
Public Function GetContacts(ByVal SelectedCompanyID As Integer) As List(Of Contact)
Using DataContext As New DataClassesDataContext
Dim result = Nothing
result = (From c In DataContext.tblContacts
Where c.IDCompany = SelectedCompanyID
Select New Contact With
.IDContact = c.IDContact,
(From ct In c.tblContactsTypes
Select New ContactsTypes With
.IDContactType = ct.IDContactType,
.IDType = ct.IDType,
.IDContact = ct.IDContact
(From cc In c.tblContactsChildrens
Select New ContactsChild With
.IDContactChild = cc.IDContactChild,
.IDCompany = cc.IDCompany,
.IDContact_Company = cc.IDContact_Company,
.IDContact_Person = cc.IDContact_Person
Catch ex As Exception
the project runs in Visual Studio 2010 under .NET 4. The database server is a MS SQL 2008 R2.
Let me know if you need more information. I'd even send the whole project to you if you think that could help.