8,492
社区成员
发帖
与我相关
我的任务
分享求大神来帮忙，linq to entities 怎么实现“动态查询“，类ibatis的动态SQL
最近在用MVC3+entity framework做WEB开发，发现以前最常用的动态查询实现不了，好困惑。比如说，数据库里的一张表有10个字段，然后这10个字段都有可能成为查询条件但都不是必须的。用LINQ怎么样实现？就像是ibatis的那个动态SQL的的效果，真心不知道怎么实现，难道想用个entity framework 这么难么？，求大神呀！！
...全文
请发表友善的回复…
发表回复
laokaizzz 2014-06-26
- 打赏
- 举报
上面最后一句写错了，可以忽略。
或者你可以参考这个
http://www.cnblogs.com/happyhippy/archive/2010/01/26/1656961.html
laokaizzz 2014-06-26
- 打赏
- 举报
使用 PredicateBuilder
var innerChild = PredicateBuilder.True<表对应的实体类>();
innerChild = innerChild.And(p => p.字段a== 参数1);
if (!string.IsNullOrEmpty(参数2))
{
innerChild = innerChild.And(p => p.字段b.Contains(参数2));
}
if (!string.IsNullOrEmpty(参数3))
{
innerChild = innerChild.And(p => p.字段c== Convert.ToDateTime(参数3));
}
//然后把innerChild作为where条件传进去。
penguin_ku 2014-06-20
- 打赏
- 举报
dynamic linq完全可以解决你的问题。当然，版主说的也对，你拼接下不就行了，不就是拼接where么？
save4me 2014-06-18
- 打赏
- 举报
Dynamically Composing Expression Predicates(英文)
Code Listings for C# 5.0 in a Nutshell这本书的代码下载
using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;
public static class PredicateBuilder
{
public static Expression<Func<T, bool>> True<T> () { return f => true; }
public static Expression<Func<T, bool>> False<T> () { return f => false; }
public static Expression<Func<T, bool>> Or<T> (this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
return Expression.Lambda<Func<T, bool>>
(Expression.OrElse (expr1.Body, invokedExpr), expr1.Parameters);
}
public static Expression<Func<T, bool>> And<T> (this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
return Expression.Lambda<Func<T, bool>>
(Expression.AndAlso (expr1.Body, invokedExpr), expr1.Parameters);
}
}
save4me 2014-06-18
- 打赏
- 举报
参考: Build Where Clause Dynamically in Linq(英文)
通过一个Filter类来动态创建WHERE。下面的代码支持泛型和多种比较运算。
public static class ExpressionBuilder
{
private static MethodInfo containsMethod = typeof(string).GetMethod("Contains" );
private static MethodInfo startsWithMethod =
typeof(string).GetMethod("StartsWith", new Type [] {typeof(string)});
private static MethodInfo endsWithMethod =
typeof(string).GetMethod("EndsWith", new Type [] { typeof(string)});
public static Expression<Func<T,
bool >> GetExpression<T>(IList<Filter> filters)
{
if (filters.Count == 0)
return null ;
ParameterExpression param = Expression.Parameter(typeof (T), "t" );
Expression exp = null ;
if (filters.Count == 1)
exp = GetExpression<T>(param, filters[0]);
else if (filters.Count == 2)
exp = GetExpression<T>(param, filters[0], filters[1]);
else
{
while (filters.Count > 0)
{
var f1 = filters[0];
var f2 = filters[1];
if (exp == null )
exp = GetExpression<T>(param, filters[0], filters[1]);
else
exp = Expression.AndAlso(exp, GetExpression<T>(param, filters[0], filters[1]));
filters.Remove(f1);
filters.Remove(f2);
if (filters.Count == 1)
{
exp = Expression .AndAlso(exp, GetExpression<T>(param, filters[0]));
filters.RemoveAt(0);
}
}
}
return Expression.Lambda<Func<T, bool>>(exp, param);
}
private static Expression GetExpression<T>(ParameterExpression param, Filter filter)
{
MemberExpression member = Expression.Property(param, filter.PropertyName);
ConstantExpression constant = Expression.Constant(filter.Value);
switch (filter.Operation)
{
case Op.Equals:
return Expression.Equal(member, constant);
case Op.GreaterThan:
return Expression.GreaterThan(member, constant);
case Op.GreaterThanOrEqual:
return Expression.GreaterThanOrEqual(member, constant);
case Op.LessThan:
return Expression.LessThan(member, constant);
case Op.LessThanOrEqual:
return Expression.LessThanOrEqual(member, constant);
case Op.Contains:
return Expression.Call(member, containsMethod, constant);
case Op.StartsWith:
return Expression.Call(member, startsWithMethod, constant);
case Op.EndsWith:
return Expression.Call(member, endsWithMethod, constant);
}
return null ;
}
private static BinaryExpression GetExpression<T>
(ParameterExpression param, Filter filter1, Filter filter2)
{
Expression bin1 = GetExpression<T>(param, filter1);
Expression bin2 = GetExpression<T>(param, filter2);
return Expression.AndAlso(bin1, bin2);
}
}
public class Filter
{
public string PropertyName { get ; set ; }
public Op Operation { get ; set ; }
public object Value { get ; set ; }
}
public enum Op
{
Equals,
GreaterThan,
LessThan,
GreaterThanOrEqual,
LessThanOrEqual,
Contains,
StartsWith,
EndsWith
}
// 调用
List<Filter> filter = new List<Filter>()
{
new Filter { PropertyName = "City" ,
Operation = Op .Equals, Value = "Mitrovice" },
new Filter { PropertyName = "Name" ,
Operation = Op .StartsWith, Value = "L" },
new Filter { PropertyName = "Salary" ,
Operation = Op .GreaterThan, Value = 9000.0 }
};
var deleg = ExpressionBuilder.GetExpression<Person>(filter).Compile();
var filteredCollection = persons.Where(deleg).ToList();
wg5945 2014-06-18
- 打赏
- 举报
q107770540 2014-06-18
- 打赏
- 举报
那你用dynamic linq试试
google一下
aini137025866 2014-06-18
- 打赏
- 举报
版猪您好，SQL拼接当然可以解决这个问题，可现在用LINQ怎么解决呀？还有你写的这个代码太坑了，我要是有100个字段那不是要写100个if ，我想过用反射，可lambda不能将反射的方法转换成SQL呀。
q107770540 2014-06-17
- 打赏
- 举报
拼接SQL不能解决你的问题么？
var q= db.users;
if(userid>0)
{
q=q.Where(x=>x.id==id);
}
if(name!="")
{
q=q.Where(x=>x.name==name);
}
