在EF Core中我们经常会用System.Linq.Expressions系统命名空间的Expression<TDelegate>类型来作为EF Core的查询条件,比如:
using EFLambdaExpression.Entities; using System; using System.Linq; using System.Linq.Expressions; namespace EFLambdaExpression { class Program { static void Main(string[] args) { using (FinanceDigitalToolContext dbContext = new FinanceDigitalToolContext()) { Expression<Func<User, bool>> userExperssion = u => u.UserCode != null; var users = dbContext.User.Where(userExperssion).ToList(); } Console.WriteLine("Press key to quit...."); Console.ReadLine(); } } }
但是如果我们将Expression<Func<User, bool>> userExperssion = u => u.UserCode != null改为Expression<Func<User, bool>> userExperssion = u => { return u.UserCode != null; }那么C#会报错,提示:
A lambda expression with a statement body cannot be converted to an expression tree
所以Expression<Func<User, bool>> userExperssion不能接受带函数体的u => { return u.UserCode != null; }这种lambda函数,它只支持简单的lambda表达式u => u.UserCode != null
虽然Expression<Func<User, bool>> userExperssion右边的lambda表达式中可以使用自定义函数,但是不建议这么做,因为这么做会导致EF Core对数据库表做全表查询
比如我们先执行下面的代码:
using EFLambdaExpression.Entities; using Microsoft.EntityFrameworkCore; using System; using System.Linq; using System.Linq.Expressions; namespace EFLambdaExpression { class Program { static void Main(string[] args) { using (FinanceDigitalToolContext dbContext = new FinanceDigitalToolContext()) { Expression<Func<User, bool>> userExperssion = u => EF.Functions.Like(u.UserCode, "%ADMIN%"); var users = dbContext.User.Where(userExperssion).ToList(); } Console.WriteLine("Press key to quit...."); Console.ReadLine(); } } }
可以从EF Core的日志中看到生成了如下SQL语句:
=============================== EF Core log started =============================== Executed DbCommand (122ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT [u].[ID], [u].[CompanyCode], [u].[CreateTime], [u].[DataStatus], [u].[FirstName], [u].[LastName], [u].[MailAddress], [u].[MiddleName], [u].[Password], [u].[UserCode], [u].[Username] FROM [MD].[User] AS [u] WHERE [u].[UserCode] LIKE N'%ADMIN%' =============================== EF Core log finished ===============================
因为EF.Functions.Like方法是EF Core定义的系统函数,所以我们看到EF Core可以将该C#方法转换为SQL查询中的LIKE语句作为查询的WHERE条件。
如果现在我们将EF.Functions.Like的调用放到我们自定义的一个C#方法UserCodeLike中去,然后在Expression<Func<User, bool>> userExperssion右边的lambda表达式中调用自定义方法UserCodeLike,代码如下所示:
using EFLambdaExpression.Entities; using Microsoft.EntityFrameworkCore; using System; using System.Linq; using System.Linq.Expressions; namespace EFLambdaExpression { class Program { static bool UserCodeLike(User user, string pattern) { return EF.Functions.Like(user.UserCode, pattern); } static void Main(string[] args) { using (FinanceDigitalToolContext dbContext = new FinanceDigitalToolContext()) { Expression<Func<User, bool>> userExperssion = u => UserCodeLike(u, "%ADMIN%"); var users = dbContext.User.Where(userExperssion).ToList(); } Console.WriteLine("Press key to quit...."); Console.ReadLine(); } } }
可以从EF Core的日志中看到生成了如下SQL语句:
=============================== EF Core log started =============================== Executed DbCommand (124ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT [u].[ID], [u].[CompanyCode], [u].[CreateTime], [u].[DataStatus], [u].[FirstName], [u].[LastName], [u].[MailAddress], [u].[MiddleName], [u].[Password], [u].[UserCode], [u].[Username] FROM [MD].[User] AS [u] =============================== EF Core log finished ===============================
可以看到,虽然最后查出来的结果是一样的,但是这次EF Core对User表做了全表查询,在SQL的查询语句中没有生成任何WHERE条件,说明EF Core不认识我们定义的UserCodeLike方法,不知道怎么将UserCodeLike方法转换为对应的SQL语句,所以干脆就做了全表查询,将User表的数据从数据库全查出来后,再调用我们的UserCodeLike方法来过滤数据,如果User表的数据量非常大,这样效率其实会非常低。所有不建议在EF Core的lambda表达式中使用自定义函数。