List of enums EF Core LINQ query

February 23, 2023

public enum GeneticModificationTypes
{
    SomeType = 1,
    AnotherType = 2,
    ThirdType = 3
}

public class GeneticModification: FullAuditedEntity<Guid>
{
   public ICollection<GeneticModificationTypes> Types { get; set; } 
}

modelBuilder.Entity<GeneticModification>()
        .Property(e => e.Types)
        .HasConversion(
            v => string.Join(',', v),
            v => v.Split(',', StringSplitOptions.RemoveEmptyEntries).ToList() ?? new List<GeneticModiciation>())
        .Metadata.SetValueComparer(valueComparer);

var valueComparer = new ValueComparer<ICollection<string>>(
                    (c1, c2) => c1.SequenceEqual(c2),
                    c => c.Aggregate(0, (a, v) => HashCode.Combine(a, v.GetHashCode())),
                    c => (ICollection<string>)c.ToHashSet());

When I try to:

_context.GeneticModification.Where(r => r.GeneticModification.Any(b => listOfTypes.Contains(b)))

_context.GeneticModification.Where(r => r.GeneticModification.Contains(listOfTypes.Contains(b))

edit

it throws:

Message:

System.InvalidOperationException : The LINQ expression 'DbSet<GeneticModification>()
    .Where(m => m.OrganizationId == __organizationId_0)
    .OrderBy(m => m.CreationTime)
    .Skip(__p_1)
    .Take(__p_2)
    .Where(m => m.GeneticModificationTypes
        .Contains(__filters_GeneticModificationTypes_Value_3))' could not be translated. Additional information: Translation of method 'System.Linq.Enumerable.Contains' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. 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 this model does is creating a Varchar column on the Table GeneticModification where the ICollection on my model gets translated to "Type1, Type2".

The table is the follow:
GeneticModification
Id | Types
Guid | SomeType, AnotherType

>Solution :

It looks like your database has a column with multiple numeric values concatenated into one string. This is VERY VERY bad if you want to filter or sort base don individual value within that column. It’s messy and inefficient even in straight SQL, and Linq doesn’t have any magic to generate that SQL.

Ideally I would use a related table instead of concatenated values in that column. If you can’t change the database structure, then C# Has better ways of turning those strings into arrays, and can filter and sort from there, but you’ll have to load mode data into memory that you otherwise would.

To do that, you’d build your base query, then insert AsEnumerable() to change from a SQL context to an in-memory context, then split the strings and do whatever filtering you need to from there.