How to create a dynamic LINQ join extension method
Solution 1
I've fixed it myself now. It was a schoolboy error passing too many parameters to the CreateQuery(... ) call. Paste the following code into the Dynamic.cs file within the DynamicQueryable class for a dynamic Join extension method. You can find the source for the DynamicQuery sample project at http://code.msdn.microsoft.com/csharpsamples.
Enjoy.
public static IQueryable Join(this IQueryable outer, IEnumerable inner, string outerSelector, string innerSelector, string resultsSelector, params object[] values)
{
if (inner == null) throw new ArgumentNullException("inner");
if (outerSelector == null) throw new ArgumentNullException("outerSelector");
if (innerSelector == null) throw new ArgumentNullException("innerSelector");
if (resultsSelector == null) throw new ArgumentNullException("resultsSelctor");
LambdaExpression outerSelectorLambda = DynamicExpression.ParseLambda(outer.ElementType, null, outerSelector, values);
LambdaExpression innerSelectorLambda = DynamicExpression.ParseLambda(inner.AsQueryable().ElementType, null, innerSelector, values);
ParameterExpression[] parameters = new ParameterExpression[] {
Expression.Parameter(outer.ElementType, "outer"), Expression.Parameter(inner.AsQueryable().ElementType, "inner") };
LambdaExpression resultsSelectorLambda = DynamicExpression.ParseLambda(parameters, null, resultsSelector, values);
return outer.Provider.CreateQuery(
Expression.Call(
typeof(Queryable), "Join",
new Type[] {outer.ElementType, inner.AsQueryable().ElementType, outerSelectorLambda.Body.Type, resultsSelectorLambda.Body.Type },
outer.Expression, inner.AsQueryable().Expression, Expression.Quote(outerSelectorLambda), Expression.Quote(innerSelectorLambda), Expression.Quote(resultsSelectorLambda)));
}
//The generic overload.
public static IQueryable<T> Join<T>(this IQueryable<T> outer, IEnumerable<T> inner, string outerSelector, string innerSelector, string resultsSelector, params object[] values)
{
return (IQueryable<T>)Join((IQueryable)outer, (IEnumerable)inner, outerSelector, innerSelector, resultsSelector, values);
}
Solution 2
You can install the nuget package of System.Linq.Dynamic.Core - https://github.com/StefH/System.Linq.Dynamic.Core
This has the join method implemented along with various other helper methods.
Using this library you can do a simple join in the following the way
myContext.TableA.Join(myContext.TableB,'Id','TableAId','outer',null)
in the result selector outer
and inner
are key words to access the result of the join.
Using a key with multiple properties and/or selecting a result with multiple properties can be done in the following way
myContext.TableA.Join(myContext.TableB,'new (Id as key1,Code as key2)','new (TableAId as key1,AnotherCol as key2)','new(outer.Id,inner.Desc)',null)
Solution 3
Here is some sample code showing a join on multiple columns. Using a datatable and datarows you need to always access fields via the indexer.
DataTable t1 = new DataTable();
t1.Columns.Add("FundId", typeof(int));
t1.Columns.Add("Date", typeof(DateTime));
t1.Columns.Add("CodeA", typeof(string));
t1.Rows.Add(1, new DateTime(2010, 01, 01), "A1");
t1.Rows.Add(2, new DateTime(2010, 01, 01), "A2");
t1.Rows.Add(3, new DateTime(2010, 01, 01), "A3");
DataTable t2 = new DataTable();
t2.Columns.Add("FundId", typeof(int));
t2.Columns.Add("Date", typeof(DateTime));
t2.Columns.Add("CodeB", typeof(string));
t2.Rows.Add(1, new DateTime(2010, 01, 01), "B1");
t2.Rows.Add(2, new DateTime(2010, 01, 01), "B2");
t2.Rows.Add(3, new DateTime(2010, 01, 01), "B3");
IQueryable outerTable = t1.AsEnumerable().AsQueryable();
IEnumerable innerTable = t2.AsEnumerable();
var query = outerTable.Join
(
innerTable,
"new(get_Item(0) as FundId, get_Item(1) as Date)",
"new(get_Item(0) as FundId, get_Item(1) as Date)",
"new(outer.get_Item(0) as FundId, outer.get_Item(2) as CodeA, inner.get_Item(2) as CodeB)"
);
user257902
I work with software development teams needing greater business effectiveness. I give lean and agile leadership advice plus coaching and training in Scrum, Kanban and XP practices. Dramatic improvements in productivity and predictability can be found in most teams. I also write code, design products and perform technical leadership.
Updated on June 14, 2020Comments
-
user257902 almost 4 years
There was a library of dynamic LINQ extensions methods released as a sample with Visual Studio 2008. I'd like to extend it with a join method. The code below fails with a parameter miss match exception at run time. Where is the problem?
public static IQueryable Join(this IQueryable outer, IEnumerable inner, string outerSelector, string innerSelector, string resultsSelector, params object[] values) { if (inner == null) throw new ArgumentNullException("inner"); if (outerSelector == null) throw new ArgumentNullException("outerSelector"); if (innerSelector == null) throw new ArgumentNullException("innerSelector"); if (resultsSelector == null) throw new ArgumentNullException("resultsSelctor"); LambdaExpression outerSelectorLambda = DynamicExpression.ParseLambda(outer.ElementType, null, outerSelector, values); LambdaExpression innerSelectorLambda = DynamicExpression.ParseLambda(inner.AsQueryable().ElementType, null, innerSelector, values); ParameterExpression[] parameters = new ParameterExpression[] { Expression.Parameter(outer.ElementType, "outer"), Expression.Parameter(inner.AsQueryable().ElementType, "inner") }; LambdaExpression resultsSelectorLambda = DynamicExpression.ParseLambda(parameters, null, resultsSelector, values); return outer.Provider.CreateQuery( Expression.Call( typeof(Queryable), "Join", new Type[] { outer.ElementType, inner.AsQueryable().ElementType, outerSelectorLambda.Body.Type, innerSelectorLambda.Body.Type, resultsSelectorLambda.Body.Type }, outer.Expression, inner.AsQueryable().Expression, Expression.Quote(outerSelectorLambda), Expression.Quote(innerSelectorLambda), Expression.Quote(resultsSelectorLambda)) ); }
-
alpav almost 13 yearsExample of usage is here: stackoverflow.com/questions/5996403/…
-
psycho over 11 yearsThat does not answer the question. Here you use the Join method provided by Linq, the question - was about creating this method, because it wasn't provided by Linq yet.
-
user2945722 almost 8 yearsSimple usage of this code is as follows:
var result = myDbContext.Person .Join(myDbContext.Roles,"new(Id as firstKey,SomeOtherId as secondKey)","new(PersonId as firstKey,AlternativeId as secondKey)", "new (inner as r, outer as p)");
note that if it is joined on multiple columns you must us "as [somekey]" if they are not named the same or an error will occur. Also in the result selector you use the keywords "outer" and "inner" -
Shekhar Dalvi about 5 yearsHow to implement if I want to compare case insensitive string in Join?
-
Ameerudheen.K about 4 yearsHow can i do the same if my number of tables are also dynamic?