Inner-Join query in Entity Framework Core
The INNER JOIN term is used in RDMS when we want to fetch records from two or more tables. And it selects records that have matching values in both tables.
For example:
Suppose we have following two tables:
Customer (id, firstname, lastname)
id | firstname | lastname
1 | First1 | Last1
2 | First2 | Last2
3 | First3 | Last3
CustomerDetails (id, CustomerId, DetailText)
id | CustomerId | detailtext
1 | 1 | details1
2 | 2 | details2
And we want fetch records with matching values only from these two tables as:
id | firstname | lastname | detailtext
1 | First1 | Last1 | details1
2 | First2 | Last2 | details2
In SQL we can achieve it using DML as:
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
In this article we will see how to achieve thi in EF by useing:
- Linq Query
- Lambda Expression
Suppost we have DB conext object : _dbContext, then following will be the code in Linq and lamda respectively:
Linq Query
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 = m.DetailText
};
Lambda Expression
var result = _dbContext.Customer.Join(
_dbContext.CustomerDetails,
customer => customer.Id,
detail => detail.CustomerId,
(x,y) => new {
id = x.Id,
firstname = x.Firstname,
lastname = x.Lastname,
detailText = y.DetailText
});
Conclusion
It this article we will see the Linq and Lambda-expression ways to create inner join query in Entity Framework Core.
0 Comments