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

Osama Al-qarutee 0 Reputation points
2023-11-09T11:44:30.2966667+00:00

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'.								
Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
633 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. abdelilah fettah 0 Reputation points
    2023-11-09T14:04:42.5233333+00:00

    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();
    
    0 comments No comments

  2. Bruce (SqlWork.com) 49,051 Reputation points
    2023-11-09T17:54:12.27+00:00

    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.

    0 comments No comments