Efcore: Nullable DateTimeOffset .Value is executed locally in F#

[Kimserey]

I can't seem to achieve the same semantic between a C# query and a F# query.
Do I need to do something extra to produce the same behavior as C#?

Here is the record type and db context that I am using:

// From a .NET Standard C# library
public class BlogRecord
{
    public int Id { get; set; }
    public DateTimeOffset? CreationDate { get; set; }
}

public class BloggingContext : DbContext
{
    public BloggingContext(DbContextOptions<BloggingContext> options)  : base(options) { }
    public DbSet<BlogRecord> Blogs { get; set; }
}

In C#, the following query:

var blogs = dbContext.Blogs
    .Where(x => x.CreationDate.HasValue && x.CreationDate.Value > date)
    .ToList();

Results in the expected SQL query:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (11ms) [Parameters=[@__date_0='?'], CommandType='Text', CommandTimeout='30']
      SELECT "x"."Id", "x"."CreationDate", "x"."Serial", "x"."Url"
      FROM "Blogs" AS "x"
      WHERE "x"."CreationDate" IS NOT NULL AND ("x"."CreationDate" > @__date_0)

But in F#, the (kind of) same query:

let blogs = dbContext.Blogs
    .Where(fun (x: BlogRecord) -> x.CreationDate <> Nullable() && x.CreationDate.Value > date)
    .ToList()

Results in a warning whereby the .Value can't be translated and the function is introducing a copyOfStruct:

warn: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'where (copyOfStruct => Convert(copyOfStruct, DateTimeOffset).Invoke([x].CreationDate) > 26/11/2018 21:58:41 +00:00)' could not be translated and will be evaluated locally.

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (79ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [x].[Id], [x].[CreationDate], [x].[Serial], [x].[Url]
      FROM [Blogs] AS [x]
      WHERE [x].[CreationDate] IS NOT NULL

Further technical details

EF Core version: 2.1.4
Database Provider: Microsoft.EntityFrameworkCore.SqlServer and Sqlite

[Kimserey]

After more digging, I realized that the problem revolves around the fact that the entity type coming from C# is considered as a F# class. If it was a record type, the query would be translated properly.

[divega]

Hey @cartermp, F# question here:

It seems the in the expression three generated by the compiler for a predicate like x.CreationDate.Value > date looks like (using expression printer syntax) `copyOfStruct => Convert(copyOfStruct, DateTimeOffset).Invoke([x].CreationDate) > 26/11/2018 21:58:41 +00:00)'. This seems somewhat unusual.

Although we can possibly “teach” EF Core to interpret this, we wanted to make sure that it is by-design.

@Kimserey also mentions that he is seing different behavior depending on whether you use a record type or a class, which we don’t understand either.

[Kimserey]

Thanks @divega for following up.

Hi @cartermp, just to add more information, here's the code working for a record type:

[<CLIMutable>]
type BlogRecord =
    {
        id: int
        url: string
        creationDate: Nullable<DateTimeOffset>
    }

type BloggingContext(opts: DbContextOptions<BloggingContext>) =
    inherit DbContext(opts)

    [<DefaultValue>]
    val mutable blogs: DbSet<BlogRecord>
    member this.Blogs
        with get() = this.blogs
        and set v = this.blogs <- v

When making a query context.Blogs.Where(fun x -> x.creationDate.HasValue && x.creationDate.Value < now) will translate properly:

Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "x"."id", "x"."creationDate", "x"."url"
      FROM "Blogs" AS "x"
      WHERE "x"."creationDate" IS NOT NULL AND ("x"."creationDate" < '2018-11-30 10:58:53.6627609+00:00')

But with a class type:

type BlogRecord() =
    member val id = Unchecked.defaultof<int> with get, set
    member val url = Unchecked.defaultof<string> with get, set
    member val creationDate = Unchecked.defaultof<Nullable<DateTimeOffset>> with get, set

(same db context)

Will fail translation:

warn: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'where (copyOfStruct => (copyOfStruct != null).Invoke([x].creationDate) AndAlso (copyOfStruct => Convert(copyOfStruct, DateTimeOffset).Invoke([x].creationDate) < 30/11/2018 11:01:57 +00:00))' could not be translated and will be evaluated locally.

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "x"."id", "x"."creationDate", "x"."url"
      FROM "Blogs" AS "x"

The issue being that the BlogRecord type in our project is coming from a C# project hence is treated as a class type.

[cartermp]

Also tagging @dsyme - there shouldn't be a difference in the class vs. record if the underlying type is the same.

[Kimserey]

Thanks @cartermp for the follow up, I am not sure what you mean by "there shouldn't be a difference in the class vs. record if the underlying type is the same."

What do you call the _underlying type_?

[cartermp]

@Kimserey Sorry, I mean that the underlying field type is the same (Nullable<DateTimeOffset>), so in theory it shouldn't matter, since a Record field gets compiled down to a property, and in a class it is also a property. In theory, you should see the same behavior for both, but given that this is not the case there's either a legitimate issue or a weird and complicated _by design_ behavior that I'm unaware of 😢

[Kimserey]

Right, I understand now what you meant. I might be digging at the wrong place but It seems that the problem comes from the copyOfStruct which only appears when a class is used.

For example the following types:

type X = { date: Nullable<DateTimeOffset>  }
type X'() =
    member val date: Nullable<DateTimeOffset> = Nullable(DateTimeOffset.Now) with get, set

let quote = <@ fun (x: X) -> x.date.HasValue @>
let quote' = <@ fun (x: X') -> x.date.HasValue @>

Will produce the following quotations:

val quote : Quotations.Expr<(X -> bool)> =
  Lambda (x, PropertyGet (Some (PropertyGet (Some (x), date, [])), HasValue, []))

val quote' : Quotations.Expr<(X' -> bool)> =
  Lambda (x, Let (copyOfStruct, PropertyGet (Some (x), date, []), PropertyGet (Some (copyOfStruct), HasValue, [])))

Which EF doesn't seem to understand.

[ajcvickers]

Triage: moving this issue to the backlog to cover translating these expression in EF Core. However, we would also that anyone wanting to work on this hold comment here because it may not be worth implementing this until more of the query 3.0 work is done since the code here is dependent on Relinq, which is being removed.