left join 2 tables using linq

Question

I have a linq query that I need to have doing left joins instead of inner joins. All of the examples I see show only 1 left join but when I tried it with 2 i couldn't get it right. So how would this change to use 2 left joins?

ruleSets = (from rs in db.RuleSets
                    join brs in db.BatchRuleSets on rs.ID equals brs.RuleSetID
                    join b in db.Batches on brs.BatchID equals b.id
                    where !clientId.HasValue || b.ClientID == clientId.Value
                    where !batchId.HasValue || brs.BatchID == batchId.Value
                    select new
                    {
                        rs.ID,
                        rs.Description,
                        rs.IsActive,
                        rs.CreatedDate,
                        rs.EffectiveDate,
                        rs.ExpirationDate,
                        BatchName = b.FileName,
                        b.ClientID
                    }).ToList().Select(x => new { 
                        x.ID,
                        x.Description,
                        x.IsActive,
                        x.CreatedDate,
                        x.EffectiveDate,
                        x.ExpirationDate,
                        x.BatchName,
                        ClientName = GetClientName(x.ClientID)});

im not getting an error i just want all of the records regardless of having a BatchRuleSet or Batch — Rush Frisby
– Rush Frisby, Commented Jun 23, 2011 at 15:18
Why are you selecting, then calling ToList(), then selecting again? First of all the ToList() isn't really doing anything because the second select is turning it back to an IEnumerable<T> and second of all you could do your whole transform in the first select — Coding Flow
– Coding Flow, Commented Jun 23, 2011 at 15:36
Actually you can't because EF doesn't know how to interpret the GetClientName method. You have to get the data back first before you can call it which is why I do ToList. — Rush Frisby
– Rush Frisby, Commented Jun 23, 2011 at 15:53

Gaurav Agrawal · Accepted Answer · 2011-06-23 15:22:07Z

1

use left join in linq like this....

join t in Web
on websites.WebsiteID equals t.WebsiteID
into wt1
from wt in wt1.DefaultIfEmpty()

after this wt will be use i where conditions and select statement......

by using this concept you can make a left join query in LINQ.....

answered Jun 23, 2011 at 15:22

Gaurav Agrawal

4,44110 gold badges47 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rush Frisby Over a year ago

my question was how do you do 2 of them

Coding Flow Over a year ago

@rushonerok, erm you do the above twice

Rush Frisby · Accepted Answer · 2011-06-23 15:53:54Z

    ruleSets = (from rs in db.RuleSets
                join brs in db.BatchRuleSets on rs.ID equals brs.RuleSetID into j1

                from jbrs in j1.DefaultIfEmpty()
                join b in db.Batches on jbrs.BatchID equals b.id into j2

                from jb in j2.DefaultIfEmpty()
                where !clientId.HasValue || jb.ClientID == clientId.Value
                where !batchId.HasValue || jbrs.BatchID == batchId.Value
                select new
                {
                    rs.ID,
                    rs.Description,
                    rs.IsActive,
                    rs.CreatedDate,
                    rs.EffectiveDate,
                    rs.ExpirationDate,
                    BatchName = jb.FileName,
                    jb.ClientID
                }).ToList().Select(x => new { 
                    x.ID,
                    x.Description,
                    x.IsActive,
                    x.CreatedDate,
                    x.EffectiveDate,
                    x.ExpirationDate,
                    x.BatchName,
                    ClientName = GetClientName(x.ClientID)});

Vlad Bezden · Accepted Answer · 2011-06-23 16:33:06Z

void Main()
{
    var customers = new List<Customer> {new Customer() { CustomerId = 1}, new Customer() { CustomerId = 2}};
    var orders = new List<Order> {new Order() { OrderId = 1, CustomerId = 1}, new Order() { OrderId = 2, CustomerId = 1}};
    var items = new List<Item> {new Item() { ItemId = 1, OrderId = 1}, new Item() { ItemId = 2, OrderId = 1}, new Item() { ItemId = 3, OrderId = 2}};

    var doubleJoin = from customer in customers
    join order in orders on customer.CustomerId equals order.CustomerId
    into customerOrders
    from co in customerOrders.DefaultIfEmpty()
    where (co != null)
    join item in items on co.OrderId equals item.OrderId
    select new {Customer = customer, Orders = co,  Items = item};

    doubleJoin.Dump();
}

public class Customer
{
    public int CustomerId { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    public int CustomerId { get; set;}
}

public class Item
{
    public int ItemId { get; set; }
    public int OrderId { get; set; }
}

Collectives™ on Stack Overflow

left join 2 tables using linq

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related