LINQ to SQL Multiple joins ON multiple Columns Is this possible

Question

Given   A table named TABLE 1 with the following columns    ID ColumnA ColumnB ColumnC   I have SQL query where TABLE 1 joins on itself twice based off of ColumnA  ColumnB  ColumnC  The query might look something like this   Select t1 ID  t2 ID  t3 ID   From TABLE 1 t1   Left Join TABLE 1 t2 On        t1 ColumnA   t2 ColumnA    And t1 ColumnB   t2 ColumnB    And t1 ColumnC   t2 ColumnC   Left Join TABLE 1 t3 On        t2 ColumnA   t3 ColumnA    And t2 ColumnB   t3 ColumnB    And t2 ColumnC   t3 ColumnC     and query continues on etc    Problem   I need that Query to be rewritten in LINQ  I ve tried taking a stab at it   var query       from t1 in myTABLE1List    List lt TABLE 1 gt      join t2 in myTABLE1List       on t1 ColumnA equals t2 ColumnA        amp  amp  t1 ColumnB equals t2 ColumnA            and at this point intellisense is making it very obvious        I am doing something wrong      How do I write my query in LINQ  What am I doing wrong

User · Accepted Answer

Joining on multiple columns in Linq to SQL is a little different.

var query =
    from t1 in myTABLE1List // List<TABLE_1>
    join t2 in myTABLE1List
      on new { t1.ColumnA, t1.ColumnB } equals new { t2.ColumnA, t2.ColumnB }
    ...

You have to take advantage of anonymous types and compose a type for the multiple columns you wish to compare against.

This seems confusing at first but once you get acquainted with the way the SQL is composed from the expressions it will make a lot more sense, under the covers this will generate the type of join you are looking for.

EDIT Adding example for second join based on comment.

var query =
    from t1 in myTABLE1List // List<TABLE_1>
    join t2 in myTABLE1List
      on new { A = t1.ColumnA, B = t1.ColumnB } equals new { A = t2.ColumnA, B = t2.ColumnB }
    join t3 in myTABLE1List
      on new { A = t2.ColumnA, B =  t2.ColumnB } equals new { A = t3.ColumnA, B = t3.ColumnB }
    ...

User · Answer

You can also join if the number of columns are not same in both tables and can map static value to table column  from t1 in Table1  join t2 in Table2  on new  X   t1 Column1  Y   0   on new  X   t2 Column1  Y   t2 Column2   select new  t1  t2

User · Answer

In my opinion  this is the simplest way to join two tables with multiple fields   from a in Table1 join b in Table2            on  a Field1 ToString       amp     a Field2 ToString                equals   b Field1 ToString       amp     b Field2 ToString           select a

User · Answer

Title Authors is a look up two things join at a time project results and continue chaining          DataClasses1DataContext db   new DataClasses1DataContext            var queryresults   from a in db Authors                                                               join ba in db Title Authors                                                on a Au ID equals ba Au ID into idAuthor                     from c in idAuthor                     join t in db Titles                       on c ISBN equals t ISBN                      select new   Author   a Author1 Title  t Title1             foreach  var item in queryresults                        MessageBox Show item Author               MessageBox Show item Title               return

User · Answer

In LINQ2SQL you seldom need to join explicitly when using inner joins   If you have proper foreign key relationships in your database you will automatically get a relation in the LINQ designer  if not you can create a relation manually in the designer  although you should really have proper relations in your database     Then you can just access related tables with the  dot-notation   var q   from child in context Childs         where child Parent col2    4         select new                       childCol1   child col1              parentCol1   child Parent col1               will generate the query  SELECT  t0   col1  AS  childCol1    t1   col1  AS  parentCol1  FROM  dbo   Child  AS  t0  INNER JOIN  dbo   Parent  AS  t1  ON   t1   col1     t0   col1   AND   t1   col2     t0   col2   WHERE  t1   col2     p0 --  p0  Input Int  Size   -1  Prec   0  Scale   0   4  -- Context  SqlProvider Sql2008  Model  AttributedMetaModel Build  4 0 30319 1   In my opinion this is much more readable and lets you concentrate on your special conditions and not the actual mechanics of the join   Edit This is of course only applicable when you want to join in the line with our database model  If you want to join  outside the model  you need to resort to manual joins as in the answer from Quintin Robinson

User · Answer

You can write your query like this   var query   from t1 in myTABLE1List    List lt TABLE 1 gt              join t2 in myTABLE1List                on t1 ColumnA equals t2 ColumnA                and t1 ColumnB equals t2 ColumnA   If you want to compare your column with multiple columns

User · Answer

U can also use    var query       from t1 in myTABLE1List      join t2 in myTABLE1List       on new   ColA t1 ColumnA  ColB t1 ColumnB   equals new   ColA t2 ColumnA  ColB t2 ColumnB       join t3 in myTABLE1List       on new  ColC t2 ColumnA  ColD t2 ColumnB   equals new   ColC t3 ColumnA  ColD t3 ColumnB

User · Answer

I would like to give another example in which multiple  3  joins are used    DataClasses1DataContext ctx   new DataClasses1DataContext             var Owners   ctx OwnerMasters          var Category   ctx CategoryMasters          var Status   ctx StatusMasters          var Tasks   ctx TaskMasters           var xyz   from t in Tasks                   join c in Category                   on t TaskCategory equals c CategoryID                   join s in Status                   on t TaskStatus equals s StatusID                   join o in Owners                   on t TaskOwner equals o OwnerID                   select new                                           t TaskID                        t TaskShortDescription                        c CategoryName                        s StatusName                        o OwnerName

[c#] LINQ to SQL: Multiple joins ON multiple Columns. Is this possible?

Examples related to c#

Examples related to linq-to-sql

Examples related to left-join

Examples related to multiple-columns