Left Join query in Entity Framework Core

Priya
Priya
Member
64 Points
27 Posts

I am trying to use left join in Linq using ASP.NET Core and EntityFramework Core.

I have following situation with two tables:

Customer (id, firstname, lastname)
CustomerDetails (id, CustomerId, DetailText)

In SQL it works fine:

SELECT p.id, p.Firstname, p.Lastname, d.DetailText FROM Customer p
LEFT JOIN CustomerDetails d on d.id = p.Id
ORDER BY p.id ASC

results as expected:

# | id | firstname | lastname | detailtext
1 | 1 | First1 | Last1 | details1
2 | 2 | First2 | Last2 | details2
3 | 3 | First3 | Last3 | NULL

But using linq how I can achieve this

var result = from customer in _dbContext.Customer
  join detail in _dbContext.CustomerDetails on customer.Id equals detail.CustomerId
  select new
  {
    id = customer.Id,
    firstname = customer.Firstname,
    lastname = customer.Lastname,
    detailText = detail.DetailText
  };

It's working as inner join. I want here left outer join.

Views: 217
Total Answered: 2
Total Marked As Answer: 0
Posted On: 20-Oct-2019 01:37

Share:   fb twitter linkedin
Answers
Smith
Smith
Participant
418 Points
119 Posts
         

Use following linq query:

var result = from customer in _dbContext.Customer
join detail in _dbContext.CustomerDetails on customer.Id equals detail.CustomerId into Details
from m in Details.DefaultIfEmpty()
   select new
    {
        id = customer.Id,
        firstname = customer.Firstname,
        lastname = customer.Lastname,
        detailText = m != null ? m.DetailText : ""
    };

 

Posted On: 25-Oct-2019 09:41
Rahul Maurya
Rahul M...
Moderator
1174 Points
212 Posts
         

Lambda expression version of left outer join=> GroupJoin does the left outer join, the SelectMany part is only needed depending on what you want to select:

var result = _dbContext.Customer.GroupJoin(
          _dbContext.CustomerDetails,
          customer => customer.Id,
          detail => detail.CustomerId,
          (x,y) => new { Customer = x, Details = y })
    .SelectMany(
          x => x.Details.DefaultIfEmpty(),
          (x,y) => new { customer=x.Customer, Details=y});

If we want to get only those record in which CustomerDetail is null then use where clause x.Details==null 

var result = _dbContext.Customer.GroupJoin(
          _dbContext.CustomerDetails,
          customer => customer.Id,
          detail => detail.CustomerId,
          (x,y) => new { Customer = x, Details = y })
    .SelectMany(
          x => x.Details.DefaultIfEmpty(),
          (x,y) => new { customer=x.Customer, Details=y})
    .where(x.Detail==null);
Posted On: 08-Nov-2019 20:24
 Log In to Chat