Complex Sorting and Grouping on DataBase not working.
Hey people (:
We are currently stuck on an issue regarding filtering and grouping in grids.
I cannot provide an exmaple project, because the issues requires database connection - for local data everything works fine. The issue is the following:
If we have complex data on our database like
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
public File File { get; set; }
}
public class File
{
public int? FileId { get; set; }
public string Name { get; set; }
}
And in our grid we mainly display our Orders, but also the file name like here in the last column:
<SfGrid DataSource="@Orders" AllowPaging="true" AllowSorting="true" AllowFiltering="true" AllowGrouping="true">
<SfDataManager AdaptorInstance="@typeof(CustomAdaptor)" Adaptor="Adaptors.CustomAdaptor"></SfDataManager>
<GridPageSettings PageSize="5"></GridPageSettings>
<GridSelectionSettings CheckboxOnly="true" CheckboxMode="CheckboxSelectionType.ResetOnRowClick" Type="SelectionType.Multiple"></GridSelectionSettings>
<GridSortSettings Columns="@(new List<GridSortColumn>{ new GridSortColumn(){Field = "File.Name", Direction = SortDirection.Ascending}})"></GridSortSettings>
<GridColumns>
<GridColumn Type="ColumnType.CheckBox" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field="File.Name" HeaderText="FileName" Width="120"></GridColumn>
<GridColumn>
<Template>
@(((Order)context).OrderDate)
</Template>
</GridColumn>
</GridColumns>
<GridEvents TValue="Order"
RowDeselected="@OnRowSelected"
RowSelected="@OnRowSelected"
OnRecordClick="@OnRowClicked"></GridEvents>
</SfGrid>
Additionally we use a custom adaptor, this one is very basic and does nothing besides calling the DataOperation methods.
So when wie call PerformSorting
if (dm.Sorted != null && dm.Sorted.Count > 0)
{
// Sorting
DataSource = DataOperations.PerformSorting(DataSource, dm.Sorted);
}
We end up in the QueryableExtensions.cs file in the OrderBy method:
public static IQueryable OrderBy(
this IQueryable source,
string propertyName,
Type sourceType)
{
if (source == null)
throw new ArgumentNullException(nameof (source));
ParameterExpression paramExpression = Expression.Parameter(source.ElementType, sourceType?.Name);
LambdaExpression propertyNullCheck = QueryableExtensions.GetLambdaWithComplexPropertyNullCheck(source, propertyName ?? string.Empty, paramExpression, sourceType);
return source.Provider.CreateQuery((Expression) Expression.Call(typeof (Queryable), nameof (OrderBy), new Type[2]
{
source.ElementType,
propertyNullCheck.Body.Type
}, source.Expression, (Expression) propertyNullCheck));
}
The problem: For complex data like File.Name we get a ComplexPropertyNullCheck LambdaExpression seen on the screenshot:
The Convert Method of this is not invokable in Linq, so everytime we sort we get:
"System.NotSupportedException: Unable to cast the type 'System.String' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types
at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.ValidateAndAdjustCastTypes(TypeUsage toType, TypeUsage fromType, Type toClrType, Type fromClrType)"
We get the same exception for grouping. I hope you understand our problem and can help us finding a solution for our issue.
Best
