Date filter in datagridcolumn truncated

manu · November 19, 2019, 4:39pm

I have a datagrid column of time date-time. If it is too close to the right side, it gets truncated when opening the filter:

enchev · November 20, 2019, 4:50am

We will do our best to fix this in one of coming updates.

enchev · November 20, 2019, 9:23am

Just published Radzen.Blazor 1.1.35 with this issue fixed:

manu · November 20, 2019, 10:02am

Thank you very much. I can confirm you that the issue is not present anymore.

However, I can't use the datagrid to filter by dates as the dynamic linq gives me this error:

System.NotSupportedException: 'LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)' method, and this method cannot be translated into a store expression.'

This is my code:

    protected async void GridLoadData(LoadDataArgs query)
    {
        var queryItems = _auditLogService.Query();

        if (!string.IsNullOrEmpty(query.Filter)) {
            queryItems = queryItems.Where(query.Filter);
        }

        if (!string.IsNullOrEmpty(query.OrderBy)) {
            queryItems = queryItems.OrderBy(query.OrderBy);
        } else {
            queryItems = queryItems.OrderBy(o => o.Id);
        }

        count = queryItems.Count();

        if (query.Skip.HasValue) {
            queryItems = queryItems.Skip(query.Skip.Value);
        }

        if (query.Top.HasValue) {
            queryItems = queryItems.Take(query.Top.Value);
        }

        items = queryItems.ToList();
    }

I don't understand why the datagrid creates the query data in text form. It is only asking for trouble. You know the underlying column type using TItem and Property, and you can get an IQueryable in the LoadData event and apply strongly typed filtering, sorting and paging to it instead. Lots easier for the developer and no more conversion problems.

enchev · November 20, 2019, 10:20am

If you have IQueryable you can simply assign it to Data property of the DataGrid and everything will work automatically. You do not need LoadData at all. More info can be found here:

https://blazor.radzen.com/datagrid

github.com

akorchev/blazor.radzen.com/blob/master/Pages/DataGridPage.razor#L19


<RadzenExample Name="DataGrid">
    <RadzenGrid AllowFiltering="true" FilterCaseSensitivity="FilterCaseSensitivity.CaseInsensitive" AllowPaging="true" PageSize="4"
                AllowSorting="true" Data="@employees" TItem="Employee" ColumnWidth="200px">
        <Columns>
            <RadzenGridColumn TItem="Employee" Property="EmployeeID" Title="Employee ID" />
            <RadzenGridColumn TItem="Employee" Property="Photo" Title="Photo" Sortable="false" Filterable="false">
                <Template Context="data">
                    <RadzenImage Path="@data?.Photo" />
                </Template>
            </RadzenGridColumn>
            <RadzenGridColumn TItem="Employee" Property="LastName" Title="Last Name" />
            <RadzenGridColumn TItem="Employee" Property="FirstName" Title="First Name" />
            <RadzenGridColumn TItem="Employee" Property="Title" Title="Title" />
            <RadzenGridColumn TItem="Employee" Property="TitleOfCourtesy" Title="Title Of Courtesy" />
            <RadzenGridColumn TItem="Employee" Property="BirthDate" Title="Birth Date">
                <Template Context="data">
                    @String.Format("{0:d}", data.BirthDate)
                </Template>
            </RadzenGridColumn>
            <RadzenGridColumn TItem="Employee" Property="HireDate" Title="Hire Date">
                <Template Context="data">

manu · November 20, 2019, 11:02am

Thank you Vladimir. I first tried that but it gave me this error:

blazor.server.js:15 [2019-11-20T10:31:41.250Z] Error: System.NotSupportedException: The method 'Skip' is only supported for sorted input in LINQ to Entities. The method 'OrderBy' must be called before the method 'Skip'.
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.OrderByLifter.PassthroughOrderByLifter.Skip(DbExpression k)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.OrderByLifter.Skip(DbExpressionBinding input, DbExpression skipCount)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Skip(DbExpressionBinding input, DbExpression skipCount)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SkipTranslator.TranslatePagingOperator(ExpressionConverter parent, DbExpression operand, DbExpression count)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.PagingTranslator.TranslateUnary(ExpressionConverter parent, DbExpression operand, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.UnarySequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateSet(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.UnarySequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Convert()
   at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass41_0.<GetResults>b__1()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass41_0.<GetResults>b__0()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__31_0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Radzen.Blazor.RadzenGrid`1.get_PagedView()
   at Radzen.Blazor.RadzenGrid`1.BuildRenderTree(RenderTreeBuilder __builder)
   at Microsoft.AspNetCore.Components.ComponentBase.<.ctor>b__6_0(RenderTreeBuilder builder)
   at Microsoft.AspNetCore.Components.Rendering.ComponentState.RenderIntoBatch(RenderBatchBuilder batchBuilder, RenderFragment renderFragment)
   at Microsoft.AspNetCore.Components.RenderTree.Renderer.RenderInExistingBatch(RenderQueueEntry renderQueueEntry)
   at Microsoft.AspNetCore.Components.RenderTree.Renderer.ProcessRenderQueue()

So I tried the other approach. However, presorting the IQueryable makes it work, so I don't need to use the LoadData event anymore. However, even when assigning the IQueryable to the Data property, the DateTime filtering is not working:

[2019-11-20T10:40:03.076Z] Error: System.NotSupportedException: LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)' method, and this method cannot be translated into a store expression.
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.DefaultTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.BinaryTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateSet(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.UnarySequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateSet(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.UnarySequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Convert()
   at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass41_0.<GetResults>b__1()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass41_0.<GetResults>b__0()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__31_0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Radzen.Blazor.RadzenGrid`1.get_PagedView()
   at Radzen.Blazor.RadzenGrid`1.BuildRenderTree(RenderTreeBuilder __builder)
   at Microsoft.AspNetCore.Components.Rendering.ComponentState.RenderIntoBatch(RenderBatchBuilder batchBuilder, RenderFragment renderFragment)
   at Microsoft.AspNetCore.Components.RenderTree.Renderer.RenderInExistingBatch(RenderQueueEntry renderQueueEntry)
   at Microsoft.AspNetCore.Components.RenderTree.Renderer.ProcessRenderQueue()

enchev · November 20, 2019, 11:13am

I'm not aware of such issues. Is your IQueryable DbContext DbSet or something custom? As far as I know the DataGrid can page and filter by dates normally with Microsoft SQL Server, MySQL, PostgreSQL and SQLite DbSets.

Our demos for example with SQLite:

github.com

akorchev/blazor.radzen.com/blob/master/Data/NorthwindContext.cs

using Microsoft.AspNetCore.Http;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System;
using System.IO;
using System.Threading;

namespace NorthwindBlazor.Data
{
    public partial class NorthwindContext : Microsoft.EntityFrameworkCore.DbContext
    {
        public NorthwindContext(DbContextOptions<NorthwindContext> options) : base(options)
        {
        }

        public NorthwindContext()
        {
        }

        private readonly IHttpContextAccessor _httpContextAccessor;

This file has been truncated. show original

manu · November 20, 2019, 11:27am

Vladimir, I'm using EF 6.3.0 (not EF Core) with Microsoft SQL Server.

My IQueriable is a DbSet that I obtain from the DbContext calling:

return base.Set()

from a class inheriting DbContext.

I was using AutoMapper to map the database entities to Data transfer objects as it can make the conversion without evaluating the IQueryable, but before reporting the bug I tried getting the database entities from the DbContext to avoid potential AutoMapper IQueryable handling errors.

enchev · November 20, 2019, 11:39am

I see. Never tested the DataGrid against EF, only using EF Core. I'll check to see if we can fix DateTime filtering with EF and I'll let you know.

manu · November 20, 2019, 11:41am

Thank you very much Vladimir!

manu · November 21, 2019, 11:09pm

Hello Vladimir. With the version 1.2.4 works fine.

I don't know if you touched something or it is related to:

enchev · November 22, 2019, 5:20am

Yes, I’ve changed the DateTime expression to support EF versions prior to EF 7 / Core.

manu · November 22, 2019, 8:29am

Thank you very much Vladimir!