Using Any() inside Where () in EF Core 3.1

Question

await this.dbContext
          .UserTeams
          .Where(ut =>
                   teamMembers.Any(tm => tm.UserId == ut.UserId 
                                         && ut.TeamId == tm.TeamId))
          .ToListAsync();

here teamMember is a simple list that contains grouped UserIds and TeamIds. This works if i use Contains() but UserId and TeamId is a composite key here.

This is a fairly simple query and cannot be translated.

Error:

System.InvalidOperationException: The LINQ expression 'DbSet<UserTeam>
     .Where(u => __teamMembers_0
         .Any(tm => u.TeamId == tm.TeamId && u.UserId == tm.UserId))' 
could not be translated. 
Either rewrite the query in a form that can
be translated, or switch to client evaluation explicitly by inserting
a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or
ToListAsync()

@CyrilDurand, its a simple list. you can assume `var teamMembers = someList.Select(tm => new {tm.UserId, tm.TeamId}).ToList();` — Mujahid Daud Khan, Apr 06 '20 at 13:16
This never worked (EF or EF Core) mainly because there is no such SQL construct (`IN` does not support composite values). — Ivan Stoev, Apr 06 '20 at 13:17
@IvanStoev, Thanks i got that. i am not good with SQL. So The recommended approach is to use SP for that? — Mujahid Daud Khan, Apr 06 '20 at 13:20
SP won't help except if you can pass TVP. See this answer https://stackoverflow.com/questions/26198860/entityframework-contains-query-of-composite-key/26201371#26201371 for different options. It's for EF6, but the same applies to EF Core. — Ivan Stoev, Apr 06 '20 at 13:22
@IvanStoev, For the record, i have tried that `var teamMemberList = teamMembers.Select(tm => new Tuple(tm.UserId, tm.TeamId)).ToList();` to pass members, but still same query translation error. — Mujahid Daud Khan, Apr 06 '20 at 13:25
@MujahidDaudKhan TVP stands for Table Valued Parameter, not Tuple. — juharr, Apr 06 '20 at 13:36
One option would be to translate the pair of Guid values into a string representation and then search on that. The down side is that you will not be able to take advantage of any indexes on those columns. If your table isn't very large that might be a viable option. — juharr, Apr 06 '20 at 13:38
Or you can filter in the DB on separate lists for User Id and Team Id, then in memory refine the search to the matching pairs. — juharr, Apr 06 '20 at 13:43
@juharr, Yes, i am currently doing that. As i am already filtering records based on date (range), So i can go with In-Memory options as the number of records are less then ~100.. — Mujahid Daud Khan, Apr 06 '20 at 13:45

Maarten · Answer 1 · 2020-04-07T06:28:37.337

The problem is that EF cannot translate complex objects. EF can only translate .NET memory object in SQL parameters, and use those SQL parameters in the SQL query.

I see one option that might work. Since you are not doing anything specific than a simple look-up with the combined values, you can try to convert the user-id/team-id combination in a single SQL parameter. For instance, a string with value '-'. I'm assuming that you id's are simple integers. If they are string, then a different separator character might be necessary. Use that combined value to find any match in your db source.

The code below is not tested and might not work. An alternative is to create a view of some sort to create the combination-value on the db side.

    var memberIds = teamMembers.Select(tm => $"{tm.UserId}-{tm.TeamId}").ToArray();
    await this.dbContext
              .UserTeams
              .Where(ut => memberIds.Contains(
                               SqlFunctions.StringConvert((double?)tm.UserId) + "-" +
                               SqlFunctions.StringConvert((double?)tm.TeamId)
                           )
                    )
              .ToListAsync();

seems like a nice idea. If We really want indexing we can create a column that already contains computed key pair instead of evaluating that on run-time for every query. Then we can use this logic inside domain class with read-only property or use Triggers to auto-insert this value in this column. — Mujahid Daud Khan, Apr 06 '20 at 13:55

score 0 · Answer 2 · answered Mar 22 '23 at 22:13

I took Maarten's idea and I wrote the next code that's working

var idsToFilter = teamMembers.Select(tm => $"{tm.UserId}{tm.TeamId}").ToList();

            
await dbContext.UserTeams
    .Where(ut => idsToFilter.Contains(ut.UserId.ToString() + ut.TeamId.ToString()))
    .ToListAsync();

Using Contains inside the Where clause, I can filter the UserTeams that are in teamMembers. You have to notice that the string I make to filter (inside the Contains) has to be the same as the string inside the Select clause.

Using Any() inside Where () in EF Core 3.1

2 Answers2