F# Left Joins Throws NotImplementedException.

[devinlyons]

Describe your issue

When I try to create a LEFT JOIN using groupJoin in F#, I get an exception. I know you aren't working on F# issues yet but if you could give me some guidance on solving this problem myself, that would be helpful.

Exception message: The method or operation is not implemented.
Stack trace:
   at LinqToDB.Linq.Builder.SelectContext.ProcessScalar[T,TContext](TContext context, Expression expression, Int32 level, Func`5 action, Func`2 defaultAction, Boolean throwOnError)
   at LinqToDB.Linq.Builder.SelectContext.GetContext(Expression expression, Int32 level, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.SelectContext.ProcessScalar[T,TContext](TContext context, Expression expression, Int32 level, Func`5 action, Func`2 defaultAction, Boolean throwOnError)
   at LinqToDB.Linq.Builder.SelectContext.GetContext(Expression expression, Int32 level, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.TableBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.DefaultIfEmptyBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.SelectManyBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.Build[T]()
   at LinqToDB.Linq.Query`1.CreateQuery(ExpressionTreeOptimizationContext optimizationContext, ParametersContext parametersContext, IDataContext dataContext, Expression expr)
   at LinqToDB.Linq.Query`1.GetQuery(IDataContext dataContext, Expression& expr, Boolean& dependsOnParameters)
   at LinqToDB.Linq.ExpressionQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at Microsoft.FSharp.Collections.SeqModule.ToList[T](IEnumerable`1 source)
   at <StartupCode$FSharp-Data-LinqToDB-Examples>.$Program.main@()

Steps to reproduce

Here is the data context:

//Entities

public class Parent
{
    [PrimaryKey]
    public int Id { get; set; }

    public string Name { get; set; } = default!;
}

public class Child
{
    [PrimaryKey]
    public int Id { get; set; }

    public int ParentId { get; set; }

    public string Name { get; set; } = default!;
}

public class Pet
{
    [PrimaryKey]
    public int Id { get; set; }

    public int ParentId { get; set; }

    public string Name { get; set; } = default!;
}

public class Car
{
    [PrimaryKey]
    public int Id { get; set; }

    public int ParentId { get; set; }

    public string Name { get; set; } = default!;
}

// Data Context
public class ExampleContext : DataConnection
{
    public ExampleContext(LinqToDBConnectionOptions options)
        : base(options)
    { }

    public ITable<Parent> Parents  => this.GetTable<Parent>();
    public ITable<Child> Children => this.GetTable<Child>();
    public ITable<Pet> Pets => this.GetTable<Pet>();
    public ITable<Car> Cars => this.GetTable<Car>();
}

And the F# program:

open System
open System.Linq
open FSharp.Data.LinqToDB.DataContext
open LinqToDB.Configuration

// configure connection string
let cfg = 
    LinqToDBConnectionOptionsBuilder()
        .UsePostgreSQL("postgres://YourUserName:YourPassword@YourHostname:5432/YourDatabaseName")
        .Build()

let context = new ExampleContext(cfg)
let q = 
    context.Parents
        .Join(context.Children, 
            (fun p -> p.Id),
            (fun c -> c.ParentId),
            (fun p c -> {| p = p; c = c |})
        )
        .GroupJoin(context.Pets, 
            (fun o -> o.p.Id),
            (fun pet -> pet.ParentId),
            (fun o pets -> {| p = o.p; c = o.c; pets = pets |})
        )
        .SelectMany((fun o -> o.pets.DefaultIfEmpty()),
            (fun o pet -> {| p = o.p; c = o.c; pet = pet |})
        )

let xs = List.ofSeq q // error happens here
for x in xs do
    printfn "%O" x

printfn "Press any key to exit..."
Console.ReadKey() |> ignore

Environment details

Linq To DB version: 4.1.1

Database (with version): N/A

ADO.NET Provider (with version): N/A

Operating system: Windows

.NET Version: 6.0

[MaceWindu]

Have you checked workarounds from #1813 ?

[devinlyons]

Yes, I have.

[MaceWindu]

Looked into it. Problem is that for this expression

(fun o pets -> {| p = o.p; c = o.c; pets = pets |})

F# generates following:

(o, pets) => p => new  { c = o.c, p, pets }.Invoke(o.p)

and we don't support such template.

I'm not familiar with F# enough, so I don't have idea how to prevent nested lambda generation

[MaceWindu]

Found it: https://github.com/dotnet/fsharp/issues/11127

Just tested workaround - it works

[MaceWindu]

Note to future me: as fix we probably should implement Expression interceptor to rewrite expressions, generated by F# to readable form

[devinlyons]

That did the trick. Thanks!

[sksk571]

We have run into this issue by using GroupJoin+SelectMany in C#. When DefaultIfEmpty is specified in the GroupJoin clause, query throws NotImplementedException:

using LinqToDB;
using LinqToDB.Mapping;
 
[Table(Name = "[dbo].[UserAccounts]")]
public class UserAccount
{
  [PrimaryKey, Identity]
  public int ID { get; set; }

  [Column]
  public string Email { get; set; } = string.Empty;
}

[Table(Name = "[dbo].[UserAccountSettings]")]
public class UserAccountSetting
{
  [Column]
  public int UserID { get; set; }
}

public class MainDb : LinqToDB.Data.DataConnection
{
  public MainDb(string connectionString) : base(LinqToDB.ProviderName.SqlServer2019, connectionString) { }

  public ITable<UserAccount>  Accounts  => this.GetTable<UserAccount>();
  public ITable<UserAccountSetting>  AccountSettings  => this.GetTable<UserAccountSetting>();

  // ... other tables ...
}

var db = new MainDb(
  @"Server=.\;Database=Test;Trusted_Connection=True;Enlist=False;TrustServerCertificate=True");
  
// This code fails with the NotImplementedException
var r1 = db.Accounts.GroupJoin(db.AccountSettings,
            a => a.ID, s => s.UserID, (a, s) => new
            {
                Account = a,
                AllSettings = s.DefaultIfEmpty()
            })
            .SelectMany(x => x.AllSettings.Select(s => new { x.Account, Settings = s }))
            .ToList();

// This code works
var r2 = db.Accounts.GroupJoin(db.AccountSettings,
            a => a.ID, s => s.UserID, (a, s) => new
            {
                Account = a,
                AllSettings = s
            })
            .SelectMany(x => x.AllSettings.DefaultIfEmpty().Select(s => new { x.Account, Settings = s }))
            .ToList();