Dear Carl,
there's a problem with your current implementation: When using the "efcustomers" controller, the query first returns all records from the database and then filtering is done only in memory. This might be a no-go for most real-world applications.
But it can be fixed in AvnRepository (in QueryFilter.GetFilteredList) by changing the query expressions so that they are accepted by Entity Framework Core. The trick is to avoid reflection and build simple expressions that are supported EF Core (e. g. c => c.Name, String.StartsWith, ...).
Here's my patch for the StartsWith operator (as an example):
else if (filterProperty.Operator == FilterOperator.StartsWith)
//if (filterProperty.CaseSensitive == false)
{
var arg = Expression.Parameter(typeof(TEntity), "c");
var getProp = Expression.MakeMemberAccess(arg, prop);
var startsWith = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });
var targetValue = Expression.Constant(value);
MethodCallExpression propStartsWith;
if (filterProperty.CaseSensitive)
{
// Compare as-is for case-sensitive comparison
propStartsWith = Expression.Call(getProp, startsWith, targetValue);
}
else
{
// Convert ToLower to achieve case-insensitive comparison
// Please note that SQL Server is case-insenstive by default (depending on the configured COLLATION)
MethodInfo toLower = typeof(string).GetMethod("ToLower", Array.Empty<Type>());
MethodCallExpression propStartsWithLower = Expression.Call(getProp, toLower);
propStartsWith = Expression.Call(propStartsWithLower, startsWith, targetValue);
}
expression = Expression.Lambda<Func<TEntity, bool>>(propStartsWith, arg);
//expression = s => s.GetType().GetProperty(filterProperty.Name).GetValue(s).ToString().ToLower().StartsWith(value);
}
//else
// expression = s => s.GetType().GetProperty(filterProperty.Name).GetValue(s).ToString().StartsWith(value);A similar change is required for OrderByPropertyName. Here's my patch:
// order by
if (OrderByPropertyName != "")
{
PropertyInfo prop = typeof(TEntity).GetProperty(OrderByPropertyName);
if (prop != null)
{
var arg = Expression.Parameter(typeof(TEntity), "c");
var getProp = Expression.MakeMemberAccess(arg, prop);
var orderByExpression = Expression.Lambda<Func<TEntity, string>>(getProp, arg);
if (OrderByDescending)
query = query.OrderByDescending(orderByExpression);
else
query = query.OrderBy(orderByExpression);
}
}With that in place, the code in BlazorRepositoryDemo (in EFRepository.GetAsync) can be changed to this. Note that it no longer calls GetAllAsync and passes the IQueryable<TEntity> DbSet directly into the filter:
public async Task<IEnumerable<TEntity>> GetAsync(QueryFilter<TEntity> Filter)
{
//var allitems = (await GetAllAsync()).ToList();
//return await Task.FromResult(Filter.GetFilteredList(allitems));
var filteredItems = Filter.GetFilteredList(dbSet);
return await Task.FromResult(filteredItems);
}This will then give you real database server side filtering (and ordering). See here for the query log (taken from SQL Server Profiler):
SELECT [c].[Id], [c].[Email], [c].[Name]
FROM [Customer] AS [c]
WHERE LOWER([c].[Name]) LIKE N'j%'
ORDER BY [c].[Name] DESC
Note #1 Unfortunately this case-sensitivity because SQL server treats string comparisons as case-insensitive by default. But this can be changed on SQL database side by using an appropriate collation.
Note #2 The OrderBy expression is still not quite correct because it only works with string typed columns. I don't know yet how to call Expression.Lambda when some argument types (here: the last argument for the return type) are only known at runtime (here: prop.PropertyType).
Best regards,
Chris
PS: Sorry for not providing a clean PR, but the change affects two repos.
Dear Carl,
there's a problem with your current implementation: When using the
"efcustomers"controller, the query first returns all records from the database and then filtering is done only in memory. This might be a no-go for most real-world applications.But it can be fixed in AvnRepository (in
QueryFilter.GetFilteredList) by changing the query expressions so that they are accepted by Entity Framework Core. The trick is to avoid reflection and build simple expressions that are supported EF Core (e. g.c => c.Name,String.StartsWith, ...).Here's my patch for the
StartsWithoperator (as an example):A similar change is required for
OrderByPropertyName. Here's my patch:With that in place, the code in BlazorRepositoryDemo (in
EFRepository.GetAsync) can be changed to this. Note that it no longer callsGetAllAsyncand passes theIQueryable<TEntity>DbSet directly into the filter:This will then give you real database server side filtering (and ordering). See here for the query log (taken from SQL Server Profiler):
Note #1 Unfortunately this case-sensitivity because SQL server treats string comparisons as case-insensitive by default. But this can be changed on SQL database side by using an appropriate collation.
Note #2 The
OrderByexpression is still not quite correct because it only works with string typed columns. I don't know yet how to callExpression.Lambdawhen some argument types (here: the last argument for the return type) are only known at runtime (here:prop.PropertyType).Best regards,
Chris
PS: Sorry for not providing a clean PR, but the change affects two repos.