Wednesday, July 09, 2008 12:08 AM
LINQ to Entities – Join Queries
This is my first project on LINQ, and I got surprised on how tuff it is to build join queries. But the trick is to understand how the LINQ to Entities work.
I will discuss in this blog some of the complicated queries that I built and how I built them.
But let's first understand how the LINQ to Entities works: each entity contains a set of properties, and a set of entity objects if it is linked to other tables. So, it is already linked together.
A City might contain many hubs, and a hub can be for many cities. The relation between city and hub is many-to-many.
First let's get all the hubs for a specific city, to write it in LINQ:from h in db.Hubs from ch in h.CityHubswhere ch.Cities.CityID == 1 select h;
The trick was in the second line:
from ch in h.CityHubs
As you can see, cities and hubs are already linked in the Entity Data Model, so, no need to write a "join" query. But to reference it, you can use the from on the referenced Hubs which is h.
Now, let's do something more challenging. We want to get all the hubs that are not for a specific city. In T-SQL, we use nested query and the "not in" as following:SELECT *FROM Hubs hWHERE h.HubId NOT IN (SELECT HubID FROM CityHubs
WHERE CityID = 1)
This is one way to write it in T-SQL, but there is another way:SELECT h.*FROM Hubs h, Common.CityHubs chWHERE ch.CityID = 1
AND ch.HubId != h.HubId
There is no "not in" operator in LINQ, so we're going to transform the second T-SQL to LINQ as following:from h in db.Hubs from ch in db.CityHubswhere ch.Cities.CityID == 1 && h.HubID != ch.Hubs.HubIDselect h;
As you can see, you can do a Cross Join in LINQ using many times "from" operator
Now, another more sophisticated query:
Each flight has two Hubs, one arrival and the other one is the departure. The query has to get the available flights for the selected arrival and departure directorates.
In T-SQL, it can be written as following:SELECT f.*FROM flights f
INNER JOIN Hubs sh ON f.HubFrom = sh.HubId INNER JOIN CityHubs sch
ON sh.HubId = sch.HubId INNER JOIN Cities sc ON sch.CityId = sc.CityId
INNER JOIN Directorates sd ON sc.CityId = sd.CityId INNER JOIN Hubs dh ON f.HubTo = dh.HubId INNER JOIN CityHubs dch ON dh.HubId = dch.HubId
INNER JOIN Cities dc ON dch.CityId = dc.CityId INNER JOIN Directorates dd ON dd.CityId = dc.CityIdWHERE sd.DirectorateID = @sourceDirectorateID AND dd.DirectorateId = @destinationDirectorateID
In LINQ, it is written as following:from f in db.Flights from fc in f.Hubs.Cities from fd in fc.Directorates from tc in f.Hubs1.Cities from td in tc.Directorateswhere fd.DirectorateID == sourceDirectorateID && td.DirectorateID == destinationDirectorateIDselect f;
Enjoy them J
Filed under: ASP.NET, .NET 3.5, Linq to Entities, Linq, C#