none
Dynamic LINQ join extehsion RRS feed

  • Question

  • Need to perform a Custom Join in LINQ dynamically between two collections.

    The required behavior is explained using he SQL use-case scenario.

     

     

     

    Table Item1  columns  ( Id, FloatField,DateField etc )

    Table Item2  columns  ( Id, Amount,DateField etc)

     

    Content of Item1

     

    Id

    Amount

    1000333

    50

    1000334

    50

    1001133

    50

    1001134

    50

    1001333

    1025

    1001334

    1025

    1001335

    1025

     

     

     

    Content of Item2

     

    Id

    Amount

    202

    50.02

    203

    49.89

    1002

    50

    1003

    50

    1202

    1075

    1203

    1075

    1302

    1075

     

    Inner join with ABS difference in SQL

    SelectA.Id, A.Amount, B.Id, B.Amount from Item1 A inner join #Item2 B on (ABS (A.Amount - B.Amount) < 0.5)

     

    The expected results: All the rows with difference < 0.5 and I’m getting the result in SQL query.

    I want to perform the similar join in Dynamic LINQ (between two collections).

     I will know the column name, its data type & difference value only at the run time.

     

     

    Please guide me on this.



    • Edited by SoccerFan1 Monday, October 27, 2014 6:11 PM
    Monday, October 27, 2014 6:10 PM

All replies

  • What exactly would you like to do with Dynamic? The full expression?

    If does, I don't think it's possible. 

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

    You could also study the option of using a Datatable

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

    Check out this question:

    http://stackoverflow.com/questions/389094/how-to-create-a-dynamic-linq-join-extension-method

    • Proposed as answer by Deric Ferreira Monday, October 27, 2014 6:41 PM
    Monday, October 27, 2014 6:41 PM
  • Inner join with ABS difference in SQL

    SelectA.Id, A.Amount, B.Id, B.Amountfrom Item1 A innerjoin #Item2 B on (ABS(A.Amount- B.Amount) < 0.5)

    Please guide me on this.

    http://msdn.microsoft.com/en-us/library/vstudio/bb738542(v=vs.100).aspx

    If you can do it with T-SQL, then you can do the samething with Entity-SQL.

    You know,  Linq can't do it all, and one can do things more dynamically with E-SQL

    http://msdn.microsoft.com/en-us/library/vstudio/bb738684(v=vs.100).aspx

    http://msdn.microsoft.com/en-us/library/vstudio/bb387118(v=vs.100).aspx

    E-SQL can do it all when it comes to querying, like innerjoin, outterjoin and the hole nine yards  just like T-SQL, which can be built dynamically.

    • Proposed as answer by Deric Ferreira Monday, October 27, 2014 9:55 PM
    Monday, October 27, 2014 8:57 PM
  • Hello SoccerFan1,

    Since you are using Entity Framework, you could try to use the Table Value Function to do something that LINQ could not implement it easily:

    http://msdn.microsoft.com/en-us/data/hh859577.aspx

    The below LINQ query does not use the inner, however, it should achieve the result you want:

    var result = (from o in db.Orders
                                  from c in db.Customers
                                  where Math.Abs(c.CustomerID - o.OrderID) < 1
                                  select new { o.OrderID, o.OrderName, c.CustomerID, c.CustomerName }).ToList();

    Please have a try.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, October 28, 2014 1:56 AM
    Moderator
  • Thanks Fred.

    I need to evaluate this at RunTime and how can I make it dynamic?

    Tuesday, October 28, 2014 9:42 PM
  • Hello,

    >> I need to evaluate this at RunTime and how can I make it dynamic?

    I should not notice that you know the column name, its data type & difference value only at the run time.

    You could use the way darnold924 mentioned: E-SQL since it is similar with T-SQL and you could create it dynamicly.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 29, 2014 8:57 AM
    Moderator
  • @Fred

    You could use the way darnold924 mentioned: E-SQL since it is similar with T-SQL and you could create it dynamicly.

    People are so hung up on Linq and trying to do things dynamically when 9 times out of 10 Linq can't do it or no 3rd party Linq provider can do it dynamically. :)

    It's always nice to be able to fallback to E-SQL  and a datareader or use the EF backdoor, use in-line T-SQL or a sproc,  use straight-up ADO.NET, SQL Command objects and a datareader.

    I have been there and done that to get out of a hole using EF. :)

    Thursday, October 30, 2014 10:49 AM