none
Join on multiple fields RRS feed

  • Question

  • What is the L2S syntax for joining on muliple fields like in SQL:

     

    Code Snippet

    select *

    from table1 t1 inner join table2 t2 on t1.column1 = t2.columnA and t1.coulmn2 = t2.columnB

     

     

     

    this doesn't work

     

    Code Snippet

    from t1 in table1

    join t2 in table2 on t1.column1 equals t2.columnA && t1.coulmn2 equals t2.columnB

     

     

     

    Thursday, October 30, 2008 4:10 PM

Answers

  • You may use anonyous type and join on it

    eg: something like

     

    var y = from t1 in db.table1

    join t2 in db.table2 on new { col1 = t1.column1 , col2= t1.coulmn2 }

    equals new { col1 = t2.column1 , col2= t2.coulmn2 }

    select t1;

     

    ~Deepti
    Monday, November 3, 2008 11:12 PM

All replies

  • HI

    Assume we have to table like Customer & Orders & each Customer has many orders

    we want to make query to join this tables to return each customer with its orders like :

     

    Code Snippet

    var result = (from c in db.Customers

    from o in db.Orders

    where c.Id == o.Customer_Id

    select new

    {

    FirstName = c.FirstName,

    LastName = c.LastName,

    OrderCode = o.Code,

    OrderDare = o.Date

    }).ToList();

     

     

    this is the corresponding query in sql server

    Code Snippet

    SELECT [t0].[FirstName],

    [t0].[LastName],

    [t1].[Code] AS [OrderCode],

    [t1].[Date] AS [OrderDare]

    FROM [dbo].[Customer] AS [t0],

    [dbo].[Order] AS [t1]

    WHERE [t0].[Id] = [t1].[Customer_Id]

     

     

     

    Sunday, November 2, 2008 12:39 PM
    Answerer
  • You may use anonyous type and join on it

    eg: something like

     

    var y = from t1 in db.table1

    join t2 in db.table2 on new { col1 = t1.column1 , col2= t1.coulmn2 }

    equals new { col1 = t2.column1 , col2= t2.coulmn2 }

    select t1;

     

    ~Deepti
    Monday, November 3, 2008 11:12 PM
  • You can also use another 'from' clause.

     

    from t1 in db.Table1

    from t2 in db.Table2 where t1.column1 == t2.column1 && t1.column2 == t2.column2

    select t1;

     

    Tuesday, November 4, 2008 6:12 PM
    Moderator
  • this syntax will produce:

    select t1.*
    from Table1 t1, Table2 t2
    where
    t1.column1 = t2.column1
    and t1.column2 = t2.column2

    which is not the same as a join because while the above is equivalent to inner join it is not equivalent to outer join.


    Wednesday, November 5, 2008 2:45 PM
  • this works. but how can it be changed to use left outer join?
    Wednesday, November 5, 2008 2:58 PM