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.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 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.Hubs
     from ch in db.CityHubs
where ch.Cities.CityID == 1   && h.HubID != ch.Hubs.HubID
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.Flights
  from 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