Issue

I’m using an existing code first approach in order to acquire data from an existing database. This project is encapsulated in a .NET C# project and contains the model as well as the configuration descriptions.

My aim is to use the existing database context implementation for a F# project. I have been testing many times the database access with a XUnit test project which is also written in F#.

My problem is an unexpected behaviour, when I try to select the last name and the person’s id.

In order to explain my implementation and illustrate the issue I implemented four test functions of which only the first two are executed successfully. The last two test functions fail.

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'*

I do not understand the error because I am not actually using any client specific implementation for the query evaluation. So normally the LINQ-Provider should convert it into a database appropriate SQL definition. For the tests I am using the Entity Framework memory database instance. On top of that, please note that the error also exists on the real database.

Another issue that I do not understand is why the second test works while the third one fails. I changed only the last name select with the id select.

However, I also added a F# query expression since this is actually recommended from the documentation, but with no success.

Is the main problem the usage of the Entity Framework context? If so, how can I then reuse the implementation of the EF database context?

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.

Apart from that I’m trying to make it reproduceable for a larger community. Unfortunately, the outcome of my test is the same. So, I created a simple database query and changed the order of the named selections. If I change the alphabetical order of the column names, the error appears. Therefore, why is the alphabetical order important in order to have a valid select statement?

I found another closed issue on stackoverflow which describes the usage of anonymous records but the different order is not treated (F# Query Expression / select operator / changing column headings in result).

// 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 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

Current findings

It seems that this issue is related to the issues 1226 and 3782. Both issues describe some problems with the order of named selections.

The dapper issue 1226 had a similar problem with the order of anonymous records for the query definition. However, thanks to Isaac Abraham (isaacabraham) who is using the CLIMutable decoration, I thought of turning off the ordering restrictions. So basically the idea was to try it for my tests since the query generation through the LINQ provider could have a positive effect. Unfortunately this was without success, maybe because of the implementation of the LINQ provider because the generation process is implemented with F# and that is the reason why the CLIMutable attribute does not affect it.

After continuing my search, I found another issue 3782 which indicates my problem. The issue has the main focus on the usage of tuples for the data selection but also the issue with records. So, I added another issue description 11131 in order to help with my current findings. Finally, I will keep track the outcome and add it to this issue.