I have the following table structure:
That is, a Supplier has a many-to-many relationship with People (of Person).
Supplier 1--* SupplierPerson *--1 Person
A Person has a one-to-many relationship with EmaiLAddresses, TelephoneNumbers and WebResources.
Person 1--* EmailAddress
Person 1--* TelephoneNumber
Person 1--* WebResource
I've highlighted the navigation properties.
Given the LINQ-to-Entities using .Include() to load the dependencies and shape the result-set:
ObjectQuery<Supplier> supplierQuery=((SopEntities)Context).Suppliers
.Include("People")
.Include("People.TelephoneNumbers")
.Include("People.EmailAddresses")
.Include("People.WebResources");
#if TRACE
Trace.WriteLine(string.Format("GetAll(): {0}", supplierQuery.ToTraceString()));
#endif
return supplierQuery;
I get the insanely large and rather useless SQL:
SELECT
[Project5].[ID] AS [ID],
[Project5].[Key] AS [Key],
[Project5].[CompanyName] AS [CompanyName],
[Project5].[AddressLine1] AS [AddressLine1],
[Project5].[AddressLine2] AS [AddressLine2],
[Project5].[TownCity] AS [TownCity],
[Project5].[CountyState] AS [CountyState],
[Project5].[Postcode] AS [Postcode],
[Project5].[Country] AS [Country],
[Project5].[C25] AS [C1],
[Project5].[C2] AS [C2],
[Project5].[C3] AS [C3],
[Project5].[C4] AS [C4],
[Project5].[C5] AS [C5],
[Project5].[C6] AS [C6],
[Project5].[C7] AS [C7],
[Project5].[C8] AS [C8],
[Project5].[C9] AS [C9],
[Project5].[C10] AS [C10],
[Project5].[C11] AS [C11],
[Project5].[C1] AS [C12],
[Project5].[C12] AS [C13],
[Project5].[C13] AS [C14],
[Project5].[C14] AS [C15],
[Project5].[C15] AS [C16],
[Project5].[C16] AS [C17],
[Project5].[C17] AS [C18],
[Project5].[C18] AS [C19],
[Project5].[C19] AS [C20],
[Project5].[C20] AS [C21],
[Project5].[C21] AS [C22],
[Project5].[C22] AS [C23],
[Project5].[C23] AS [C24],
[Project5].[C24] AS [C25]
FROM ( SELECT
[Extent1].[ID] AS [ID],
[Extent1].[Key] AS [Key],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[AddressLine1] AS [AddressLine1],
[Extent1].[AddressLine2] AS [AddressLine2],
[Extent1].[TownCity] AS [TownCity],
[Extent1].[CountyState] AS [CountyState],
[Extent1].[Postcode] AS [Postcode],
[Extent1].[Country] AS [Country],
[UnionAll2].[C1] AS [C1],
[UnionAll2].[C2] AS [C2],
[UnionAll2].[C3] AS [C3],
[UnionAll2].[C4] AS [C4],
[UnionAll2].[C5] AS [C5],
[UnionAll2].[C6] AS [C6],
[UnionAll2].[C7] AS [C7],
[UnionAll2].[C8] AS [C8],
[UnionAll2].[C9] AS [C9],
[UnionAll2].[C10] AS [C10],
[UnionAll2].[C11] AS [C11],
[UnionAll2].[C12] AS [C12],
[UnionAll2].[C13] AS [C13],
[UnionAll2].[C14] AS [C14],
[UnionAll2].[C15] AS [C15],
[UnionAll2].[C16] AS [C16],
[UnionAll2].[C17] AS [C17],
[UnionAll2].[C18] AS [C18],
[UnionAll2].[C19] AS [C19],
[UnionAll2].[C20] AS [C20],
[UnionAll2].[C21] AS [C21],
[UnionAll2].[C22] AS [C22],
[UnionAll2].[C23] AS [C23],
[UnionAll2].[C24] AS [C24],
CASE WHEN ([UnionAll2].[C2] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C25]
FROM [dbo].[Supplier] AS [Extent1]
OUTER APPLY (SELECT
[UnionAll1].[C1] AS [C1],
[UnionAll1].[PersonID] AS [C2],
[UnionAll1].[SupplierID] AS [C3],
[UnionAll1].[SupplierID1] AS [C4],
[UnionAll1].[ID] AS [C5],
[UnionAll1].[Title] AS [C6],
[UnionAll1].[FirstName] AS [C7],
[UnionAll1].[Initials] AS [C8],
[UnionAll1].[LastName] AS [C9],
[UnionAll1].[Position] AS [C10],
[UnionAll1].[DepartmentID] AS [C11],
[UnionAll1].[ID1] AS [C12],
[UnionAll1].[Number] AS [C13],
[UnionAll1].[Name] AS [C14],
[UnionAll1].[PersonID1] AS [C15],
[UnionAll1].[TelephoneNumberTypeID] AS [C16],
[UnionAll1].[C2] AS [C17],
[UnionAll1].[C3] AS [C18],
[UnionAll1].[C4] AS [C19],
[UnionAll1].[C5] AS [C20],
[UnionAll1].[C6] AS [C21],
[UnionAll1].[C7] AS [C22],
[UnionAll1].[C8] AS [C23],
[UnionAll1].[C9] AS [C24]
FROM (SELECT
CASE WHEN ([Extent4].[ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
[Extent2].[PersonID] AS [PersonID],
[Extent2].[SupplierID] AS [SupplierID],
[Extent2].[SupplierID] AS [SupplierID1],
[Extent3].[ID] AS [ID],
[Extent3].[Title] AS [Title],
[Extent3].[FirstName] AS [FirstName],
[Extent3].[Initials] AS [Initials],
[Extent3].[LastName] AS [LastName],
[Extent3].[Position] AS [Position],
[Extent3].[DepartmentID] AS [DepartmentID],
[Extent4].[ID] AS [ID1],
[Extent4].[Number] AS [Number],
[Extent4].[Name] AS [Name],
[Extent4].[PersonID] AS [PersonID1],
[Extent4].[TelephoneNumberTypeID] AS [TelephoneNumberTypeID],
CAST(NULL AS int) AS [C2],
CAST(NULL AS varchar(1)) AS [C3],
CAST(NULL AS varchar(1)) AS [C4],
CAST(NULL AS int) AS [C5],
CAST(NULL AS int) AS [C6],
CAST(NULL AS varchar(1)) AS [C7],
CAST(NULL AS varchar(1)) AS [C8],
CAST(NULL AS int) AS [C9]
FROM [dbo].[SupplierPerson] AS [Extent2]
INNER JOIN [dbo].[Person] AS [Extent3] ON [Extent3].[ID] = [Extent2].[PersonID]
LEFT OUTER JOIN [dbo].[TelephoneNumber] AS [Extent4] ON [Extent3].[ID] = [Extent4].[PersonID]
WHERE [Extent1].[ID] = [Extent2].[SupplierID]
UNION ALL
SELECT
2 AS [C1],
[Extent5].[PersonID] AS [PersonID],
[Extent5].[SupplierID] AS [SupplierID],
[Extent5].[SupplierID] AS [SupplierID1],
[Extent6].[ID] AS [ID],
[Extent6].[Title] AS [Title],
[Extent6].[FirstName] AS [FirstName],
[Extent6].[Initials] AS [Initials],
[Extent6].[LastName] AS [LastName],
[Extent6].[Position] AS [Position],
[Extent6].[DepartmentID] AS [DepartmentID],
CAST(NULL AS int) AS [C2],
CAST(NULL AS varchar(1)) AS [C3],
CAST(NULL AS varchar(1)) AS [C4],
CAST(NULL AS int) AS [C5],
CAST(NULL AS int) AS [C6],
[Extent7].[ID] AS [ID1],
[Extent7].[Email] AS [Email],
[Extent7].[Name] AS [Name],
[Extent7].[PersonID] AS [PersonID1],
CAST(NULL AS int) AS [C7],
CAST(NULL AS varchar(1)) AS [C8],
CAST(NULL AS varchar(1)) AS [C9],
CAST(NULL AS int) AS [C10]
FROM [dbo].[SupplierPerson] AS [Extent5]
INNER JOIN [dbo].[Person] AS [Extent6] ON [Extent6].[ID] = [Extent5].[PersonID]
INNER JOIN [dbo].[EmailAddress] AS [Extent7] ON [Extent6].[ID] = [Extent7].[PersonID]
WHERE [Extent1].[ID] = [Extent5].[SupplierID]) AS [UnionAll1]
UNION ALL
SELECT
3 AS [C1],
[Extent8].[PersonID] AS [PersonID],
[Extent8].[SupplierID] AS [SupplierID],
[Extent8].[SupplierID] AS [SupplierID1],
[Extent9].[ID] AS [ID],
[Extent9].[Title] AS [Title],
[Extent9].[FirstName] AS [FirstName],
[Extent9].[Initials] AS [Initials],
[Extent9].[LastName] AS [LastName],
[Extent9].[Position] AS [Position],
[Extent9].[DepartmentID] AS [DepartmentID],
CAST(NULL AS int) AS [C2],
CAST(NULL AS varchar(1)) AS [C3],
CAST(NULL AS varchar(1)) AS [C4],
CAST(NULL AS int) AS [C5],
CAST(NULL AS int) AS [C6],
CAST(NULL AS int) AS [C7],
CAST(NULL AS varchar(1)) AS [C8],
CAST(NULL AS varchar(1)) AS [C9],
CAST(NULL AS int) AS [C10],
[Extent10].[ID] AS [ID1],
[Extent10].[Url] AS [Url],
[Extent10].[Name] AS [Name],
[Extent10].[PersonID] AS [PersonID1]
FROM [dbo].[SupplierPerson] AS [Extent8]
INNER JOIN [dbo].[Person] AS [Extent9] ON [Extent9].[ID] = [Extent8].[PersonID]
INNER JOIN [dbo].[WebResource] AS [Extent10] ON [Extent9].[ID] = [Extent10].[PersonID]
WHERE [Extent1].[ID] = [Extent8].[SupplierID]) AS [UnionAll2]
) AS [Project5]
ORDER BY [Project5].[ID] ASC, [Project5].[C25] ASC, [Project5].[C2] ASC, [Project5].[C3] ASC, [Project5].[C5] ASC, [Project5].[C1] ASC
Which populates the Supplier with People, but not the TelephoneNumbers, EmailAddresses or WebResources.
When executed in SQL Management Studio, I get the incomplete record set:
Clearly, the .Include() is not working due to my misunderstanding of its capabilities.
I really would like to avoid having to call the People schema seperately. How can I have this generate useful SQL?
