C#•2mo ago

✅ Why ins't this EF Core query being evaluated client-side?

Currently, I have this query:

C#
var board = Database.KanbanBoards
    .Where(k => k.OwnerUserId == AuthUser.OwningId && k.Id == boardId)
    .Select(k => new
    {
        Data =
            Database.KanbanColumns
                .Where(c => c.ParentBoardId == k.Id)
                .OrderBy(c => c.Order)
                .Select(c => new
                {
                    column = new
                    {
                        c.Id,
                        c.Color,
                        c.Label
                    },
                    cards = Database.KanbanCards
                        .Where(a => a.ParentColumnId == c.Id)
                        .OrderBy(a => a.Order)
                        .AsEnumerable() // <!--- run the rest on client side
                        .Select(a => new
                        {
                            a.Id,
                            Title = a.CardTitle,
                            Info = new
                            {
                                a.Value,
                                a.Temperature,
                                AssociatedUsers = a.AssociatedUsers
                                    .Select(n => AdapterCacheUnit.FetchUser(n)) // <!-- client side function
                                    .Where(n => n != null)
                                    .Select(n => new
                                    {
                                        n!.UniqueId,
                                        n.UserName
                                    })
                            }
                        })
                })
                .ToArray()
    })
    .FirstOrDefault();

C#
var board = Database.KanbanBoards
    .Where(k => k.OwnerUserId == AuthUser.OwningId && k.Id == boardId)
    .Select(k => new
    {
        Data =
            Database.KanbanColumns
                .Where(c => c.ParentBoardId == k.Id)
                .OrderBy(c => c.Order)
                .Select(c => new
                {
                    column = new
                    {
                        c.Id,
                        c.Color,
                        c.Label
                    },
                    cards = Database.KanbanCards
                        .Where(a => a.ParentColumnId == c.Id)
                        .OrderBy(a => a.Order)
                        .AsEnumerable() // <!--- run the rest on client side
                        .Select(a => new
                        {
                            a.Id,
                            Title = a.CardTitle,
                            Info = new
                            {
                                a.Value,
                                a.Temperature,
                                AssociatedUsers = a.AssociatedUsers
                                    .Select(n => AdapterCacheUnit.FetchUser(n)) // <!-- client side function
                                    .Where(n => n != null)
                                    .Select(n => new
                                    {
                                        n!.UniqueId,
                                        n.UserName
                                    })
                            }
                        })
                })
                .ToArray()
    })
    .FirstOrDefault();

In the query above, in the Database.KanbanCards sub-query I make the rest of the query client-side to transform the "a.AssociatedUsers" column into other information. This is an JSON column.

20 Replies

cypherpotatoOP•2mo ago

Btw i'm getting the following exception, telling my expression ins't being evaluated on client side.

System.InvalidOperationException: The LINQ expression 'n => AdapterCacheUnit.FetchUser(n)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038
for more information.

System.InvalidOperationException: The LINQ expression 'n => AdapterCacheUnit.FetchUser(n)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038
for more information.

what am I doing wrong?

Unknown User•2mo ago

Message Not Public

jcotton42•2mo ago

@cypherpotato only the last Select can be client side Oh wait, you added AsE

Unknown User•2mo ago

Message Not Public

jcotton42•2mo ago

Oh Yeah that AsE needs to be in the “root” query chain.

cypherpotatoOP•2mo ago

I thought I could use AsEnumerable in nested queries. If I use it at the root, will the nested queries also be executed on the client side? yea by the end i've rewrited the entire query to use IEnumerable instead

C#
var query = Database.KanbanColumns
    .Where(c => c.ParentBoardId == boardId)
    .OrderBy(c => c.Order)
    .Select(c => new
    {
        Column = c,
        Cards = Database.KanbanCards
            .Where(a => a.ParentColumnId == c.Id)
            .OrderBy(a => a.Order)
            .ToArray()
    });

foreach (var data in query)
{
    yield return new
    {
        column = new
        {
            data.Column.Id,
            data.Column.Color,
            data.Column.Label
        },
        cards = data.Cards
            .Select(a => new
            {
                a.Id,
                Title = a.CardTitle,
                Info = new
                {
                    a.CreatedAt,
                    a.Value,
                    a.Temperature,
                    AssociatedUsers = a.AssociatedUsers
                        .Select(n => AdapterCacheUnit.FetchUser(n))
                        .Where(n => n != null)
                        .Select(n => new
                        {
                            n!.UniqueId,
                            n.UserName,
                            n.ProfilePictureUrl
                        })
                }
            })
    };
}

C#
var query = Database.KanbanColumns
    .Where(c => c.ParentBoardId == boardId)
    .OrderBy(c => c.Order)
    .Select(c => new
    {
        Column = c,
        Cards = Database.KanbanCards
            .Where(a => a.ParentColumnId == c.Id)
            .OrderBy(a => a.Order)
            .ToArray()
    });

foreach (var data in query)
{
    yield return new
    {
        column = new
        {
            data.Column.Id,
            data.Column.Color,
            data.Column.Label
        },
        cards = data.Cards
            .Select(a => new
            {
                a.Id,
                Title = a.CardTitle,
                Info = new
                {
                    a.CreatedAt,
                    a.Value,
                    a.Temperature,
                    AssociatedUsers = a.AssociatedUsers
                        .Select(n => AdapterCacheUnit.FetchUser(n))
                        .Where(n => n != null)
                        .Select(n => new
                        {
                            n!.UniqueId,
                            n.UserName,
                            n.ProfilePictureUrl
                        })
                }
            })
    };
}