实体:
class student{
//编号
string code{get;set;}
}
查询编号是“1122”的,注意这里1122是字符串类型的:
测试1(db是数据库上下文对象):
string _code="1122";
db.student.Where(o=>o.code==_code).FirstOrDefault();
//生成的SQL是
select * from student where code=1122 and rownum<=1;//1122没有引号,这就是问题,会导致索引失效。
测试2:
db.student.Where(o=>o.code=="1122").FirstOrDefault();
//生成SQL是
select * from student where code='1122' and rownum<=1;//这是正确的
实际项目中一般不会用测试2,因为查询参数多数是动态的,“测试1”的写法不变的情况下,如何生成带引号的SQL呢?
方法1:使用System.Linq.Dynamic。
写法1:
string _code="1122";
db.student.Where($"o=>o.code==\"{_code}\"").FirstOrDefault();
写法2:
db.student.Where("o=>o.code==@0",_code).FirstOrDefault();
方法2:扩展方法,如下:
/// <summary>
/// 解决Where方法 字符串类型的值生成SQL没有引号的问题
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="expr"></param>
/// <returns></returns>
public static IQueryable<T> EQ<T>(this IQueryable<T> query, Expression<Func<T,bool>> expr)
{
Expression e = expr.Body;
BinaryExpression be = e as BinaryExpression;
MemberExpression me = be.Left as MemberExpression;
//字段名
string field = me.Member.Name;
#region 字段值
object value = new object();
var body = expr.Body;
if (body.NodeType == ExpressionType.Constant)
{
value = ((ConstantExpression)body).Value;
}
else
{
var memberExpression = (MemberExpression)be.Right;
var @object = ((ConstantExpression)(memberExpression.Expression)).Value;
if (memberExpression.Member.MemberType == MemberTypes.Field)
{
value = ((FieldInfo)memberExpression.Member).GetValue(@object);
}
else if (memberExpression.Member.MemberType == MemberTypes.Property)
{
value = ((PropertyInfo)memberExpression.Member).GetValue(@object);
}
}
#endregion
var param = DynamicLinq.CreateLambdaParam<T>("o");
Expression left = Expression.Property(param, field);
Expression right = Expression.Constant(value);
Expression filter2 = Expression.Equal(left, right);
Expression<Func<T, bool>> lambda = param.GenerateTypeLambda<T>(filter2); //最终组成lambda
query = query.Where(lambda);
return query;
}
用法:
string _code="1122";
db.student.EQ(o=>o.code==_code).FirstOrDefault();
以上两种方法都可以正常生成带引号的SQL。