EntityFramework之原始查询及性能优化（六）

2021-07-11 21:30:08

前言

在EF中我们可以通过Linq来操作实体类，但是有些时候我们必须通过原始sql语句或者存储过程来进行查询数据库，所以我们可以通过EF Code First来实现，但是SQL语句和存储过程无法进行映射，于是我们只能手动通过上下文中的SqlQuery和ExecuteSqlCommand来完成。

SqlQuery

sql语句查询实体

通过DbSet中的SqlQuery方法来写原始sql语句返回实体实例，如果是通过Linq查询返回的那么返回的对象将被上下文（context）所跟踪。

首先给出要操作的Student（学生类），对于其映射这里不再叙述，本节只讲查询。

    public class Student 
    {
        public int ID { get; set; }

        public string Name { get; set; }

        public int Age { get; set; }

    }

如果我们要查询学生表（Student）所有数据应该如何操作呢？下面我们通过代码来进行演示：

EntityDbContext ctx = new EntityDbContext();

SqlParameter[] parameter = { };

ctx.Database.SqlQuery<Student>("select * from student", parameter).ToList();

我们通过Sql Server Profiler监控其执行语句如下图，达到预期所想。

【注意1】上述我标注实体实例为红色的地方，返回的必须是一个实体即所有列，如果有些列未返回将报错！假设我们只查出学生表中Age和Name,我们这样写查看语句

ctx.Database.SqlQuery<Student>("select Name, Age from Student").ToList();

这样将会报错如下：

【注意2】上述我标注了 ToList() 为红色的地方，正如上述所说Linq查询一样，这个查询语句直到结果全部被枚举完也就是ToList()之后才会执行。

那问题来了，接下来我们进行如下操作，数据库会进行相应的修改？

                var entity = ctx.Database.SqlQuery<Student>("select * from student").ToList();

                entity.Last().Name = "0928";

                ctx.SaveChanges();

我们查询出数据，并将其最后一条数据为xpy0928的修改为0928。结果如下：

显示并未进行修改，那我们接着进行如下操作，又会如何呢？

                var entity = ctx.Set<Student>().SqlQuery("select * from student").ToList();

                entity.Last().Name = "0928";

                ctx.SaveChanges();

结果如下，显示进行了相应的改变：

所以基于此我们得出结论：

ctx.Database.SqlQuery<TEntity>()：SqlQuery方法获得的实体查询是在数据库（Database）上，实体不会被上下文跟踪。

ctx.Set<TEntity>().SqlQuery()：SqlQuery方法获得实体查询在上下文中的实体集合上（DbSet）上，实体会被上下文跟踪。

那么问题来了，如果要是有参数的话该如何进行查询呢？

例如：要查询Name="xpy0928"和Age=5的学生该如何查询呢？下面我们一步一步来进行尝试和操作

            var Name = "xpy0928";
            
            var Age = 5;
            
            var sql = "select Name, Age from Student where Name = @Name and Age = @Age";

            ctx.Database.SqlQuery<Student>(sql, Name, Age).ToList();

我们运行看看，结果出错如下：

先不管错误，我们进行第二次尝试：

            var Name = "xpy0928";
            
            var Age = 5;
            
            var sql = "select ID, Name, Age from Student where Name = {0} and Age = {1}";

            ctx.Database.SqlQuery<Student>(sql, Name, Age).FirstOrDefault();

结果查询正常进行，未出错，从下面监控中可以看到：

从出错的上面那个到这个正常运行的相信你看到区别了，我也已进行红色标记，既然上面的参数@符号不好使，我们用SqlParameter试试看：

            var Name = "xpy0928";

            var Age = 5;

            var sql = "select ID, Name, Age from Student where Name = @Name and Age = @Age";

            ctx.Database.SqlQuery<Student>(
                sql, 
                new SqlParameter("@Name", Name),
                new SqlParameter("@Age", Age));

结果运行正确，所以第一种出现的错误就是因为未使用SqlParameter，而该SqlParameter是继承自DbContext中的DbParameter通过下图可以看出：

至此我们总结出进行查询的两种方式：

通过使用参数如{0}语法来实现

通过使用DbParameter子类并且使用@ParamateName语法来实现

sql语句查询非实体类型

通过sql语句我们能返回任意类型的实例包括类型！假设我们只查出学生表中（某一列）所有学生的Age（年龄），我们通过SqlQuery方法这样做：

ctx.Database.SqlQuery<int>("select Age from Student").ToList();

我们通过快速监视查到返回Age的集合如下，如我们所期望：

从上述你是不是发现EF通过sql查询和ADO.NET查询数据库没什么区别呢？no，远不止于此，请继续往下看！

*通过存储过程加载实体

我们可以加载实体通过存储过程获得的结果。例如：我们获得所有的学生列表，可以进行如下操作:

ctx.Database.SqlQuery<Student>("dbo.GetList").ToList();

如此将执行数据库中名为 GetList() 的存储过程，就是这么简单！似乎没什么特别的，你会想还不如用sql语句查询了，其实远不止于此，上述给的例子是无参数，如果我们需要参数呢？假设我们要获得Age(年龄)等于5的所有人的姓名和年龄，那么该如何实现呢？

我们一步一步实现：

先创建要调用的存储过程GetList

CREATE PROCEDURE [dbo].[GetList]
    @Age INT
AS
BEGIN
    SELECT ID, Name, Age FROM dbo.Student WHERE Age = @Age
END

/*查询出的所有列必须对应返回实体中的所有字段，缺一不可，否则报错*/

EF上下文调用存储过程：

 var param = new SqlParameter("Age", 5);

 var list = ctx.Database.SqlQuery<Student>("dbo.GetList @Age", param).ToList();

运行结果如预期一样！【注意】在调用存储过程中，如果数据库是Sql 2005要在存储过程名称前加上 EXEC ，否则报错。

那么问题又来了，如果要输出参数的值，那么该如何操作呢？

假设要通过学生名字(Name)来进行分页，此时还要获得数据总条数。于是我们进行下面操作：

第一步：创建要调用存储过程

CREATE PROCEDURE [dbo].[Myproc]
    @Name NVARCHAR(max),
    @PageIndex int,
    @PageSize INT,
    @TotalCount int OUTPUT
as 

    declare @startRow int
    declare @endRow    int
    
    set @startRow = (@PageIndex - 1) * @PageSize + 1
    set @endRow = @startRow + @PageSize - 1
    
    select *
    FROM
    (
        select top (@endRow)
           ID,
            Age,
            Name,
            row_number() over(order by [ID] desc) as [RowIndex]
        from dbo.Student
    ) as T    
    where [RowIndex] >= @startRow AND T.Name = @Name
    
    SET @TotalCount=(select count(1) as N 
    FROM dbo.Student WHERE Name = @Name)

EF上下文调用存储过程：

            var name = new SqlParameter { ParameterName = "Name", Value = Name };
            var currentpage = new SqlParameter { ParameterName = "PageIndex", Value = currentPage };
            var pagesize = new SqlParameter { ParameterName = "PageSize", Value = pageSize };
            var totalcount = new SqlParameter { ParameterName = "TotalCount", Value = 0, Direction = ParameterDirection.Output };

            var list = ctx.Database.SqlQuery<Student>("Myproc @Name, @PageIndex, @PageSize, @TotalCount output",
        name, currentpage, pagesize, totalcount);

            totalCount = (int)totalcount.Value;  /*获得要输出参数totalcount的值*/

【注意】此时要在要输出的输出参数标记为output。见如图红色标记。

那么问题来了，当通过存储过程查询大量数据时，此时查询出的数据未进行跟踪（由上已知），因为我们要进行后续如删除之类的操作，所以要EF上下文来进行跟踪，我们应该如何操作来提升最大的性能呢？

我们可以对存储过程进行封装，并且可以简化调用存储过程同时提高查询的性能，请看如下：

        public IList<TEntity> ExecuteStoredProcedureList<TEntity>(string commandText, params object[] parameters) where TEntity : class
        {
            if (parameters != null && parameters.Length > 0)
            {
                for (int i = 0; i <= parameters.Length - 1; i++)
                {
                    var p = parameters[i] as DbParameter;
                    if (p == null)
                        throw new Exception("Not support parameter type");

                    commandText += i == 0 ? " " : ", ";

                    commandText += "@" + p.ParameterName;
                    if (p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Output)
                    {

                        commandText += " output";
                    }
                }
            }

            var result = this.Database.SqlQuery<TEntity>(commandText, parameters).ToList();

 
            bool acd = this.Configuration.AutoDetectChangesEnabled;
            
            try
            {
                this.Configuration.AutoDetectChangesEnabled = false;

                for (int i = 0; i < result.Count; i++)
                    result[i] = this.Set<TEntity>().Attach(result[i]);
            }
            finally
            {
                this.Configuration.AutoDetectChangesEnabled = acd;
            }

            return result;
        }

此时存储过程名称后面就无需继续填写存储过程中如@参数了，调用如下：

  var list = ctx.ExecuteStoredProcedureList<Student>("Myproc", pageindex, pagesize, totalcount);

只是做了个简化而已，最关键的是性能上的提高（就是上述红色标记的地方，如果不明白可以参考我有关【我为EF正名】这篇文章），做了下实际测试，当查询10000条数据时，如果不用红色标记，直接将其附加到上下文容器中，则需要如下时间（单位是毫秒）

当添加后，只需如下时间：

第一个和第二个我们分别按照399秒和3秒来算的话，也就是133倍，可想而知，我们仅仅只是一个小的操作，就达到如此大的性能的提升。通过实际测验，如果你现在还担心EF性能的问题，那我也默默无语了，只要你恰当的运用而不是滥用一通。

对于SqlQuery无论是实体还是非实体抑或存储过程查询都存在一定的局限性。因为很容易会出现数据读取器与指定的实体类不兼容，该类型中缺少的成员在同名的数据读取器中没有对应的列，也就是说必须查出该实体中所有字段即映射到数据库中所有列。

非查询命令ExecuteSqlCommand

该查询主要是针对非查询的命令如删除（delete）、修改（update）等，其操作方式和上述SqlQuery一样。

【注意】用此方法对数据库作出的任何的更改，直到实体从数据库中被加载或重新加载，否则此更改对于EF上下文是不透明的。

SqlQuery和ExecuteSqlCommand方法主要区别：SqlQuery返回实体数据或者集合数据，而ExecuteSqlCommand是非查询命令，所以只是返回删除（delete）和更新（update）以及插入（insert）是否成功或者失败的状态码。

为什么要使用DbContext而不使用ObjectContext

DbContext是比较新的API，它其中简单的API被设计的是如此的巧妙，对于开发者来说无疑是一次全新的体验，但是如果你想要使用更加复杂的特性时，这时你不得不从DbContext中来获得ObjectContext并且使用旧的API。并且ADO.NET团队也建议使用越来越受欢迎的DbContext。

EF 4.x生成器创建了更多复杂的类，但是在内部其利用了关系修正，但是此特性却被证明当和延迟加载一起使用时却是相当的低效，所以新的DbContext生成器不再使用那。

所以基于上述描述，ObjectContext未被完全抛弃，它们完全是可以相互进行转换的。

因此在代码上从一个API到另一个的转换也是完全支持的。

（1）db=>ob（通过IObjectContextAdapter中的Adapter从DbContext迁移至ObjectContext）

 var context = ((IObjectContextAdapter)ctx).ObjectContext;

（2）ob=>db（通过DbContext的构造器中的ObjectContext来创建一个新的DbContext上下文实例）

 ObjectContext ob;
 var context = new DbContext(ob, true);

例如在EF 4.x版本中的ObjectContext中使用编译查询（CompiledQuery）来提高查询性能（因为在Linq To Entity使用Linq，EF需要解析表达式树并将其转换为SQL，所以当需要多次查询时可以使用编译查询来保存输出），该编译查询不兼容DbContext。如下：

               Func<EntityDbContext,string,IQueryable<Student>> query= 
 CompiledQuery.Compile<EntityDbContext,string,IQueryable<Student>>
                       ((EntityDbContext ctx,string property)=>
                             from o in ctx.Set<Student>().ToList() where o.Name == property select o 
                       );

               foreach (var item in query(EntityDbContext,"xpy0928")
               {
                   Console.WriteLine(item.Name);
               }

当然使用编译查询也有诸多限制，比如说此查询执行至少不止一次，并且仅仅是参数不同而已等等。

性能优化

（1）AsNoTracking

前几篇文章也已涉及到关于变更追踪的问题，如果当从数据库查出数据后并对其数据进行相应的更改，此时可以通过局部关闭变更追踪以及手动更改其状态达到一点点小小的优化。如下：

                var list = ctx.Set<Student>().AsNoTracking().ToList();

                var entity = list.Last(d => d.Name == "0928");
             
                ctx.Set<Student>().Attach(entity);

                entity.Name = "xpy0928";

                ctx.Entry(entity).State = EntityState.Modified;

                ctx.SaveChanges();

  /*
                先关闭追踪，然后对其实体数据进行修改，然后Attach附加到到上下文中，并通过Entry方法更改其为修改状态，最后调用SaveChanges检测其已被修改，更新数据到数据库 
             */

AsNoTracking补充

在此感谢园友流年莫逝，经其提示，上述当关闭追踪时，进行修改后，无需利用Attach附加到上下文中，因为通过Entry方法就已经添加到上下文中（因为调用DetectChanges方法），所以上述 ctx.Set<Student>().Attach(entity); 是多此一举，此句略去。后经过尝试，总结如下：

当【修改】时无论是否关闭局部追踪，都无需利用Attach来进行附加到上下文中只需通过Entry方法修改其为修改状态（Modified）即可，但是当【删除】时如果关闭局部追踪，此时必须通过Attach来附加到上下文中并通过Entry方法标记为删除状态（Deleted），当然你也可以先将其标记为删除状态然后进行删除即可，也无需附加，而如果未关闭局部追踪则无需通过Attach附加到上下文只需通过Entry方法标记为删除状态（Deleted）。

若关闭局部追踪并进行删除未利用Attach附加到上下文中，此时报错如下,，在ObjectStateManager里对象未被找到，所以无法检测到对象。

（2）AsNonUnicode

我们执行如下语句，并用SqlProfiler监控其SQL：

var query = ctx.Set<Student>().Where(d => d.Name == “Recluse_Xpy”).ToList();

生成的SQL语句如下:

接下来我们这样操作，再看看生成的SQL语句:

 var query = ctx.Set<Student>().Where(d => d.Name == EntityFunctions.AsNonUnicode("Recluse_Xpy")).ToList();

其生成的SQL语句如下：

相信你也看出其中生成的SQL语句区别了，一个加了N，一个未加N，都知道N是将字符串作为Unicode格式进行存储。因为.Net字符串是Unicode格式，在上述SQL的Where子句中当一侧有N型而另一侧没有N型时，此时会进行数据转换，也就是说如果你在表中建立了索引此时会失效代替的是造成全表扫描。用 EntityFunctions.AsNonUnicode 方法来告诉.Net 将其作为一个非Unicode来对待，此时生成的SQL语句两侧都没有N型，就不会进行更多的数据转换，也就是说不会造成更多的全表扫描。所以当有大量数据时如果不进行转换会造成意想不到的结果，因此在进行字符串查找或者比较时建议用AsNonUnicode()方法来提高查询性能。

AsNonUnicode补充及注意

在此感谢园友筱伯，经其提示我也才发现 AsNonUnicode 已经被弃用，当敲代码时查看其方法便有提示此方法已经被否决，现已用 DbFunctions 方法来代替，请注意！

*小心EF 6.1字符串尾随空格问题

当比较字符串时SQL Server会自动忽略空格，但是在.NET中尤其是在EF中却不会忽略空格，例如“1234 ”和“1234”在SQL Server中会被认为是相等的，但是在EF中因为关系修正却不会忽略空格。

对于上述问题我们最好是通过一个示例来进行演示以此来加深理解并去解决它。

假设场景：一朵小红花（Flower）对应多个学生(Student)，但是这个小红花肯定只会被一个学生拿走也就只对应一个学生（两个类都用字符串作为主键）。鉴于此，我们给出如下类，并给出相应的映射。

小红花类：

    public class Flower
    {
        public string Id { get; set; }
        public string Remark { get; set; }

        public virtual ICollection<Student> Students { get; set; }
    }

学生类：

    public class Student
    {
        public string Id { get; set; }
        public string Name { get; set; }

        public string FlowerId { get; set; }
        public virtual Flower Flower { get; set; }
     
    }

映射类：

    public class StudentMap : EntityTypeConfiguration<Student>
    {
        public StudentMap()
        {
            ToTable("Student");
            HasKey(key => key.Id);
            HasRequired(p => p.Flower).WithMany(p => p.Students).HasForeignKey(p => p.FlowerId);

        }

    }

接下来我们插入数据进行测试：（在Flower类上的主键值Id有尾随空格但是在Student类的外键值FlowerId没有尾随空格）

               ctx.Set<Flower>().Add(new Flower()
                {
                    Id = "flowerId    ",
                    Remark = "so bad"

                });


                ctx.Set<Student>().Add(new Student() { Id = "xpy0928", FlowerId = "flowerId", Name = "xpy0928 study ef" });
                ctx.Set<Student>().Add(new Student() { Id = "xpy0929", FlowerId = "flowerId", Name = "xpy0929 study ef" });

接着我们进行打印插入的数据：

                var flower = ctx.Set<Flower>().Include(p => p.Students).ToList();

                Console.WriteLine("小花在内存中的数量" + ctx.Set<Flower>().Local.Count);
                Console.WriteLine("学生在内存中的数量" + ctx.Set<Student>().Local.Count);
                Console.WriteLine("学生在小花的外键导航属性的数量" + flower[0].Students.Count);

什么情况，结果告诉我们出错了，如下：

此时我们将上述红色标记尾随空格去掉，再进行测试，结果如下，如我们预期一样：

出现有错误的结果就是我们要说的问题。当我们从数据库中查询插入的所有Student和Flower时，此时如我们预期的一样，成功的返回了数据，因为数据库此时忽略上述红色标记的空格。但是在Flower上的导航属性Student却没有成功的被填充进来，因为EF不会忽略空格所以值也就无法进行匹配。我们简单的将此问题进行描述如下

EF实体框架在内存中的语义为【关系修正(Relationship FixUp)】，当进行匹配时，在关系修正的过程中EF主要着眼于主键和外键的值以及填充导航属性，但是其就在处理字符串尾随空格的执行方式上与SQL Server不同。

既然问题已经很明显了，我们接下来的工作就是去解决。之前系列文章中讲过监听者，我们可以在查询之前利用监听者（或者说叫拦截者）来进行解决。

无需对数据库或者对现有的代码进行改造，在EF 6.1中利用监听者（拦截器）和公开构造的查询树来进行解决。

下面是EF在查询之前进行操作来忽略尾随空格的代码

    public class EFConfiguration : DbConfiguration
    {
        public EFConfiguration()
        {
            AddInterceptor(new StringTrimmerInterceptor());
        }
    }
    public class StringTrimmerInterceptor : IDbCommandTreeInterceptor
    {
        public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
        {
            if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace)
            {
                var queryCommand = interceptionContext.Result as DbQueryCommandTree;
                if (queryCommand != null)
                {
                    var newQuery = queryCommand.Query.Accept(new StringTrimmerQueryVisitor());
                    interceptionContext.Result = new DbQueryCommandTree(
                        queryCommand.MetadataWorkspace,
                        queryCommand.DataSpace,
                        newQuery);
                }
            }
        }

        private class StringTrimmerQueryVisitor : DefaultExpressionVisitor
        {
            private static readonly string[] _typesToTrim = { "nvarchar", "varchar", "char", "nchar" };

            public override DbExpression Visit(DbNewInstanceExpression expression)
            {
                var arguments = expression.Arguments.Select(a =>
                {
                    var propertyArg = a as DbPropertyExpression;
                    if (propertyArg != null&& _typesToTrim.Contains(propertyArg.Property.TypeUsage.EdmType.Name))
                    {
                        return EdmFunctions.Trim(a);
                    }
 
                    return a;
                });
 
                return DbExpressionBuilder.New(expression.ResultType, arguments);
            }
        }
    }

上述就是对根表达树进行遍历来获得其属性，再将其含有字符串的除去尾随空格，然后让其监听者去执行我们修改过的命令，最后只需将监听者（或者说是拦截器）进行注册即可。

结果运行正常：

补充：实体各个状态（EntityState）以及使用

EntityState

Added:实体被上下文追踪，但是在数据库中不存在
Unchanged:实体被上下文追踪，在数据库中存在，并且从数据库中获取的属性值未发生改变
Modified:实体被上下文追踪，在数据库中存在，并且其部分或者全部属性值已经被修改
Deleted:实体被上下文追踪，在数据库中存在，但是当下一次SaveChanges被调用时，已经被标记为删除
Detached:实体不会被上下文追踪

添加（Add）一个实体到上下文

方法一

using (var context = new BloggingContext()) 
{ 
    var blog = new Blog { Name = "ADO.NET Blog" }; 
    context.Blogs.Add(blog); 
    context.SaveChanges(); 
}

此实体通过DbSet中的Add方法被添加到上下文中，此时实体状态将为Added State，也就意味着当SaveChanges被调用的时候，该实体会插入到数据库中。

方法二

using (var context = new BloggingContext()) 
{ 
    var blog = new Blog { Name = "ADO.NET Blog" }; 
    context.Entry(blog).State = EntityState.Added; 
    context.SaveChanges(); 
}

直接通过Entry方法来设置其状态为Added状态。

附加（Attach ）已存在实体到上下文

如果一个实体总是存在数据库中，但是没有被上下文所追踪，所以此时需要通过DbSet上的Attach方法来跟踪该实体，然后该实体的状态为UnChanged。如下：

方法一

var existingBlog = new Blog { BlogId = 1, Name = "ADO.NET Blog" }; 
 
using (var context = new BloggingContext()) 
{ 
    context.Blogs.Attach(existingBlog); 
 
    context.SaveChanges(); 
}

【注意】当调用SaveChanges时如果没有对实体做任何操作，此时数据库数据不会有任何改变，因为此时实体状态为UnChanged。

方法二

直接通过Entry方法来更改其状态为UnChanged

var existingBlog = new Blog { BlogId = 1, Name = "ADO.NET Blog" }; 
 
using (var context = new BloggingContext()) 
{ 
    context.Entry(existingBlog).State = EntityState.Unchanged; 
 
    context.SaveChanges(); 
}

【注意】如果附加到上文容器中的实体的引用到了其他实体没有被追踪，那么此时这些新的实体将也会被附加到上下文中，并且其状态为UnChanged

附加（Attach）一个已存在但是修改的实体到上下文

如果一个实体总是存在数据库中并且此时对该实体作了相应的修改，那么此时应该修改它的状态为Modified

var existingBlog = new Blog { BlogId = 1, Name = "ADO.NET Blog" }; 
 
using (var context = new BloggingContext()) 
{ 
    context.Entry(existingBlog).State = EntityState.Modified; 
 
    context.SaveChanges(); 
}

更改被追踪实体的状态

如果一个实体一直被上下文所追踪，可以改变其状态通过Entry来设置状态属性。

var existingBlog = new Blog { BlogId = 1, Name = "ADO.NET Blog" }; 
 
using (var context = new BloggingContext()) 
{ 
    context.Blogs.Attach(existingBlog);

    context.Entry(existingBlog).State = EntityState.Unchanged; 
 
    context.SaveChanges(); 
}

【注意】虽然Add和Attach方法是用来追踪一个实体，但是也可以被用来改变实体的状态。例如，上述通过调用Attach方法将当前处于Added状态的实体更改为UnChanged状态

码农公寓

前言