第三章 查询之左连接和在TPH中通过派生类排序

3-10应用左连接

问题

你想使用左外连接来合并两个实体的属性。

解决方案

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

图3-11 一个包含Product和TopSelling的模型

畅销产品有一个与之关联的TopSelling实体。当然,不是所有的产品都是畅销产品。这就是为什么关系为零或者1。当一个产品为畅销产品时,与之关联的topSelling实体包含一个用户评级。你想查找和呈现所有的产品,和与之关联的 热销 榜单实体,即使产品不是一个畅销产品。如果产品没有相关联的 热销 榜单,我们就简单地用0来设置用户评级。 在数据库中,这叫做左外连接。

代码清单3-22演示了三种略有不同的方法来解决这个问题。

代码清单3-22. 两个实体间的左外连接

using (var context = new EFRecipesEntities())
{
    // 删除之前的测试数据
    context.Database.ExecuteSqlCommand("delete from chapter3.topselling");
    context.Database.ExecuteSqlCommand("delete from chapter3.product");
    // 添加新的测试数据
    // 注意p1没有与之关联的TopSelling实体
    var p1 = new Product { Name = "Trailrunner Backpack" };
    var p2 = new Product
    {
        Name = "Green River Tent",
        TopSelling = new TopSelling { Rating = 3 }
    };
    var p3 = new Product
    {
        Name = "Prairie Home Dutch Oven",
        TopSelling = new TopSelling { Rating = 4 }
    };
    var p4 = new Product
    {
        Name = "QuickFire Fire Starter",
        TopSelling = new TopSelling { Rating = 2 }
    };
    context.Products.Add(p1);
    context.Products.Add(p2);
    context.Products.Add(p3);
    context.Products.Add(p4);
    context.SaveChanges();
}

using (var context = new EFRecipesEntities())
{
    var products = from p in context.Products
                   orderby p.TopSelling.Rating descending
                   select p;
    Console.WriteLine("All products, including those without ratings");

    foreach (var product in products)
    {
        Console.WriteLine("\t{0} [rating: {1}]", product.Name,
            product.TopSelling == null ? "0"
                : product.TopSelling.Rating.ToString());
    }
}

using (var context = new EFRecipesEntities())
{
    var products = from p in context.Products
                   join t in context.TopSellings on
                       //注意,我们如何将结果集投影到另一个名为'g'的序列中,以及应用DefaultIfEmpty方法
                      p.ProductID equals t.ProductID into g
                   from tps in g.DefaultIfEmpty()
                   orderby tps.Rating descending
                   select new
                   {
                       Name = p.Name,
                       Rating = tps.Rating == null ? 0 : tps.Rating
                   };

    Console.WriteLine("\nAll products, including those without ratings");
    foreach (var product in products)
    {
        Console.WriteLine("\t{0} [rating: {1}]", product.Name,
            product.Rating.ToString());
    }
}

using (var context = new EFRecipesEntities())
{
    var esql = @"select value p from products as p
     order by case when p.TopSelling is null then 0
                        else p.TopSelling.Rating end desc";
    var products = ((IObjectContextAdapter)context).ObjectContext.CreateQuery<Product>(esql);
    Console.WriteLine("\nAll products, including those without ratings");
    foreach (var product in products)
    {
        Console.WriteLine("\t{0} [rating: {1}]", product.Name,
            product.TopSelling == null ? "0"
                : product.TopSelling.Rating.ToString());
    }
}

Console.WriteLine("\nPress <enter> to continue...");
Console.ReadLine();
}

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

Top selling products sorted by rating
Prairie Home Dutch Oven [rating: 4]
Green River Tent [rating: 3]
QuickFire Fire Starter [rating: 2]
Trailrunner Backpack [rating: 0]Top selling products sorted by rating
Prairie Home Dutch Oven [rating: 4]
Green River Tent [rating: 3]
QuickFire Fire Starter [rating: 2]
Trailrunner Backpack [rating: 0]Top selling products sorted by rating
Prairie Home Dutch Oven [rating: 4]
Green River Tent [rating: 3]
QuickFire Fire Starter [rating: 2]
Trailrunner Backpack [rating: 0]


原理

在代码清单3-22中,我们展示了三种不的方法来处理这个问题。第一种方法最简单。因为实体框架会为关联的实体自动创建join连接,它是基于模型创建时被创建的导航属性。这两个实体的关联为1-0...1,这意味着,实体框架在自动生成SQL查询时,会在这两个实体间包含一个左外连接。当product实体被实例化时,任何与之关联的topsellers也会被实例化。导航属性TopSeller也会被设置为相关联的实体,如是不存在TopSellers就为null。如果给定的产品不存在与之关联的TopSeller实体(也就是说,它没有被评为畅销产品)。我们会简单的分配一个0值给产品的评级。

在某些情况下,你想连接的两个实体间可能没有一个关系(对实例而言,是一个导航属性)。这样的话,你可以显式连接实体。把结果集投影到一个匿名类型。我们之所以需要将结果集投影到匿名类型,是因为没有关联的两个实体间没有导航属性。否则我们不可以引用相关联的实体。

在第二段查询的代码中演示了这种方法。通过键ProductId连接两个实体,然后把结果集投影到一个新的名为'g'的序列中。随后,我们在g上应用DefaultIfEmpty()方法,当g为空时,它会用null值填充。毫无疑问,当SQL语句被产生时,两个实体间将会包含一个左外连接。我们通过一个orderby从句,让结果集根据rating排序。最后,我们将结果投影到一个匿名类型。

在第三中方法中,我产展示了在Entity SQL中,如何更明确地使用左外连接。在查询中嵌入一个Entity SQL语句。

3-11通过派生类排序

问题

当你使用TPH继承映射时,你想通过派生类来给结果集排序。

解决方案

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

图3-12 使用TPH继承映射的模型,包含三个派生类弄型

这个模型使用TPH继承映射,它是实体框架一个特性,TPH创建这样的一个继承结构,父类和子类都源至数据库中的同一张表。

在这个示例中,Media实体有一个属性名为Mediatype,它被用作TPH结构中的鉴别属性。MediaType的值决定着数据库表中的行被映射到哪个派生类型(Article,Picture,或者Video)。鉴别列的值为1时代表Article类型,为2时代表Video类型,为3时代表Picture类型。因为这个属性被用来决定派生类型,它将不在Media实体中显示。

我们在代码清单3-23中使用Code-First,创建了实体类,为了保持示例的简单性,我们只创建了空的派生子对象。因为我只是为了演示在查询中通过派生类型来排序。

代码清单3-23. 父类和子类实体类型

public class Media
{
    public int MediaId { get; set; }
    public string Title { get; set; }
}
public class Article : Media
{
}
public class Picture : Media
{
}
public class Video : Media
{
}

接下来,在代码清单3-24中,我们创建了数据库上下文对象,它是我们凭借实体框架中Code-First方法的入口。注意,如何在OnModelCreating方法中,使用FluentAPI(换句话说就是将扩展方法链接起来创建一个操作)。显式映射鉴别列,MediaType,到子类型。

代码清单3-24 DbContext对象

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

    public DbSet<Media> Media { get; set; }

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


        modelBuilder.Entity<Media>().Map<Article>(x => x.Requires("MediaType").HasValue(1));
        modelBuilder.Entity<Media>().Map<Picture>(x => x.Requires("MediaType").HasValue(2));
        modelBuilder.Entity<Media>().Map<Video>(x => x.Requires("MediaType").HasValue(3));

        base.OnModelCreating(modelBuilder);
    }
}

随着Code-First的构件被创建,我们将查询模型中所有的media,并通过派生类型:Article,Video,和Picture进行排序。如代码清单3-25所示。

代码清单3-25.在TPH继承映射中通过类型排序

using (var context = new EFRecipesEntities())
{
    //删除之前的测试数据
    context.Database.ExecuteSqlCommand("delete from chapter3.Media");
    // 添加新的测试数据
    context.Media.Add(new Article
    {
        Title = "Woodworkers' Favorite Tools"
    });
    context.Media.Add(new Article
    {
        Title = "Building a Cigar Chair"
    });
    context.Media.Add(new Video
    {
        Title = "Upholstering the Cigar Chair"
    });
    context.Media.Add(new Video
    {
        Title = "Applying Finish to the Cigar Chair"
    });
    context.Media.Add(new Picture
    {
        Title = "Photos of My Cigar Chair"
    });
    context.Media.Add(new Video
    {
        Title = "Tour of My Woodworking Shop"
    });
    context.SaveChanges();
}

using (var context = new EFRecipesEntities())
{
    var allMedium = from m in context.Media
        let mediumtype = m is Article
            ? 1
            : m is Video ? 2 : 3
        orderby mediumtype
        select m;
    Console.WriteLine("All Medium sorted by type...\n");
    foreach (var medium in allMedium)
    {
        Console.WriteLine("Title: {0} [{1}]", medium.Title, medium.GetType().Name);
    }
}

Console.WriteLine("\nPress <enter> to continue...");
Console.ReadLine();
}

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

All Media sorted by type...
Title: Woodworkers' Favorite Tools [Article]
Title: Building a Cigar Chair [Article]
Title: Upholstering the Cigar Chair [Video]
Title: Applying Finish to the Cigar Chair [Video]
Title: Tour of My Woodworking Shop [Video]
Title: Photos of My Cigar Chair [Picture]

原理

当我们使用TPH继承映射时,我们凭借表中一列来区别给定的行代表的派生类型。这一列,通常被称作鉴别列。它不能被映射到实体中的属性。因为我们没有属性包含鉴别值 ,所以,需要创建一个变量来保存鉴别值以便我们进行排序。为了实现这个目的,我们使用了LINQ中的let从句。它创建了一个mediatype变量。我们通过条件语句来将整型的鉴别值分配给这个变量。Article类型的分配1,Video类型的分配2,其它类型的分配3。这里的其它类型其实就是Picture类型,因为没有别的派生类型了。

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