第二篇 Entity Framework Plus 之 Query Future
从性能的角度出发,能够减少 增,删,改,查,跟数据库打交道次数,肯定是对性能会有所提升的(这里单纯是数据库部分)。
今天主要怎样减少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
衷心感谢打赏者的厚爱与支持!也感谢点赞和评论的园友的支持!谢谢!打赏时您可以备注或联系告知我您希望下方出现的打赏者格式! | |||
---|---|---|---|
打赏者 | 打赏金额 | 打赏次数 | 打赏日期 |
dotnet的诱惑 | 2.00 | 1 | 2016-04-11 |
鬼脚君 | 5.00 | 1 | 2016-04-24 |