async/await inside LINQ where clause not working

Question

I'm tring to make a database query inside a LINQ statement asynchronous, but I'm running into an error. The code below runs fine with out async/await

    var newEntities = _repositoryMapping.Mapper.Map<List<Entry>>(entries);

    newEntities = newEntities.Where(async e => await !_context.Entries.AnyAsync(c => c.Id == e.Id)).ToList();

Severity Code Description Project File Line Suppression State Error CS4010 Cannot convert async lambda expression to delegate type 'Func<Entry, bool>'. An async lambda expression may return void, Task or Task, none of which are convertible to 'Func<Entry, bool>'

Other than breaking this up into a foreach loop, how can I make this work with async/await?

Getting this async lamda to work and hitting the db multiple times is likely the wrong approach. Getting this to work with the one logical sql translation would be the better approach. What are you trying to do, just check if ids of given entities exist ? is there a bigger problem you are trying to solve here ? — TheGeneral, Jan 06 '21 at 08:29
@Qbertsuit? Why don't you simply make a left join (with if null)? — Peter Csala, Jan 06 '21 at 08:53
@00110001 I'm just trying to add async/await to some existing code. Currently its wrapped in a Task.Run which doesn't make sense. I think the original author of the code just wanted to remove any items from newEntries that already exists in the database — Q-bertsuit, Jan 06 '21 at 09:56
Check these: [1](https://stackoverflow.com/questions/5537995/entity-framework-left-join), [2](https://stackoverflow.com/questions/44188927/left-join-in-entity-framework-on-null-values) — Peter Csala, Jan 06 '21 at 10:02
@PeterCsala Thanks! I had some trouble getting it to work since I'm a bit of a noob when it comes to linq and sql. I posted a separate question for it https://stackoverflow.com/questions/65594332/linq-query-that-finds-duplicates-and-removed-them-from-a-list — Q-bertsuit, Jan 06 '21 at 10:47

score 1 · Answer 1 · answered Jan 06 '21 at 14:36

If you care about performance, code should be smarter. You just need to send one query and check what is already present in database.

Prepared extension which can do that in generic way:

newEntities = (await newEntities.FilterExistentAsync(_context.Entries, e => e.Id)).ToList();

Implementation is not so complex

public static class QueryableExtensions
{
    public static async Task<IEnumerable<T>> FilterExistentAsync<T, TProp>(this ICollection<T> items,
        IQueryable<T> dbQuery, Expression<Func<T, TProp>> prop, CancellationToken cancellationToken = default)
    {
        var propGetter = prop.Compile();
        var ids = items.Select(propGetter).ToList();
        var parameter = prop.Parameters[0];

        var predicate = Expression.Call(typeof(Enumerable), "Contains", new[] { typeof(TProp) }, Expression.Constant(ids), prop.Body);
        var predicateLambda = Expression.Lambda(predicate, parameter);

        var filtered = Expression.Call(typeof(Queryable), "Where", new[] {typeof(T)}, dbQuery.Expression,
            predicateLambda);

        var selectExpr = Expression.Call(typeof(Queryable), "Select", new[] {typeof(T), typeof(TProp)}, filtered, prop);
        var selectQuery = dbQuery.Provider.CreateQuery<TProp>(selectExpr);

        var existingIds = await selectQuery.ToListAsync(cancellationToken);

        return items.Where(i => !existingIds.Contains(propGetter(i)));
    }
}

This looks pretty complex to me :-) But really cool if it works! I'll give it a try. Thanks a lot! — Q-bertsuit, Jan 06 '21 at 19:20
Don’t worry, it is simplest extension that i have written before and it is started working from the first run. — Svyatoslav Danyliv, Jan 06 '21 at 19:29

score 0 · Answer 2 · answered Jan 06 '21 at 11:12

0

For the Exception, you can add a extension for IEnumerable to support async

    public static class MyExtensions
    {
        public static async Task<IEnumerable<T>> Where<T>(this IEnumerable<T> source, 
                                                          Func<T, Task<bool>> func)
        {
            var tasks = new List<Task<bool>>();

            foreach (var element in source)
            {
                tasks.Add(func(element));
            }

            var results = await Task.WhenAll<bool>(tasks.ToArray());

            var trueIndex = results.Select((x, index) => new { x, index })
                                   .Where(x => x.x)
                                   .Select(x => x.index).ToList();

            var filterSource = source.Where((x, index) => trueIndex.Contains(index));

            return filterSource;
        }
    }

Then you can use someting like below

 var result = await users.Where(async x => await TestAsync(x));

Full code here https://dotnetfiddle.net/lE2swz

answered Jan 06 '21 at 11:12

MichaelMao

2,596
2
23
54

This seems highly inefficient. If the `source` contains a large number of items, let's say `n` then this would do `n` number of round trips to the database to check existence. – Peter Csala Jan 06 '21 at 11:27
It's a nice solution, but I agree with @PeterCsala. Thanks alot for your input though! – Q-bertsuit Jan 06 '21 at 11:31
This solution is generally of complexity `O(n^2)` but could be implemented with `O(n)`, just use a loop over `source` and `result` instead of `trueIndex.Contains(index)` – Ackdari Jan 06 '21 at 14:09
@PeterCsala Yes, you are right so I said just for exception – MichaelMao Jan 07 '21 at 03:27

async/await inside LINQ where clause not working

2 Answers2

Linked