CODEWITHSUNDEEP
c#mysqllinqjoin
marijanluvlee
marijanluvlee

Reputation: 15

How to get a Left Join on Linq

I have this query with Join. This gives me an inner join. What I want to accomplish is a left join. I tried using DefaultIfEmpty() but I couldn't get it to work. Perhaps I am putting it on the wrong part of the query.

Can anyone point me out to the right way of using DefaultIfEmpty()?

Below is my current query:

var AppList = (de.ComputerUserApplication)
                    .Where(CUA => CUA.EmployeeID == employeeID)
                    .DefaultIfEmpty()
                    .Join(de.ApplicationTypeMasters,
                        CUA => CUA.RecordType,
                        ATM => ATM.Code,
                        (CUA, ATM) => new ApplicationModel
                        {
                            ApplicationNo = CUA.ApplicationNo,
                            ApplicationCode = CUA.RecordType,
                            ApplicationTypeCode = "",
                            ApplicationName = ATM.Title + " - " + CUA.Description,
                            Status = CUA.Status
                        });

Also, I'm not quite sure if I have the correct query. if you may, below is my original query:

select Cua_ApplicationNo, Cua_Type_Rec, ATM_ApplicationTitle, Cua_Status  from ComputerUserApplication
left join ApplicationTypeMaster
    on Cua_Type_Rec = ATM_ApplicationCode
        where Cua_EmployeeID = 'someID'

Upvotes: 0

Views: 221

Answers (2)

Mark Brackett
Mark Brackett

Reputation: 85645

You need to use GroupJoin for an outer join; this will work assuming there's 0 or 1 matching ApplicationTypeMaster rows; if more, then you'd need to do a DefaultIfEmpty followed by SelectMany. 

de.ComputerUserApplication
  .Where(x => x.EmployeeID == employeeID)
  .GroupJoin(
      de.ApplicationTypeMasters, 
      CUA => CUA.RecordType, 
      ATM => ATM.Code, 
      (CUA, ATM) => new ApplicationModel
      {
          ApplicationNo = CUA.ApplicationNo,
          ApplicationCode = CUA.RecordType,
          ApplicationTypeCode = "",
          ApplicationName = ATM.SingleOrDefault()?.Title + " - " + CUA.Description,
          Status = CUA.Status
      }
  );

If you don't know how many matching rows there are, then SelectMany will give you the equivalent results as SQL:

de.ComputerUserApplication
  .Where(x => x.EmployeeID == employeeID)
  .GroupJoin(
      de.ApplicationTypeMasters, 
      CUA => CUA.RecordType, 
      ATM => ATM.Code, 
      (x, y) => new { CUA = x, ATMs = y.DefaultIfEmpty() }
  ).SelectMany(x => x.ATMs.Select(ATM => new ApplicationModel
      {
         ApplicationNo = x.CUA.ApplicationNo,
         ApplicationCode = x.CUA.RecordType,
         ApplicationTypeCode = "",
         ApplicationName = ATM?.Title + " - " + x.CUA.Description,
         Status = x.CUA.Status
      }
  );

As an aside, this is one of the few times I prefer the query syntax (which does SelectMany without all the noise):

  from CUA in de.ComputerUserApplication
  join x in de.ApplicationTypeMasters on CUA.RecordType equals x.Code into g
  from ATM in g.DefaultIfEmpty()
  select new ApplicationModel()
  {
     ApplicationNo = CUA.ApplicationNo,
     ApplicationCode = CUA.RecordType,
     ApplicationTypeCode = "",
     ApplicationName = ATM?.Title + " - " + CUA.Description,
     Status = CUA.Status
   };

Upvotes: 2

gmcoding
gmcoding

Reputation: 239

Using DefaultIfEmpty() you can get left outer join result. And taking care of null.

Upvotes: -1

Related Questions