EF Core / .NET 6. Using an expression Select on entities

Question

I'm trying to implement a Select with an Expression to keep the object as an IQueryable and selecting into a child navigation collection

Example Code:

public Expression<Func<Item, ItemModel>> MapToModel =>
        Ent => new ItemModel
        {
            Id = Ent.Id,
            Name = Ent.Name,
            SKU = Ent.SKU,
            Variations = Ent.Variations == null ? default : Ent.Variations.Select(this.VariationMapper),
        };

With the VariationMapper being the following:

public Expression<Func<ItemVariation, VariationModel>> VariationMapper =>
        Ent => new VariationModel()
        {
            Id = Ent.Id,
            Name = Ent.Name,
        };

I receive the following error however:

'ICollection' does not contain a definition for 'Select' and the best extension method overload 'Queryable.Select<ItemVariation, VariationModel>(IQueryable, Expression<Func<ItemVariation, VariationModel>>)' requires a receiver of type 'IQueryable'

I'm a bit perplexed as to how to resolve the issue. Or if this is even possible. That subproperty is type ICollection<T> as it's a navigation property.

Also found this fantastic if tangent related answer: https://stackoverflow.com/questions/62115690/ef-core-queries-all-columns-in-sql-when-mapping-to-object-in-select/62138200#62138200 — Ezra Bailey, Jul 26 '23 at 14:57

score 2 · Answer 1 · answered Jul 26 '23 at 15:08

You do not need to check for null for collections, EF Core will handle it. Also EF Core will not translate correctly your mapper. It needs expanding which is supplied by LINQKit.

public Expression<Func<Item, ItemModel>> MapToModel =>
        Ent => new ItemModel
        {
            Id = Ent.Id,
            Name = Ent.Name,
            SKU = Ent.SKU,
            Variations = Ent.Variations.Select(this.VariationMapper.Expand()),
        };

How to activate LINQKit, you can find in this answer, along with sample how to create reusable AsDto methods.

Guru Stron · Answer 2 · 2023-07-26T14:49:00.547

1

Try using the AsQueryable:

Variations = Ent.Variations == null 
    ? default 
    : Ent.Variations
         .AsQueryable()
         .Select(this.VariationMapper)
         .ToList()

edited Jul 26 '23 at 14:49

answered Jul 26 '23 at 14:43

Guru Stron

102,774
10
95
132

This seemed to run successfully, but does it still offload that into the query for DB work rather than in memory? For example if I later do a `.Where(x => x.Variations.All(y + y.Id > 5))` will that be projected onto the database query? – Ezra Bailey Jul 26 '23 at 14:57
@EzraBailey there is a simple answer - check the generated SQL. EF Core allows some untranslatable custom function invocations in final projections but in this case it should generate a query. – Guru Stron Jul 26 '23 at 15:05

EF Core / .NET 6. Using an expression Select on entities

2 Answers2