As it is known, not every method can be translated into SQL via LINQ-to-SQL. These methods or properties being met in expressions causes exception at runtime - 'The member 'PropertyName' has no supported translation to SQL'
Evidently, these properties must be calculated on server-side. But is there any approach to do it automatically? Typically we use complex expressions in .Where() clause, and some of expression subclauses can be filtered out by SQL, some cannot. The more records are filtered by SQL the less redundant data are transmitted from SQL server and the better performance we have, hence we should try to apply as much SQL conditions as possible.
Of course, we can care about this manually, and firstly apply .Where() to the SQL-translatable subclauses to filter them out at SQL server, then call .AsEnumerable() and finally apply .Where() to the rest untranslatable clauses which will be performed already on server-side. Just this approach I always apply.
like
IEnumerable<Entity> selection = Entities.Where(e=> e.TranslatableCondition()).AsEnumerable().Where(e=> e.UntranslatableCondition)
I suppose there is an option to do it automatically, but don't know how. For example, method which is untranslatable to SQL may be marked with peculiar attribute
like
[SQLTranslatable(false)] or something like that
public string SuperComplexProperty {get; set;}
Then developer must not care to separate manually translatable and untranslatable clauses and put them into different .Where() clauses. But I doubt if such option is available in LINQ despite it would be very necessary
Thanks in advance for suggestions.