Multiple locales affecting filtering by date

[DrGriff]

Describe the bug I get an error when the locale of my webserver is different to the locale of the SQL Server when filtering by date. It seems to be based on the string format of the date.

To Reproduce This is a Blazor Server application. SQL Server is on a machine whose locale is "en-US" and the webserver is on a machine whose locale is "en-GB".

I have a table where one column (DateCreated) has the datatype of DateTime. The filter recognizes the type and asks for my filter value in the format "dd/mm/yyyy". This is the normal format for the en-GB locale: since this is Blazor Server, I guess that this is determined by the locale of my webserver rather than the locale of my browser (both of which are en-GB).

I fill in the value as "20/10/2015" (20th October 2015) and hit apply. When this hits the method in the service I see that the query contains:

DateCreated12015-10-20

So, this is in the format 'yyyy-mm-dd' which is great. However, when it calls 'server.ItemsToDisplay' I then get an exception:

Microsoft.Data.SqlClient.SqlException: 'Conversion failed when converting date and/or time from character string.'

I happen to use a ViewModel and expose that rather than my EF entity. When setting it's property, I changed it from: vm.DateCreated = e.DateCreated to vm.DateCreated = new DateTime(e.DateCreated.Ticks, DateTimeKind.Local),

And now the error is:

Message=The LINQ expression 'Where( source: OrderBy<Destination, string>( source: DbSet, keySelector: (d) => d.DestinationName), predicate: (d) => new DateTime( d.DateCreated.Ticks, Local ) == 20/10/2015 00:00:00)' could not be translated.

So you can see from '== 20/10/2015 00:00:00' that the 'en-GB' locale being used here rather than an ISO format. I'm not sure if this is a problem with the Blazor Grid, or with EF itself.

Expected behavior Would expect the date time to be locale-independant in the query.

Desktop (please complete the following information):

• OS: Windows
• Chrome
• Latest

[quangdat88]

Filter datetime is not exactly. You could use DATEDIFFDAY for compare datetime in grid

[gustavnavar]

I can not reproduced this issue.

My database server is installed in "en-US" locale and the application is running in "en-GB" locale and I got no error.

I've also tested the application in a server with "es-ES" locale and it works as expected.

Can you share the blazor page code and the service/repository to access the database? It will also help if you share the model and view model.

[quangdat88]

I change in GridShared Filtering/Types/DateTimeFilterType.cs

public override Expression GetFilterExpression(Expression leftExpr, string value, GridFilterType filterType) { Expression binaryExpression = null; var dateExpr = Expression.Property(leftExpr, leftExpr.Type, "Date"); var dateDiffMethod = typeof(SqlServerDbFunctionsExtensions).GetMethod("DateDiffDay", new[] { typeof(DbFunctions), typeof(DateTime), typeof(DateTime) }); var dateObjn = GetTypedValue(value); var dateDiffExpression = Expression.Call(dateDiffMethod, Expression.Default(typeof(DbFunctions)), Expression.Constant(dateObjn, typeof(DateTime)), dateExpr);

        switch (filterType)
        {
            case GridFilterType.Equals:
                binaryExpression = Expression.MakeBinary(ExpressionType.Equal, dateDiffExpression, Expression.Constant(0, typeof(int)));
                break;
            case GridFilterType.GreaterThan:
                binaryExpression = Expression.MakeBinary(ExpressionType.GreaterThan, dateDiffExpression, Expression.Constant(0, typeof(int)));
                break;
            case GridFilterType.LessThan:
                binaryExpression = Expression.MakeBinary(ExpressionType.LessThan, dateDiffExpression, Expression.Constant(0, typeof(int)));
                break;
            case GridFilterType.GreaterThanOrEquals:
                binaryExpression = Expression.MakeBinary(ExpressionType.GreaterThanOrEqual, dateDiffExpression, Expression.Constant(0, typeof(int)));
                break;
            case GridFilterType.LessThanOrEquals:
                binaryExpression = Expression.MakeBinary(ExpressionType.LessThanOrEqual, dateDiffExpression, Expression.Constant(0, typeof(int)));
                break;
            default:
                binaryExpression = Expression.MakeBinary(ExpressionType.Equal, dateDiffExpression, Expression.Constant(0, typeof(int)));
                break;
        }

        return binaryExpression;
    }

[DrGriff]

Hi Gustavo

I have obfuscated the names and removed irrelevant columns, hopefully this will give you what you need. The DB is SQL Server and the field in question is [DateCreated] [datetime] NOT NULL

My Razor Page

protected override async Task OnInitializedAsync()
{
    Action<IGridColumnCollection<MyViewModel>> columns = c =>
    {
        c.Add().RenderComponentAs<AllDeleteButton>(new List<Action<object>>() { this.OnDelete });
        c.Add().RenderComponentAs<AllEditButton>();
        c.Add(o => o.Id);
        c.Add(o => o.DateCreated).Format("{0:yyyy-MM-dd}");
    };

    var query = new QueryDictionary<StringValues>();
    query.Add("grid-page", "1");

    var client = new GridClient<MyViewModel>(q => this.Service.GetAllRecords(columns, q), query, false, "myGrid", columns)
        .Filterable()
        .Searchable()
        .Sortable()
        .WithGridItemsCount()
        .WithMultipleFilters();

    _grid = client.Grid;

    _task = client.UpdateGrid();
    await _task;
}

My View Model

public class MyViewModel
{
    public string Id { get; set; } = string.Empty;
    public DateTime DateCreated { get; set; }
}

My Service

private MyContext Context => new MyContext(new DbContextOptions<MyContext>());

public ItemsDTO<MyViewModel> GetAllRecords(Action<IGridColumnCollection<MyViewModel>> columns, QueryDictionary<StringValues> query)
{
    using var context = this.Context;

    IQueryable<FirstEntity> firsts = context.Firsts
        .AsNoTracking()
        .AsQueryable();

    var dvm = firsts.Select(d => new MyViewModel
    {
        // DateCreated = new DateTime(d.DateCreated.Ticks, DateTimeKind.Local),
        DateCreated = d.DateCreated,
        Id = d.FirstId
    });

    var server = new GridServer<MyViewModel>(dvm, new QueryCollection(query), true, "myGrid", columns, 10)
        .Filterable()
        .Searchable()
        .Sortable()
        .WithGridItemsCount()
        .WithMultipleFilters();

    return server.ItemsToDisplay;
}

My Context

public class MyContext : DbContext
{
    public MyContext(DbContextOptions<MyContext> options)
        : base(options)
    {
    }
    public DbSet<FirstEntity> Firsts { get; set; } = null!;
    public DbSet<SecondEntity> Seconds { get; set; } = null!;

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (optionsBuilder != null)
        {
            if (optionsBuilder.IsConfigured == false)
            {
                IConfigurationRoot configuration = new ConfigurationBuilder()
                    .SetBasePath(AppDomain.CurrentDomain.BaseDirectory)
                    .AddJsonFile("appsettings.json")
                    .Build();

                optionsBuilder
                    .UseSqlServer(configuration.GetConnectionString("MyDatabase"), providerOptions => providerOptions.CommandTimeout(60));
            }
        }

        base.OnConfiguring(optionsBuilder);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        if (modelBuilder == null)
        {
            throw new ArgumentNullException(nameof(modelBuilder), $"The parameter {nameof(modelBuilder)} may not be null.");
        }

        modelBuilder
            .Entity<FirstEntity>(e =>
            {
                e.HasKey(i => i.FirstId);
                e.Property(i => i.FirstId).HasColumnName("FirstIDInDbTable");
            });

        modelBuilder
            .Entity<SecondEntity>(e =>
            {
                e.HasKey(i => i.SecondName);
                e.Property(i => i.SecondName).HasColumnName("SecondNameInDbTable");
            });

        modelBuilder
            .Entity<LinkingEntity>(e =>
            {
                e.HasKey(i => new { i.FirstId, i.SecondName });
                e.Property(i => i.SecondName).HasColumnName("SecondNameInDbLinkingTable");
                e.Property(i => i.FirstId).HasColumnName("FirstIDInDbLinkingTable");
            });

        // Relationships
        modelBuilder.Entity<LinkingEntity>()
            .HasOne(svd => svd.FirstEntity)
            .WithMany(d => d.FirstsSeconds)
            .HasForeignKey(svd => svd.FirstId);

        modelBuilder.Entity<LinkingEntity>()
            .HasOne(svd => svd.SecondEntity)
            .WithMany(v => v.FirstsSeconds)
            .HasForeignKey(svd => svd.SecondName);

        base.OnModelCreating(modelBuilder);
    }
}

Entities

public class FirstEntity
 {
     public string FirstId { get; set; } = string.Empty;
     public string FirstName { get; set; } = string.Empty;
     public DateTime DateCreated { get; set; }

     public List<LinkingEntity>? FirstsSeconds { get; set; }
 }

 public class SecondEntity
 {
     public string SecondName { get; set; } = string.Empty;

     public List<LinkingEntity>? FirstsSeconds { get; set; }
 }

  public class LinkingEntity
  {
      public string FirstId { get; set; } = string.Empty;
      public FirstEntity? FirstEntity { get; set; }

      public string SecondName { get; set; } = string.Empty;
      public SecondEntity? SecondEntity { get; set; }
  }

[gustavnavar]

Hi,

if you want to use View Models as source of the grid, the best option is to use keyless entities: https://docs.microsoft.com/en-us/ef/core/modeling/keyless-entity-types

This is even more important with EF Core 3.0 because query client evaluation has been disabled and all queries must be executed on the database side.

First of all you have to create a view on the database. You can use the SQL Management Studio GUI or you can execute the following sql script:

    CREATE VIEW [dbo].[View_MyViewModel]
    AS
    SELECT        dbo.FirstEntity.FirstId AS Id, dbo.FirstEntity.DateCreated 
    FROM            dbo.FirstEntity

    GO

Then you have to add the following code in the OnModelCreating method of the context:

    modelBuilder.Entity<MyViewModel>(eb =>
    {
        eb.HasNoKey();
        eb.ToView("View_MyViewModel");
    });

and the following attribute in the context:

    public DbSet<MyViewModel> MyViewModel { get; set; }

And finally you have to modify the service to get the source from the view model:

    public ItemsDTO<MyViewModel> GetAllRecords(Action<IGridColumnCollection<MyViewModel>> columns, QueryDictionary<StringValues> query)
{
    using var context = this.Context;

    var server = new GridServer<MyViewModel>(context.MyViewModel, new QueryCollection(query), true, "myGrid", columns, 10)
        .Filterable()
        .Searchable()
        .Sortable()
        .WithGridItemsCount()
        .WithMultipleFilters();

    return server.ItemsToDisplay;
}

[DrGriff]

I removed the ViewModel. My initial design was never to expose my Entities to Razor, just bespoke ViewModels: that way I can separate UI logic (e.g. form validation etc) from the Model. However, I guess when doing a read-only view of data (in the Grid) then there's no benefit to using the View Model.

So, I changed my Service accordingly. It is now:

private MyContext Context => new MyContext(new DbContextOptions<MyContext>());

public ItemsDTO<MyViewModel> GetAllRecords(Action<IGridColumnCollection<MyViewModel>> columns, QueryDictionary<StringValues> query)
{
    using var context = this.Context;

    IQueryable<FirstEntity> firsts = context.Firsts
        .AsNoTracking()
        .AsQueryable();

    var server = new GridServer<MyViewModel>(firsts , new QueryCollection(query), true, "myGrid", columns, 10)
        .Filterable()
        .Searchable()
        .Sortable()
        .WithGridItemsCount()
        .WithMultipleFilters();

    return server.ItemsToDisplay;
}

But, still the problem remains.

Microsoft.Data.SqlClient.SqlException HResult=0x80131904 Message=Conversion failed when converting date and/or time from character string. Source=Core Microsoft SqlClient Data Provider StackTrace: at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows) at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) at Microsoft.Data.SqlClient.SqlDataReader.Read() at Microsoft.EntityFrameworkCore.Storage.RelationalDataReader.Read() at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.QueryingEnumerable1.Enumerator.MoveNext() at System.Linq.Enumerable.Single[TSource](IEnumerable1 source) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) at System.Linq.Queryable.Count[TSource](IQueryable1 source) at GridMvc.GridBase1.PreProcess() at GridMvc.GridBase1.PrepareItemsToDisplay() at GridMvc.SGrid1.GetItemsToDisplay() at GridMvc.Server.GridServer1.get_ItemsToDisplay() at MyApplication.Data.MyService.GetAllRecords(Action1 columns, QueryDictionary`1 query) in C:\PathToMyApplication\Data\MyService.cs:line 58

[gustavnavar]

Some references to the "MyViewModel" class remain in your code:

public ItemsDTO<MyViewModel> GetAllRecords(Action<IGridColumnCollection<MyViewModel>> columns, QueryDictionary<StringValues> query)

and

var server = new GridServer<MyViewModel>

And I supose that there will also remain in the blazor page. Could you test the code using only "FirstEntity"?

[gustavnavar]

I´ve created a new repository with the code that I used to reproduce this issue. You can find it in https://github.com/gustavnavar/MultiLocales

It connects to a SQL Server database created in Azure with en-US locale.

You can download and test it against this database or upload the included database (in the Data directory) on your own server.

This will help in determining where the issue is.

[gustavnavar]

Any update from your side about this issue?

[comichael]

We struggled with the same issue, changing datatype from datetime to datetime2 in Sql Server fixed it.