Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
228 lines (189 sloc) 4.27 KB

Joins

LINQ To DB supports all standard SQL join types: INNER, LEFT, FULL, RIGHT, CROSS JOIN. For join types that do not have a direct LINQ equivalent, such as a left join, we have a few examples further down of methods that are provided to cleanly write such joins.

INNER JOIN

Join operator on single column

var query =
    from c in db.Category
    join p in db.Product on c.CategoryID equals p.CategoryID
    where !p.Discontinued
    select c;

Using "Where" condition

var query =
    from c in db.Category
    from p in db.Product.Where(pr => pr.CategoryID == c.CategoryID)
    where !p.Discontinued
    select c;

Using "InnerJoin" function

var query =
    from c in db.Category
    from p in db.Product.InnerJoin(pr => pr.CategoryID == c.CategoryID)
    where !p.Discontinued
    select c;

Resulting SQL

SELECT
    [c].[CategoryID],
    [c].[CategoryName],
    [c].[Description],
    [c].[Picture]
FROM
    [Categories] [c]
        INNER JOIN [Products] [p] ON [c].[CategoryID] = [p].[CategoryID]
WHERE
    [p].[Discontinued] <> 1

Join operator on multiple columns

var query =
    from p in db.Product
    from o in db.Order
    join d in db.OrderDetail
        on     new { p.ProductID, o.OrderID }
        equals new { d.ProductID, d.OrderID }
    where !p.Discontinued
    select new
    {
        p.ProductID,
        o.OrderID,
    };

Resulting SQL

SELECT
    [t3].[ProductID] as [ProductID1],
    [t3].[OrderID] as [OrderID1]
FROM
    (
        SELECT
            [t1].[ProductID],
            [t2].[OrderID],
            [t1].[Discontinued]
        FROM
            [Products] [t1],
            [Orders] [t2]
    ) [t3]
        INNER JOIN [Order Details] [d] ON [t3].[ProductID] = [d].[ProductID] AND [t3].[OrderID] = [d].[OrderID]
WHERE
    [t3].[Discontinued] <> 1

LEFT JOIN

Join operator on single column

var query =
    from c in db.Category
    join p in db.Product on c.CategoryID equals p.CategoryID into lj
    from lp in lj.DefaultIfEmpty()
    where !lp.Discontinued
    select c;

Using "Where" condition

var query =
    from c in db.Category
    from lp in db.Product.Where(p => p.CategoryID == c.CategoryID).DefaultIfEmpty()
    where !lp.Discontinued
    select c;

Using "LeftJoin" function

var query =
    from c in db.Category
    from p in db.Product.LeftJoin(pr => pr.CategoryID == c.CategoryID)
    where !p.Discontinued
    select c;

Resulting SQL

SELECT
    [c1].[CategoryID],
    [c1].[CategoryName],
    [c1].[Description],
    [c1].[Picture]
FROM
    [Categories] [c1]
        LEFT JOIN [Products] [lj] ON [c1].[CategoryID] = [lj].[CategoryID]
WHERE
    1 <> [lj].[Discontinued]

RIGHT JOIN

Using "RightJoin" function

var query =
    from c in db.Category
    from p in db.Product.RightJoin(pr => pr.CategoryID == c.CategoryID)
    where !p.Discontinued
    select c;

Resulting SQL

SELECT
    [t2].[CategoryID],
    [t2].[CategoryName],
    [t2].[Description],
    [t2].[Picture]
FROM
    [Categories] [t2]
        RIGHT JOIN [Products] [t1] ON [t1].[CategoryID] = [t2].[CategoryID]
WHERE
    1 <> [t1].[Discontinued]

FULL JOIN

Using "FullJoin" function

var query =
    from c in db.Category
    from p in db.Product.FullJoin(pr => pr.CategoryID == c.CategoryID)
    where !p.Discontinued
    select c;

Resulting SQL

SELECT
    [t2].[CategoryID],
    [t2].[CategoryName],
    [t2].[Description],
    [t2].[Picture]
FROM
    [Categories] [t2]
        FULL JOIN [Products] [t1] ON [t1].[CategoryID] = [t2].[CategoryID]
WHERE
    1 <> [t1].[Discontinued]

CROSS JOIN

Using SelectMany

var query =
    from c in db.Category
    from p in db.Product
    where !p.Discontinued
    select new {c, p};

Resulting SQL

SELECT
    [t1].[CategoryID],
    [t1].[CategoryName],
    [t1].[Description],
    [t1].[Picture],
    [t2].[ProductID],
    [t2].[ProductName],
    [t2].[SupplierID],
    [t2].[CategoryID] as [CategoryID1],
    [t2].[QuantityPerUnit],
    [t2].[UnitPrice],
    [t2].[UnitsInStock],
    [t2].[UnitsOnOrder],
    [t2].[ReorderLevel],
    [t2].[Discontinued]
FROM
    [Categories] [t1],
    [Products] [t2]
WHERE
    1 <> [t2].[Discontinued]
You can’t perform that action at this time.