How to filter a query in EF Core using a Value Object (Email value object) and the filter using Contains method?

Question

I'm using C# EF Core version 7.

I have a value object called Email which contains a string Value getter property which will hold the email value, and i have entity called Person which have Id and email

i have configuered my entity using fluent api as below:

the Id is the key

and i have added a convertor for Email Value object

Then i made a query to retrive all emails that contains "Alex" as below

var result = _myContext.Person.Where(p => p.Email.Value.Contains("Alex")).ToList();

but it encountered error as below:

The LINQ expression 'DbSet<Person>()
    .Where(p => p.Email.Value.Contains("Alex"))' 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'.								

Answer 1

Hi Osama,

It's clear from the message that the query couldn't be translated to sql, this is due to the use of Contains() function which is not supported when trying to evaluate results directly from the database, you can get around this by loading all the results first like they are suggesting using AsEnumerable for example ...

you would have something like this in this case :

var result = _myContext.Person.AsEnumerable(). Where(p => p.Email.Value.Contains("Alex")).ToList();

This approach is however not recommanded because it will load all the table data to memory before filtering causing a performance issue , you should review your query or at least use lazy loading .

In your case for example , there are not many names that contains "Alex" , so I would just use this instead

var result = _myContext.Person
. Where(p => p.FirstName.Value =="Alex" 
	||p => p.FirstName.Value =="Alexandre"  
	||p => p.FirstName.Value =="Alexis"  
).ToList();

Answer 2

The .Contains() is fine (string value.Contains translates to a wildcarded like.)

You can not map custom objects to a column, you could create a email table and have a relationship.

as the query expression will is converted to SQL you are very limited in the properties and methods you can Use on column values.

Answer 3

maybe this can help you : https://youtube.com/shorts/dPOxyzFgMh0?si=EiYHDeSBkGKD8Ww2