Carpathea Carpathea - 9 months ago 62
C# Question

Structuring two conditional left outer joins into one query using LINQ, C#

Currently, I am executing two queries based upon whether

w.Type
is either 1 or 2. If
w.Type
is 1 we perform a join to the Issues table and if the Type is 2 we join to the TSubs table. I am trying to merge these queries into one.

var productIdOne = (from w in listAbandonedCarts
join i in Issues on w.ProductId equals i.Id
where w.Type == 1
select new { i.Title.Name }).ToList();

var productIdTwo = (from w in listAbandonedCarts
join ts in TSubs on w.ProductId equals ts.Id
where w.Type == 2
select new { ts.Title.Name }).ToList();


I am considering using two left outer joins based upon this SQL psuedo-code

SELECT*
FROM P_carts pc
LEFT OUTER tSubs ts on ts.id = pc.productid and pc.Type = 2
LEFT OUTER issues i on i.id = pc.productid and pc.Type = 1


So far i have some linq pseudo coded but i'm struggling to get the syntax correct for the two conditional joins

var listProducts = (from w in listAbandonedCarts
join i in Issues on w.ProductId equals i.Id into iN && w.ProductId == 1
from i in iN.DefaultIfEmpty()
join into ts in TSubs.....


The problem I'm struggling with is that this isn't a double left outer its two separate joins. My current error is that i cannot have
w.ProductId
after the equals because I'm out of scope of w at this point and can't figure out how to structure the linq statement. Any help would be much appreciated, thanks!

Answer Source

Give this a shot:

        var productIds = (
            from w in listAbandonedCarts
            from i in Issues.Where(issue => issue.Id == w.ProductId && w.Type == 1).DefaultIfEmpty()
            from t in TSubs.Where(tsub => tsub.Id == w.ProductId && w.Type == 2).DefaultIfEmpty()
            select new {Name = (w.Type == 1 ? i.Title.Name : t.Title.Name)})
            .ToList();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download