Left Join query in Entity Framework Core

Priya

1194 Points

33 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.

linq linq-to-entities c#.netcore entity-framework-core

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

Share:

Answers

Smith

2890 Points

78 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

Thanks Smith

- Priya 08-Feb-2020 03:13

Rahul M...

4918 Points

28 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