Whilst implementing the AdventureWorks db in a sample application using Linq 2 SQL i've ran into a problem with the way in which Linq 2 SQL generates SQL statements for derived types in the inheritance mapping defined in my DBML.
I have two entities in my DBML, Person and PersonPhone (one Person to many PersonPhones) WITHOUT any inheritance mapping defined (Person will be a inheritance base class in a later example). If I then run the following Linq statement I notice that whilst doing a trace the following SQL is generated as expected:
void Main()
{
this.DeferredLoadingEnabled = false;
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Person>(n => n.PersonPhones);
this.LoadOptions = dlo;
Person person = this.Persons.SingleOrDefault(n => n.BusinessEntityID == 291);
person.PersonPhones.Dump();
}
DECLARE @p0 Int = 291
SELECT [t0].[BusinessEntityID], [t0].[PersonType], [t0].[NameStyle], [t0].[Title], [t0].[FirstName], [t0].[MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[EmailPromotion], [t0].[AdditionalContactInfo], [t0].[Demographics], [t0].[rowguid], [t0].[ModifiedDate], [t1].[BusinessEntityID] AS [BusinessEntityID2], [t1].[PhoneNumber], [t1].[PhoneNumberTypeID], [t1].[ModifiedDate] AS [ModifiedDate2], (
SELECT COUNT(*)
FROM [Person].[PersonPhone] AS [t2]
WHERE [t2].[BusinessEntityID] = [t0].[BusinessEntityID]
) AS [value]
FROM [Person].[Person] AS [t0]
LEFT OUTER JOIN [Person].[PersonPhone] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[BusinessEntityID] = @p0
ORDER BY [t0].[BusinessEntityID], [t1].[PhoneNumber], [t1].[PhoneNumberTypeID]
The result is that the SQL is generated in a single statement. Please ignore the fact that this might be inefficient SQL, the point i'm making is that its ran in a single statement.
But if I include inheritance mapping information in my DBML and define a new entity type called StoreContact (derives from Person) and run the exact same query I actually get different results as shown below. In that TWO SQL statements are actually run.
DECLARE @p0 Int = 291
SELECT [t0].[PersonType], [t0].[BusinessEntityID], [t0].[NameStyle], [t0].[Title], [t0].[FirstName], [t0].[MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[EmailPromotion], [t0].[AdditionalContactInfo], [t0].[Demographics], [t0].[rowguid], [t0].[ModifiedDate]
FROM [Person].[Person] AS [t0]
WHERE [t0].[BusinessEntityID] = @p0
GO
DECLARE @x1 Int = 291
SELECT [t0].[BusinessEntityID], [t0].[PhoneNumber], [t0].[PhoneNumberTypeID], [t0].[ModifiedDate]
FROM [Person].[PersonPhone] AS [t0]
WHERE [t0].[BusinessEntityID] = @x1
This is somewhat problematic in that this will reduce the speed of our applications rather substantially in that Linq 2 SQL is required to make duplicate calls to the DB in the case of implementing the inheritance feature. Looks like having an OOP model does come at a cost.
Is there a workaround for this problem to make the SQL run in a single statement?
ac.PersonPhonesis notIqueryable. LINQ does not optimize this in any way. This statement cannot possibly influence the execution ofquery. Right now I have doubts that the code shown corresponds to the SQL shown. Can you review this? Maybe single-step through and observe which lines generate which SQL. – usr Feb 14 at 11:54