Different property order of a LINQ select expression cannot be translated

[jphilipps]

Different property order of a LINQ select expression cannot be translated

This issue has a strong connection to my findings on Stack Overflow but I think it is important to put it in the right place (hopefully). Furthermore, there is a similar issue (#3782) which highlights the usage of a tuple type for the select expression of a query.

For this issue I would like to emphasise that unfortunately, the F# LINQ expression cannot be translated, when the order of the property names of an anonymous or type record changes.

In order to evaluate the issue and make it testable I implemented some XUnit tests. Apart from that, I also have a minimum testable example which can be applied with LINQPad.

Any suggestions or solutions would be greatly appreciated.

The following error appears

System.InvalidOperationException : The LINQ expression 'LastName' could not
be translated. Either rewrite the query in a form that can be translated, or 
switch to client evaluation explicitly by inserting a call to 'AsEnumerable',
'AsAsyncEnumerable', 'ToList', or 'ToListAsync'*

Test and evaluation with LINQPad 6

I tested the behaviour with LINQPad in order to make the use case more simple. Therefore, I used the DemoDB which should be available for everyone.

// successful query
query { 
    for c in this.Categories do
    select {| A = c.CategoryID; B = c.CategoryName; |}
}

// failed query
query { 
    for c in this.Categories do
    select {| B = c.CategoryID; A = c.CategoryName; |}
}

The argument 'value' was the wrong type. Expected 
'System.Func`2[System.Int32,<>f__AnonymousType1383943985`2[System.String,System.Int32]]'. 
Actual '<>f__AnonymousType1383943985`2[System.String,System.Int32]'.

Test and evaluation with a F# unit test project

Test result summary

I tested the behaviour with .NET 3.1 and .NET 5.0 (projects as well as LINQPad 6). Furthermore, all dependencies have been adjusted accordingly (e.g. Entity Framework 5.0 or 3.1).

Test	Result
A anonymous record	successful
B anonymous record	successful
C anonymous record	failed
D anonymous record	failed
E partial person type	failed
F partial person type	successful
G partial person type	successful
H partial person type	failed
I partial person type	failed

Test outcome

System.InvalidOperationException : The LINQ expression 'LastName' could not be translated. Either rewrite the query in a form that can be translated, or switch
    to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'
Entity Framework Core "5.0.3" initialized 
'"TestContext"' using provider '"Microsoft.EntityFrameworkCore.InMemory"'

EF core code first database .NET 5 project

public class Person
{
     public int Id { get; set; }
     public string LastName { get; set; }
}
...
public void Configure(EntityTypeBuilder<Person> builder)
{
      builder.ToTable("PERSON");
      builder.HasKey(x => x.Id);
      builder.Property(x => x.Id)
             .HasColumnName("ID")
             .ValueGeneratedNever();
      builder.Property(x => x.LastName)
             .HasColumnName("LASTNAME")
             .HasMaxLength(512)
             .IsUnicode(false);
}
...
public class TestContext : DbContext
{
     public DbSet<Person> Persons { get; private set; }
     public TestContext(DbContextOptions<TestContext> options) : base(options)
     {}
     protected override void OnModelCreating(ModelBuilder modelBuilder)
     {
          modelBuilder.ApplyConfiguration(new PersonConfig());
     }
}

F# xunit test project in order to evaluate the EF core database context access

type PartialPerson = { LastName: string; ID : int; }

type ``success database execution queries`` (output: ITestOutputHelper) =
    let sLogger =
        LoggerConfiguration()
            .MinimumLevel.Verbose()
            .MinimumLevel.Override("Microsoft", LogEventLevel.Information)
            .MinimumLevel.Override("Microsoft.EntityFrameworkCore", LogEventLevel.Information)
            .Enrich.FromLogContext()
            .WriteTo.TestOutput(output, Events.LogEventLevel.Verbose)
            .WriteTo.Debug()
            .CreateLogger()
    let loggerFactory =
        (new LoggerFactory())
            .AddSerilog(sLogger)
    let options = DbContextOptionsBuilder<TestContext>()
                      .EnableSensitiveDataLogging(true)
                      .UseLoggerFactory(loggerFactory)
                      .UseInMemoryDatabase(Guid.NewGuid().ToString())
                      .Options;
    let context = new TestContext(options)
    [<Fact>]
    let ``success person select lastname Test A`` () =
        let rs =
            context.Persons.Select(
                fun person -> {| Name = person.LastName |} )
        rs |> should be Empty // successful
    [<Fact>]
    let ``success person select id and lastname Test B`` () =
        let rs =
            context.Persons.Select(
                fun person ->
                    {| ID = person.Id
                       LastName = person.LastName |})
        rs |> should be Empty // successful
    [<Fact>]
    let ``success person select id and lastname Test C`` () =
        let rs =
            context.Persons.Select(
                fun person ->
                    {| LastName = person.LastName
                       ID = person.Id |} )
        rs |> should be Empty // failed
    [<Fact>]
    let ``success person select id and lastname Test D`` () =
        let rs =
            query {
                for person in context.Persons do
                select 
                    {| LastName = person.LastName
                       ID = person.Id |}
            }
        rs |> should be Empty // failed

    // avoid anonymous record and use the partial person type
    // type PartialPerson = { LastName: string; ID : int; }
    [<Fact>]
    let ``success partial person select id and lastname Test E`` () =
        let rs =
            context.Persons.Select(
                fun person ->
                    { ID = person.Id
                      LastName = person.LastName })
        rs |> should be Empty // failed
    [<Fact>]
    let ``success partial person select id and lastname Test F`` () =
        let rs =
            context.Persons.Select(
                fun person ->
                    { LastName = person.LastName
                      ID = person.Id } )
        rs |> should be Empty // successful
    [<Fact>]
    let ``success partial person select id and lastname Test G`` () =
        let rs =
            query {
                for person in context.Persons do
                select 
                     { LastName = person.LastName
                       ID = person.Id }
            }
        rs |> should be Empty // successful
    [<Fact>]
    let ``success partial person select id and lastname Test H`` () =
        let rs =
            query {
                for person in context.Persons do
                select 
                     { ID = person.Id
                       LastName = person.LastName }
            }
        rs |> should be Empty // failed
    [<Fact>]
    let ``success partial person select id and lastname Test I`` () =
        let rs =
            query {
                for person in context.Persons do
                select 
                     { ID = person.Id
                       LastName = person.LastName }
            }
        rs.ToList() |> should be Empty // failed

[albertwoo]

Why this bug is marked as Impact-Low? I think it is a very serious issue.

[Happypig375]

@albertwoo Same impact as #11127.

[dsyme]

@albertwoo I'll take a look, thanks for the detailed report.

[albertwoo]

We should thank @jphilipps for the report 😊.
But I also think it is very serious bug because it almost make fsharp with ef core not functional.
The strange thing is if I use with linq2db.EntityframeworkCore the issue goes away.

[dsyme]

But I also think it is very serious bug because it almost make fsharp with ef core not functional.

Is the workaround to alwys use alphabetical ordering on the anonymous record?

Also, could you please link a full project that contains the repro code

[albertwoo]

@dsyme here is the repro code https://github.com/albertwoo/FSharpEFCoreRecordIssue

Just set FSharpEFCoreRecordIssue fsharp project as startup project then run. The order issue should popup.

[albertwoo]

This issue still hanpens in .NET 6, fsharp 6.