Error: IAsyncEnumerable cannot be used for parameter of type IEnumerable on Entity framework

Question

I've got these two LINQ expressions. I'm using Entity Framework. The only difference is the position of the items on the list of CardIds.

The first example returns the AdditionalCards that comes from "MemberAdditionalCards" first and then it adds the Card that is stored on the Members table.

The second example does the opposite, gets first the Card that is stored on the Members table and then the AdditionalCards that comes from "MembersAdditionalCards".

First LINQ:

            members = await _context.Members
                .Where(s => EF.Functions.Like(s.FirstNames, searchTerm))
                .Select(s => new Members
                {
                    Id = s.Id,
                    CardIds = s.MemberAdditionalCards
                        .Select(m => new AdditionalCard
                        {
                            CardId = m.CardId
                        })
                        .Union(new List<AdditionalCard>
                        {
                            new AdditionalCard
                            {
                                CardId = s.CardId
                            }
                        })
                        .ToList()
                })
                .AsNoTracking()
                .ToListAsync();

Second LINQ:

            members = await _context.Members
                .Where(s => EF.Functions.Like(s.FirstNames, searchTerm))
                .Select(s => new Members
                {
                    Id = s.Id,
                    CardIds = new List<AdditionalCard>
                    {
                        new AdditionalCard
                        {
                            CardId = s.CardId
                        }
                    }
                    .Union(s.MemberAdditionalCards
                        .Select(a => new AdditionalCard
                        {
                            CardId = a.CardId
                        }))
                    .ToList()
                })
                .AsNoTracking()
                .ToListAsync();

With the Second LINQ I've got an error:

The first LINQ expression works fine but is not how I want to have the items on the list result.

With the Second LINQ expression: if I remove the "await" keyword from the second expression and in the end I do ToList() rather than ToListAsync() it works just fine. I do understand the issue is the async execution. But how can I make the last bit to be executed as async and why that issue doesn't happen with the First LINQ expression?

Thanks!

Answer 1

It's complaining about this part:

.Union(s.MemberAdditionalCards
    .Select(a => new AdditionalCard
    {
        CardId = a.CardId
    }))

Since you're doing a Union on a List<T> (which is IEnumerable), then Union will only accept an IEnumerable as a parameter. But s.MemberAdditionalCards.Select() returns IAsyncEnumerable.

I'm not sure if it will like the await inside the Linq expression, so if it doesn't, then remove the await and change ToListAsync() to ToList():

.Union(await s.MemberAdditionalCards
    .Select(a => new AdditionalCard
    {
        CardId = a.CardId
    }).ToListAsync())

I'm not sure what the resulting SQL will look like. Someone might have a better idea.

Answer 2

If you want the linq to be resolved in a single query you better remove all .ToList() calls within the select expression.

Try this:

members = await _context.Members
    .Where(s => EF.Functions.Like(s.FirstNames, searchTerm))
    .Select(s => new Members
    {
        Id = s.Id,
        CardIds = new List<AdditionalCard>
        {
            new AdditionalCard
            {
                CardId = s.CardId
            }
        }
        .Union(s.MemberAdditionalCards
            .Select(a => new AdditionalCard
            {
                CardId = a.CardId
            }))
    })
    .AsNoTracking()
    .ToListAsync();