第二篇 Entity Framework Plus 之 Query Future

首页 > 代码库 > 第二篇 Entity Framework Plus 之 Query Future

第二篇 Entity Framework Plus 之 Query Future

2024-09-17 02:35:47 213人阅读

小分享：我有几张阿里云优惠券，用券购买或者升级阿里云相应产品最多可以优惠五折！领券地址：https://promotion.aliyun.com/ntms/act/ambassador/sharetouser.html?userCode=ohmepe03

从性能的角度出发，能够减少增，删，改，查，跟数据库打交道次数，肯定是对性能会有所提升的（这里单纯是数据库部分）。

今天主要怎样减少Entity Framework查询跟数据库打交道的次数，来提高查询性能。

举一个大家最常用功能 “分页” 功能。先贴一段代码。

       private static IEnumerable<OrderModel> FindPagerOrders(int pageSize, int pageIndex, out int totalCount) { using (var dbContext = new EntityFrameworkPlusDbContext()) { var orders = dbContext.Orders.OrderBy(o => o.CreateDateTime); totalCount = orders.Count(); var pagerOrders = orders.Skip((pageIndex - 1) * pageSize).Take(pageSize); return pagerOrders .ToList(); } }

这类型的代码，大家估计都看到过，也自己写过，简单分析一下。

orders.Count() 返回int 类型，肯定要查询出数据库才知道订单总笔数。

pagerOrders.ToList() 返回 IEnumerable<T> 类型，这个不用解释Entity Framework IEnumerable 和 IQueryable 区别是

IEnumerable 会执行SQL，IQueryable 而不会。所以这句也会去数据库查询一次。

那整个分页功能用Entity Framework 就是最少要两次数据库查询，刚刚上面说了，一个基本的提高性能方法就要减少与数据库打交道次数。

从“分页”功能来说，要是变成只有一次与数据库打交道，那就是对性能有大提升。Entity Framework 自身是没有提供这样的方法。

Entity Framework Plus 库 Query Future 扩展，是一个对Entity Framework 功能的延伸和扩展，能够做到减少数据库打交道次数。使查询性能更高。

一 . Entity Framework Plus 库 Query Future 安装

1. 解决方案还是我上一篇第一篇 Entity Framework Plus 之 Audit 用的解决方案“EntityFrameworkPlusSolution”，新增 “EntityFrameworkPlus.QueryFuture.Demo” 控制台项目，作为Entity Framework Plus 库 Query Future 扩展应用和展示功能项目。项目结构截图如下

技术分享

项目关系图（代码图）

技术分享

2. 为了方便Demo，新增商品业务相关的 Model，Mapping，以及改动DbContext 如下代码

GoodsModel

using System; namespace EntityFrameworkPlus.Models { public class GoodsModel { public System.Guid GoodsGuid { get; set; } public string GoodsNo { get; set; } public string GoodsName { get; set; } public string GoodsBrand { get; set; } public decimal UnitPrice { get; set; } public string Description { get; set; } public string Creator { get; set; } public System.DateTime CreateDateTime { get; set; } public string LastModifier { get; set; } public DateTime? LastModifiedDateTime { get; set; } } }

GoodsMap

using System.Data.Entity.ModelConfiguration; using EntityFrameworkPlus.Models; namespace EntityFrameworkPlus.Mappings { public class GoodsMap: EntityTypeConfiguration<GoodsModel> { public GoodsMap() { // Primary Key
            this.HasKey(t => t.GoodsGuid); // Properties
            this.Property(t => t.GoodsNo) .IsRequired() .HasMaxLength(50); this.Property(t => t.GoodsName) .IsRequired() .HasMaxLength(50); this.Property(t => t.GoodsBrand) .IsRequired() .HasMaxLength(50); this.Property(t => t.Creator) .IsRequired() .HasMaxLength(20); this.Property(t => t.LastModifier) .HasMaxLength(20); // Table & Column Mappings
            this.ToTable("Sample_Goods"); this.Property(t => t.GoodsGuid).HasColumnName("GoodsGuid"); this.Property(t => t.GoodsNo).HasColumnName("GoodsNo"); this.Property(t => t.GoodsName).HasColumnName("GoodsName"); this.Property(t => t.GoodsBrand).HasColumnName("GoodsBrand"); this.Property(t => t.UnitPrice).HasColumnName("UnitPrice"); this.Property(t => t.Description).HasColumnName("Description"); this.Property(t => t.Creator).HasColumnName("Creator"); this.Property(t => t.CreateDateTime).HasColumnName("CreateDateTime"); this.Property(t => t.LastModifier).HasColumnName("LastModifier"); this.Property(t => t.LastModifiedDateTime).HasColumnName("LastModifiedDateTime"); } } }

EntityFrameworkPlusDbContext

using System.Data.Entity; using EntityFrameworkPlus.Mappings; using EntityFrameworkPlus.Models; using Z.EntityFramework.Plus; namespace EntityFrameworkPlus.DbContext { public class EntityFrameworkPlusDbContext : System.Data.Entity.DbContext { public EntityFrameworkPlusDbContext() : base("EntityFrameworkPlusConnection") { } public DbSet<AuditEntry> AuditEntries { get; set; } public DbSet<AuditEntryProperty> AuditEntryProperties { get; set; } public DbSet<OrderModel> Orders { get; set; } public DbSet<GoodsModel> Goodses { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Configurations.Add(new OrderMap()); modelBuilder.Configurations.Add(new GoodsMap()); base.OnModelCreating(modelBuilder); } } }

3. 右键 “EntityFrameworkPlus.QueryFuture.Demo” 项目，选择“管理NuGet程序包”，关联部分右上角搜索“Z.EntityFramework.Plus” ，然后选择 “EntityFramework Plus (EF6) | Query Deferred”&“EntityFramework Plus (EF6) | Query Futurn” 两项安装

技术分享

二. Entity Framework Plus 库 Query Future 扩展功能实作

1. 在 “EntityFrameworkPlus.QueryFuture.Demo” 项目 Program 新增3个静态方法，分别是

FindOrdersWithGoodsies() 查询订单信息和商品信息

FindPagerOrders(int pageSize, int pageIndex, out int totalCount) 订单分页查询

FindGoodsMaxWithMinUnitPrice() 查询单价最大和最小的商品

详细代码如下

using System.Collections.Generic; using System.Linq; using EntityFrameworkPlus.DbContext; using EntityFrameworkPlus.Models; using Z.EntityFramework.Plus; namespace EntityFrameworkPlus.QueryFuture.Demo { class Program { static void Main(string[] args) { //1.查询订单信息和商品信息
 FindOrdersWithGoodsies(); //2. 订单分页查询 //var totalCount = 0; //FindPagerOrders(10, 1, out totalCount); //3.查询单价最大和最小的商品 //FindGoodsMaxWithMinUnitPrice();
 } private static void FindOrdersWithGoodsies() { using (var dbContext = new EntityFrameworkPlusDbContext()) { var futureOrders = dbContext.Orders.Future(); var futureGoodsies = dbContext.Goodses.Future(); var orders = futureOrders.ToList(); var goodsies = futureGoodsies.ToList(); } } private static IEnumerable<OrderModel> FindPagerOrders(int pageSize, int pageIndex, out int totalCount) { using (var dbContext = new EntityFrameworkPlusDbContext()) { var orders = dbContext.Orders.OrderBy(o => o.CreateDateTime); var futureCount = orders.DeferredCount().FutureValue(); var futurePagerOrders = orders.Skip((pageIndex - 1) * pageSize).Take(pageSize).Future(); totalCount = futureCount.Value; return futurePagerOrders.ToList(); } } private static void FindGoodsMaxWithMinUnitPrice() { using (var dbContext = new EntityFrameworkPlusDbContext()) { var futureMaxGoodsUnitPrice = dbContext.Goodses.DeferredMax(g => g.UnitPrice).FutureValue<decimal>(); var futureMinGoodsUnitPrice = dbContext.Goodses.DeferredMin(g => g.UnitPrice).FutureValue<decimal>(); var maxGoodsUnitPrice = futureMaxGoodsUnitPrice.Value; var minGoodsUnitPrice = futureMaxGoodsUnitPrice.Value; } } } }

2. 3个方法的SQL追踪和截图

FindOrdersWithGoodsies

-- EF+ Query Future: 1 of 2
SELECT 
    [Extent1].[OrderGuid] AS [OrderGuid], [Extent1].[OrderNo] AS [OrderNo], [Extent1].[OrderCreator] AS [OrderCreator], [Extent1].[OrderDateTime] AS [OrderDateTime], [Extent1].[OrderStatus] AS [OrderStatus], [Extent1].[Description] AS [Description], [Extent1].[Creator] AS [Creator], [Extent1].[CreateDateTime] AS [CreateDateTime], [Extent1].[LastModifier] AS [LastModifier], [Extent1].[LastModifiedDateTime] AS [LastModifiedDateTime]
    FROM [dbo].[Sample_Order] AS [Extent1]


-- EF+ Query Future: 2 of 2
SELECT 
    [Extent1].[GoodsGuid] AS [GoodsGuid], [Extent1].[GoodsNo] AS [GoodsNo], [Extent1].[GoodsName] AS [GoodsName], [Extent1].[GoodsBrand] AS [GoodsBrand], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[Description] AS [Description], [Extent1].[Creator] AS [Creator], [Extent1].[CreateDateTime] AS [CreateDateTime], [Extent1].[LastModifier] AS [LastModifier], [Extent1].[LastModifiedDateTime] AS [LastModifiedDateTime]
    FROM [dbo].[Sample_Goods] AS [Extent1]

FindPagerOrders(int pageSize, int pageIndex, out int totalCount)

技术分享

-- EF+ Query Future: 1 of 2
SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[Sample_Order] AS [Extent1] ) AS [GroupBy1]


-- EF+ Query Future: 2 of 2
SELECT 
    [Extent1].[OrderGuid] AS [OrderGuid], [Extent1].[OrderNo] AS [OrderNo], [Extent1].[OrderCreator] AS [OrderCreator], [Extent1].[OrderDateTime] AS [OrderDateTime], [Extent1].[OrderStatus] AS [OrderStatus], [Extent1].[Description] AS [Description], [Extent1].[Creator] AS [Creator], [Extent1].[CreateDateTime] AS [CreateDateTime], [Extent1].[LastModifier] AS [LastModifier], [Extent1].[LastModifiedDateTime] AS [LastModifiedDateTime]
    FROM [dbo].[Sample_Order] AS [Extent1]
    ORDER BY [Extent1].[CreateDateTime] ASC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

FindGoodsMaxWithMinUnitPrice()

技术分享

-- EF+ Query Future: 1 of 2
SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        MAX([Extent1].[UnitPrice]) AS [A1]
        FROM [dbo].[Sample_Goods] AS [Extent1] ) AS [GroupBy1]


-- EF+ Query Future: 2 of 2
SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        MIN([Extent1].[UnitPrice]) AS [A1]
        FROM [dbo].[Sample_Goods] AS [Extent1] ) AS [GroupBy1]

至此比较常用到场景，就已经实作完成，大家看到截图和SQL说明都是一次执行，其他大家可以根据 EntityFramework Plus 源代码和文档（不过是英文，但是基本能够看懂），进行更加深入的了解，了解实现原理，我这里还是抛砖引玉一下。

这篇博文的源代码：https://github.com/haibozhou1011/EntityFramework-PlusSample

参考页面：http://qingqingquege.cnblogs.com/p/5933752.html

第二篇 Entity Framework Plus 之 Query Future

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > 第二篇 Entity Framework Plus 之 Query Future

第二篇 Entity Framework Plus 之 Query Future

看完仍有疑问？有类似问题直接问程序猿