C# Lambda Left Join - can't get it working

Question

I am trying to convert the following SQL:

select * from business
left outer join permissions on permissions.permid = busid
and anotherid = 17

into a C# lambda expression, then convert it to an enumerable and do another filter on it. I have tried the following code:

       IEnumerable<DTO_business> business= db.business
            .Join(db.permissions,
                bus => bus.busid,
                perm => perm.perm_busid,                  
                (bus, perm) => new { bus, perm })
            .Where(e => e.perm.anotherid == 17).DefaultIfEmpty()
            .AsEnumerable()
            .Where(User.IsInRole("Administrator")
            .Select(bus =>
                          new DTO_business()
                                              {
                                                  BusinessID = bus.bus.busid.Convert(),
                                                  BusinessName = bus.bus.busname.Convert()
                                              });

But I belive it's not working as the where is outside the join. I'm not sure how to actually get that where within the join and then run DefaultIfEmpty() on the join which should give me the left join.

EDIT: The bit I cannot get working is: and anotherid = 17. If I put it in a .Where in linq it filters it completely and is not part of the left join.

score 1 · Answer 1 · answered Apr 22 '15 at 10:02

1

For me LINQ join clause are always simple with query syntax. If you prefer it, this is how you can do:-

var result = from permission in db.permissions
             join business in db.business
             on permission.permid equals business.busid into b
             from bus in b.DefaultIfEmpty()
             where permission.anotherid  == 17
             select new DTO_business()
                            {
                                 BusinessID = bus != null ? bus.busid : 0,
                                 BusinessName = bus != null ? bus.busname : String.Empty
                            };

answered Apr 22 '15 at 10:02

Rahul Singh

21,585
6
41
56

Not sure I can use query syntax as I need to convert to an enumerable (get the results in memory) as I perform C# (non SQL) functions on each item that comes back. – user3129594 Apr 22 '15 at 10:25
@user3129594 - A query syntax ultimately converts into lambda and produces the same output. I don't find any reason why you cannot use a query syntax here. – Rahul Singh Apr 22 '15 at 10:29
Yeah, but to do this wouldn't I need to create another DTO (which accepts all the results from bus and permissions), then turn the iqueryable into an ienumeral, then do the conversions in the method? Or am I missing something? – user3129594 Apr 22 '15 at 10:49
@user3129594 - Nope, it will also return `IEnumerable` – Rahul Singh Apr 22 '15 at 10:50
Just tried it, it still doesn't work as the C# functions won't go (as it tries to go in the query, which isn't possible as they aren't SQL based) – user3129594 Apr 22 '15 at 11:11

score 1 · Answer 2 · edited May 23 '17 at 12:06

1

Query syntax would be easier imho but you could do it using GroupJoin like this:

 var  business= db.business 
                  .GroupJoin(
                        db.permissions,
                        bus => bus.busid,
                        perm => perm.perm_busid,                  
                        (bus, perm) => new { bus, perm })
                  .SelectMany(
                      z => z.permissions.DefaultIfEmpty(),
                      (x, y) => new { Business = x.Bus, Permission = y })
                  .Where(z => z.Permission.anotherid == 17)
                  .Select(s => new DTO_business
                  {
                      BusinessID = s.Business.busid.Convert(),
                      BusinessName = s.Business.busname.Convert()
                  });

Reference: LEFT OUTER JOIN in LINQ

edited May 23 '17 at 12:06

Community

1
1

answered Apr 22 '15 at 10:05

Marco

22,856
9
75
124

Thanks for the fast answer! The problem with this is that it is still doing a WHERE, which is filtering out all the results. The filter needs to be on the LEFT OUTER JOIN, so if the right table (permissions) returns nothing, the left table (business) still shows. Using where still filters all the results :-( – user3129594 Apr 22 '15 at 10:24
Logically, if I could put the .Where above the SelectMany, that should work (as it would do the filter, which would eliminate the permission rows, but then the SelectMany will return them with null values. The problem with this is having the Where above the SelectMany doesn't allow me to filter by perm! – user3129594 Apr 22 '15 at 11:22

score 0 · Answer 3 · answered Apr 22 '15 at 15:56

You can use composite key in the join condition.

         from p in db.permissions
         join b in db.business
         on new { BusId = b.busid, AnotherId= b.anotherId} 
         equals new { BusId = p.perm_busid, AnotherId = 17 } into all
         from b in all.DefaultIfEmpty()             
         select new DTO_business
                {
                  BusinessID = b.busid, // check for null reference
                  BusinessName = b.busname // check for null reference
                };

You can use composite keys similarly in extension syntax also.

I see in your code you are using Convert in the select function. This is not allowed in Linq that convert to Sql.

C# Lambda Left Join - can't get it working

3 Answers3