EF Core enum collection property comparing

Question

I'm using code first with Entity Framework Core for handling som addresses in an application. These addresses all have a HousingTypes property which is a collection of enums that can by any of the available enum types.

public enum HousingType
{
    Condominium = 1,
    SummerHouse = 2,
    StudentApartment = 3,
    ServiceHousing = 4
}

public class Address
{
    public int Id { get; set; }
    public ICollection<HousingType> HousingTypes { get; set; }
}

I've created a ValueConverter and ValueComparer as described by others previously to convert these value to comma separated strings in the database like this in the DatabaseContext:

ModelBuilder.Entity<Address>().Property(nameof(HousingTypes))
    .HasConversion(
        x => string.Join(",", x),
        x => string.IsNullOrWhiteSpace(x) ? new List<HousingType>() : x.Split(new[] { ',' }).ToList()))
    .Metadata.SetValueComparer(new ValueComparer<ICollection<HousingType>>(
        (x, y) => x.SequenceEqual(y),
        x => x.Aggregate(0, (a, v) => HashCode.Combine(a, v.GetHashCode())),
        x => x.ToList()););

This is all and well, and I can read and write data as normal. But I've come to a point where the user needs to filter out addresses depending on selected HousingTypes. Now Entity Framework does not know how to compare multiple housing types to each other.

// Only receive addresses that has any of these housing types included
var filterExample = new HousingType[] { HousingType.Condominium, HousingType.SummerHouse };

await _context.Addresses
    .Where(a => a.HousingTypes.Any(ht => filterExample.Contains(ht))) // Entity Framework does not like this!
    .ToListAsync;

When retrieving the data I get the following error message:

The LINQ expression 'ht => __filterExample_3.Contains(ht)' 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'.

I would rather not inserting a call to ToList earlier, since that would load in all addresses (lots amounts of data) that is not needed, and makes it problematic to use paging and such when retrieving data.

All the problems come from the (bad) design. Not sure how do you expect EF Core (or general SQL database) to split a string in order to emulate client side list and Contains. Few databases (for instance, PostgreSQL) can do that, but not all and not in general. So either use a "standard" design with dependent "owned" entity collection/table, or make the enum `Flags` of type int or long and store it in int or long column in the database. This way you can handle 32 or 64 "value sets" with easy checks using bitwise operators. — Ivan Stoev, Jan 26 '23 at 13:37
I hope [this link](https://stackoverflow.com/a/8480/8404545) will help you redefine correctly your design :) — Max, Jan 26 '23 at 13:39
Thank you for your help in another direction @IvanStoev. But how would you make EF compare the two enum values using bitwise comparison? — einord, Jan 27 '23 at 07:45
@einord Just use `==`, `!=`, `&` and `|` operators, they will be translated to the corresponding integer type SQL operators/functions, every major relational database supports them. For instance, `setA.ContainsAny(setB)` (that you need, `setB` could be single or multiple or'ed flag values) would be `(setA & setB) != 0`. And `setA.ContainsAll(setB)` would be `(setA & setB) == setB`, `setA.Intersect(setB)` -> `(setA & setB)`, `setA.Union(setB`) -> `(setA | setB)` etc. — Ivan Stoev, Jan 27 '23 at 08:11

score 0 · Accepted Answer · answered Feb 03 '23 at 06:19

First I tried using Flags with the enum, but this complicated a lot of the code (specially to make it compatible with the front-end).

Also, it's not intuitive how to make comparisons between two enums and compare wether they are included in each other or not, etc. This could of course be made simpler with help or extension methods.

But I ended up using foreign key tables for this instead. Adding a AddressHousingType model which contains a normal enum and a foreign key to Address (one to many relation).

This solution is not ideal either, but works and is simpler to understand. I think that if this was a new project I would consider comparing bitwise and creating help methods for this, but since this is a project that has been in development for quite some time this felt like the best solution this time.

EF Core enum collection property comparing

1 Answers1