关联(Fluent API)
1-1-or-0关联(WithOptional)
1-1关联(Required,Principal,Dependent)
*-*关联(HasMany,WithMany,.Map)
单向关联()
关联删除
设置混合外键
重命名一个外键名称(.Map)
定义一个非惯例的外键名称
创建索引
实体类
有映射关系的实体类
TPH、TPC、TPT
存储过程
变更跟踪
更新数据库
Enable-Migrations
update-database
Add-Migration [name]
关联(Fluent API)
1-1-or-0关联(WithOptional)
下面是1和1或者0关联的示例,OfficeAssignment拥有InstructorId
// Configure the primary key for the OfficeAssignment
modelBuilder.Entity()
.HasKey(t => t.InstructorID);
// Map one-to-zero or one relationship
modelBuilder.Entity()
.HasRequired(t => t.Instructor)
.WithOptional(t => t.OfficeAssignment);
1-1关联(Required,Principal,Dependent)
一对一关联时,需要指定哪一个是主体(Principal)的哪一个是依赖(Dependent)的 在都是Optional时候,需要在HasOptional后使用WithOptionalPrincipal或WithOptionalDependent。 都是Required的时候,需要在HasRequired后使用WithRequiredPrincipal或WithRequiredDependent方法。
// Configure the primary key for the OfficeAssignment
modelBuilder.Entity()
.HasKey(t => t.InstructorID);
modelBuilder.Entity()
.HasRequired(t => t.OfficeAssignment)
.WithRequiredPrincipal(t => t.Instructor);
//下面是1-to-1单向关系,blogTable中没有BlogInfo表的Id
blogTable
.HasRequired(c => c.Info)
.WithRequiredPrincipal(c=>c.Blog);
//下面是1-to-1双向关系,blogTable中有BlogInfo表的Id
blogTable
.HasRequired(c => c.Info)
.WithRequiredDependent(c => c.Blog);
*-*关联(HasMany,WithMany,.Map)
多对多关联是教师和课程之间的示例。 如果不用.Map的话,EF会根据惯例自动生成中间表。列名分别是:Course_CourseID 和 Instructor_InstructorID
modelBuilder.Entity()
.HasMany(t => t.Instructors)
.WithMany(t => t.Courses)
.Map(m =>
{
m.ToTable("CourseInstructor");
m.MapLeftKey("CourseID");
m.MapRightKey("InstructorID");
});
blogArticleTable
.HasKey(c => c.Id)
.HasMany(c => c.File)
.WithMany(c => c.Article)
//Map用来自定义多对多中间表名称和列名的
.Map(m =>
{
m.ToTable("BlogArticleFile");//中间表名
m.MapLeftKey("BlogArticleId");//blogArticleTable的ID在其他表上显示的名字
m.MapRightKey("BlogFileId");//其他表的ID在blogArticleTable上显示的名字
});
单向关联()
单向关联就是把其中的一个关联属性去掉,如下,就只能从教师找到教室了
// Configure the primary Key for the OfficeAssignment
modelBuilder.Entity()
.HasKey(t => t.InstructorID);
modelBuilder.Entity()
.HasRequired(t => t.OfficeAssignment)
.WithRequiredPrincipal();
关联删除
可以使用WillCascadeOnDelete设置关联删除(cascade)。
如果一个外键依赖于一个非空实体,EF就会自动设置关联删除;
如果外键依赖的实体是可空的,EF就不会设置关联删除,并且主体删除后,外键会设置为null。
用下面这两句可以关闭关联删除:
modelBuilder.Conventions.Remove()
modelBuilder.Conventions.Remove()
下面是关闭关联删除的方法。
modelBuilder.Entity()
.HasRequired(t => t.Department)
.WithMany(t => t.Courses)
.HasForeignKey(d => d.DepartmentID)
.WillCascadeOnDelete(false);
设置混合外键
如果需要把Department的主键设置为DepartmentID 和 Name这两个属性,就可以按照下面方法设置Department的主键和Course的外键(有个好处是数据库里面不光能看到部门ID还能看到名称,这样更清晰一些)。
// Composite primary key
modelBuilder.Entity()
.HasKey(d => new { d.DepartmentID, d.Name });
// Composite foreign key
modelBuilder.Entity()
.HasRequired(c => c.Department)
.WithMany(d => d.Courses)
.HasForeignKey(d => new { d.DepartmentID, d.DepartmentName });
重命名一个外键名称(.Map)
modelBuilder.Entity()
.HasRequired(c => c.Department)
.WithMany(t => t.Courses)
.Map(m => m.MapKey("ChangedDepartmentID"));
定义一个非惯例的外键名称
如果不想用自动生成的外键名称的话,就可以用下面的方法自定义一个外键。
modelBuilder.Entity()
.HasRequired(c => c.Department)
.WithMany(d => d.Courses)
.HasForeignKey(c => c.SomeDepartmentID);
上面例子所用的数据库模型
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
// add a reference to System.ComponentModel.DataAnnotations DLL
using System.ComponentModel.DataAnnotations;
using System.Collections.Generic;
using System;
public class SchoolEntities : DbContext
{
public DbSet Courses { get; set; }
public DbSet Departments { get; set; }
public DbSet Instructors { get; set; }
public DbSet OfficeAssignments { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// Configure Code First to ignore PluralizingTableName convention
// If you keep this convention then the generated tables will have pluralized names.
modelBuilder.Conventions.Remove();
}
}
public class Department
{
public Department()
{
this.Courses = new HashSet();
}
// Primary key
public int DepartmentID { get; set; }
public string Name { get; set; }
public decimal Budget { get; set; }
public System.DateTime StartDate { get; set; }
public int? Administrator { get; set; }
// Navigation property
public virtual ICollection Courses { get; private set; }
}
public class Course
{
public Course()
{
this.Instructors = new HashSet();
}
// Primary key
public int CourseID { get; set; }
public string Title { get; set; }
public int Credits { get; set; }
// Foreign key
public int DepartmentID { get; set; }
// Navigation properties
public virtual Department Department { get; set; }
public virtual ICollection Instructors { get; private set; }
}
public partial class OnlineCourse : Course
{
public string URL { get; set; }
}
public partial class OnsiteCourse : Course
{
public OnsiteCourse()
{
Details = new Details();
}
public Details Details { get; set; }
}
public class Details
{
public System.DateTime Time { get; set; }
public string Location { get; set; }
public string Days { get; set; }
}
public class Instructor
{
public Instructor()
{
this.Courses = new List();
}
// Primary key
public int InstructorID { get; set; }
public string LastName { get; set; }
public string FirstName { get; set; }
public System.DateTime HireDate { get; set; }
// Navigation properties
public virtual ICollection Courses { get; private set; }
}
public class OfficeAssignment
{
// Specifying InstructorID as a primary
[Key()]
public Int32 InstructorID { get; set; }
public string Location { get; set; }
// When the Entity Framework sees Timestamp attribute
// it configures ConcurrencyCheck and DatabaseGeneratedPattern=Computed.
[Timestamp]
public Byte[] Timestamp { get; set; }
// Navigation property
public virtual Instructor Instructor { get; set; }
}
创建索引
完整的创建索引的方法参考Code First Data Annotations
单一index
modelBuilder
.Entity()
.Property(t => t.Name)
.HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute()));
多重index
modelBuilder
.Entity()
.Property(t => t.Name)
.HasColumnAnnotation(
"Index",
new IndexAnnotation(new[]
{
new IndexAttribute("Index1"),
new IndexAttribute("Index2") { IsUnique = true }
})));
封装一个创建索引的扩展
//调用入口
public static EntityTypeConfiguration HasIndex(
this EntityTypeConfiguration entityTypeConfiguration,
string indexName,
Func, PrimitivePropertyConfiguration> propertySelector,
params Func, PrimitivePropertyConfiguration>[] additionalPropertySelectors)
where TEntity : class
{
return entityTypeConfiguration.HasIndex(indexName, IndexOptions.Nonclustered,
propertySelector, additionalPropertySelectors);
}
//一个支持多种参数的重载
public static EntityTypeConfiguration HasIndex(
this EntityTypeConfiguration entityTypeConfiguration,
string indexName, IndexOptions indexOptions,
Func, PrimitivePropertyConfiguration> propertySelector,
params Func, PrimitivePropertyConfiguration>[] additionalPropertySelectors)
where TEntity : class
{
AddIndexColumn(indexName, indexOptions, 1, propertySelector(entityTypeConfiguration));
for (int i = 0; i < additionalPropertySelectors.Length; i++)
{
AddIndexColumn(indexName, indexOptions, i + 2, additionalPropertySelectors[i](entityTypeConfiguration));
}
return entityTypeConfiguration;
}
//将IndexAttribute添加到IndexAnnotation
private static void AddIndexColumn(
string indexName,
IndexOptions indexOptions,
int column,
PrimitivePropertyConfiguration propertyConfiguration)
{
var indexAttribute = new IndexAttribute(indexName, column)
{
IsClustered = indexOptions.HasFlag(IndexOptions.Clustered),
IsUnique = indexOptions.HasFlag(IndexOptions.Unique)
};
var annotation = GetIndexAnnotation(propertyConfiguration);
if (annotation != null)
{
var attributes = annotation.Indexes.ToList();
attributes.Add(indexAttribute);
annotation = new IndexAnnotation(attributes);
}
else
{
annotation = new IndexAnnotation(indexAttribute);
}
propertyConfiguration.HasColumnAnnotation(IndexAnnotation.AnnotationName, annotation);
}
//对属性进行反射得到IndexAnnotation的帮助方法
private static IndexAnnotation GetIndexAnnotation(PrimitivePropertyConfiguration propertyConfiguration)
{
var configuration = typeof (PrimitivePropertyConfiguration)
.GetProperty("Configuration", BindingFlags.Instance | BindingFlags.NonPublic)
.GetValue(propertyConfiguration, null);
var annotations = (IDictionary) configuration.GetType()
.GetProperty("Annotations", BindingFlags.Instance | BindingFlags.Public)
.GetValue(configuration, null);
object annotation;
if (!annotations.TryGetValue(IndexAnnotation.AnnotationName, out annotation))
return null;
return annotation as IndexAnnotation;
}
这样就可以使用下面方法进行索引的创建
.HasIndex("IX_Customers_Name", // Provide the index name.
e => e.Property(x => x.LastName), // Specify at least one column.
e => e.Property(x => x.FirstName)) // Multiple columns as desired.
.HasIndex("IX_Customers_EmailAddress", // Supports fluent chaining for more indexes.
IndexOptions.Unique, // Supports flags for unique and clustered.
e => e.Property(x => x.EmailAddress));
实体类
有映射关系的实体类
TPH、TPC、TPT
Table-Per-Hierarchy
TPH就是将所有的对象数据都放到同一个数据表中,再以Discriminator来分隔。
modelBuilder.Entity()
.Map(m => m.Requires("Type").HasValue("Course"))
.Map(m => m.Requires("Type").HasValue("OnsiteCourse"));
Table-Per-Type (TPT)
TPT表示将类内的属性存到各自的数据表内,父类拥有自己的表格,而继承线则由Foreign Key关联替代,因此子类只会保存该类内的属性。 TPT的设计比较偏向一般数据库设计方针。
modelBuilder.Entity().ToTable("Course");
modelBuilder.Entity().ToTable("OnsiteCourse");
Table-Per-Concrete Class (TPC)
TPC的方法是将继承的数据表放在各自的类型数据表中,并没有特别的关联,也就是各自独立。实现上数据表是分开的,而且数据表内会重复存放继承而来的结构。
modelBuilder.Entity()
.Property(c => c.CourseID)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
modelBuilder.Entity().Map(m =>
{
m.MapInheritedProperties();
m.ToTable("OnsiteCourse");
});
modelBuilder.Entity().Map(m =>
{
m.MapInheritedProperties();
m.ToTable("OnlineCourse");
});
存储过程
更新数据库
Enable-Migrations
在Package Manager Console中执行Enable-Migrations。 然后项目中就会增加Migration/Configuration.cs里面有DatabaseMigration相关代码。
update-database
执行Update-Database,就可以将数据库升级为最新版。
Add-Migration [name]
在修改数据后执行Add-Migration [name]就可以根据实体自动产生相应的Migration代码,其中[name]这次修改数据库的命名。