Pablo7747
Pablo7747

Reputation: 53

EF Core SQL Filter Translation

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.

  1. 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.

  2. 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

Answers (1)

Ivan Stoev
Ivan Stoev

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

Related Questions