Skip to content

Instantly share code, notes, and snippets.

@dasiths
Last active January 23, 2022 12:17
Show Gist options
  • Save dasiths/19b885c58442226d9fc8b89bc78511e4 to your computer and use it in GitHub Desktop.
Save dasiths/19b885c58442226d9fc8b89bc78511e4 to your computer and use it in GitHub Desktop.
EF Core string to datetime/datetimeoffset value converter example with CAST SQL generation
using Microsoft.EntityFrameworkCore;
Console.WriteLine("Hello, World!");
using var x = new MyDbContext();
x.Database.EnsureCreated();
var paramValueDateTimeOffset = DateTimeOffset.Now.AddDays(-1);
var paramValueDateTime = DateTime.Now.AddDays(-1);
var query = x.CustomerLeases.Where(c => c.TextDateTime >= DateTimeOffset.Now
&& c.TextDateTime >= DateTime.Now
&& c.TextDateTime >= paramValueDateTimeOffset
&& c.TextDateTime >= paramValueDateTime
);
// If you use date param inside the query it will not generate a SQL statement with CAST for the column
// But if you use the datetimeoffset parameter type it will generate the CAST statement for the column
/*
DECLARE @__paramValueDateTimeOffset_0 datetimeoffset = '2022-01-22T23:16:28.2471153+11:00';
DECLARE @__paramValueDateTime_1 nvarchar(48) = N'2022-01-22 23:16:28.2494416';
SELECT [c].[Id], [c].[TextDateTime]
FROM [CustomerLeases] AS [c]
WHERE (((CAST([c].[TextDateTime] AS datetimeoffset) >= SYSDATETIMEOFFSET()) AND
([c].[TextDateTime] >= GETDATE())) AND (CAST([c].[TextDateTime] AS datetimeoffset) >= @__paramValueDateTimeOffset_0)) AND
([c].[TextDateTime] >= @__paramValueDateTime_1)
*/
Console.WriteLine(query.ToQueryString());
var result = query.ToList();
Console.ReadLine();
public class MyDbContext : DbContext
{
public DbSet<CustomerLease> CustomerLeases { get; set; }
public MyDbContext()
{
}
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseSqlServer("data source=.;Initial Catalog = TestDb; Integrated Security = True;");
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder
.Entity<CustomerLease>()
.Property(c => c.TextDateTime)
.HasConversion<string>();
base.OnModelCreating(modelBuilder);
}
}
public class CustomerLease
{
public int Id { get; set; }
public DateTime TextDateTime { get; set; }
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment