Linq Join On Multiple Fields

We all know we can do a linq join query like this

from c in Customers
join o in Orders on c.CustomerID equals o.CustomerID

If we have different companies and they all have their own customers, so we need to make join query based on two fields namely companyID and CustomerID

New query could be like

from c in Customers
join o in Orders on new {c.CompanyID, c.CustomerID} equals new {o.CompanyID, o.CustomerID}

This is using anonymous type to construct complicated equal conditions for linq join.

A couple of points here:
1. The above example only works if these two fields in two tables having same name, same type
2. If column names are different we just need to give field a name in anonymous type
3. If type is different, very likely, int? in one table and int in another, you might get a compile error message like this:

The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to ‘GroupJoin’.

All we need to do is just typecast the int? to int.

So finally the more general example should be like this

from c in Customers
join o in Orders on new { CompanyID= c.CompanyID, CustomerID=c.CustomerID} 
equals new { CompanyID=(int)o.CustomerCompanyID, CustomerID= o.CustomerID}

Happy coding


This entry was posted on Saturday, October 9th, 2010 at 3:10 am and is filed under ASP.NET. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

3 Responses to “Linq Join On Multiple Fields”

  1. juan says:

    Thanks for the tip! saved my day!

  2. Amit says:

    Nice tips thanks a lot

  3. rajkumar says:

    thank you so much

  4. Mateusz says:

    Really great!!! helped me a lot 🙂

    Thanks and keep up the good work 🙂

Leave a Reply