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<TABLE_1>
    join t2 in myTABLE1List
      on t1.ColumnA equals t2.ColumnA
      && 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?

Answer 1

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

Answer 2

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 }

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

You can use LINQ Method Syntax to join on multiple columns. It's an example here,

var query = mTABLE_1.Join( // mTABLE_1 is a List<TABLE_1>
                mTABLE_1, 
                t1 => new
                {
                    ColA = t1.ColumnA,
                    ColB = t1.ColumnB,
                    ColC = t1.ColumnC
                },
                t2 => new
                {
                    ColA = t2.ColumnA,
                    ColB = t2.ColumnB,
                    ColC = t2.ColumnC
                },
                (t1, t2) => new { t1, t2 }).Join(
                mTABLE_1,
                t1t2 => new
                {
                    ColA = t1t2.t2.ColumnA,
                    ColB = t1t2.t2.ColumnB,
                    ColC = t1t2.t2.ColumnC
                },
                t3 => new
                {
                    ColA = t3.ColumnA,
                    ColB = t3.ColumnB,
                    ColC = t3.ColumnC
                },
                (t1t2, t3) => new
                {
                    t1 = t1t2.t1,
                    t2 = t1t2.t2,
                    t3 = t3
                });

Note: The compiler converts query syntax into method syntax at compile time.

Answer 7

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}

Answer 8

The A and B alias must line up with Hrco and Position code from e table and t table - Hrco and Position Code combinations in the "equal new" filter. This will save you time because I kept getting "Not in scope on the left side" compile errors because I thought the filter was e.Hrco, t.Hrco pairing for the filter.

select * from table1 e
   join table2 t on
      e.Hrco=t.Hrco and e.PositionCode=t.PositionCode

   Notice the association of the columns to the labels A and B. The As equal and the Bs equal filter.

   IList<MyView> list = await (from e in _dbContext.table1
                                              join t in _dbContext.table2
                                              on new { A= e.Hrco, B= e.PositionCode }
                                              equals new {A= t.Hrco,B=t.PositionCode }
                                              where e.XMan == employeeNumber

                                              select new MyView
                                                                  { 
                                                                        
         Employee=e.Employee,
         LastName=e.LastName,
         FirstName=e.FirstName,
         Title=t.JobTitle
         ).ToListAsync<MyView>();