Inner-Join query in Entity Framework Core

Views: 7502
Comments: 0
Like/Unlike: 0
Posted On: 11-Apr-2020 23:46 

Share:   fb twitter linkedin
Smith
2890 Points
78 Posts

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
 Log In to Chat