C# Expression使用实践——为FreeSql增加WhereLike方法
1.背景
前端有一个是否模糊查询的参数，当未勾选时，所有字符串类型的值使用a = 'xxx'，勾选后使用a like '%xxx%'
如果使用WhereIf来写，每一个判断条件都需要写两行
.WhereIf(!request.IsLike && request.A.HasValue(), (i, o) => o.A== request.A)
.WhereIf(request.IsLike && request.A.HasValue(), (i, o) => o.A.Contains(request.A))
2.初次尝试
public static ISelect<T1, T2> WhereLike<T1, T2>(this ISelect<T1, T2> query, Func<HzyTuple<T1, T2>, string> filed, string value, bool isLike = false)
where T2 : class
{
if (isLike)
{
query = query.Where(x => filed(x).Contains(value));
}
else
{
query = query.Where(x => filed(x) == value);
}
return query;
}
public static ISelect<T1, T2> WhereLike<T1, T2>(this ISelect<T1, T2> query, Func<T1, T2, string> filed, string value, bool isLike = false) where T2 : class
{
if (isLike)
{
query = query.Where(x => filed(x.t1, x.t2).Contains(value));
}
else
{
query = query.Where(x => filed(x.t1, x.t2) == value);
}
return query;
}
执行后，发现生成的sql语句不正确where = 'xxx'，少了字段名，猜测FreeSql底层表达式解析可能不支持这种写法
3.解决办法
开始大量Google Expression 相关资料，最终找到了解决办法
public static ISelect<T1> WhereLike<T1>(this ISelect<T1> query, Expression<Func<T1, string>> filed, string value, bool isLike = false) where T1 : class
{
var valueExp = Expression.Constant(value);
if (isLike)
{
var method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
query = query.Where(Expression.Lambda<Func<T1, bool>>(Expression.Call(filed.Body, method, valueExp), filed.Parameters));
}
else
{
query = query.Where(Expression.Lambda<Func<T1, bool>>(Expression.Equal(filed.Body, valueExp), filed.Parameters));
}
return query;
}
#region HzyTuple
public static ISelect<T1, T2> WhereLike<T1, T2>(this ISelect<T1, T2> query, Expression<Func<HzyTuple<T1, T2>, string>> filed, string value, bool isLike = false)
where T2 : class
{
var valueExp = Expression.Constant(value);
if (isLike)
{
var method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
query = query.Where(Expression.Lambda<Func<HzyTuple<T1, T2>, bool>>(Expression.Call(filed.Body, method, valueExp), filed.Parameters));
}
else
{
query = query.Where(Expression.Lambda<Func<HzyTuple<T1, T2>, bool>>(Expression.Equal(filed.Body, valueExp), filed.Parameters));
}
return query;
}
#endregion
#region Not HzyTuple
public static ISelect<T1, T2> WhereLike<T1, T2>(this ISelect<T1, T2> query, Expression<Func<T1, T2, string>> filed, string value, bool isLike = false)
where T2 : class
{
var valueExp = Expression.Constant(value);
if (isLike)
{
var method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
query = query.Where(Expression.Lambda<Func<T1, T2, bool>>(Expression.Call(filed.Body, method, valueExp), filed.Parameters));
}
else
{
query = query.Where(Expression.Lambda<Func<T1, T2, bool>>(Expression.Equal(filed.Body, valueExp), filed.Parameters));
}
return query;
}
#endregion
4.附
以上表达式代码有一定规律，一个个写太费时间了，所以搞了个python脚本生成代码
脚本如下
#!/usr/bin/python3
def PrintHzyTuple(number):
TValue = ', '.join(['T'+str(i+1) for i in range(number)])
TWhere = ''
for i in range(number-1):
TWhere += f' where T{str(i+2)} : class\n'
template = f'''
public static ISelect<{TValue}> WhereLike<{TValue}>(this ISelect<{TValue}> query, Expression<Func<HzyTuple<{TValue}>, string>> filed, string value, bool isLike = false)
{TWhere}
{{
var valueExp = Expression.Constant(value);
if (isLike)
{{
var method = typeof(string).GetMethod("Contains", new[] {{ typeof(string) }});
query = query.Where(Expression.Lambda<Func<HzyTuple<{TValue}>, bool>>(Expression.Call(filed.Body, method, valueExp), filed.Parameters));
}}
else
{{
query = query.Where(Expression.Lambda<Func<HzyTuple<{TValue}>, bool>>(Expression.Equal(filed.Body, valueExp), filed.Parameters));
}}
return query;
}}
'''
print(template)
def PrintHzyTupleIf(number):
TValue = ', '.join(['T'+str(i+1) for i in range(number)])
TWhere = ''
for i in range(number-1):
TWhere += f' where T{str(i+2)} : class\n'
template = f'''
public static ISelect<{TValue}> WhereLikeIf<{TValue}>(this ISelect<{TValue}> query, bool condition, Expression<Func<HzyTuple<{TValue}>, string>> filed, string value, bool isLike = false)
{TWhere}
{{
if (!condition){{
return query;
}}
var valueExp = Expression.Constant(value);
if (isLike)
{{
var method = typeof(string).GetMethod("Contains", new[] {{ typeof(string) }});
query = query.Where(Expression.Lambda<Func<HzyTuple<{TValue}>, bool>>(Expression.Call(filed.Body, method, valueExp), filed.Parameters));
}}
else
{{
query = query.Where(Expression.Lambda<Func<HzyTuple<{TValue}>, bool>>(Expression.Equal(filed.Body, valueExp), filed.Parameters));
}}
return query;
}}
'''
print(template)
def PrintNotHzyTuple(number):
TValue = ', '.join(['T'+str(i+1) for i in range(number)])
TWhere = ''
for i in range(number-1):
TWhere += f' where T{str(i+2)} : class\n'
template = f'''
public static ISelect<{TValue}> WhereLike<{TValue}>(this ISelect<{TValue}> query, Expression<Func<{TValue}, string>> filed, string value, bool isLike = false)
{TWhere}
{{
var valueExp = Expression.Constant(value);
if (isLike)
{{
var method = typeof(string).GetMethod("Contains", new[] {{ typeof(string) }});
query = query.Where(Expression.Lambda<Func<{TValue}, bool>>(Expression.Call(filed.Body, method, valueExp), filed.Parameters));
}}
else
{{
query = query.Where(Expression.Lambda<Func<{TValue}, bool>>(Expression.Equal(filed.Body, valueExp), filed.Parameters));
}}
return query;
}}
'''
print(template)
def PrintNotHzyTupleIf(number):
TValue = ', '.join(['T'+str(i+1) for i in range(number)])
TWhere = ''
for i in range(number-1):
TWhere += f' where T{str(i+2)} : class\n'
template = f'''
public static ISelect<{TValue}> WhereLikeIf<{TValue}>(this ISelect<{TValue}> query, bool condition, Expression<Func<{TValue}, string>> filed, string value, bool isLike = false)
{TWhere}
{{
if (!condition){{
return query;
}}
var valueExp = Expression.Constant(value);
if (isLike)
{{
var method = typeof(string).GetMethod("Contains", new[] {{ typeof(string) }});
query = query.Where(Expression.Lambda<Func<{TValue}, bool>>(Expression.Call(filed.Body, method, valueExp), filed.Parameters));
}}
else
{{
query = query.Where(Expression.Lambda<Func<{TValue}, bool>>(Expression.Equal(filed.Body, valueExp), filed.Parameters));
}}
return query;
}}
'''
print(template)
def RunHzyTuple(num,start = 2):
if start < num:
PrintHzyTuple(start)
start += 1
RunHzyTuple(num,start)
else:
PrintHzyTuple(start)
def RunNotHzyTuple(num,start = 2):
if start < num:
PrintNotHzyTuple(start)
start += 1
RunNotHzyTuple(num,start)
else:
PrintNotHzyTuple(start)
def RunHzyTupleIf(num,start = 2):
if start < num:
PrintHzyTupleIf(start)
start += 1
RunHzyTupleIf(num,start)
else:
PrintHzyTupleIf(start)
def RunNotHzyTupleIf(num,start = 2):
if start < num:
PrintNotHzyTupleIf(start)
start += 1
RunNotHzyTupleIf(num,start)
else:
PrintNotHzyTupleIf(start)
if __name__=="__main__":
print('''
public static ISelect<T1> WhereLike<T1>(this ISelect<T1> query, Expression<Func<T1, string>> filed, string value, bool isLike = false) where T1 : class
{
var valueExp = Expression.Constant(value);
if (isLike)
{
var method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
query = query.Where(Expression.Lambda<Func<T1, bool>>(Expression.Call(filed.Body, method, valueExp), filed.Parameters));
}
else
{
query = query.Where(Expression.Lambda<Func<T1, bool>>(Expression.Equal(filed.Body, valueExp), filed.Parameters));
}
return query;
}
''')
print('''
public static ISelect<T1> WhereLikeIf<T1>(this ISelect<T1> query, bool condition, Expression<Func<T1, string>> filed, string value, bool isLike = false) where T1 : class
{
if (!condition){
return query;
}
var valueExp = Expression.Constant(value);
if (isLike)
{
var method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
query = query.Where(Expression.Lambda<Func<T1, bool>>(Expression.Call(filed.Body, method, valueExp), filed.Parameters));
}
else
{
query = query.Where(Expression.Lambda<Func<T1, bool>>(Expression.Equal(filed.Body, valueExp), filed.Parameters));
}
return query;
}
''')
print(" #region HzyTuple")
RunHzyTuple(16)
print(" #endregion\n\n")
print(" #region HzyTuple If")
RunHzyTupleIf(16)
print(" #endregion\n\n")
print(" #region Not HzyTuple")
RunNotHzyTuple(16)
print(" #endregion")
print(" #region Not HzyTuple If")
RunNotHzyTupleIf(16)
print(" #endregion")
