DevExtreme DataSourceLoader SQL Error "Conversion failed when converting date and/or time from character string" with EF Core

Question

I am building an ASP.NET Core Web API with an Angular frontend using the DevExtreme DataGrid. I encounter a SqlException when trying to filter my grid by a date column:

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

My database (SQL Server) stores dates in a standard DateTime column. When DataSourceLoader.LoadAsync processes the filter, it seems to be translating the date filter into a SQL string literal (e.g. '2025-01-01') instead of a parameterized query (@p0). This causes a crash because of a format mismatch with the SQL Server's region settings.

I attempted to "intercept" the date filter manually to force Entity Framework Core to use a parameter, but I am struggling to cleanly remove the filter from DataSourceLoadOptions so that the DataSourceLoader doesn't try to apply it a second time (causing the crash).

My setup:

• Backend: .NET 8 / EF Core 9
• Frontend: Angular 18 / DevExtreme 24
• Database: SQL Server

Here is my code:

1. Backend Store: I am trying to manually apply the Where clause for CreatedDate and then remove it from loadOptions, but DataSourceLoader still seems to interfere or the removal logic is flaky.

public async Task<LoadResult> GetLogsAsync(string tenantId, DataSourceLoadOptions loadOptions, CancellationToken ct)
{
 // 1. Helper to fix incoming JSON strings into DateTime objects
 loadOptions.FixDateFilters("createdDate"); 
 await using var ctx = _contextFactory.CreateDbContext();
 var query = ctx.Logs.AsNoTracking();
 // 2. ATTEMPTED FIX: Manual Interception
 // I try to apply the filter manually to force a SQL Parameter (@p0)
 // and then remove it from loadOptions.
 query = ApplyDateFilter(query, loadOptions, "createdDate", x => x.CreatedDate);
 // 3. Projection
 var projectQuery = query.Select(x => new LogDto
 {
 Id = x.Id,
 Message = x.Message,
 CreatedDate = x.CreatedDate
 });
 // 4. Load
 // The crash happens here if the filter wasn't successfully removed/handled above
 return await DataSourceLoader.LoadAsync(projectQuery, loadOptions, ct);
}
private IQueryable<LogEntity> ApplyDateFilter(IQueryable<LogEntity> query, DataSourceLoadOptions loadOptions, string jsonKey, Expression<Func<LogEntity, DateTime?>> selector)
{
 // Custom logic to find the ["createdDate", ">=", "2025..."] node
 var filterNode = FindRecursive(loadOptions.Filter, jsonKey);
 if (filterNode != null && filterNode.Count > 2 && filterNode[2] is DateTime dt)
 {
 // Force EF Core to use a parameter
 query = query.Where(r => selector.Compile()(r) >= dt);
 // PROBLEM: How do I robustly remove this from loadOptions.Filter?
 // If I don't remove it, DataSourceLoader applies it again as a String, crashing SQL.
 // loadOptions.RemoveFilter(jsonKey); // This part is complex to implement correctly
 }
 return query;
}

2. Frontend (Angular): I am forcing ISO format to avoid locale issues:

initDataSource() {
 // Default filter: Show logs from the last 120 days
 const d = new Date();
 d.setDate(d.getDate() - 120);
 const dateStr = formatDate(d, "yyyy-MM-dd'T'HH:mm:ss", 'en-US');
 this.dataSource = {
 store: createStore({
 key: 'id',
 loadUrl: `${this.apiUrl}/logs`,
 // ... headers ...
 }),
 filter: [
 ['status', '=', 'Active'],
 'and',
 ['createdDate', '>=', dateStr] // Sending ISO string
 ]
 };
}

The grid configuration:

<dx-data-grid 
 [dataSource]="dataSource" 
 dateSerializationFormat="yyyy-MM-ddTHH:mm:ss">
 <dxi-column dataField="createdDate" dataType="datetime"></dxi-column>
</dx-data-grid>

What I have tried

1. Formatting: I ensured the frontend sends strict ISO 8601 (T separator).

2. Extensions: I wrote a FixDateFilters extension that parses the incoming string to a C# DateTime object before the query runs. This works, but DataSourceLoader still generates invalid SQL for it.

3. Manual Where: As shown above, I tried manually applying the Where clause. This generates correct SQL (@p0), but I can't seem to stop DataSourceLoader from also applying the filter as a string literal, which ruins the query.

What is the standard way to handle date filtering in DevExtreme + EF Core so that it always uses SQL parameters? Should I be creating a completely "Clean" DataSourceLoadOptions object (copying only Skip/Take/Sort) to pass to the loader, effectively hiding the filters from it?

• AlwaysLearning: Where's the definition for the Logs entity? Seems like createdDate uses a string type instead of DateTime.

Answer 1

DateSourceLoader parameterizes automatically Datetime objects. For that reason, you only need to convert the dateString in the filters list to a DateTime object through your extension method.

Any string that cannot be converted immediately throws FormatException.

Here is how to go about this:

 public static class DataSourceLoadOptionsExtensions
 {
 public static void FixDateFilters(
 this DataSourceLoadOptions options,
 params string[] dateFields)
 {
 if (options.Filter == null || dateFields.Length == 0)
 return;
 var fields = new HashSet<string>(
 dateFields,
 StringComparer.OrdinalIgnoreCase);
 FixDateFiltersRecursive(options.Filter, fields);
 }
 private static void FixDateFiltersRecursive(
 object node,
 HashSet<string> dateFields)
 {
 if (node is not IList list)
 return;
 // Filter condition: [field, operator, value]
 if (list.Count == 3 &&
 list[0] is string field &&
 dateFields.Contains(field))
 {
 if (list[2] is string dateString)
 {
 if (!TryParseFilterDate(dateString, out var parsedDate))
 {
 throw new FormatException(
 $"Invalid filter date for field '{field}': '{dateString}'. " +
 "Expected an ISO 8601 date string (e.g., '2025-01-01T00:00:00').");
 }
 list[2] = parsedDate;
 return;
 }
 }
 // Recurse into nested filter groups
 foreach (var item in list)
 {
 FixDateFiltersRecursive(item, dateFields);
 }
 }
 private static bool TryParseFilterDate(
 string value,
 out DateTime parsed)
 {
 return DateTime.TryParse(
 value,
 CultureInfo.InvariantCulture,
 DateTimeStyles.AssumeUniversal | DateTimeStyles.AdjustToUniversal,
 out parsed);
 }
 }

Usage

public async Task<LoadResult> GetLogsAsync(
 string tenantId,
 DataSourceLoadOptions loadOptions,
 CancellationToken ct)
{
 loadOptions.FixDateFilters("createdDate");
 await using var ctx = _contextFactory.CreateDbContext();
 var query = ctx.Logs.AsNoTracking();
 var projected = query.Select(x => new LogDto
 {
 Id = x.Id,
 Message = x.Message,
 CreatedDate = x.CreatedDate
 });
 return await DataSourceLoader.LoadAsync(projected, loadOptions, ct);
}

• Dilshan Prasad: Did not work this one