Convert Join to GROUP JOIN while using Lambda expression in linq c#

Question

I am trying to change a join to LEFT outer join but getting all sorts of conversion errors. Below is my current join, can anybody provide any suggestion on how to do this without changing the actual logic of this join?

 BRAND_NAME_MAP_MASTER objBrandNameMap = DB.PFC_MAP_MASTERs.Join(
          DB.BRAND_NAME_MAPs,
          a => a.BRAND_NAME_MAP_ID, b => b.BRAND_NAME_MAP_ID,
    (a, b) => new { a, b }).Where(x => x.a.BRAND_NAME_MAP_ID == BrandNameMapID && 
        x.b.BRAND_NAME_MAP_ID == BrandNameMapID).Select(x => x.a).FirstOrDefault();

possible duplicate of (https://stackoverflow.com/questions/584820/how-do-you-perform-a-left-outer-join-using-linq-extension-methods) — Ryan Wilson, May 21 '18 at 20:00
What conversion errors, can you please share those errors with us? — CodingYoshi, May 21 '18 at 20:01
What do you hope to accomplish? WIthout changing the logic, doesn't that imply the same answer should be returned? If you left join a to b and keep only a, then isn't that the same as just a? — NetMage, May 21 '18 at 21:32

score 1 · Answer 1 · answered May 21 '18 at 21:39

Since you are only keeping a in the end changing to a left join implies not caring whether b matches or not, so the result is just:

BRAND_NAME_MAP_MASTER objBrandNameMap = DB.PFC_MAP_MASTERs
                                          .Where(a => a.BRAND_NAME_MAP_ID == BrandNameMapID)
                                          .FirstOrDefault();

score 0 · Accepted Answer · answered May 22 '18 at 10:41

You should use NetMage's answer if thats your entire query. But, if you still need to do a left outer join then use this:

BRAND_NAME_MAP_MASTER objBrandNameMap = DB.PFC_MAP_MASTERs.GroupJoin(
          DB.BRAND_NAME_MAPs,
          a => a.BRAND_NAME_MAP_ID, 
          b => b.BRAND_NAME_MAP_ID,
          (a, b) => new { a, b })
          .SelectMany(
          x => x.b.DefaultIfEmpty(),
          (x,y) => new { x.a, y})
.Where(x => x.a.BRAND_NAME_MAP_ID == BrandNameMapID)
.Select(x => x.a).FirstOrDefault();

This works perfectly. I wanted a left join on the current query. Thank you. — Programmermid, May 22 '18 at 13:43

score 0 · Answer 3 · answered May 22 '18 at 10:41

Your intent doesn't make sense since you are only fetching a. If you only want a then there is no need of left join. Though i have written the query which will give you left join result. You can use it the way you want.

BRAND_NAME_MAP_MASTER objBrandNameMap = 
    (from Master in DB.PFC_MAP_MASTERs.Where(x=>x.BRAND_NAME_MAP_ID ==BrandNameMapID)
     join Map in DB.BRAND_NAME_MAPs.Where(z=>z.BRAND_NAME_MAP_ID ==BrandNameMapID)
     on Master.BRAND_NAME_MAP_ID equals Map.BRAND_NAME_MAP_ID
     into result
     from res in result.DefaultIfEmpty()
     select new {Master,res}).ToLisT()

Hope it helps.

Convert Join to GROUP JOIN while using Lambda expression in linq c#

3 Answers3