1

I Would like to build the dynamic Linq to Sql query for groupjoin clause where groupjoin will include multiple dynamic columns for joining.

I have looked the below artile for left join but it do not have the facility for multiple columns to join: How do I do a left outer join with Dynamic Linq?

Below is the query at compile time which i need to acheive dynamically:

`var source = lParent.GroupJoin(lChild,
p => new{ p.PID,p.CategoryId (These are dynamic columns)}
c => new{ c.PID,c.CategoryId (These are dynamic columns)}
(p, g) =>  new { Parent = p, Childs= g })
.SelectMany( p => p.Childs.DefaultIfEmpty(),
      (p,g) => new { Parent=p.Parent, Childs=g});`

Thankyou.

Ashutosh.

Community
  • 1
  • 1
Ashutosh
  • 53
  • 6
  • "but it do not have the facility for multiple columns to join". Are you sure? Did you try passing "new (outer.PID as PID, outer.CategoryId as CategoryId)" as `outerKeySelector` (and similar to `innerKeySelector`)? – Ivan Stoev Jan 17 '16 at 15:03
  • Hi Ivan, Thankyou for the reply. I tried to pass the way you suggested but it did not worked below is the code ' IQueryable qry1 = foos.GroupJoin(bars, "new(outer.Id as Id, outer.CatID as CatID)", "new(inner.Id as Id, inner.CatID as CatID)", "new(outer.Id as Foo, group as Bars)") .SelectMany("Bars.DefaultIfEmpty()", "new(outer.Foo as Foo, inner as Bar)"); ' It gives me exci – Ashutosh Jan 18 '16 at 11:37
  • What was the problem? – Ivan Stoev Jan 18 '16 at 11:40
  • See, you can't define anonymous types at runtime, so you really need Dynamic LINQ or something like that. Please note that the article answer seems to require Dynamic LINQ source code modification. – Ivan Stoev Jan 18 '16 at 11:46
  • It gives me error as : at System.Linq.Dynamic.ExpressionParser.Parse(Type resultType) in Dynamic.cs at System.Linq.Dynamic.DynamicExpression.ParseLambda(ParameterExpression[] parameters, Type resultType, String expression, Object[] values) in – Ashutosh Jan 18 '16 at 11:46
  • Could you help me in solving the above issue or any thoughts. – Ashutosh Jan 18 '16 at 11:50
  • I'll try. But there seem to be a different versions of Dynamic LINQ, where did you get your sources from (link)? – Ivan Stoev Jan 18 '16 at 12:03
  • Yes. I have got the source from below link: http://stackoverflow.com/questions/7364436/how-do-i-do-a-left-outer-join-with-dynamic-linq – Ashutosh Jan 18 '16 at 12:11
  • Yes, but did you read the accepted answer? "Add `void DefaultIfEmpty();` to interface `IEnumerableSignatures`". That means you need to find the source code of the System.Linq.Dynamic, modify it and use it, instead of dll from nuget package. – Ivan Stoev Jan 18 '16 at 12:30
  • I have already added void DefaultIfEmpty(); to interface and added the method as defined in the post. I have already done all the setup of this and the only problewm is that I am not able to pass multiple columns dynamilcally. – Ashutosh Jan 18 '16 at 13:24
  • Interesting. I don't have that `DefaultIfEmpty` mod, but `tableA.GroupJon(tableB, "new (outer.PID as PID, outer.CategoryId as CategoryId)", "new (inner.PID as PID, inner.CategoryId as CategoryId)", "new (outer as Parent, group as Childs)")` works for me. The names should be exactly `outer`, `inner` and `group`. and `new (...)`, not `new {...}` – Ivan Stoev Jan 18 '16 at 13:32
  • Hi Ivan, Thankyou very very much for the help. I tired the above solution suggested by you and it worked. Once again thank you very much. I would require your help if I get stuck up in future for dynamic query construction.Thank you!!! – Ashutosh Jan 18 '16 at 15:43
  • Hi Ivan, Could you please help me to achieve the same query dynamically for Full Outer Join insted of Left Outer Join? – Ashutosh Jan 19 '16 at 09:28
  • It's hard even with "static" LINQ - see [LINQ - Full Outer Join](http://stackoverflow.com/questions/5489987/linq-full-outer-join). I guess you have to build dynamically 2 queries - one for left outer join, one for right outer join, and then combine them with `Union` or `Concat` – Ivan Stoev Jan 19 '16 at 10:31
  • Hi Ivan, I tired but I am not able to get the intellisence for Union or Concat. since the groupjoin returns IQuerable Below is the groupjoin function `public static IQueryable GroupJoin(this IQueryable outer, IEnumerable inner, string outerKeySelector, string innerKeySelector, string resultSelector, params object[] values)`. Could you please suggest me how to convert Iquerable to class type or datatable? – Ashutosh Jan 19 '16 at 11:38
  • Hi Ivan, Could you please help me to have dynamic Intersect clause to get common records from "Full Outer Join". Below intersect clause do not works.`public static IQueryable Intersect(this IQueryable source, IQueryable other) { if (source == null) throw new ArgumentNullException("source"); return source.Provider.CreateQuery( Expression.Call( typeof(Queryable), "Intersect", new Type[] { source.ElementType }, source.Expression, other.Expression)); }` – Ashutosh Jan 20 '16 at 15:03
  • Well, if you look at [Queryable.Intersect](https://msdn.microsoft.com/en-US/library/bb345609(v=vs.110).aspx), the second argument is of type `IEnumerable`, so instead of `source.Expression` try `Expression.Constant(source)`. But why do you need that? If still working on making full outer join work, IMO you'd better filter the right join part to include only the records that have **no match**, and then `Concat` that to the left outer join query. – Ivan Stoev Jan 20 '16 at 15:25
  • I am tring to get the code to create dynamic INNER JOIN for Multiple Columns for below code `var source = lParent.GroupJoin(lChild, p => new{ p.PID,p.CategoryId (These are dynamic columns)} c => new{ c.PID,c.CategoryId (These are dynamic columns)} (p, g) => new { Parent = p, Childs= g }) .SelectMany( p => p.Childs.DefaultIfEmpty(), (p,g) => new { Parent=p.Parent, Childs=g})' but I am not able to get code for inner join for multiple columns – Ashutosh Jan 20 '16 at 15:31
  • Actually `INNER JOIN` is the simplest join. Remove `.DefaultIfEmpty()`, it is the only thing that converts the `INNER` to `OUTER` join. – Ivan Stoev Jan 20 '16 at 15:37
  • Hi Ivan I have removed DefaultIfEmpty() from the query below is the code`var source = lParent.GroupJoin(lChild, p => new{ p.PID,p.CategoryId (These are dynamic columns)} c => new{ c.PID,c.CategoryId (These are dynamic columns)} (p, g) => new { Parent = p, Childs= g })` It do not works. – Ashutosh Jan 20 '16 at 15:50
  • I removed DefaultIfEmpty() and it worked. Thanks a lot Ivan – Ashutosh Jan 20 '16 at 16:03
  • Hi Ivan, Could you please help me to shape the results of the dynamic join queries as the result is IQueryable so how can I map the Iquerable results returned by dynamic join to a ClassType. Below is the query for dynamic join which I need to Shape : `var innerJn = source.AsQueryable().GroupJoin(destination.AsQueryable(),"new (outer.SecurityId as SecurityId, outer.CUSIP as CUSIP)","new (inner.SecurityId as SecurityId, inner.CUSIP as CUSIP)","new (outer as sources, group as destinations)").SelectMany("destinations", "new(outer as sources, inner as destinations)"); – Ashutosh Jan 20 '16 at 16:47

0 Answers0