Welcome to today’s post.
In today’s post I will show how to use LINQ to join database tables using Entity Framework Core.
As you would be aware, in the Entity Framework the method we use to query data from SQL tables is through an existing data context, with the query being an abstraction of SQL queries that we would normally use if we used direct raw SQL commands through the .NET SqlClient library.
I will show two methods for executing joins between tables. The first method is with the LINQ to SQL method with the familiar join operator, and the second is with Entity Framework Core and the Join() extension method.
Joining Tables using the LINQ to SQL Approach
With the LINQ to SQL approach, we can use our knowledge of basic LINQ queries to construct a query that joins data from two or more tables as shown below:
var query =
(from b in _db.Books
join l in db.Loans on b.ID equals l.BookID);
After the above join completes, the output from the query variable shows columns from the two tables, Books and Loans. To selectively return the fields we want from the two joined tables, we use an additional clause after the join.
To project data from the above LINQ to SQL query we can use the select clause as shown:
var query =
(from b in _db.Books
join l in db.Loans on b.ID equals l.BookID
select new { title = b.Title, author = b.Author }
);
We have seen how to join two tables using a LINQ to SQL query, in the next section, I will show how to execute joins using an extension method.
Joining Tables using the Join() Extension Method
With Entity Framework Core we can use the Join() extension method from the System.Linq library.
What the Join method does is to correlate (or join) elements of two sequences using based on matching keys.
Let us look at an example on how to join two tables using Entity Framework Core and the Join() method.
Below is a structure we will store our resulting query result:
public class LoanedBookViewModel
{
public string Title { get; set; }
public string Author { get; set; }
public int YearPublished { get; set; }
public string Genre { get; set; }
public string LoanedBy { get; set; }
}
We wish to join two tables, a Books table, and a Loan table through matching ID and BookID fields in the respective tables. The Entity Framework Core join is shown below:
var loanedBooks = _db.Books
.Join(_db.Loans,
b => b.ID,
l => l.BookID,
(b, l) => new
{
author = b.Author,
title = b.Title,
genre = b.Genre,
yearPublished = b.YearPublished,
loanedBy = l.LoanedBy
});
The above structure will give us a collection of anonymous types that cannot be cast onto the type we wish to return.
We can try projecting using an n-Tuple as follows:
var loanedBooks = _db.Books
.Join(_
db.Loans,
b => b.ID,
l => l.BookID,
(b, l) => new
{
author = b.Author,
title = b.Title,
genre = b.Genre,
yearPublished = b.YearPublished,
loanedBy = l.LoanedBy
}).Select(
b => Tuple.Create(
b.author,
b.title,
b.genre,
b.yearPublished,
b.loanedBy
)
);
Will the above query give us a result that casts to the LoanedBookViewModel structure? I will answer this question in the next section.
Processing n-Tuple Output from the Join
When we viewing the output corresponding to the n-tuples returned from the previous join query and projected data in our debug watch shows our data, it looks valid:
However, each element shows a key name Item1, … ,ItemN, where N = 1..5 for each projected tuple element:
If we try the following casting as follows:
return (List<LoanedBookViewModel>)loanedBooks;
We will get the following error:
System.InvalidCastException: Unable to cast object of type
'Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[System.Tuple`5[System.String,System.String,System.String,System.Int32,System.String]]'
to type 'System.Collections.Generic.List`1[BookLoan.Models. LoanedBookViewModel]'.
Given the query result is a list of tuples, it will not cast onto our structure, we will have to populate our target list by iterating through all tuple elements.
This is done as follows:
public async Task<List<LoanedBookViewModel>> LoanedBooksReport()
{
var loanedBooksList = new List<LoanedBookViewModel>();
var loanedBooks = _db.Books
.Join(_db.Loans,
b => b.ID,
l => l.BookID,
(b, l) => new
{
author = b.Author,
title = b.Title,
genre = b.Genre,
yearPublished = b.YearPublished,
loanedBy = l.LoanedBy
})
.Select(b =>
Tuple.Create(b.author, b.title, b.genre, b.yearPublished, b.loanedBy));
foreach (Tuple<string, string, string, int, string> record in loanedBooks)
{
loanedBooksList.Add(new LoanedBookViewModel()
{
Author = record.Item1,
Title = record.Item2,
Genre = record.Item3,
YearPublished = record.Item4,
LoanedBy = record.Item5
});
}
return loanedBooksList;
}
Using the above iteration, we have a resulting list that shows the correct classes in each list item:
And each list item has the correct property names:
You can see, unlike a single table query returning a list of objects can be casted onto a list of classes, a join returns generic list of an anonymous type.
What I have showed is how to use the join method in an Entity Framework Core LINQ query.
In the next post I will be showing how to use LINQ grouping with Entity Framework Core data contexts.
That is all for today’s post.
I hope you found this post useful and informative.
Andrew Halil is a blogger, author and software developer with expertise of many areas in the information technology industry including full-stack web and native cloud based development, test driven development and Devops.
