Reputation: 53
I'm using EF Core 5.0.1 with ASP NET 5.0.1 Web API and I want to build a query with PredicateBuilder
using LinqKit.Microsoft.EntityFrameworkCore 5.0.2.1
For the purposes of the question I simplified my model to:
public class User
{
public long IdUser { get; set; }
public string Name { get; set; }
public virtual ICollection<UserDepartment> UserDepartments { get; set; }
}
public class Department
{
public long IdDepartament { get; set; }
public string Name { get; set; }
public virtual ICollection<UserDepartment> UsersDepartment { get; set; }
}
public UserDepartment
{
public long IdUser { get; set; }
public long IdDepartment { get; set; }
public virtual Department { get; set; }
public virtual User { get; set; }
}
One User
can have many Departaments
and one Departament
can have many Users
Three models have its correspondent table in SQL Server and a IEntityTypeConfiguration
class with the appropriate relationships set up.
All I want to achieve is to search any User
that belongs to any Departament
which Department.Name
is in a List<String>
.
The List<String>
contains a list of keywords, not the exact Department Name
Department
table has this kind of rows:
IdDepartment | Name |
---|---|
1 | Administration |
2 | HHRR |
3 | Sales |
4 | Marketing |
And the List<String>
can be any keyword like "Admin", "Sal", "Mark" and so on.
First attempt
... was to build a predicate like that:
List<string> kwDepartments = new List<String> {"mark","admin"};
var predicate = PredicateBuilder.New<User>(true);
predicate = predicate.And(x => x.UserDepartments.Where(y => kwDepartments.Any(c => y.Department.Name.Equals(c))).Any());
This produces a SQL with IN operator, like that:
...[t].[Name] IN (N'mark', N'admin'))
Obviously this is not what I want, but if I use .Contains
instead of .Equals
, an exception is thrown
The LINQ expression could not be translated
I think this is because I'm trying to evaluate a non-primitive value.
Second attempt
... was to iterate over kwDepartments
and add an .Or
for each string, like that:
foreach (string dep in kwDepartments )
{
predicateDep = predicateDep.Or(x => x.UserDepartments.Where(y=> y.Department.Name.Contains(dep)).Any());
}
predicate = predicate.And(predicateDep);
This returns the match that I expect, but two problems too.
The SQL translation is poor performance as EF Core anidates a INNER JOIN
for each keyword. No matter if kwDepartment
has two or three elements, but with 100 or 1000 elements will be inadmissible.
Each INNER JOIN
on Departments
table have a SELECT
with all the table fields and i did not need none of them. I've tried to put a .Select(x=> x.Name)
statement in the predicate to take only two fields but it make no effect.
Third attempt
... was using Full Text Search using EF.Functions.FreeText
but it seems to make no difference.
My goal is to build a predicate that translate in something similar to:
SELECT [c.IdUser]. [c.Name]
FROM [User] AS [c]
WHERE EXISTS (
SELECT 1
FROM [UserDepartment] AS [u]
INNER JOIN (
SELECT [c0].[IdDepartment], [c0].[Name] <--ONLY NEED TWO FIELDS INSTEAD OF ALL FIELDS
FROM [Department] AS [c0]
) AS [t] ON [u].[IdDepartment] = [t].[IdDepartment]
WHERE ([c].[IdUser] = [u].[IdUser]) AND ([t].[Name] like (N'admin%') or [t].[Name] like (N'mark%')))
It is not mandatory to use the LIKE
operator, but i put there for better understanding.
Thanks again!
Upvotes: 1
Views: 866
Reputation: 205589
You are on the right track with Or
predicate, but instead of multiple Or
predicates on user.UserDepatments.Any(single_match)
you should create single Or
based predicate to be used inside the single user.UserDepatments.Any(multi_or_match)
.
Something like this:
var departtmentPredicate = kwDepartments
.Select(kw => Linq.Expr((Department d) => EF.Functions.Like(d.Name, "%" + kw + "%")))
.Aggregate(PredicateBuilder.Or);
and then
predicate = predicate.And(u => u.UserDepartments
.Select(ud => ud.Department) // navigate to department
.AsQueryable() // to be able to use departtmentPredicate expression directly
.Any(departtmentPredicate));
With that code and the sample list, DbSet<User>().Where(predicate)
is translated to something like this:
DECLARE @__p_1 nvarchar(4000) = N'%mark%';
DECLARE @__p_2 nvarchar(4000) = N'%admin%';
SELECT [u].[IdUser], [u].[Name]
FROM [User] AS [u]
WHERE EXISTS (
SELECT 1
FROM [UserDepartment] AS [u0]
INNER JOIN [Department] AS [d] ON [u0].[IdDepartment] = [d].[IdDepartament]
WHERE ([u].[IdUser] = [u0].[IdUser]) AND (([d].[Name] LIKE @__p_1) OR ([d].[Name] LIKE @__p_2)))
Upvotes: 1