CODEWITHSUNDEEP
c#.netentity-frameworkentity-framework-core
D. Ivanov
D. Ivanov

Reputation: 138

EF Core : Filtering by not mapped boilerplate properties

I have some entity classes that contain a not mapped integer property that is used to categorize the data, which is represented by the entity.

 public partial class HouseEntity
 {
     [NotMapped]
     public int DataCategory { get => 5; }

     // other mapped properties

     // mapped bit property
     public bool IsPublic { get; set; }
 }

I want to be able to write complex filtering statements, based on in-memory constants and also based on stored values like this one:

public IEnumerable<HouseEntity> GetHouses()
{
    List<int> categoryPermissions = new List<int>() {1, 2, 3}; // usually obtained through service 
        
    return context.HouseEntities
                  .Where(he => categoryPermissions.Contains(he.DataCategory) ||  he.IsPublic == true )
                  .ToList();
}

The problem is that the linq statement above throws an exception because it can not be compiled to sql.

Are there any workarounds or any other approaches for filtering by hardcoded in-memory properties?

Upvotes: 1

Views: 2277

Answers (3)

Stefan
Stefan

Reputation: 17658

Since it's [NotMapped], you cannot perform the query against the database.

But you could to little in memory tricks like this:

public IEnumerable<HouseEntity> GetHouses()
{
    List<int> categoryPermissions = new List<int>() {1, 2, 3}; // usually obtained through service 
        
    // note the tolist here
    return context.HouseEntities.ToList()
                  .Where(he => categoryPermissions.Contains(he.DataCategory) ||  he.IsPublic == true )
                  .ToList();
}

Alternatively you can use extension methods to obtain a more fluent syntax - it would also to help you improve performance: i.e.: execute the database indexed part first, then "ToList" and then the in-memory part. Although, it can be tricky to write an OR like expression by chaining queries.

Upvotes: 2

Prolog
Prolog

Reputation: 3374

How would you write your own SQL query with WHERE statement using a non-existing column? You can't.

What you can do is filter by those properties that are mapped to columns and then after the query returns the result from the database filter by those properties that are not mapped, because by then you'll operating on a collection stored in memory:

public IEnumerable<HouseEntity> GetHouses()
{
    List<int> categoryPermissions = new List<int>() {1, 2, 3}; // usually obtained through service 
        
    return context.HouseEntities
                  .Where(he => he.IsPublic == true)
                  .ToList()
                  .Where(he => categoryPermissions.Contains(he.DataCategory))
                  .ToList();
}

Also consider returning a List of HouseEntity or IReadOnlyList because this is what you are returning in the end.

Also, your query may not be working because of Contains() called on a list. Try using IEnumerable or updating to EF Core 3.1 More info on the issue:

Upvotes: 1

D. Ivanov
D. Ivanov

Reputation: 138

I confirm that I was unable to run the code above with Entity Framework Core 3.1.1

The problem is that I can not make EF to "look into the future" and convince it to get the the value from the property, probably because it requires an entity instance and there is no way of knowing that the getter will return a constant value across all instances.

I also substituted the List<int> with IEnumerable<int> again achieving no positive results.

A workaround I found for my specific scenario was this one:

public IEnumerable<HouseEntity> GetHouses() 
{ 
   List<int> categoryPermissions = new List<int>() {1, 2, 3}; // usually obtained through service 

   int category = (new HouseEntity()).DataCategory;

   return context.HouseEntities
                 .Where(he => categoryPermissions.Contains(category) || he.IsPublic == true )
                 .ToList(); 
}

Although this seems a little bit like a non sense, such approach was needed, because I needed to be able to write a method for generating universal where clause Expressions, which does not work with a concrete Entity class but with an Interface, enforcing the Entities, which implement it, to categorize themselves through the not mapped property and providing access to some common properties like the IsPublic column.

Thank you all for the suggestions!

Upvotes: 0

Related Questions