mysql创建存储过程selectore,EntityFramework关联、存储过程和更新数据库

关联(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]这次修改数据库的命名。