Hello Mark,
Why do you think the direct query clumsy and inefficient? Only after
clarifying the performance bottleneck, we can do some specified research
and find dedicated solution for this.
Currently, the following is my research result,
Based on your description, I create the following three tables and test in
my side,
People,
PeopleId
PeopleName
DepartmentId
Department,
DepartmentId
DepartmentName
CompanyId
Company
CompanyId
CompanyName
Company.CompanyId links to Department.CompanyId and Department.DepartmentId
links to People.DepartmentId. Is the the same schema as you describe? If
that is the case, I think, the Department acts as an bridge between People
and Company. We cannot get a specified company's whole people without
indirect querying the Department table. The application itself will not
know which people belongs to which company without the department
information.
--------------------------------
Let's go back to the question how to perform such a query,
1. The first way is as you described in your original question,
var people = from p in context.People
where p.Department.Company.CompanyId == 1
select p;
foreach (var p in people)
{
Console.WriteLine(p.PeopleID);
Console.WriteLine(p.PeopleName);
}
This way results the following SQL command to be executed and retrieve the
desired results. Actually, I do not think this way is inefficient.
SELECT
1 AS [C1],
[Extent1].[PeopleID] AS [PeopleID],
[Extent1].[PeopleName] AS [PeopleName],
[Extent1].[DepartmentId] AS [DepartmentId]
FROM [dbo].[People] AS [Extent1]
INNER JOIN [dbo].[Department] AS [Extent2] ON [Extent1].[DepartmentId] =
[Extent2].[DepartmentId]
WHERE 1 = [Extent2].[CompanyID]
2. I am not sure if you are trying to look after the second way I am going
to describe. Actually, we can query about the Company and use Eager Loading
to load all departments and people into the memory. After that iterating
through the results will not cause additional query to database. The codes
look like,
var company = (from c in
context.Company.Include("Department.People")
where c.CompanyId == 1
select c).First();
foreach (var d in company.Department)
{
foreach (var p in d.People)
{
Console.WriteLine(p.PeopleID);
Console.WriteLine(p.PeopleName);
}
}
And this way generate the following SQL command to retrieve all information
regarding to a company's department and people.
SELECT
[Project3].[CompanyId] AS [CompanyId],
[Project3].[CompanyName] AS [CompanyName],
[Project3].[C1] AS [C1],
[Project3].[C4] AS [C2],
[Project3].[DepartmentId] AS [DepartmentId],
[Project3].[DepartmentName] AS [DepartmentName],
[Project3].[CompanyId1] AS [CompanyId1],
[Project3].[C3] AS [C3],
[Project3].[C2] AS [C4],
[Project3].[PeopleID] AS [PeopleID],
[Project3].[PeopleName] AS [PeopleName],
[Project3].[DepartmentId1] AS [DepartmentId1]
FROM ( SELECT
[Limit1].[CompanyId] AS [CompanyId],
[Limit1].[CompanyName] AS [CompanyName],
[Limit1].[C1] AS [C1],
[Project2].[DepartmentId] AS [DepartmentId],
[Project2].[DepartmentName] AS [DepartmentName],
[Project2].[CompanyID] AS [CompanyId1],
[Project2].[PeopleID] AS [PeopleID],
[Project2].[PeopleName] AS [PeopleName],
[Project2].[DepartmentId1] AS [DepartmentId1],
CASE WHEN ([Project2].[C1] IS NULL) THEN CAST(NULL AS int) ELSE CASE WHEN
([Project2].[PeopleID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END END AS
[C2],
CASE WHEN ([Project2].[C1] IS NULL) THEN CAST(NULL AS int) ELSE CASE WHEN
([Project2].[PeopleID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END END AS
[C3],
[Project2].[C1] AS [C4]
FROM (SELECT TOP (1)
[Extent1].[CompanyId] AS [CompanyId],
[Extent1].[CompanyName] AS [CompanyName],
1 AS [C1]
FROM [dbo].[Company] AS [Extent1]
WHERE 1 = [Extent1].[CompanyId] ) AS [Limit1]
LEFT OUTER JOIN (SELECT
[Extent2].[DepartmentId] AS [DepartmentId],
[Extent2].[DepartmentName] AS [DepartmentName],
[Extent2].[CompanyID] AS [CompanyID],
[Extent3].[PeopleID] AS [PeopleID],
[Extent3].[PeopleName] AS [PeopleName],
[Extent3].[DepartmentId] AS [DepartmentId1],
1 AS [C1]
FROM [dbo].[Department] AS [Extent2]
LEFT OUTER JOIN [dbo].[People] AS [Extent3] ON [Extent2].[DepartmentId] =
[Extent3].[DepartmentId] ) AS [Project2] ON [Limit1].[CompanyId] =
[Project2].[CompanyID]
) AS [Project3]
ORDER BY [Project3].[CompanyId] ASC, [Project3].[C4] ASC,
[Project3].[DepartmentId] ASC, [Project3].[C3] ASC
To know more about the Eager loading, please have a look at the following
artilces,
http://blogs.msdn.com/alexj/archive/2009/03/23/tip-6-when-and-how-to-use-eag
er-loading.aspx
http://blogs.msdn.com/adonet/archive/2008/10/07/migrating-from-linq-to-sql-t
o-entity-framework-eager-loading.aspx
Have a nice day Sir!
Best regards,
Colbert Zhou
Microsoft Newsgroup Support Team
