CODEWITHSUNDEEP
c#linqf#entity-framework-6left-join
symbiont
symbiont

Reputation: 1554

F# left join with multiple conditions, as a query expression

In C# i can do a left join query, with multiple conditions. Like this C# example that runs fine:

using (var db = new CompanyContext())
{
    var q =
        from d in db.deps
        from e in db.emps.Where(e => d.id==e.dep_id && d.start_time<e.modified).DefaultIfEmpty() // left join
        select new { d, e };
    var result = q.ToList();
    // ...
}

I've tried to translate this to the following in F#:

use db = new CompanyContext()
let q = query {
    for d in db.deps do
    for e in db.emps.Where(fun e -> d.id=e.dep_id && d.start_time<e.modified).DefaultIfEmpty() do // left join
    select ( d, e )}
let result = q.ToList()
// ...

But the F# version results in an Exception:

System.InvalidOperationException: 'variable '_arg1' of type 'MyLib.dep' referenced from scope '', but it is not defined'

What is wrong with my F# query?

EDIT: using an anonymous record select {| d = d; e = e |} instead of select ( d, e ) as suggested, results in the same Exception

EDIT: Full Exception (There is no inner exception. It's null):

System.InvalidOperationException: variable '_arg1' of type 'MyLib.dep' referenced from scope '', but it is not defined
   at System.Linq.Expressions.Compiler.VariableBinder.Reference(ParameterExpression node, VariableStorageKind storage)
   at System.Linq.Expressions.Compiler.VariableBinder.VisitParameter(ParameterExpression node)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Linq.Expressions.Compiler.VariableBinder.VisitUnary(UnaryExpression node)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.Visit(ReadOnlyCollection`1 nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitNewArray(NewArrayExpression node)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitArguments(IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitArguments(IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.Visit(ReadOnlyCollection`1 nodes)
   at System.Linq.Expressions.Compiler.VariableBinder.VisitLambda[T](Expression`1 node)
   at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)
   at System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda, DebugInfoGenerator debugInfoGenerator)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.GetLambdaExpression(Expression argument)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.GetLambdaExpression(MethodCallExpression callExpression, Int32 argumentOrdinal)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.DefaultIfEmptyTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectManyTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Convert()
   at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass41_0.<GetResults>b__1()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass41_0.<GetResults>b__0()
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__31_0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ConsoleApplication1.main(String[] argv) in C:\Temp\ConsoleApplication1\ConsoleApplication1\Program.fs:line 780

groupJoin doesn't seem to compile with my extra condition. Visual Studio complains that it doesn't know d, and underlines it in d.start_time<e.modified:

use db = new CompanyContext()
let q = query {
    for d in db.deps do
    groupJoin e in db.emps.Where(fun e -> d.start_time<e.modified) on (d.id=e.dep_id) into es // left join
    for e in es.DefaultIfEmpty() do
    select ( d, e )}
let result = q.ToList()

FS0039 The value, namespace, type or module 'd' is not defined.

Upvotes: 3

Views: 240

Answers (1)

symbiont
symbiont

Reputation: 1554

I think i've found a solution. This seems to produce the expected result, with groupJoin:

use db = new CompanyContext()
let q = query {
    for d in db.deps do
    groupJoin e in db.emps on (d.id=e.dep_id) into es
    for e in es.Where(fun e -> d.start_time<e.modified).DefaultIfEmpty() do
    select ( d , e )}
let result = q.ToList()

You don't have to use the on clause, by using a dummy condition that's always true, like 0=0:

use db = new CompanyContext()
let q = query {
    for d in db.deps do
    groupJoin e in db.emps on (0=0) into es
    for e in es.Where(fun e -> d.id=e.dep_id && d.start_time<e.modified).DefaultIfEmpty() do
    select ( d , e )}
let result = q.ToList()

The examples also work with leftOuterJoin instead of groupJoin.

Opinion:

I don't like how leftOuterJoin implicitly applies .DefaultIfEmpty() to what is declared by the into part (into es in the above examples). Because then if you want to add more conditions in a following for clause like me with .Where, then you still need to apply .DefaultIfEmpty() so that there is at least 1 result again anyway, because for is a next and separate operation. Might as well use groupJoin that always needs .DefaultIfEmpty() to do a left join

Upvotes: 1

Related Questions