LINQ to Entities – Join Queries - Salim Fayad
Wednesday, July 9, 2008 12:08 AM The Eagle

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:

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 Filed under: , , , ,

Comments

# re: LINQ to Entities – Join Queries

Wednesday, August 13, 2008 11:17 PM by Murali

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!

# re: LINQ to Entities – Join Queries

Thursday, September 18, 2008 12:49 PM by Steiger

Perfect post! Saved a lot of time too!

# re: LINQ to Entities – Join Queries

Thursday, September 25, 2008 5:13 AM by The Eagle

Hi Nalaka,

"Navigation Properties", this is a Entities Data Model

# re: LINQ to Entities – Join Queries

Friday, November 14, 2008 4:03 AM by Yuvaraj Ilangovan

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.

# re: LINQ to Entities – Join Queries

Thursday, January 8, 2009 3:42 PM by dsoltesz

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

# re: LINQ to Entities – Join Queries

Friday, January 9, 2009 6:36 AM by The Eagle

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

# re: LINQ to Entities – Join Queries

Monday, February 23, 2009 5:41 AM by Biju Raju

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

# re: LINQ to Entities – Join Queries

Monday, March 30, 2009 5:51 AM by oerbyy

Mega post!! Thanks a lot :)))

# re: LINQ to Entities – Join Queries

Wednesday, October 21, 2009 10:27 PM by k3nn

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: LINQ to Entities – Join Queries

Wednesday, October 28, 2009 1:49 PM by samw

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<Project_Material_Change> addsList = (from p in proposedList select p).Except(from u in unchangedList select u).ToList();

Nice writeup!! Thanks!

# re: LINQ to Entities – Join Queries

Monday, January 25, 2010 6:20 AM by Ankit

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()

                  where

                      id.User_id == DDUser.SelectedValue

                  select new

                  {

                      id.User_id,

                      lr.Resourse_name,

                      lr.Status,

                      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

# re: LINQ to Entities – Join Queries

Thursday, March 11, 2010 5:23 PM by Kris

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.

Thanks!

# re: LINQ to Entities – Join Queries

Saturday, August 14, 2010 7:59 AM by مروان

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

مشكوووووووووووووووووور

# re: LINQ to Entities – Join Queries

Wednesday, January 19, 2011 3:00 PM by shiv

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);

# re: LINQ to Entities – Join Queries

Saturday, September 3, 2011 6:06 PM by hooher tod

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

# re: LINQ to Entities – Join Queries

Wednesday, September 14, 2011 7:00 PM by Dave

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

# re: LINQ to Entities – Join Queries

Tuesday, October 2, 2012 6:16 AM by dharma reddy

its good and useful

# re: LINQ to Entities – Join Queries

Tuesday, February 19, 2013 11:00 PM by <a href="http://www.spydig.com/">remove spyware</a>

hello 557697cbc43deb8fbe920e52b5c6e94e