Skip to content

Multiple Join Fields in LINQ

September 3, 2008

Say you want to write a join in LINQ but you need to do it on more than one field.  You’d expect you’d just say something like,

on a.ID equals b.ID && a.EmployeeID equals b.EmployeeID

or something like that right?  right?  Wrong.

 

If you want to do a multi-field join you need to use an anonymous type like so:

using (SFA2DBDataContext db = new SFA2DBDataContext())
            {
                var foo = from a in db.KitItems
                          join b in db.PackageKits on a.KitID equals b.KitID
                          join c in db.BundlePackages on b.PackageID equals c.PackageID
                          join d in db.BuilderOverrideBundlePackageKitItems on 
                          new { doo=a.ID, goo=b.ID }
                          equals new { doo=d.KitItemID, goo=d.PackageKitID }
                          select new
                          {
                              a.ItemID,a.KitID,b.PackageID,c.BundleID,
                              Commish = d.BuilderCommission ?? a.Item.BuilderCommissionPercent
                          };
            }

Notice that the fields must not only be the same type but they must also be named the same way. 

I found the solution in the MS forums posted by the grand pooba of C# himself, Anders Helsberg. 

Advertisements
3 Comments leave one →
  1. July 10, 2009 8:56 pm

    Wow. I spent a lot of time trying to implement this sort of joins. Thanks a mill.

  2. shityoucantremember permalink
    September 10, 2009 3:28 pm

    Awesome, exactly what I needed!

  3. cihan celik permalink
    February 13, 2011 1:41 pm

    SELECT dbo.unvan.unvanId, dbo.unvan.unvanKod, dbo.unvanDil.unvanAd
    FROM dbo.unvan LEFT OUTER JOIN
    dbo.unvanDil ON @dilId = dbo.unvanDil.dilId AND dbo.unvan.unvanId = dbo.unvanDil.unvanId

    sql output
    unvanId unvanKod unvanAd
    1 001 Tekniker
    2 002 Teknisyen
    3 003 NULL

    What is the output of the LINQ query?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: