第三章 查询之异步查询

前一章,我们展示了常见数据库场景的建模方式,本章将向你展示如何查询实体数据模型,一般来说,有三种方式:

1、LINQ to Entities;

2、Entity SQL;

3、Native SQL;

我们将在本章演示这三种方式,为了帮助你理解实体框架查询的基本知识,本章覆盖了常见和不常见的场景。同时我们也展示了实体框架6新的查询功能。

3-1.异步查询

你一个长耗时的实体框架查询,当执行查询时,你不想打断应用程序主线程运行,在数据返加之前,能让用户做一些别的操作。同时,使用LINQ to Entities来查询模型也一样重要,它是查询数据库模型的首选方案。

解决方案

假设你有如图3-1所示的模型。

图3-1 模型中,一个代表助理的Associate的实体类型和一个代表助理工资历史的AssociateSalary实体

在这个模型中,我们有两个代表助理和他们工资历史的实体。

作为开始,我们在示例中使用Code-First方法创建类,在代码清单3-1中,创建这些实体类。

代码清单3-1 Associate 和 AssociateSalary实体类型

public class Associate
{
    public Associate()
    {
        AssociateSalaries = new HashSet<AssociateSalary>();
    }
    public int AssociateId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<AssociateSalary> AssociateSalaries { get; set; }
}
public class AssociateSalary
{
    public int SalaryId { get; set; }
    public int AssociateId { get; set; }
    public decimal Salary { get; set; }
    public DateTime SalaryDate { get; set; }
    public virtual Associate Associate { get; set; }
}

接下来,代码清单3-2使用Code-First创建DbContentx上下文对象,注意在OnModelCreateing方法中,我们显示地将SalaryId属性映射为AssociateSalary表的主键。当我们使用Code Firtst时,如果一个属性的名字是Id或者<表名>Id,实体框架为假定该属性是对应表的主键。别外,像这里这样,要显示设置主键。

代码清单3-2 Dbcontext上下文对象

public class EFRecipesEntities : DbContext
{
    public EFRecipesEntities()
        : base("ConnectionString")
    {
    }

    public DbSet<Associate> Associates { get; set; }
    public DbSet<AssociateSalary> AssociateSalaries { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Associate>().ToTable("Chapter3.Associate");
        modelBuilder.Entity<AssociateSalary>().ToTable("Chapter3.AssociateSalary");

        //显示分配实体键为AssociateSalary表的主键,以免实体框架使用默认映射约定
        modelBuilder.Entity<AssociateSalary>().HasKey(x => x.SalaryId);
        base.OnModelCreating(modelBuilder);
    }
}

代码清单3-3 演示,如何借助新实体框架的异步方法实现异步查询,删除、加载、获取数据。

代码清单3-3.异步处理实体框架查询

private static void Main()
{
    var asyncTask = EF6AsyncDemo();

    foreach (var c in BusyChars())
    {
        if (asyncTask.IsCompleted)
        {
            break;
        }
        Console.Write(c);
        Console.CursorLeft = 0;
        Thread.Sleep(100);
    }
    Console.WriteLine("\nPress <enter> to continue...");
    Console.ReadLine();
}

private static IEnumerable<char> BusyChars()
{
    while (true)
    {
        yield return '\\';
        yield return '|';
        yield return '/';
        yield return '-';
    }
}

private static async Task EF6AsyncDemo()
{
    await Cleanup();
    await LoadData();
    await RunForEachAsyncExample();
    await RunToListAsyncExampe();
    await RunSingleOrDefaultAsyncExampe();
}

private static async Task Cleanup()
{
    using (var context = new EFRecipesEntities())
    {
        // 清除原始数据
        // 异步执行原始SQL语句
        Console.WriteLine("Cleaning Up Previous Test Data");
        Console.WriteLine("=========\n");

        await context.Database.ExecuteSqlCommandAsync("delete from chapter3.AssociateSalary");
        await context.Database.ExecuteSqlCommandAsync("delete from chapter3.Associate");
        await Task.Delay(5000);
    }
}

private static async Task LoadData()
{
    using (var context = new EFRecipesEntities())
    {
        // 添加测试数据
        Console.WriteLine("Adding Test Data");
        Console.WriteLine("=========\n");

        var assoc1 = new Associate { Name = "Janis Roberts" };
        var assoc2 = new Associate { Name = "Kevin Hodges" };
        var assoc3 = new Associate { Name = "Bill Jordan" };
        var salary1 = new AssociateSalary
        {
            Salary = 39500M,
            SalaryDate = DateTime.Parse("8/4/09")
        };
        var salary2 = new AssociateSalary
        {
            Salary = 41900M,
            SalaryDate = DateTime.Parse("2/5/10")
        };
        var salary3 = new AssociateSalary
        {
            Salary = 33500M,
            SalaryDate = DateTime.Parse("10/08/09")
        };
        assoc1.AssociateSalaries.Add(salary1);
        assoc2.AssociateSalaries.Add(salary2);
        assoc3.AssociateSalaries.Add(salary3);
        context.Associates.Add(assoc1);
        context.Associates.Add(assoc2);
        context.Associates.Add(assoc3);

        // 异步保存
        await context.SaveChangesAsync();
        await Task.Delay(5000);
    }
}

private static async Task RunForEachAsyncExample()
{
    using (var context = new EFRecipesEntities())
    {
        Console.WriteLine("Async ForEach Call");
        Console.WriteLine("=========");

        // 借助 ForEachAsync 方法
        await context.Associates.Include(x => x.AssociateSalaries).ForEachAsync(x =>
        {
            Console.WriteLine("Here are the salaries for Associate {0}:", x.Name);

            foreach (var salary in x.AssociateSalaries)
            {
                Console.WriteLine("\t{0}", salary.Salary);
            }
        });
        await Task.Delay(5000);
    }
}

private static async Task RunToListAsyncExampe()
{
    using (var context = new EFRecipesEntities())
    {
        Console.WriteLine("\n\nAsync ToList Call");
        Console.WriteLine("=========");

        // 借助 ToListAsync 方法
        var associates = await context.Associates.Include(x => x.AssociateSalaries).OrderBy(x => x.Name).ToListAsync();

        foreach (var associate in associates)
        {
            Console.WriteLine("Here are the salaries for Associate {0}:", associate.Name);
            foreach (var salaryInfo in associate.AssociateSalaries)
            {
                Console.WriteLine("\t{0}", salaryInfo.Salary);
            }
        }
        await Task.Delay(5000);
    }
}

private static async Task RunSingleOrDefaultAsyncExampe()
{
    using (var context = new EFRecipesEntities())
    {
        Console.WriteLine("\n\nAsync SingleOrDefault Call");
        Console.WriteLine("=========");

        var associate = await context.Associates.
            Include(x => x.AssociateSalaries).
            OrderBy(x => x.Name).
            FirstOrDefaultAsync(y => y.Name == "Kevin Hodges");

        Console.WriteLine("Here are the salaries for Associate {0}:", associate.Name);
        foreach (var salaryInfo in associate.AssociateSalaries)
        {
            Console.WriteLine("\t{0}", salaryInfo.Salary);
        }
        await Task.Delay(5000);
    }
}

代码清单3-3输出如下:

Cleaning Up Previous Test Data
=========
Adding Test Data
=========
Async ForEach Call
=========
Here are the salaries for Associate Janis Roberts:
39500.00
Here are the salaries for Associate Kevin Hodges:
41900.00
Here are the salaries for Associate Bill Jordan:
33500.00
Async ToList Call
=========
Here are the salaries for Associate Bill Jordan:
33500.00
Here are the salaries for Associate Janis Roberts:
39500.00
Here are the salaries for Associate Kevin Hodges:
41900.00
Async SingleOrDefault Call
=========
Here are the salaries for Associate Kevin Hodges:
41900.00


原理

在这个示例中,我们演示了实体框架的两个关键概念的用途:使用LINQ扩展查询模型以及实体框架6中实现的新的异步功能。

对于绝大多数的查询操作,你都需要用到LINQ。这样做会给你带来,智能提示、编译时检查,以及强类型的编程体验。如果你需要在运行时动态构建查询,你可以考虑使用Entity SQL,它能连接查询表达式各个部分的字符串。你将在本节后面看到相关的示例。

开始时,我们先清除之前数据库中的测试数据。请注意我们是如何把Cleanup()操作包装在一个异步方法中的。然后我们生成原始的SQL语句,并调用新的ExecuteSqlCommandAsync()方法。请注意我们是如何凭借.NET framework4.5中的async/await异步模式。这种模式能够不通过显示实例化一个后台线程来实现异步;此外,它释放当前等待数据库操作完成的CLR线程控制权。(译注:也就是不卡住当前线程 ,让它可以继续执行别的操作)。

接下来,我们加载测试数据Associate和AssoicateSalareies。为了执行异步调用,像前面一样,我们将LoadData()操作包装在一个异步方法中,并通过最新增加的SaveChangesAsync()方法在数据库中插入测试数据。

接下为,我们呈现了三种不同的模型查询方式,每种方式都凭借了实体框架中的LINQ扩展,每种方式都凭借await/async模式包含在一个异步方法中。在RunForEachAsyncExample()方法中,由于没有与foreach语句相匹配异步方法,我们使用了ForEachAsync()扩展方法。凭借这个异步方法以及 Inclued()方法,我们能够异步查询和枚举这些对象。

在随后的RunToListAsyncExample()和RunSingeOrDefaultAsyncExample()查询中,我们凭借ToList()和SingleOrDefault()方法的新的异步方法来实现。

实体框架现在公布了大量的异步操作方法。它们的命名约定是,在已存在的api名称中加上后缀Asyn,使其相对简单地在添加或者获取数据时实现异步。

《Entity Framework 6 Recipes》中文翻译系列