Summary
This article mainly introduces a new feature in EF Core 5.0, that is, the Include method supports delegate parameters with filters, and explains a big pitfall of this feature in actual use. I hope readers will avoid pitfalls in future development.
This article uses Dotnet 6.0 and EF Core 7.0.
Code and implementation
Case introduction
This article mainly uses the case of a book and author.
- An author Author has multiple books written by himself
- A book has a publisher Publisher
- An author Author is a system user User
We need to find the top two writers who have written the most books. The writer needs to be over 20 years old and have a French nationality. Their FirstName, LastName, Email, UserName and book information they published before 1900 are required, including book title and publication date.
See the appendix for entity class definitions.
Code implementation based on new features
using var dbContext = new AppDbContext();
var date = new DateTime(1900, 1, 1);
var authors = dbContext.Authors
.AsNoTracking()
.Include(x => x.Books.Where(b => b.Published < date))
.Include(x => x.User)
.Where(x => x.Country == "France" & amp; & amp; x.Age >= 20)
.OrderByDescending(x => x.BooksCount)
.Take(2)
.ToList();
Executing the above code, the generated SQL is as follows:
SELECT [t].[Id], [t].[Age], [t].[BooksCount], [t].[Country], [t].[NickName
], [t].[UserId], [u].[Id], [t0].[Id], [t0].[AuthorId], [t0].[ISBN], [t0].[Name],
[t0].[Published], [t0].[PublisherId], [u].[Created], [u].[Email], [u].[EmailCon
firmed], [u].[FirstName], [u].[LastActivity], [u].[LastName], [u].[UserName]
FROM (
SELECT TOP(@__p_1) [a].[Id], [a].[Age], [a].[BooksCount], [a].[Country
], [a].[NickName], [a].[UserId]
FROM [Authors] AS [a]
WHERE [a].[Country] = N'France' AND [a].[Age] >= 20
ORDER BY [a].[BooksCount] DESC
) AS [t]
INNER JOIN [Users] AS [u] ON [t].[UserId] = [u].[Id]
LEFT JOIN (
SELECT [b].[Id], [b].[AuthorId], [b].[ISBN], [b].[Name], [b].[Published], [b].[PublisherId]
FROM [Books] AS [b]
WHERE [b].[Published] < @__date_0
) AS [t0] ON [t].[Id] = [t0].[AuthorId]
ORDER BY [t].[BooksCount] DESC, [t].[Id], [u].[Id]
Judging from the execution results, Include(x => x.Books.Where(b => b.Published < date)) has taken effect, and LEFT JOIN is indeed filtered first when joining the [Books] table.
Requirements change, filtering features become invalid
The above example returns an Author object, but it actually needs to be converted into an AuthorWeb object and then returned to the front end. We adjust the code as follows:
using var dbContext = new AppDbContext();
var date = new DateTime(1900, 1, 1);
var authors = dbContext.Authors
.AsNoTracking()
.Include(x => x.Books.Where(b => b.Published < date))
.Include(x => x.User)
.Where(x => x.Country == "France" & amp; & amp; x.Age >= 20)
.OrderByDescending(x => x.BooksCount)
.Select(x => new AuthorWeb
{<!-- -->
UserFirstName = x.User.FirstName,
UserLastName = x.User.LastName,
UserEmail = x.User.Email,
UserName = x.User.UserName,
BooksCount = x.BooksCount,
AllBooks = x.Books
.Select(y => new BookWeb
{<!-- -->
Name = y.Name,
Published = y.Published,
}).ToList(),
AuthorAge = x.Age,
AuthorCountry = x.Country,
}).ToList()
.Take(2)
.ToList();
return authors;
Executing the above code, the generated SQL is as follows:
SELECT [u].[FirstName], [u].[LastName], [u].[Email], [u].[UserName], [a].[
BooksCount], [a].[Id], [u].[Id], [b].[Name], [b].[Published], [b].[Id], [a].[Age
], [a].[Country]
FROM [Authors] AS [a]
INNER JOIN [Users] AS [u] ON [a].[UserId] = [u].[Id]
LEFT JOIN [Books] AS [b] ON [a].[Id] = [b].[AuthorId]
WHERE [a].[Country] = N'France' AND [a].[Age] >= 20
ORDER BY [a].[BooksCount] DESC, [a].[Id], [u].[Id]
From the generated SQL, we can see that when left-joining the [Books] table, no filtering is performed, and the filter feature of the Include method fails.
The reason for the failure is that the Include and Select methods with filter parameters cannot coexist. Select will invalidate Include’s filter. In the above example, if we must use the new features of Include, please use the following code:
using var dbContext = new AppDbContext();
var date = new DateTime(1900, 1, 1);
var authors = dbContext.Authors
.AsNoTracking()
.Include(x => x.Books.Where(b => b.Published < date))
.Include(x => x.User)
.Where(x => x.Country == "France" & amp; & amp; x.Age >= 20)
.OrderByDescending(x => x.BooksCount)
.Take(2)
.AsEnumerable().Select(x => new AuthorWeb
{<!-- -->
UserFirstName = x.User.FirstName,
UserLastName = x.User.LastName,
UserEmail = x.User.Email,
UserName = x.User.UserName,
BooksCount = x.BooksCount,
AllBooks = x.Books.Select(y => new BookWeb
{<!-- -->
Name = y.Name,
Published = y.Published,
}).ToList(),
AuthorAge = x.Age,
AuthorCountry = x.Country,
}).ToList();
After obtaining the Author and Book data from the database, convert the sequence into IEnumerable. EF will not generate SQL for LINQ that returns IEnumerable.
So the final generated SQL is as follows:
SELECT [t].[Id], [t].[Age], [t].[BooksCount], [t].[Country], [t].[NickNam
], [t].[UserId], [u].[Id], [t0].[Id], [t0].[AuthorId], [t0].[ISBN], [t0].[Name]
[t0].[Published], [t0].[PublisherId], [u].[Created], [u].[Email], [u].[EmailCo
firmed], [u].[FirstName], [u].[LastActivity], [u].[LastName], [u].[UserName]
FROM (
SELECT TOP(@__p_1) [a].[Id], [a].[Age], [a].[BooksCount], [a].[Countr
], [a].[NickName], [a].[UserId]
FROM [Authors] AS [a]
WHERE [a].[Country] = N'France' AND [a].[Age] >= 20
ORDER BY [a].[BooksCount] DESC
) AS [t]
INNER JOIN [Users] AS [u] ON [t].[UserId] = [u].[Id]
LEFT JOIN (
SELECT [b].[Id], [b].[AuthorId], [b].[ISBN], [b].[Name], [b].[Publish
d], [b].[PublisherId]
FROM [Books] AS [b]
WHERE [b].[Published] < @__date_0
) AS [t0] ON [t].[Id] = [t0].[AuthorId]
ORDER BY [t].[BooksCount] DESC, [t].[Id], [u].[Id]
Judging from the SQL statement, the filter feature of Include takes effect again.
Although the above solution can solve the problem that Include’s filter parameters cannot coexist with Select, it needs to be converted into IEnumerable and then processed, which is more cumbersome.
If we abandon the Include method, use the Select method and pass in the filter parameters, let’s see the effect.
using var dbContext = new AppDbContext();
var date = new DateTime(1900, 1, 1);
var authors = dbContext.Authors
.AsNoTracking()
.Include(x => x.User)
.Where(x => x.Country == "France" & amp; & amp; x.Age >= 20)
.OrderByDescending(x => x.BooksCount)
.Take(2)
.Select(x => new AuthorWeb
{<!-- -->
UserFirstName = x.User.FirstName,
UserLastName = x.User.LastName,
UserEmail = x.User.Email,
UserName = x.User.UserName,
BooksCount = x.BooksCount,
AllBooks = x.Books
.Where(x => x.Published < date) .Select(y => new BookWeb
{<!-- -->
Name = y.Name,
Published = y.Published,
}).ToList(),
AuthorAge = x.Age,
AuthorCountry = x.Country,
}).ToList();
.Where(x => x.Published < date) is placed in Select, and the Include User statement is deleted, because Select itself can automatically load navigation properties and generate joint table statements. The generated SQL code is as follows:
SELECT [u].[FirstName], [u].[LastName], [u].[Email], [u].[UserName], [t].[
BooksCount], [t].[Id], [u].[Id], [t0].[Name], [t0].[Published], [t0].[Id], [t].[
Age], [t].[Country]
FROM (
SELECT TOP(@__p_0) [a].[Id], [a].[Age], [a].[BooksCount], [a].[Country
], [a].[UserId]
FROM [Authors] AS [a]
WHERE [a].[Country] = N'France' AND [a].[Age] >= 20
ORDER BY [a].[BooksCount] DESC
) AS [t]
INNER JOIN [Users] AS [u] ON [t].[UserId] = [u].[Id]
LEFT JOIN (
SELECT [b].[Name], [b].[Published], [b].[Id], [b].[AuthorId]
FROM [Books] AS [b]
WHERE [b].[Published] < @__date_1
) AS [t0] ON [t].[Id] = [t0].[AuthorId]
ORDER BY [t].[BooksCount] DESC, [t].[Id], [u].[Id]
Judging from the final generated code, the Select method plus Where filter parameters and the Include method plus Where filter parameters have the same effect.
Conclusion
The new filter feature of the Include method can indeed make our code simpler in some cases, but it is affected by the problem that it cannot coexist with the Select statement, and the Select statement itself can also receive the Where filter parameter, plus the Select method It is compatible with versions prior to EF Core5.0, so it is recommended to use Select.
Appendix
public class Author
{<!-- -->
public int Id {<!-- --> get; set; }
public int Age {<!-- --> get; set; }
public string Country {<!-- --> get; set; }
public int BooksCount {<!-- --> get; set; }
public string NickName {<!-- --> get; set; }
[ForeignKey("UserId")]
public User User {<!-- --> get; set; }
public int UserId {<!-- --> get; set; }
public virtual List<Book> Books {<!-- --> get; set; } = new List<Book>();
}
public class Book
{<!-- -->
public int Id {<!-- --> get; set; }
public string Name {<!-- --> get; set; }
[ForeignKey("AuthorId")]
public Author Author {<!-- --> get; set; }
public int AuthorId {<!-- --> get; set; }
public DateTime Published {<!-- --> get; set; }
public string ISBN {<!-- --> get; set; }
[ForeignKey("PublisherId")]
public Publisher Publisher {<!-- --> get; set; }
public int PublisherId {<!-- --> get; set; }
}
public class Publisher
{<!-- -->
public int Id {<!-- --> get; set; }
public string Name {<!-- --> get; set; }
public DateTime Established {<!-- --> get; set; }
}
public class User
{<!-- -->
public int Id {<!-- --> get; set; }
public string FirstName {<!-- --> get; set; }
public string LastName {<!-- --> get; set; }
public string UserName {<!-- --> get; set; }
public string Email {<!-- --> get; set; }
public virtual List<UserRole> UserRoles {<!-- --> get; set; } = new List<UserRole>();
public DateTime Created {<!-- --> get; set; }
public bool EmailConfirmed {<!-- --> get; set; }
public DateTime LastActivity {<!-- --> get; set; }
}
public class Role
{<!-- -->
public int Id {<!-- --> get; set; }
public virtual List<UserRole> UserRoles {<!-- --> get; set; } = new List<UserRole>();
public string Name {<!-- --> get; set; }
}
public class AuthorWeb
{<!-- -->
public DateTime UserCreated {<!-- --> get; set; }
public bool UserEmailConfirmed {<!-- --> get; set; }
public string UserFirstName {<!-- --> get; set; }
public DateTime UserLastActivity {<!-- --> get; set; }
public string UserLastName {<!-- --> get; set; }
public string UserEmail {<!-- --> get; set; }
public string UserName {<!-- --> get; set; }
public int UserId {<!-- --> get; set; }
public int AuthorId {<!-- --> get; set; }
public int Id {<!-- --> get; set; }
public int RoleId {<!-- --> get; set; }
public int BooksCount {<!-- --> get; set; }
public List<BookWeb> AllBooks {<!-- --> get; set; }
public int AuthorAge {<!-- --> get; set; }
public string AuthorCountry {<!-- --> get; set; }
public string AuthorNickName {<!-- --> get; set; }
}
public class BookWeb
{<!-- -->
public int Id {<!-- --> get; set; }
public string Name {<!-- --> get; set; }
public DateTime Published {<!-- --> get; set; }
public int PublishedYear {<!-- --> get; set; }
public string PublisherName {<!-- --> get; set; }
public string ISBN {<!-- --> get; set; }
}