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.