创建动态DLinq元组比较查询 [英] Creating dynamic DLinq tuple comparison query
问题描述
我为< =和<创建了动态扩展方法SQL比较运算符：
public static IQueryable< TLessThanOrEqual< T>（此IQueryable< Tsource，
字符串属性，对象值）;
public static IQueryable< TLessThan< T>（此IQueryable< Tsource，string
属性，对象值）;
例如
var q = db.Customers.LessThanOrEqual（" City"，" London"）;
var res = q.Count（）;
生成SQL
SELECT COUNT（*）
来自客户
WHERE City< =' 'London'';
我需要为2个属性元组比较生成类似的SQL语句
（c1，c2）< =（v1，v2） ：
WHERE ...和c1< = v1 AND（c1< v1或c2< = v2）
like（City，Id ）< =（'''伦敦''，''CUST23''）：
WHERE ...和城市< =''伦敦''AND（城市<''伦敦''或Id< =''CUST23''）;
var q1 = q.LessThanOrEqual（City，London和）;
var q2 = db.Customers.LessThan（" City"，" London"）;
var q3 = db.Customers.LessThanOrEqual（" Id" ;，CUST23;;
var qResult = q1.Where（q2.Or（q3））;
但最后一行会导致错误。
如何解决？
我应该使用谓词构建器还是可以使用OR组合IQueryable< T>
扩展方法？
如何创建比较多达6个属性元组的通用方法（c1，c2，
.... c6）< =（v1，v2，... v6）其中属性名称和值传递为
数组？
Andrus。
行;这看起来很有效......用LINQ-to-SQL测试过，但是这里没有什么非常好的b $ b复杂（从LINQ-provider的角度来看），所以它应该公平地转换为
很好的其他供应商。
Marc
使用系统;
使用System.Linq;
使用System.Linq.Expressions;
使用System.Reflection;
命名空间ConsoleApplication32
{
静态课程计划
{
公共静态IQueryable< TWhereLessThan< T>（此
IQueryable< Tsource，T值， params string [] properties）
{
返回WhereInequality（来源，值，真，属性）;
}
public static IQueryable< TWhereGreaterThan< T>（此
IQueryable< Tsource，T value，params string []属性）
{
return WhereInequality（source，value，false，properties）;
}
private static IQueryable< TWhereInequality< T&g t;（
IQueryable< Tsource，T value，
bool lessThan，
params string [] properties）{
// sense-check argments
if（source == null）throw new
ArgumentNullException（" source"）;
if（value == null）throw new
ArgumentNullException（" value"）;
if（properties == null）throw new
ArgumentNullException（" properties"）;
if（properties.Length == 0）throw new
ArgumentException（" No properties to comparison），"属性）;
// xParam是正在测试的项目
// testValue是用于
$ b的一组值$ b比较
ParameterExpression xParam =
Expression.Parameter（typeof（T），" x"）;
ConstantExpression testValue = Expression。常数（值，
typeof（T））;
//需要一个可重复的机制来构建不等式，
注意到
//对于字符串我们需要另外Expression.Call
string.Compare，
//因为不等式是没有直接为字符串定义;
我们将重新使用
//帮助构建字符串的整体不等式 -
比较
Func< Expression，Expression，BinaryExpression>
overallComparison，overallStringComparison;
if（lessThan）
{/ /不喜欢这里的三元条件运算符...
overallComparison = Expression.LessThan;
}
else
{
overallComparison = Expression.GreaterThan;
}
类型[] twoStrings = new [] {typeof（string），
typeof（string）};
overallStringComparison =（x，y）= overallComparison（
Expression.Call（typeof（string）.GetMethod （QUOT;比较和QUOT; ，
twoStrings），x，y），
Expression.Constant（0，typeof（int））
）;
//向后构建表达式以简化构造
//注意：已经检查过非零道具，所以expr *将*
得到设置
表达式expr = null;
for（int i = properties.Length - 1; i> = 0; i--）
{
//找到成员（道具/田地），并为正在测试的行获得
// MemberExpression和
//已知测试值
string propName = properties [i];
MemberInfo member = typeof（T）.GetMember（propName，
MemberTypes.Property | MemberTypes.Field，
BindingFlags.Public | BindingFlags.NonPublic |
BindingFlags.Instance）.Single（）;
MemberExpression xMember，testMember;
bool isString;
switch（member.MemberType）
{
case MemberTypes.Field ：
isString =（（FieldInfo）成员）.FieldType ==
typeof（string）;
xMember = Expression.Field（xParam，
（FieldInfo）成员）;
testMember = Expression.Field（testValue，
（FieldInfo）成员）;
break;
case MemberTypes.Property：
isString =（（PropertyInfo）成员）.PropertyType
== typeof（string）;
xMember = Expression.Property（xParam，
（PropertyInfo）成员）;
testMember = Expression.Property（testValue，
（PropertyInfo）成员）;
break ;
默认值：
抛出新的
NotSupportedException（string.Format（" {0}（{1}）"，成员。姓名，
member.MemberType））;
}
//为会员构建比较
考虑，
//组成现有表达式（如果有的话）
Func< Expression，Expression，BinaryExpression>
比较= isString
？ overallStringComparison：
overallComparison;
if（expr == null）
{
expr =比较（xMember， testMember）;
}
else
{
expr =
表情。 OrElse（
比较（xMember，testMember），
Expression.AndAlso（
Expression.Equal（xMember，testMember），
expr
）
）;
}
}
//构建一个lambda并使用它来过滤数据
返回source.Where（Expression.Lambda< Func< T，bool>>（expr，
xParam） ）;
}
static void Main（string [] args）
{
using（var ctx = new DataClasses1DataContext（））
{
ctx.Log = Console.Out;
var qry = ctx.Products.WhereLessThan （
新产品{ProductName =" foo"，UnitPrice =
34.3M，UnitsInStock = 3}，
" QuantityPerUnit"， UnitPrice，&quo t; UnitsInStock"）;
foreach（qry中的行）
{
Console.WriteLine（" {0 }：{1}"，row.ProductName，
row.ProductID）;
}
}
}
}
}
Marc，
好的;这看起来很有效......用LINQ-to-SQL测试过，但是这里没有什么非常好的b $ b复杂（从LINQ-provider的角度来看），所以它应该公平地转换为
以及其他供应商。
谢谢。
我发现了两个空值问题。
A. null属性值。
DbLinq生成类似
的查询
SELECT COUNT（*）
FROM dok d
WHERE d
I created dynamic extension methods for <= and < SQL comparison operators:
public static IQueryable<TLessThanOrEqual<T>(this IQueryable<Tsource,
string property, object value);
public static IQueryable<TLessThan<T>(this IQueryable<Tsource, string
property, object value);
For example
var q = db.Customers.LessThanOrEqual( "City", "London" );
var res = q.Count();
generates SQL
SELECT COUNT(*)
FROM Customers
WHERE City <= ''London'';
I need to generate similar SQL statement for 2 property tuple comparison
(c1,c2)<=(v1,v2):
WHERE ... AND c1<=v1 AND ( c1<v1 OR c2<=v2 )
like (City,Id) <= (''London'', ''CUST23'' ):
WHERE ... AND City<=''London'' AND ( City<''London'' OR Id<=''CUST23'' );
I tried
var q1 = q.LessThanOrEqual( "City", "London" );
var q2 = db.Customers.LessThan( "City", "London" );
var q3 = db.Customers.LessThanOrEqual( "Id", "CUST23" );
var qResult = q1.Where( q2.Or(q3) );
but last line causes error.
How to fix ?
Should I use predicate builder or is it possible to combine IQueryable<T>
extension methods using OR ?
How to create general method which compares up to 6-property tuples (c1,c2,
.... c6 )<=(v1, v2, ... v6) where property names and values are passed as
arrays ?
Andrus.
OK; this seems to work... tested with LINQ-to-SQL, but nothing very
complex here (from a LINQ-provider perspective), so it should
translate fairly well to other providers.
Marc
using System;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
namespace ConsoleApplication32
{
static class Program
{
public static IQueryable<TWhereLessThan<T>(this
IQueryable<Tsource, T value, params string[] properties)
{
return WhereInequality(source, value, true, properties);
}
public static IQueryable<TWhereGreaterThan<T>(this
IQueryable<Tsource, T value, params string[] properties)
{
return WhereInequality(source, value, false, properties);
}
private static IQueryable<TWhereInequality<T>(
IQueryable<Tsource, T value,
bool lessThan,
params string[] properties) {
// sense-check argments
if (source == null) throw new
ArgumentNullException("source");
if (value == null) throw new
ArgumentNullException("value");
if (properties == null) throw new
ArgumentNullException("properties");
if (properties.Length == 0) throw new
ArgumentException("No properties to compare", "properties");
// xParam is the item being tested
// testValue is the single set of values to use for
comparison
ParameterExpression xParam =
Expression.Parameter(typeof(T), "x");
ConstantExpression testValue = Expression.Constant(value,
typeof(T));
// need a repatable mechanism to construct the inequality,
noting that
// for strings we need to additionally Expression.Call
string.Compare,
// since inequality is not directly defined for strings;
we''ll re-use
// the overall inequality to aid in building the string-
comparison
Func<Expression, Expression, BinaryExpression>
overallComparison, overallStringComparison;
if (lessThan)
{ // doesn''t like the ternary conditional operator here...
overallComparison = Expression.LessThan;
}
else
{
overallComparison = Expression.GreaterThan;
}
Type[] twoStrings = new[] {typeof(string),
typeof(string)};
overallStringComparison = (x,y) =overallComparison(
Expression.Call(typeof(string).GetMethod("Compare" ,
twoStrings), x, y),
Expression.Constant(0, typeof(int))
);
// build the expression backwards to simplify construction
// note: already checked non-zero props, so expr *will*
get set
Expression expr = null;
for (int i = properties.Length - 1; i >= 0; i--)
{
// locate the member (prop/field), and obtain a
// MemberExpression for both the row being tested and
// the known test values
string propName = properties[i];
MemberInfo member = typeof(T).GetMember(propName,
MemberTypes.Property | MemberTypes.Field,
BindingFlags.Public | BindingFlags.NonPublic |
BindingFlags.Instance).Single();
MemberExpression xMember, testMember;
bool isString;
switch (member.MemberType)
{
case MemberTypes.Field:
isString = ((FieldInfo)member).FieldType ==
typeof(string);
xMember = Expression.Field(xParam,
(FieldInfo)member);
testMember = Expression.Field(testValue,
(FieldInfo)member);
break;
case MemberTypes.Property:
isString = ((PropertyInfo)member).PropertyType
== typeof(string);
xMember = Expression.Property(xParam,
(PropertyInfo)member);
testMember = Expression.Property(testValue,
(PropertyInfo)member);
break;
default:
throw new
NotSupportedException(string.Format("{0} ({1})", member.Name,
member.MemberType));
}
// build the comparison for the member being
considered,
// composing the existing expression (if any)
Func<Expression, Expression, BinaryExpression>
comparison = isString
? overallStringComparison :
overallComparison;
if (expr == null)
{
expr = comparison(xMember, testMember);
}
else
{
expr =
Expression.OrElse(
comparison(xMember, testMember),
Expression.AndAlso(
Expression.Equal(xMember, testMember),
expr
)
);
}
}
// build a lambda and use it to filter the data
return source.Where(Expression.Lambda<Func<T, bool>>(expr,
xParam));
}
static void Main(string[] args)
{
using (var ctx = new DataClasses1DataContext())
{
ctx.Log = Console.Out;
var qry = ctx.Products.WhereLessThan(
new Product { ProductName = "foo", UnitPrice =
34.3M, UnitsInStock = 3 },
"QuantityPerUnit", "UnitPrice", "UnitsInStock");
foreach (var row in qry)
{
Console.WriteLine("{0}: {1}", row.ProductName,
row.ProductID);
}
}
}
}
}
Marc,
OK; this seems to work... tested with LINQ-to-SQL, but nothing very
complex here (from a LINQ-provider perspective), so it should
translate fairly well to other providers.Thank you.
I have found two issues with null values.
A. null property values.
DbLinq generates query like
SELECT COUNT(*)
FROM dok d
WHERE d
这篇关于创建动态DLinq元组比较查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
