动态LINQ加入扩展 [英] Dynamic LINQ join extehsion
问题描述
需要在两个集合之间动态地在LINQ中执行自定义连接。
使用SQL用例场景解释所需的行为。
  ;
表 Item1
列
( Id , FloatField,DateField等
)
表 Item2
列
( Id ,金额,DateField等)
Item1的内容
Id | 金额 |
1000333 | 50 |
1000334 | 50 |
1001133 | 50 |
1001134 | 50 |
1001333 | 1025 |
1001334 | 1025 |
1001335 | 1025 |
Item2的内容
Id | 金额 |
202 | 50.02 |
203 | 49.89 |
1002 | 50 |
1003 | 50 |
1202 | 1075 |
1203 | 1075 |
1302 | 1075 |
内部联接与SQL中的ABS差异
选择 A.Id,A.Amount,B 。 Id , B.Amount
来自 Item1 A inner
加入#Item2 B
( ABS ( A.Amount
- B.Amount )
< 0.5 )
预期结果:所有差异小于< 0.5的行我在SQL查询中得到了结果。
我想在Dynamic LINQ中执行类似的连接(在两个集合之间)。
我只知道运行时的列名,数据类型和差值。
请指导我。
你想用Dynamic做什么?完整的表达式?
如果是的,我不认为这是可能的。
public static IQueryable Join(这个IQueryable外部,IEnumerable内部,字符串outerSelector,字符串innerSelector,字符串结果选择器,参数object [] values)
{
if(inner == null)抛出新的ArgumentNullException(" inner");
if(outerSelector == null)抛出新的ArgumentNullException(" outerSelector" );
if(innerSelector == null)抛出新的ArgumentNullException(" innerSelector");
if(resultsSelector == null)抛出新的ArgumentNullException("结果) sSelctor");
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,"内在的")};
LambdaExpression resultsSelectorLambda = DynamicExpression.ParseLambda(parameters,null,resultsSelector,values);
返回outer.Provider.CreateQuery(
Expression.Call(
typeof(可查询)," 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)));
}
//通用重载。
public static IQueryable< T>加入< T>(此IQueryable< T>外部,IEnumerable< T>内部,字符串outerSelector,字符串innerSelector,字符串resultsSelector,params object []值)
{
return(IQueryable< T>)加入((IQueryable)outer,(IEnumerable)inner,outerSelector,innerSelector,resultsSelector,values);
}您还可以研究使用数据表的选项
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)"
);看看这个问题:
http://stackoverflow.com/questions/389094/how-to-创建-A-动态LINQ联接延伸法
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.
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
这篇关于动态LINQ加入扩展的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!