EF Core SQL Server NodaTime Supported Methods

DATEADD Function

The DATEADD function adds a specified number value (as a signed integer) to a specified datepart of an input date value, and then returns that modified value.

The SQL DATEADD function is supported for the following types.

• Instant (extension methods)
• OffsetDateTime (native and some extension methods)
• LocalDateTime (native and some extension methods)
• LocalDate (native and some extension methods)
• LocalTime (native and some extension methods)
• Duration (native and some extension methods)

The SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime provides the following methods which use the DATEADD function.

• PlusYears
• PlusMonths
• PlusDays
• PlusHours
• PlusMinutes
• PlusSeconds
• PlusMilliseconds

Let's consider the following simple example.

public static void Example1()
{
    using (EmployeeContext context = new EmployeeContext())
    {
        var employees = context.Employees
            .Where(e => e.BirthDate.PlusYears(35) >= Instant.FromUtc(2000, 1, 1, 1, 0))
            .ToList();

        foreach (var emp in employees)
        {
            Console.WriteLine("Name: {0} \t Salary: {1}", emp.Name, emp.Salary);
        }
        
    }
}

Let's run the above example and you will see the following output.

Name: Carson Alexander   Salary: 10000
Name: Meredith Alonso    Salary: 30000
Name: Gytis Barzdukas    Salary: 40500
Name: Yan Li     Salary: 32000

Now to see the internal query generated by the EF Core, we need to call the ToQueryString method as shown below.

public static void Example2()
{
    using (EmployeeContext context = new EmployeeContext())
    {
        var employees = context.Employees
            .Where(e => e.BirthDate.PlusYears(35) >= Instant.FromUtc(2000, 1, 1, 1, 0));

        Console.WriteLine(employees.ToQueryString());
    }
}

Let's run the above example and you will see the following output.

SELECT [e].[EmployeeId], [e].[BirthDate], [e].[Name], [e].[Salary]
FROM [Employees] AS [e]
WHERE DATEADD(year, CAST(35 AS int), [e].[BirthDate]) >= '2000-01-01T01:00:00.0000000Z'