I am trying to have an extension method to implement left outer join which returns IQueryable
that run against EF Core 2.0 data context.
I read the Stack Overflow thread here for help: Extension method for IQueryable left outer join using LINQ. The accepted answer had some problems that were addressed in a later answer here by Jan Van der Haegen. I attempted to use the LeftJoin extension method described there with an EF Core 2.0 data context and ran into an exception which I cannot seem to resolve.
As I cannot find any fault with the implementation of the LeftJoin extension method, I attempted to run the same method against a data context using EF6 and it worked as expected. I have shared my experiment including the SQL scripts to generate the test data here:
Experiment with LeftJoin extension run against EF6 and EF Core 2.0
It contains two projects one running LeftJoin against EF6 and the other running it against EF Core 2.0. The extension method is shared via a .Net Standard 2.0 class library.
The extension method for LeftJoin is as follows:
namespace QueryableExtensions
{
// Much of the code copied from following URL:
// https://stackoverflow.com/questions/21615693/extension-method-for-iqueryable-left-outer-join-using-linq
internal class KeyValuePairHolder<T1, T2>
{
public T1 Item1 { get; set; }
public T2 Item2 { get; set; }
}
internal class ResultSelectorRewriter<TOuter, TInner, TResult> : ExpressionVisitor
{
private Expression<Func<TOuter, TInner, TResult>> resultSelector;
public Expression<Func<KeyValuePairHolder<TOuter, IEnumerable<TInner>>, TInner, TResult>> CombinedExpression { get; private set; }
private ParameterExpression OldTOuterParamExpression;
private ParameterExpression OldTInnerParamExpression;
private ParameterExpression NewTOuterParamExpression;
private ParameterExpression NewTInnerParamExpression;
public ResultSelectorRewriter(Expression<Func<TOuter, TInner, TResult>> resultSelector)
{
this.resultSelector = resultSelector;
this.OldTOuterParamExpression = resultSelector.Parameters[0];
this.OldTInnerParamExpression = resultSelector.Parameters[1];
this.NewTOuterParamExpression = Expression.Parameter(typeof(KeyValuePairHolder<TOuter, IEnumerable<TInner>>));
this.NewTInnerParamExpression = Expression.Parameter(typeof(TInner));
var newBody = this.Visit(this.resultSelector.Body);
var combinedExpression = Expression.Lambda(newBody, new ParameterExpression[] { this.NewTOuterParamExpression, this.NewTInnerParamExpression });
this.CombinedExpression = (Expression<Func<KeyValuePairHolder<TOuter, IEnumerable<TInner>>, TInner, TResult>>)combinedExpression;
}
protected override Expression VisitParameter(ParameterExpression node)
{
if (node == this.OldTInnerParamExpression)
return this.NewTInnerParamExpression;
else if (node == this.OldTOuterParamExpression)
return Expression.PropertyOrField(this.NewTOuterParamExpression, "Item1");
else
throw new InvalidOperationException("What is this sorcery?", new InvalidOperationException("Did not expect a parameter: " + node));
}
}
public static class JoinExtensions
{
internal static readonly System.Reflection.MethodInfo
Enumerable_DefaultIfEmpty = typeof(Enumerable).GetMethods()
.First(x => x.Name == "DefaultIfEmpty" && x.GetParameters().Length == 1);
internal static readonly System.Reflection.MethodInfo
Queryable_SelectMany = typeof(Queryable).GetMethods()
.Where(x => x.Name == "SelectMany" && x.GetParameters().Length == 3)
.OrderBy(x => x.ToString().Length).First();
internal static readonly System.Reflection.MethodInfo
Queryable_Where = typeof(Queryable).GetMethods()
.First(x => x.Name == "Where" && x.GetParameters().Length == 2);
internal static readonly System.Reflection.MethodInfo
Queryable_GroupJoin = typeof(Queryable).GetMethods()
.First(x => x.Name == "GroupJoin" && x.GetParameters().Length == 5);
public static IQueryable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(
this IQueryable<TOuter> outer,
IQueryable<TInner> inner,
Expression<Func<TOuter, TKey>> outerKeySelector,
Expression<Func<TInner, TKey>> innerKeySelector,
Expression<Func<TOuter, TInner, TResult>> resultSelector)
{
var keyValuePairHolderWithGroup = typeof(KeyValuePairHolder<,>)
.MakeGenericType(
typeof(TOuter),
typeof(IEnumerable<>).MakeGenericType(typeof(TInner))
);
var paramOuter = Expression.Parameter(typeof(TOuter));
var paramInner = Expression.Parameter(typeof(IEnumerable<TInner>));
var resultSel = Expression
.Lambda(
Expression.MemberInit(
Expression.New(keyValuePairHolderWithGroup),
Expression.Bind(
keyValuePairHolderWithGroup.GetMember("Item1").Single(),
paramOuter
),
Expression.Bind(
keyValuePairHolderWithGroup.GetMember("Item2").Single(),
paramInner
)
),
paramOuter,
paramInner
);
var groupJoin = Queryable_GroupJoin
.MakeGenericMethod(
typeof(TOuter),
typeof(TInner),
typeof(TKey),
keyValuePairHolderWithGroup
)
.Invoke(
"ThisArgumentIsIgnoredForStaticMethods",
new object[]{
outer,
inner,
outerKeySelector,
innerKeySelector,
resultSel
}
);
var paramGroup = Expression.Parameter(keyValuePairHolderWithGroup);
Expression collectionSelector = Expression.Lambda(
Expression.Call(
null,
Enumerable_DefaultIfEmpty.MakeGenericMethod(typeof(TInner)),
Expression.MakeMemberAccess(paramGroup, keyValuePairHolderWithGroup.GetProperty("Item2")))
,
paramGroup
);
Expression newResultSelector =
new ResultSelectorRewriter<TOuter, TInner, TResult>(resultSelector)
.CombinedExpression;
var selectMany1Result = Queryable_SelectMany
.MakeGenericMethod(
keyValuePairHolderWithGroup,
typeof(TInner),
typeof(TResult)
)
.Invoke(
"ThisArgumentIsIgnoredForStaticMethods",
new object[]
{
groupJoin,
collectionSelector,
newResultSelector
}
);
return (IQueryable<TResult>)selectMany1Result;
}
}
}
When I run the above with a data context by EF Core 2.0 I get the following exception thrown at runtime:
System.ArgumentNullException occurred
HResult = 0x80004003
Message = Value cannot be null.
Source =< Cannot evaluate the exception source>
StackTrace:
at Remotion.Utilities.ArgumentUtility.CheckNotNull[T](String argumentName, T actualValue)
at Remotion.Utilities.ArgumentUtility.CheckNotNullOrEmpty(String argumentName, String actualValue)
at Remotion.Linq.Clauses.GroupJoinClause..ctor(String itemName, Type itemType, JoinClause joinClause)
at Remotion.Linq.Parsing.Structure.IntermediateModel.GroupJoinExpressionNode.ApplyNodeSpecificSemantics(QueryModel queryModel, ClauseGenerationContext clauseGenerationContext)
at Remotion.Linq.Parsing.Structure.IntermediateModel.MethodCallExpressionNodeBase.Apply(QueryModel queryModel, ClauseGenerationContext clauseGenerationContext)
at Remotion.Linq.Parsing.Structure.QueryParser.ApplyAllNodes(IExpressionNode node, ClauseGenerationContext clauseGenerationContext)
at Remotion.Linq.Parsing.Structure.QueryParser.ApplyAllNodes(IExpressionNode node, ClauseGenerationContext clauseGenerationContext)
at Remotion.Linq.Parsing.Structure.QueryParser.GetParsedQuery(Expression expressionTreeRoot)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](Expression query, INodeTypeProvider nodeTypeProvider, IDatabase database, IDiagnosticsLogger`1 logger, Type contextType)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<> c__DisplayClass15_0`1.< Execute > b__0()
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
at Remotion.Linq.QueryableBase`1.GetEnumerator()
at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at TestWithEFCore2.Program.Main() in C: \Users\hewasud\Git\TestLeftJoinExtensionWithEF6\TestWithEF6\TestWithEFCore2\Program.cs:line 27
My questions:
Thank you.
As it is suggested here, Only workable solution was using the LinqKit.Core library. Despite all effort I was not able to get the Expression based solution to work with EF.Core under .Net Core or .Net Standard library projects. LinqKit.Core library was the key to have the extension be portable and used within a .Net Standard 2.0 class library project.
As it was suggested, with LinkqKit.Core the solution is very simple:
/// <summary>
/// Implement Left Outer join implemented by calling GroupJoin and
/// SelectMany within this extension method
/// </summary>
/// <typeparam name="TOuter">Outer Type</typeparam>
/// <typeparam name="TInner">Inner Type</typeparam>
/// <typeparam name="TKey">Key Type</typeparam>
/// <typeparam name="TResult">Result Type</typeparam>
/// <param name="outer">Outer set</param>
/// <param name="inner">Inner set</param>
/// <param name="outerKeySelector">Outer Key Selector</param>
/// <param name="innerKeySelector">Inner Key Selector</param>
/// <param name="resultSelector">Result Selector</param>
/// <returns>IQueryable Result set</returns>
public static IQueryable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(
this IQueryable<TOuter> outer,
IQueryable<TInner> inner,
Expression<Func<TOuter, TKey>> outerKeySelector,
Expression<Func<TInner, TKey>> innerKeySelector,
Expression<Func<TOuter, TInner, TResult>> resultSelector)
{
//LinqKit allows easy runtime evaluation of inline invoked expressions
// without manually writing expression trees.
return outer
.AsExpandable()// Tell LinqKit to convert everything into an expression tree.
.GroupJoin(
inner,
outerKeySelector,
innerKeySelector,
(outerItem, innerItems) => new { outerItem, innerItems })
.SelectMany(
joinResult => joinResult.innerItems.DefaultIfEmpty(),
(joinResult, innerItem) =>
resultSelector.Invoke(joinResult.outerItem, innerItem));
}
The Experiment in Github updated to illustrate working solution here: GitHub solution illustrating LeftJoin extension implemented in a .Net Standard Library
User contributions licensed under CC BY-SA 3.0