By Royd Brayshay


2008-12-23 15:12:34 8 Comments

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))
        );
}

3 comments

@user2945722 2016-06-13 13:42:31

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)

@TREx 2010-11-23 07:20:14

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)"
    );

@psycho 2012-12-06 15:31:44

That 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.

@Royd Brayshay 2009-01-05 11:36:00

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);
    }

@alpav 2011-05-18 19:01:45

Example of usage is here: stackoverflow.com/questions/5996403/…

@user2945722 2016-06-13 09:42:21

Simple 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 2019-03-20 14:41:01

How to implement if I want to compare case insensitive string in Join?

Related Questions

Sponsored Content

4 Answered Questions

9 Answered Questions

[SOLVED] Learning about LINQ

7 Answered Questions

[SOLVED] Multiple WHERE Clauses with LINQ extension methods

  • 2012-01-09 16:17:39
  • user609886
  • 106550 View
  • 68 Score
  • 7 Answer
  • Tags:   c# linq

0 Answered Questions

LINQ Join DbFunction.AddDays vs. DateTime.AddDays

4 Answered Questions

[SOLVED] Linq to SQL how to do "where [column] in (list of values)"

  • 2009-07-02 16:59:58
  • Nathan
  • 123617 View
  • 93 Score
  • 4 Answer
  • Tags:   linq linq-to-sql

1 Answered Questions

[SOLVED] How do I do a left outer join with Dynamic Linq?

0 Answered Questions

Dynamic linq join extension method usage

4 Answered Questions

[SOLVED] Dynamic LINQ - Is There A .NET 4 Version?

1 Answered Questions

[SOLVED] How to dynamically create Joins in LINQ?

2 Answered Questions

[SOLVED] Where is the "Fold" LINQ Extension Method?

Sponsored Content