EntityFramework进阶——Entity Splitting和Table Splitting

Entity Splitting——把单个实体拆分成多个表。Table Splitting——把单个表拆分成多个实体

Entity Splitting 

下面通过例子来说明：

假设存在如下实体类：

    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string PhoneNumber { get; set; }
        public string DetailAddress { get; set; }
        public DateTime CreatedTime { get; set; }
        public DateTime ModifiedTime { get; set; }
    }

在同一个Employee实体类的前提下，我们需要将DetailAddress，PhoneNumber字段放在一张表， 其余字段放在另一张表，就可以使用如下Fluent API代码进行配置：

    public class EmployeeMap:EntityTypeConfiguration<Employee>
    {
        public EmployeeMap()
        {
            Map(map =>
            {
                map.Properties(p => new
                {
                    p.EmployeeId,
                    p.Name,
                    p.CreatedTime,
                    p.ModifiedTime
                });
                map.ToTable("Employees");
            }).Map(map => {
                map.Properties(p => new {
                    p.PhoneNumber,
                    p.DetailAddress
                });
                map.ToTable("EmployeeDetails");               
            });
        }
    }

上下文类的代码如下图所示：

    public class SplittingModel : DbContext
    {
        public SplittingModel()
            : base("name=SplittingModel")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new EmployeeMap());
        }

        public virtual DbSet<Employee> Employee { get; set; }
    }

运行程序后，数据库建表结构如下图所示：

                                             

 

当对实体类Employee进行插入数据操作时，EF会生成Employee 和 EmployeeDetail两个表的插入语句，代码 和 截图如下所示：

static void Main(string[] args)
{
    using (SplittingModel db = new SplittingModel())
    {
         db.Database.Log = Console.WriteLine;
         Employee employee = new Employee
         {
              CreatedTime = DateTime.Now,
              ModifiedTime = DateTime.Now,
              Name = "Jack",
              PhoneNumber = "1129373834",
              DetailAddress = "USA"
         };
         db.Employee.Add(employee);
         db.SaveChanges();
         Console.ReadKey();
    }
}

        

 

对实体Employee进行查询操作时，也会同时关联两个表，代码 和 截图如下图所示：

using (SplittingModel db = new SplittingModel())
{
     db.Database.Log = Console.WriteLine;
     var e = db.Employee.First();
     Console.ReadKey();
}

 

 

 

---

 Table Splitting

表拆分使我们能将多个实体映射到一个数据库表单，与实体拆分正好相反。我们可以将模型中的两个或者多个实体映射到同一个物理数据库表中。

例子如下，比如将两个实体类Employee和EmployeePhoto合并成一个数据表，实体类如下图所示：

    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string PhoneNumber { get; set; }
        public string DetailAddress { get; set; }
        public DateTime CreatedTime { get; set; }
        public DateTime ModifiedTime { get; set; }

        //导航属性
        public virtual EmployeePhoto Photo { get; set; }
    }

    public class EmployeePhoto
    {
        public int Id { get; set; }
        public byte[] Photo { get; set; }
        public Employee Employee { get; set; }
    }

Fluent API代码如下图所示：

    public class EmployeeMap:EntityTypeConfiguration<Employee>
    {
        public EmployeeMap()
        {
            ToTable("Employees");

            HasKey(p => p.EmployeeId);

            HasRequired(e => e.Photo)
                .WithRequiredPrincipal(p => p.Employee);
        }
    }

    public class EmployeePhotoMap:EntityTypeConfiguration<EmployeePhoto>
    {
        public EmployeePhotoMap()
        {
            ToTable("Employees");
            HasKey(p => p.Id);
        }
    }

上下文类代码如下图所示：

    public class TableSplittingModel : DbContext
    {
        public TableSplittingModel()
            : base("name=TableSplittingModel")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new EmployeeMap());
            modelBuilder.Configurations.Add(new EmployeePhotoMap());
        }

        public virtual DbSet<Employee> Employee { get; set; }
        public virtual DbSet<EmployeePhoto> EmployeePhoto { get; set; }
    }

生成的数据库表结果如下图所示：

                                                

 

向数据库中插入数据，代码如下图所示：

        static void Main(string[] args)
        {
            using (TableSplittingModel db = new TableSplittingModel())
            {
                Employee employee = new Employee
                {
                    Name = "admin",
                    PhoneNumber = "1000",
                    DetailAddress = "USA",
                    CreatedTime = DateTime.Now,
                    ModifiedTime = DateTime.Now,
                    Photo = new EmployeePhoto {

                    }                   
                };
                db.Employee.Add(employee);
                db.SaveChanges();
                Console.WriteLine("OK");
            }
        }

当查询实体Employee实体时，将只返回Employee实体列而不会返回EmployeePhoto列作为Employee实体一部分：

            using (TableSplittingModel db = new TableSplittingModel())
            {
                var employee = db.Employee.ToList();
            }

当查询Employee实体且延迟加载EmployeePhoto实体时，才会返回EmployeePhoto列：

            using (TableSplittingModel db = new TableSplittingModel())
            {
                var employee = db.Employee.Include("Photo").toList();
            }