I have a grid displaying users information including it's roles. It's working well but if the user is sorting on the column containing the roles, EF complains that it can't translate to SQL.
Here's the query:
IQueryable<DTOUtilisateurSecurite> query;
query = (_context.AspNetUsers)
.Include(e => e.Roles)
.Select(a => new DTOUtilisateurSecurite()
{
Courriel = a.Email,
Id = a.Id,
LoginUtilisateur = a.UserName,
Nom = a.FirstName + " " + a.LastName,
DescriptionRole = String.Join(",", a.Roles.Select(r => r.Name)),
DictionnaireRole = (from role in a.Roles
select new DTORoleEx()
{
Code = role.Name,
Description = role.Name,
ID = role.Id
})
});
Then I try to sort the query like that:
query.OrderBy("DescriptionRole ASC")
I understand that SQL doesn't have that column in the DB, so it can't translate. Is there a way to achieve that?
It's important to mention that this use DynamicLinq library and is part of a helper, this is the complete code for the helper:
public static class GridRequestBuilder
{
private static IQueryable<object>? RunFilters<T>(IQueryable<object>? query, FilterDescriptorCollection filters, LinqWebApiContext _context) where T : class
{
foreach (var innerFilter in filters)
{
if (innerFilter is CompositeFilterDescriptor)
{
//On charge les multi filtres
var compositeFilters = ((CompositeFilterDescriptor)innerFilter).FilterDescriptors;
//Pour chaque filtre de colonne on traite la query
query = RunFilters<T>(query, compositeFilters, _context);
}
//Il n'y a qu'une colonne a filtrer, on traite directement à partir de l'objet de filtre
else
{
query = processFilters<T>(query, innerFilter, _context);
}
}
return query;
}
public static IQueryable<object>? GetFiltered<T>(GridDataType dataType, LinqWebApiContext _context, [DataSourceRequest] DataSourceRequest request) where T : class
{
//La variable de résultat qui est une requête IQueryable requise pour le retour à la grille
IQueryable<object>? query = null;
//Si on a des filtres dans la requête on les traites, sinon on retourne tout
if (request.Filters != null)
{
//Pour chaque filtre de la requête
foreach (var filter in request.Filters)
{
//Si il a des filtres sur plusieurs colonnes, le type est différent et
//il faut boucler dans l'objet
if (filter is CompositeFilterDescriptor)
{
//On charge les multi filtres
var compositeFilters = ((CompositeFilterDescriptor)filter).FilterDescriptors;
query = RunFilters<T>(query, compositeFilters, _context);
////Pour chaque filtre de colonne on traite la query
//foreach (var compFilter in compositeFilters)
//{
// query = processFilters<T>(query, compFilter, _context);
//}
}
//Il n'y a qu'une colonne a filtrer, on traite directement à partir de l'objet de filtre
else
{
query = processFilters<T>(query, filter, _context);
}
}
}
else
{
query = BaseQuery<T>(_context);
}
//Variable qui contiendra les critères de tri
string orderCriteria = "";
//Si on a des tris
if (request.Sorts != null)
{
//Pour chaque colonne à trier, on construit la chaine de charactères
foreach (var sortItem in request.Sorts)
{
orderCriteria += sortItem.Member + " " + sortItem.SortDirection.ToString().ToLower() + ",";
}
//Si il y déjà quelque chose dans la chaine, on combine
if (orderCriteria != "")
{
orderCriteria = orderCriteria.Substring(0, orderCriteria.Length - 1);
}
}
//Si il y a quelque chose dans la variable de critère, on tri la requête
if (orderCriteria != "")
{
if (query != null)
{
query = query.OrderBy(orderCriteria);
}
}
if (query != null)
{
return query;
}
else
{
return null;
}
}
/// <summary>
/// Construit les requêtes de base pour chaque type d'objet à afficher dans une grille Kendo
/// </summary>
/// <typeparam name="T">Type de l'objet à traiter</typeparam>
/// <param name="_context">Contexte de la BD</param>
/// <returns></returns>
private static dynamic BaseQuery<T>(LinqWebApiContext _context) where T : class
{
//Si le type d'objet est un utilisateur pour la sécurité
if (typeof(T) == typeof(DTOUtilisateurSecurite))
{
IQueryable<DTOUtilisateurSecurite> query;
query = (_context.AspNetUsers).Include(e => e.Roles).Select(a =>
new DTOUtilisateurSecurite()
{
Courriel = a.Email,
Id = a.Id,
LoginUtilisateur = a.UserName,
Nom = a.FirstName + " " + a.LastName,
DescriptionRole = String.Join(",", a.Roles.Select(r => r.Name)),
DictionnaireRole = (from role in a.Roles
select new DTORoleEx()
{
Code = role.Name,
Description = role.Name,
ID = role.Id
})
});
var a = 1;
return query;
}
return new List<object>();
}
/// <summary>
/// Filtre une requête de grille Kendo
/// </summary>
/// <typeparam name="T">Type d'objet</typeparam>
/// <param name="query">Requête à filtrer</param>
/// <param name="filter">Filtre à appliquer</param>
/// <param name="_context">Contexte de la BD</param>
/// <returns></returns>
private static IQueryable<object>? processFilters<T>(IQueryable<object>? query, IFilterDescriptor filter, LinqWebApiContext _context) where T : class
{
//Convertion de l'objet en filtre avec ses propriétés accessibles
var filterObj = filter as FilterDescriptor;
//Contiendras les nom de colonnes valides de la requête pour éviter les problèmes de majuscule minuscule qui sont modifiés
//par les requête ajax
List<string> allColumns = new List<string>();
string criteria = "";
//Création d'une instance du type d'objet
object? dtoObject = Activator.CreateInstance(typeof(T));
//On extrait tous les noms de colonnes valides
var collecteColumns = dtoObject!.GetType().GetProperties();
foreach (var item in collecteColumns)
{
allColumns.Add(item.Name);
}
//Si on a un objet de filtre
if (filterObj != null)
{
if (filterObj.Value != null ||
filterObj.Operator == FilterOperator.IsNull ||
filterObj.Operator == FilterOperator.IsNotNull ||
filterObj.Operator == FilterOperator.IsEmpty ||
filterObj.Operator == FilterOperator.IsNotEmpty ||
filterObj.Operator == FilterOperator.IsNullOrEmpty ||
filterObj.Operator == FilterOperator.IsNotNullOrEmpty)
{
if (IsValidJson(filterObj.Value!.ToString()))
{
string values = filterObj.Value == null ? "" : (string)filterObj.Value;
if (typeof(T) == typeof(DTOUtilisateurSecurite))
{
List<int> rolesToFind = new List<int>();
List<DTORoleEx>? roles = JsonConvert.DeserializeObject<List<DTORoleEx>>(values);
foreach (var item in roles!)
{
rolesToFind.Add(item.ID);
}
criteria = "x => x.DictionnaireRole.Any(dr => @0.Contains(dr.ID))";
if (query == null)
{
query = ((IQueryable<object>?)BaseQuery<T>(_context)!).Where(criteria, rolesToFind);
}
else
{
query = query.Where(criteria, rolesToFind);
}
}
}
else
{
//On cherche le nom de la colonne qui correspond au filtre
string? colName = allColumns.Where(c => c.ToLower().Equals(filterObj.Member.ToLower())).FirstOrDefault();
//Si la valeur du filtre est une date, on passe la date en paramètre
if (filterObj.Value is DateTime)
{
DateTime dtFilter = (DateTime)filterObj.Value;
string dateTxt = dtFilter.Year + "-" + dtFilter.Month.ToString().PadLeft(2, '0') + "-" + dtFilter.Day.ToString().PadLeft(2, '0');
switch (filterObj.Operator)
{
case FilterOperator.IsEqualTo:
criteria = "t => t." + colName + ".Date == @0";
break;
case FilterOperator.IsNotEqualTo:
criteria = "t => t." + colName + ".Date != @0";
break;
case FilterOperator.IsGreaterThanOrEqualTo:
criteria = "t => t." + colName + ".Date >= @0";
break;
case FilterOperator.IsGreaterThan:
criteria = "t => t." + colName + ".Date > @0";
break;
case FilterOperator.IsLessThanOrEqualTo:
criteria = "t => t." + colName + ".Date <= @0";
break;
case FilterOperator.IsLessThan:
criteria = "t => t." + colName + ".Date < @0";
break;
case FilterOperator.IsNull:
criteria = "x => x." + colName + " == null";
break;
case FilterOperator.IsNotNull:
criteria = "x => x." + colName + " != null";
break;
default:
criteria = "t => t." + colName + ".Value.Date == @0";
break;
}
//Si la reqête est vide, c'est le premier filtre, donc on extrait tout avec le where
if (query == null)
{
query = ((IQueryable<object>?)BaseQuery<T>(_context))!.Where(criteria, filterObj.Value);
}
//Il y a déjà un ou plusieurs filtres d'appliqué, donc on combine avec le nouveau
else
{
query = query.Where(criteria, filterObj.Value);
}
}
//Si le filtre est de type string
if (filterObj.Value is String)
{
switch (filterObj.Operator)
{
case FilterOperator.IsEqualTo:
criteria = colName + ".Equals(\"" + (string)filterObj.Value + "\")";
break;
case FilterOperator.IsNotEqualTo:
criteria = "!" + colName + ".Equals(\"" + (string)filterObj.Value + "\")";
break;
case FilterOperator.StartsWith:
criteria = colName + ".StartsWith(\"" + (string)filterObj.Value + "\")";
break;
case FilterOperator.EndsWith:
criteria = colName + ".EndsWith(\"" + (string)filterObj.Value + "\")";
break;
case FilterOperator.Contains:
criteria = colName + ".Contains(\"" + (string)filterObj.Value + "\")";
break;
case FilterOperator.IsContainedIn:
break;
case FilterOperator.DoesNotContain:
criteria = "!" + colName + ".Contains(\"" + (string)filterObj.Value + "\")";
break;
case FilterOperator.IsNull:
criteria = "x => x." + colName + " == null";
break;
case FilterOperator.IsNotNull:
criteria = "x => x." + colName + " != null";
break;
case FilterOperator.IsEmpty:
criteria = "x => x." + colName + " == \"\"";
break;
case FilterOperator.IsNotEmpty:
criteria = "x => x." + colName + " != \"\"";
break;
case FilterOperator.IsNullOrEmpty:
criteria = "x => x." + colName + " == null || x." + colName + " == \"\"";
break;
case FilterOperator.IsNotNullOrEmpty:
criteria = "x => x." + colName + " != null || x." + colName + " != \"\"";
break;
default:
criteria = colName + ".Contains(\"" + (string)filterObj.Value + "\")";
break;
}
if (query == null)
{
query = ((IQueryable<object>?)BaseQuery<T>(_context))!.Where(criteria);
}
//il y a déjà un ou plusieurs filtre, on combine
else
{
query = query.Where(criteria);
}
}
//Si le type du filtre est numérique
if (filterObj.Value is double)
{
switch (filterObj.Operator)
{
case FilterOperator.IsEqualTo:
criteria = colName + ".Equals(" + filterObj.Value + ")";
break;
case FilterOperator.IsNotEqualTo:
criteria = "!" + colName + ".Equals(" + filterObj.Value + ")";
break;
case FilterOperator.IsGreaterThanOrEqualTo:
criteria = "x => x." + colName + " >= " + filterObj.Value;
break;
case FilterOperator.IsGreaterThan:
criteria = "x => x." + colName + " > " + filterObj.Value;
break;
case FilterOperator.IsLessThanOrEqualTo:
criteria = "x => x." + colName + " <= " + filterObj.Value;
break;
case FilterOperator.IsLessThan:
criteria = "x => x." + colName + " < " + filterObj.Value;
break;
case FilterOperator.IsNull:
criteria = "x => x." + colName + " == null";
break;
case FilterOperator.IsNotNull:
criteria = "x => x." + colName + " != null";
break;
default:
criteria = colName + ".Equals(" + filterObj.Value + ")";
break;
}
if (query == null)
{
query = ((IQueryable<object>?)BaseQuery<T>(_context))!.Where(criteria);
}
//il y a déjà un ou plusieurs filtre, on combine
else
{
query = query.Where(criteria);
}
}
}
}
else
{
if (query == null)
{
query = (IQueryable<object>?)BaseQuery<T>(_context);
}
}
}
return query;
}
private static bool IsValidJson(string? strInput)
{
if (string.IsNullOrWhiteSpace(strInput))
{
return false;
}
strInput = strInput.Trim();
if ((strInput.StartsWith("{") && strInput.EndsWith("}")) || //For object
(strInput.StartsWith("[") && strInput.EndsWith("]"))) //For array
{
try
{
var obj = JToken.Parse(strInput);
return true;
}
catch (JsonReaderException jex)
{
//Exception in parsing json
Console.WriteLine(jex.Message);
return false;
}
catch (Exception ex) //some other exception
{
Console.WriteLine(ex.ToString());
return false;
}
}
else
{
return false;
}
}
}
I found a lot of answers on the net, but I can't figure it out.
I tried adding .ToList() or .AsEnumerable() to the column. I tried to do a select in that column and concatenate it in the client. I always get the same error.
string.Join has no impmented conversion to SQL equivalent, so server side sorting is not possible.
if don't think that there is other way with current EF Core version and you have to work with workaround: Materialize query to List and transform to IQueryable again:
// filter code here
if (orderCriteria != "")
{
if (query != null)
{
query = query.ToList().AsQueryable().OrderBy(orderCriteria);
}
}
Note that, if you have pagination later, it will be applied to whole filtered recordset, which may retrieve too much data from SQL server.