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.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:

FROM Hubs h
                      FROM CityHubs                                   
                      WHERE CityID = 1)

This is one way to write it in T-SQL, but there is another way:

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:

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


  • This saved me a lot of time in using Join. I've tried examples from many sites which does not look like valid one!
    Thanks for these detailed example!

  • Perfect post! Saved a lot of time too!

  • Hi Nalaka,

    "Navigation Properties", this is a Entities Data Model

  • Simple but superb...but I was navigating many sites to get LINQ to EnTITIES JOIN but couldn't...Finally you saved my time. Thanks a lot.

  • the sub entities are not loaded though. do you explicity have to load those - f.Directorates.Load()?

  • If the sub entities are not loaded (which normally happens), then you must call the Load() function. But you cannot call it from inside the LINQ query, but after getting the results

  • Could u please help me to re-write the following query in ILNQ to Entities

    select recm.REC_SR_NO , recm.REC_DT , recm.REC_REM , recm.CRT_BY_USER_CD ,usr.user_nm
    from dbo.HTT_REC_MST as recm
    left outer join dbo.HTT_REC_DTL as recd on recm.REC_MST_CD = recd.REC_MST_CD
    left outer join dbo.HTT_REC_CTN_DTL as ctndtl on recd.REC_DTL_CD = ctndtl.REC_DTL_CD
    left outer join dbo.HTT_CTN_MST as ctn on ctndtl.CTN_CD = ctn.CTN_CD
    left outer join dbo.HTT_ITEM as item on ctn.ITEM_CD = item.ITEM_CD
    left outer join dbo.HTT_ZONE_MST as zone on ctn.ZONE_MST_CD = zone.ZONE_MST_CD
    left outer join dbo.HTT_PROJECT_COUNTRY as pcntry on ctn.PRJ_CTRY_CD = pcntry.PRJ_CTRY_CD
    left outer join dbo.HTT_COUNTRY as cntry on pcntry.CTRY_CD = cntry.CTRY_CD
    left outer join dbo.HTT_USER as usr on recm.CRT_BY_USER_CD = usr.USER_CD
    left outer join dbo.HTT_LOC_MST as loc on ctn.LOC_MST_CD = loc.LOC_MST_CD
    left outer join dbo.HTT_BIN_MST as bin on ctn.BIN_MST_CD = bin.BIN_MST_CD
    left outer join dbo.HTT_DONOR_CONTRACT as dc on ctn.DONOR_CON_CD = dc.DONOR_CON_CD

  • Mega post!! Thanks a lot :)))

  • hi, nice post you got here. I now know how to write joins in linq to entities. but one thing i'm having problem is this:

    SELECT in1.Instrument#, in1.IndexedInstrumentID, in1.ScannedInstrumentID, in1.Sequence#, in1.Revision#, in1.InstrumentTypeCode, in1.SequenceYear
    FROM InstrumentNumber AS in1 LEFT outer JOIN
    InstrumentNumber AS in2 ON in1.Sequence# = in2.Sequence# AND in1.Revision# in2.Revision#
    WHERE etc...

    did you notice the left outer join? it has 2 conditions Sequence# and Revision#. I'm trying to do this in Linq to entities but everything fails like so:

    var fail =
    ( from insno in LRSEntity.getObject().Entity.InstrumentNumber
    join insno1 in LRSEntity.getObject().Entity.InstrumentNumber on insno.Sequence_ equals insno1.Sequence_
    // && insno.Revision_ equals insno1.Revision_
    select insno

    the && line for some reason fails in syntax check on vs2008. I was wondering if anyone can help me here. all examples I've come up on google just has 1 join condition...

  • RE: There is no "not in" operator in LINQ

    I'm not sure if this is the same as what you meant in your post, but in L2S you can use .Except().
    The following creates a list called addsList from all rows in proposedList that do not exist in unchangedList:

    List addsList = (from p in proposedList select p).Except(from u in unchangedList select u).ToList();

    Nice writeup!! Thanks!

  • plzzzzz help...! i am in big trouble..
    i want to make this query using linq..

    SELECT [t1].[User_id] AS [User_id], [t0].[Resourse_name], [t1].[Status] AS [s]
    FROM [dbo].[LoginResourses] AS [t0]
    LEFT OUTER JOIN [dbo].[LoginResourseMapping] AS [t1] ON ([t0].[Resourse_id]) = [t1].[Resourse_id]
    and [t1].[User_id] = 'E004'

    but m not able get this query with linq, what query i made i am sending you it includes "where" clause but i want "and" condition instead of "where"...

    var q =from lr in db.LoginResourses
    join lrm in db.LoginResourseMappings
    on lr.Resourse_id equals lrm.Resourse_id
    into tempid
    from id in tempid.DefaultIfEmpty()
    id.User_id == DDUser.SelectedValue
    select new
    s = id.Status

    result query is

    SELECT [t1].[User_id] AS [User_id], [t0].[Resourse_name], [t0].[Status], [t1].[Status] AS [s]
    FROM [dbo].[LoginResourses] AS [t0]
    LEFT OUTER JOIN [dbo].[LoginResourseMapping] AS [t1] ON ([t0].[Resourse_id]) = [t1].[Resourse_id]
    "where" [t1].[User_id] = 'E004'

    but i want and not where

  • Thanks so much! I had the relation built between entities and the join wasn't working like I expected even though according to all the other websites I user join properly.
    so when i read:
    "The trick was in the second line:from ch in h.CityHubs" it all made sense.

  • مقال جميل ما شاء الله

  • how can i perform following:

    var query = from p in db.Emp
    select new {p.EmpID, p.EmpName };

    query = query.Join( db.EmpInfo where db.Emp.EmpID equals db.EmpInfo.InfoID);

  • Yes there should realize the reader to RSS my feed to RSS commentary, quite simply

  • Great example! I've spent the last hour looking at samples and this one is BY FAR the best. Thank you.

  • its good and useful

  • hello 557697cbc43deb8fbe920e52b5c6e94e

Comments have been disabled for this content.