It is important to understand that the Entity Framework provider can only translate LINQ expression trees into SQL statements. When it comes to common IEnumerable function like IEnumerable<T>.Contains or CLR functions like String.ToUpper() the provider maps these common C# function calls to known SQL implementations.
This is why your standard custom functions cannot be translated to SQL, the provider simply does not have a corresponding known implementation. Think about it this way, if I use reflection to inspect a method, I only get back the prototype, so the name, inputs and return types, there is no way to access the internal workings of that method. It is also true that not all ALL CLR functions have been mapped, so you will see this same error when you try to use a CLR function that is not recognised by the provider.
Even though the error message suggests these options:
If this method can be mapped to your custom function, 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'
Many answers on SO just tell the OP to switch to client evaluation when in fact that is a hack and there are usually better solutions, especially for our custom functions that encapsulate common business expressions for re-use. We write these specifically because we expect them to be defined once and re-used, now lets look at how to write them correctly!
Client Evaluation of Filter is an Anti-Pattern!
Yes the exception message states client evaluation as a potential option, but don't be fooled. If you materialise the entire dataset into memory and then apply a filter, then you may have wasting network bandwidth, CPU ticks and execution time. If the results is a large set and the filter results in zero records then you have wasted a LOT of resources! In LINQ-to-SQL scenarios we really want to avoid client-side filtering at ALL costs!
Don't be lazy, do it properly! The irony of this is that if you constructed your LINQ expressions correctly you would not have likely thought about client evaluation in the first place which most likely to result in further degrading query performance and can easily spawn off Stack Overflow exceptions or violate other Memory Constraints.
Expression<Func<>>
You can define your custom C# methods specifically for LINQ by making them return a lambda expression in the form of expression tree, that is Expression<Func<>>.
You need Expression when the code needs to be analyzed, serialized, or optimized before it is run. Expression is for thinking about code, Func/Action is for running it.
That is precisely what we want to achieve here, we want to enable the LINQ provider to analyze the code to translate it into SQL!
public virtual Expression<Func<FilterUserGroupDataModel, bool>> UserHasFilter(Guid userId)
{
return x => x == null ? false :
x.FilterUsers == null ? false :
x.FilterUsers.Any(u => u.User.Id == userId);
}
This should actually translate into a SQL CASE expression, what you may notice here is that the instance of the FilterUserGroupDataModel is never passed into this method at all! That is the point of this, to be executed in the server, so in SQL, we need to use SQL parameters and references to express our logic, we don't want the SQL engine to wait for each execution instance to call back to the client to resolve the state of FilterUserGroupDataModel and if it has any FilterUsers that match our current userId.
That is in essence what the error message is describing, you have told it to call back to the C# function in the middle of what it is trying to compile into SQL, more on this later...
The implementation of this is only slightly different, notice again that we are not passing through a reference to the current
public async Task<IEnumerable<ConditionDataModel>> GetUserFilters(string pageName)
{
var user = await _configurationService.GetCurrentUser();
if (user == null)
{
return null;
}
var conditions = _context.FilterUserGroups
.Include(f => f.CompanyDataRight).ThenInclude(d => d.Page)
.Include(f => f.FilterUsers).ThenInclude(d => d.User)
.Include(f => f.FilterGroups).ThenInclude(d => d.Group).ThenInclude(g => g.UserGroups).ThenInclude(ug => ug.User)
.Where(f => f.CompanyDataRight.Page.ClassName == pageName)
.Where(UserHasFilter(user.Id)) // <-- this is the custom function call
.Include(f => f.Conditions)
.SelectMany(f => f.Conditions)
.Distinct()
.AsEnumerable();
return conditions;
}
Quick Refactor Hack
Getting the method signature correct for these types of expressions is important and hard to get right the first couple of times, one hack is to write your predicate as a complete .Where() clause using Fluent notation. Then highlight the content inside the .Where(), right click and select the Quick Actions and Refactorings... context menu option, then Extract Method. This will create a new method with the correct prototype for a predicate at that point in the query.
Mapped User Defined Function
There is of course another way, and that is we can define a custom function in C# and map it to a SQL Function. Mapping means the implementation will not be interpreted at all, the Function in the database will represent the SQL implementation to use.
- You can map to System Functions if you really need them, but we generally only do this for custom UDFs.
This technique is helpful for legacy applications or in large scale organisations where the DBAs really want to manage the related logic, applications using the technique would often include a lot of mapped Stored Procedures as well.
In a practical sense, this technique would only be used to implement existing CLR functions or complicated SQL logic.
Map CLR Method to a SQL Function using .NET EF Core walks through the mechanics, but basically we create a C# and an SQL definition of the function, then we can Map the function to the DbContext.
- When the
IQueryable LINQ-to-SQL provider translates the expression it will ignore the C# implementation altogether, but we still make a C# implementation in case the function is used by non LINQ-to-SQL contexts or if the query is evaluated on the client.
I will not post an implementation of this method here because the logic needs to lookup data in external tables. This is NOT appropriate to be implemented as a User Defined Function. UDFs should be self contained or static calculations based on the inputs and internal constants where possible and not affect or select from external resources, Views and Stored Procedures are better mechanisms to encapsulate that type of logic.
