实体拆分用于一对多表关系 [英] Entity Splitting For One-To-Many table relationships

问题描述

遵循这篇文章（多种多样的最佳做法，语言数据库设计），我将所有的数据库表分成两部分：第一个表仅包含语言中立数据（主键等），第二个表包含每种语言的一个记录，其中包含本地化数据加上语言的ISO代码。两张桌子之间的关系是一对多的。
这是数据模型的屏幕截图： https://dl.dropboxusercontent.com /u/17099565/datamodel.jpg

Following this article (What are best practices for multi-language database design?), I have all my database tables splitted in two: the first table contains only language-neutral data (primary key, etc.) and the second table contains one record per language, containing the localized data plus the ISO code of the language. The relationship between the two tables is one to many. Here a screenshot of the datamodel: https://dl.dropboxusercontent.com/u/17099565/datamodel.jpg

由于网站有8种语言，对于CourseCategory表中的每个记录，我在表CourseCategoryContents中有8条记录。 课程和课程内容也是一样。

Because the website has 8 languages, for each record in table "CourseCategory" I have 8 record in table "CourseCategoryContents". The same happens with "Course" and "CourseContent"

然后我使用实体拆分为了课程类别只有一个实体和课程的一个实体： / p>

Then I use Entity Splitting in order to have only one entity for the Course Category and one entity for the Course:

public class CourseCategoryConfiguration : EntityTypeConfiguration<WebCourseCategory>
      {
          public CourseCategoryConfiguration()
          {
              Map(m =>
              {
                  m.Properties(i => new { i.Id, i.Order, i.Online });
                  m.ToTable("CourseCategories");
              });

              Map(m =>
              {
                  m.Properties(i => new { i.LanguageCode, i.Name, i.Permalink, i.Text, i.MetaTitle, i.MetaDescription, i.MetaKeywords });
                  m.ToTable("CourseCategoryContents");
              });
          }
      }

      public class CourseConfiguration : EntityTypeConfiguration<WebCourse>
      {
          public CourseConfiguration()
          {
              Map(m =>
              {
                  m.Properties(i => new { i.Id, i.CategoryId, i.Order, i.Label, i.ThumbnailUrl, i.HeaderImageUrl });
                  m.ToTable("Courses");
              });

              Map(m =>
              {
                  m.Properties(i => new { i.LanguageCode, i.Name, i.Permalink, i.Text, i.MetaTitle, i.MetaDescription, i.MetaKeywords, i.Online });
                  m.ToTable("CourseContents");
              });
          }
      }

然后以所需语言（包括他们的类别我这样做：

Then to retrive the courses in a desired language including their category I do this:

using (WebContext dbContext = new WebContext())
{
  // all courses of all categories in the desired language
          return dbContext.Courses
              .Include(course => course.Category)
              .Where(course => course.LanguageCode == lan
                     && course.Category.LanguageCode == lan)
              .ToList();
      }
}

实体拆分工作正常，一对一关系，但在这里我有一对多关系。

Entity splitting works fine with one-to-one relationships, but here I have one-to-many relationships.

该网站有3种语言（en，de，fr）的内容（CourseCategories和课程） ）。
EF以正确的语言（例如英文）正确地返回所有课程与他们的类别，但返回每个记录3次。这是因为我有3种语言的CourseCategory。

The website has contents (CourseCategories and Courses) in 3 languages ("en", "de", "fr"). EF correctly returns all the Courses with their Category in the right language (eg. in english), but returns each record 3 times. This is because I have the CourseCategory in 3 languages too.

我提出的唯一一个工作解决方案是避免使用.Include（Category），获取所有课程在所需的语言中，首先，在一个foreach循环中，每个课程以语言重新划分其类别。我不喜欢这种懒惰的加载方式，我想一次性删除所有所需的数据。

The only one working solution I came up is avoiding using ".Include(Category)", getting all the courses in the desired language in first, then, in a foreach cycle, for each Course retriving its Category in language. I don't like this lazy loading approach, I would like to retrive all the desired data in one shot.

谢谢！

推荐答案

最好的解决方案是将表格映射到模型中，然后在模型中课程类将有一个导航属性 ICollection< CourseCategoryContent> 。

The best solution is to map tables to the model as it then in your model Course class will have a navigation property ICollection<CourseCategoryContent>.

在这种情况下，您只需将此模型投影到DTO或ViewModel应用设计

In this case you just project this model to DTO or ViewModel "according to your application design"

例如
你的模型看起来像这样

e.g. Your model will look like this

public class Course
{
    public int Id {get; set;}
    public int Order {get; set;}
    public ICollection<CourseCategoryContent> CourseCategoryContents {get; set;}
}

public class CourseCategoryContent
{
    public string LanguageId {get; set;}
    public string Name {get; set;}
}

然后只需创建新的DTO或ViewModel，如下所示：

Then just create new DTO or ViewModel like :

public class CourseDTO
{
    public int Id {get; set;}
    public int Order {get; set;}
    public string Name {get; set;}
}

最后做投影

public IQueryable<CourseDTO> GetCourseDTOQuery ()
{  
    return dbContext.Courses.Select(x=>new CourseDTO{
    Id = x.Id,
    Order = x.Order,
    Name = x.CourseCategoryContents.FirstOrDefault(lang => lang.LanguageId == lang).Name,
    });      
} 

请注意，返回类型是IQueryable，因此您可以执行任何过滤器或者在击中数据库之前对其进行分组操作。

And note that the return type is IQueryable so you could do any filter, Order or grouping operation on it before hitting the database.

希望这有助于

这篇关于实体拆分用于一对多表关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！