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.
For example
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.Hubsfrom ch in h.CityHubs
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 h
WHERE 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 ch
WHERE 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.Hubsfrom ch in db.CityHubs
where ch.Cities.CityID == 1
select 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.CityId
WHERE sd.DirectorateID = @sourceDirectorateID
AND dd.DirectorateId = @destinationDirectorateID
In LINQ, it is written as following:
from f in db.Flightsfrom fc in f.Hubs.Cities
from fd in fc.Directorates
from tc in f.Hubs1.Cities
from td in tc.Directorates
where fd.DirectorateID == sourceDirectorateID
&& td.DirectorateID == destinationDirectorateID
select f;
Enjoy them J