Why does referencing an Enum type property in DTO converter cause all columns to be retrieved (EF Core 7)?

Question

My apologies for the lengthy intro, but my question is towards the end.

I have implemented a class having a property of type Enum. I used the following article:

how-to-use-enums-when-using-entity-framework-core-with-c

In short, I have a class called Species which has a property of type Enum to indicate the common submission type (for that species)

So following the instructions from the article I ended up with the following:

public enum CommonSubmissionTypes
{

    [Description("Small Animals")]
    SmallAnimals = 1,
    Farm = 2,
    Equine = 3
}


public partial class Species : EntityBase, IAggregateRoot
{
    public string Name { get; private set; }

    // This is the equivalent of the 'HouseSize' class from the article.
    // This is just here so that a migration will create the underlying table in the database.
    // I've marked it as obsolete, because I don't want this property to be used/referenced in code.
    [Obsolete]
    private CommonSubmissionType CommonSubmissionTypeBC { get; }

    // This relates to the value of the enum
    public int CommonSubmissionTypeId { get; set; }

    // This is the equivalent of the 'HouseSizeEnum' property from the article.
    // I'm just using an extension method to cast the Id to the enum, instead of doing that manually.
    [NotMapped]
    public CommonSubmissionTypes CommonSubmissionType
    {
        get => CommonSubmissionTypeId.ToEnum<CommonSubmissionTypes>();
        set => CommonSubmissionTypeId = (int)value;
    }


}

I followed the instructions for creating and seeding the tables using migrations.
That was all fine and I now have the following data:

So far so good.
I'm now implementing a list screen where I want to show a list of species.
For this purpose, I have the following DTO

public class SpeciesListItemDto : DtoBase
{

    public SpeciesListItemDto(int id, string name, string commonSubmissionType)
    {
        Id = id;
        Name = name;
        CommonSubmissionType = commonSubmissionType;
    }

    public string Name { get; set; }
    public string CommonSubmissionType { get; set; }
}

And I implemented a DTO converter with the following method

public static IQueryable<SpeciesListItemDto> ToSpeciesListItemDto(this IQueryable<Species> source)
{
    return source.Select(x => new SpeciesListItemDto(
               x.Id, 
               x.Name, 
               x.CommonSubmissionType.GetDescription()
           ));
}

EDIT (start)

As requested in the comments, here is some more detail about how the data is retrieved.

The API endpoint calls a service method which does the following:

 using (var context = ContextFactory.GetQueryContext())
 {
     var specification = new AllSpeciesSpecification();
     return _queryRepository.FindBySpecification(context, specification).ToSpeciesListItemDto().ToList();
 }

The specification defines the where and order by statements

public AllSpeciesSpecification()
{
    AddCriteria(x => x.Deleted == false);
    AddOrderBy(x => x.Name);
}

The call to FindBySpecification returns an IQueryable<Species> which takes into account the defined where and order by statements.
Next, the call to ToSpeciesListItemDto returns an IQueryable<SpeciesListItemDto> which contains the definition of the conversion.
Finally, to materialize the results (i.e. actually query the database) there is a call to .ToList().

EDIT (end)

And this works fine:

But ... there's a problem!

If I check in SQL Profiler, I can see that the following SQL statement is being executed:

SELECT [s].[Id], [s].[Name], [s].[CommonSubmissionTypeId], [s].[CreatedBy], [s].[DateCreated], [s].[DateLastUpdated], [s].[Deleted], [s].[LastUpdatedBy], [s].[Timestamp]
FROM [Species] AS [s]
WHERE [s].[Deleted] = CAST(0 AS bit)
ORDER BY [s].[Name]

So here's my question!
Why are all columns being retrieved from the database, even though I'm using a specific Select statement on the IQueryable<Species>?
How can I ensure that I'm only retrieving the columns Id, Name and CommonSubmissionTypeId?

When I change the DTO so that it has a property of type int and pass in the CommonSubmissionTypeId, then it works as expected (i.e. only 3 columns are retrieved from the database).
It seems that as soon as I reference the non-mapped enum property (deriving from the CommonSubmissionTypeId) it suddenly starts to pull in all columns?

Any ideas?

Please note that I'm really happen with the rest of the implementation.
I'm not looking for an alternative.
I just want to restrict the number of columns being retrieved from the database.

Answer 1

The problem is that inside LINQ to Entities query Select you are accessing the unmapped property

source.Select(x => new SpeciesListItemDto(x.Id, x.Name, x.CommonSubmissionType.GetDescription()))
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ^^^ 

[NotMapped] // <--
public CommonSubmissionTypes CommonSubmissionType
{
    get => CommonSubmissionTypeId.ToEnum<CommonSubmissionTypes>();
    set => CommonSubmissionTypeId = (int)value;
}

and also are calling custom method.

Any of them is enough to make the query non translatable (one of the reasons I don't like unmapped (ignored) properties in entity classes is that you can easily make such mistakes), but especially for the unmapped property EF Core has no other chance than to try evaluate it client side after retrieving all the data (remember, all they see is the declaration of the property, they have no source code to see the implementation).

You are in fact lucky that they try to use client evalution which since EF Core 3.0 is supported in very limited cases (basically only if it is inside the final select of the query). If you add any other LINQ operator after the Select, you'll receive runtime exception. Which probably you should as well when you access unmapped property, but as I said, you are lucky - for now, since they have habits to fix such things in future releases, so be prepared it to break at any time. After all, in this particular case it is really incorrect usage.