Hello dear experts! I want to ask a question on the Entity Framework. For example, I have this code

return from city in context.City where SomeFunc(city, id, name) select new City(city); bool SomeFunc(City city, int id, int name) { return city.Id == id || city.Name == name || city.Code == name; }

when I execute code I get such an exception

This method cannot be translated to a store expression.

On the Internet, it is advised to use the AsEnumerable method

 return from city in context.City.AsEnumerable() where SomeFunc(city, id, name) select new City(city);

It works but very slowly because context.City.AsEnumerable () gives me all the cities from the table at once and then performs the function SomeFunc for them

You can write this:

 return from city in context.City where city.Id == id || city.Name == name || city.Code == name select new City(city);

and everything will work quickly. But then such a problem will turn out - this condition can be used even in some method and then it will be necessary to duplicate this code which is not good. How to be? Maybe in the Entity Framework there is some way to avoid duplicating code and fulfilling conditions in SQL and not in C #? Thank you in advance!

    2 answers 2

    Try moving from Func to Expression:

     Expression<Func<City, bool>> SomeFunc(int id, string name) { return city => city.Id == id || city.Name == name || city.Code == name; } var result = cities.Where(SomeFunc(id, name)).Select(city => new City(city));
    • Thank you very much! Can you explain how and why it works? why when using the usual Func everything was slow, and with the Expression it became fast? - JuniorTwo
    • 3
      @JuniorTwo: The fact is that LINQ can parse Expression into its component parts and turn it into SQL, so SomeFunc is actually calculated on the database side! Without Expression an ordinary function is obtained, and LINQ does not know how to look inside a function and turn it into SQL. The magic is that the lambda expression city => city.Id == id || city.Name == name || city.Code == name can be interpreted by the compiler both as an ordinary arithmetic expression and as an Expression, depending on the context. - VladD
    • @VladD thanks! - JuniorTwo
    • @JuniorTwo: please! - VladD

    If the condition is used in several places, put this filtering in a separate method:

     private IQueryable<City> GetFilteredCities(int id, string name) { return context.City .Where(city => city.Id == id || city.Name == name || city.Code == name); }

    Then, it can be used in other methods:

     var cities1 = GetFilteredCities(1, "Moscow"); var cities2 = GetFilteredCities(3, "London").Where( ... ).Select( ... );

    As a result of the execution of the GetFilteredCities method GetFilteredCities SQL query will not be executed, but the condition will simply be formed. Immediately, the query will be executed when calling the AsEnumerable() , ToList() or ToArray() .