C#之EntityFramework的应用

目录

1，名词概述。

2，实体数据模型EDM介绍。

3，规范函数。

4，查看Linq转换成的SQL语句。

5，数据的增删改查。

5.1，数据查询

5.2，数据插入

5.3，数据更新

5.4，数据删除

6，使用SQL语句

6.1，无结果集返回时

6.2，有结果集返回值时

6.3，调用存储过程

6.3.1，定义存储过程

         6.3.2，调用存储过程

7，EF性能优化。

7.1，状态说明

7.2，优化方法

1，名词概述。

1.1，ORM：对象映射模型。

1.2，EntityClient：实体代理，用来操作EDM（实体对象模型）。

1.3，ADO.Net Provider:翻译Sql语句，用来访问数据库。

1.4，EDM（EntityDataModel）:实体数据模型。

1.5，CRUD :Create，Read，Update，Delete。

2，实体数据模型EDM介绍。

        EDM包含三部分SSDL,CSDL,MSL。实现将关系数据库模型转换为实体数据模型，并将由三部分组成结构描述放在扩展名为.edmx的XML文件中。

• 添加实体数据模型

• 选择使用模式（当前使用来自数据库的EF设计器模式）

注意事项：使用Code First模型将不产生edmx文件。

• 连接数据库，添加完成后项目中自动生成edmx文件。

• 选择edmx文件右键选择打开方式，在打开方式对话框中选择XML（文本）编辑器

• 查看详细的存储结构，实体结构，映射关系等

SSDL：存储架构定义语言。负责与数据库中的数据表做实体对应（将数据表的结构与关系用xml描述）

<!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="EFDBModel.Store" Provider="System.Data.SqlClient" ProviderManifestToken="2008" Alias="Self" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns:customannotation="http://schemas.microsoft.com/ado/2013/11/edm/customannotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
        <EntityType Name="Admins">
          <Key>
            <PropertyRef Name="LoginId" />
          </Key>
          <Property Name="LoginId" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
          <Property Name="LoginPwd" Type="varchar" MaxLength="20" Nullable="false" />
          <Property Name="AdminName" Type="varchar" MaxLength="20" Nullable="false" />
        </EntityType>
        <EntityType Name="ScoreList">
          <Key>
            <PropertyRef Name="Id" />
          </Key>
          <Property Name="Id" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
          <Property Name="StudentId" Type="int" Nullable="false" />
          <Property Name="CSharp" Type="int" />
          <Property Name="SQLServerDB" Type="int" />
          <Property Name="UpdateTime" Type="smalldatetime" Nullable="false" />
        </EntityType>
        <EntityType Name="StudentClass">
          <Key>
            <PropertyRef Name="ClassId" />
          </Key>
          <Property Name="ClassId" Type="int" Nullable="false" />
          <Property Name="ClassName" Type="varchar" MaxLength="20" Nullable="false" />
        </EntityType>
        <EntityType Name="Students">
          <Key>
            <PropertyRef Name="StudentId" />
          </Key>
          <Property Name="StudentId" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
          <Property Name="StudentName" Type="varchar" MaxLength="20" Nullable="false" />
          <Property Name="Gender" Type="char" MaxLength="2" Nullable="false" />
          <Property Name="Birthday" Type="smalldatetime" Nullable="false" />
          <Property Name="StudentIdNo" Type="numeric" Precision="18" Scale="0" Nullable="false" />
          <Property Name="Age" Type="int" Nullable="false" />
          <Property Name="PhoneNumber" Type="varchar" MaxLength="50" />
          <Property Name="StudentAddress" Type="varchar" MaxLength="500" />
          <Property Name="ClassId" Type="int" Nullable="false" />
        </EntityType>
        <Association Name="fk_classId">
          <End Role="StudentClass" Type="Self.StudentClass" Multiplicity="1" />
          <End Role="Students" Type="Self.Students" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="StudentClass">
              <PropertyRef Name="ClassId" />
            </Principal>
            <Dependent Role="Students">
              <PropertyRef Name="ClassId" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
        <Association Name="fk_StudentId">
          <End Role="Students" Type="Self.Students" Multiplicity="1" />
          <End Role="ScoreList" Type="Self.ScoreList" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="Students">
              <PropertyRef Name="StudentId" />
            </Principal>
            <Dependent Role="ScoreList">
              <PropertyRef Name="StudentId" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
        <EntityContainer Name="EFDBModelStoreContainer">
          <EntitySet Name="Admins" EntityType="Self.Admins" Schema="dbo" store:Type="Tables" />
          <EntitySet Name="ScoreList" EntityType="Self.ScoreList" Schema="dbo" store:Type="Tables" />
          <EntitySet Name="StudentClass" EntityType="Self.StudentClass" Schema="dbo" store:Type="Tables" />
          <EntitySet Name="Students" EntityType="Self.Students" Schema="dbo" store:Type="Tables" />
          <AssociationSet Name="fk_classId" Association="Self.fk_classId">
            <End Role="StudentClass" EntitySet="StudentClass" />
            <End Role="Students" EntitySet="Students" />
          </AssociationSet>
          <AssociationSet Name="fk_StudentId" Association="Self.fk_StudentId">
            <End Role="Students" EntitySet="Students" />
            <End Role="ScoreList" EntitySet="ScoreList" />
          </AssociationSet>
        </EntityContainer>
      </Schema>
    </edmx:StorageModels>

CSDL: 概念架构定义语言。概念模型对应的实体类，用实体类表示数据库中的对象。

<!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="EFDBModel" Alias="Self" annotation:UseStrongSpatialTypes="false" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns:customannotation="http://schemas.microsoft.com/ado/2013/11/edm/customannotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
        <EntityType Name="Admins">
          <Key>
            <PropertyRef Name="LoginId" />
          </Key>
          <Property Name="LoginId" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
          <Property Name="LoginPwd" Type="String" MaxLength="20" FixedLength="false" Unicode="false" Nullable="false" />
          <Property Name="AdminName" Type="String" MaxLength="20" FixedLength="false" Unicode="false" Nullable="false" />
        </EntityType>
        <EntityType Name="ScoreList">
          <Key>
            <PropertyRef Name="Id" />
          </Key>
          <Property Name="Id" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
          <Property Name="StudentId" Type="Int32" Nullable="false" />
          <Property Name="CSharp" Type="Int32" />
          <Property Name="SQLServerDB" Type="Int32" />
          <Property Name="UpdateTime" Type="DateTime" Nullable="false" Precision="0" />
          <NavigationProperty Name="Students" Relationship="Self.fk_StudentId" FromRole="ScoreList" ToRole="Students" />
        </EntityType>
        <EntityType Name="StudentClass">
          <Key>
            <PropertyRef Name="ClassId" />
          </Key>
          <Property Name="ClassId" Type="Int32" Nullable="false" />
          <Property Name="ClassName" Type="String" MaxLength="20" FixedLength="false" Unicode="false" Nullable="false" />
          <NavigationProperty Name="Students" Relationship="Self.fk_classId" FromRole="StudentClass" ToRole="Students" />
        </EntityType>
        <EntityType Name="Students">
          <Key>
            <PropertyRef Name="StudentId" />
          </Key>
          <Property Name="StudentId" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
          <Property Name="StudentName" Type="String" MaxLength="20" FixedLength="false" Unicode="false" Nullable="false" />
          <Property Name="Gender" Type="String" MaxLength="2" FixedLength="true" Unicode="false" Nullable="false" />
          <Property Name="Birthday" Type="DateTime" Nullable="false" Precision="0" />
          <Property Name="StudentIdNo" Type="Decimal" Precision="18" Scale="0" Nullable="false" />
          <Property Name="Age" Type="Int32" Nullable="false" />
          <Property Name="PhoneNumber" Type="String" MaxLength="50" FixedLength="false" Unicode="false" />
          <Property Name="StudentAddress" Type="String" MaxLength="500" FixedLength="false" Unicode="false" />
          <Property Name="ClassId" Type="Int32" Nullable="false" />
          <NavigationProperty Name="ScoreList" Relationship="Self.fk_StudentId" FromRole="Students" ToRole="ScoreList" />
          <NavigationProperty Name="StudentClass" Relationship="Self.fk_classId" FromRole="Students" ToRole="StudentClass" />
        </EntityType>
        <Association Name="fk_StudentId">
          <End Role="Students" Type="Self.Students" Multiplicity="1" />
          <End Role="ScoreList" Type="Self.ScoreList" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="Students">
              <PropertyRef Name="StudentId" />
            </Principal>
            <Dependent Role="ScoreList">
              <PropertyRef Name="StudentId" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
        <Association Name="fk_classId">
          <End Role="StudentClass" Type="Self.StudentClass" Multiplicity="1" />
          <End Role="Students" Type="Self.Students" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="StudentClass">
              <PropertyRef Name="ClassId" />
            </Principal>
            <Dependent Role="Students">
              <PropertyRef Name="ClassId" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
        <EntityContainer Name="EFDBEntities" annotation:LazyLoadingEnabled="true">
          <EntitySet Name="Admins" EntityType="Self.Admins" />
          <EntitySet Name="ScoreList" EntityType="Self.ScoreList" />
          <EntitySet Name="StudentClass" EntityType="Self.StudentClass" />
          <EntitySet Name="Students" EntityType="Self.Students" />
          <AssociationSet Name="fk_StudentId" Association="Self.fk_StudentId">
            <End Role="Students" EntitySet="Students" />
            <End Role="ScoreList" EntitySet="ScoreList" />
          </AssociationSet>
          <AssociationSet Name="fk_classId" Association="Self.fk_classId">
            <End Role="StudentClass" EntitySet="StudentClass" />
            <End Role="Students" EntitySet="Students" />
          </AssociationSet>
        </EntityContainer>
      </Schema>
    </edmx:ConceptualModels>

MSL:  映射规范语言。 将存储模型中字段与概念模型中的属性对应。

  <!-- C-S mapping content -->
    <edmx:Mappings>
      <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
        <EntityContainerMapping StorageEntityContainer="EFDBModelStoreContainer" CdmEntityContainer="EFDBEntities">
          <EntitySetMapping Name="Admins">
            <EntityTypeMapping TypeName="EFDBModel.Admins">
              <MappingFragment StoreEntitySet="Admins">
                <ScalarProperty Name="LoginId" ColumnName="LoginId" />
                <ScalarProperty Name="LoginPwd" ColumnName="LoginPwd" />
                <ScalarProperty Name="AdminName" ColumnName="AdminName" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
          <EntitySetMapping Name="ScoreList">
            <EntityTypeMapping TypeName="EFDBModel.ScoreList">
              <MappingFragment StoreEntitySet="ScoreList">
                <ScalarProperty Name="Id" ColumnName="Id" />
                <ScalarProperty Name="StudentId" ColumnName="StudentId" />
                <ScalarProperty Name="CSharp" ColumnName="CSharp" />
                <ScalarProperty Name="SQLServerDB" ColumnName="SQLServerDB" />
                <ScalarProperty Name="UpdateTime" ColumnName="UpdateTime" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
          <EntitySetMapping Name="StudentClass">
            <EntityTypeMapping TypeName="EFDBModel.StudentClass">
              <MappingFragment StoreEntitySet="StudentClass">
                <ScalarProperty Name="ClassId" ColumnName="ClassId" />
                <ScalarProperty Name="ClassName" ColumnName="ClassName" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
          <EntitySetMapping Name="Students">
            <EntityTypeMapping TypeName="EFDBModel.Students">
              <MappingFragment StoreEntitySet="Students">
                <ScalarProperty Name="StudentId" ColumnName="StudentId" />
                <ScalarProperty Name="StudentName" ColumnName="StudentName" />
                <ScalarProperty Name="Gender" ColumnName="Gender" />
                <ScalarProperty Name="Birthday" ColumnName="Birthday" />
                <ScalarProperty Name="StudentIdNo" ColumnName="StudentIdNo" />
                <ScalarProperty Name="Age" ColumnName="Age" />
                <ScalarProperty Name="PhoneNumber" ColumnName="PhoneNumber" />
                <ScalarProperty Name="StudentAddress" ColumnName="StudentAddress" />
                <ScalarProperty Name="ClassId" ColumnName="ClassId" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
        </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>

3，规范函数。

        EDM配合Linq查询语言使用，但是因为EDM中的Linq查询语言最终由Ado.net Provider编译为SQL语句，限制了EDM只能使用部分规范的方法。

• 可使用的规范函数。

System.String 静态方法 Concat(),Equals(),IsNullOrEmpty()等

System.String 实例方法 Contains(),EndsWith(),StartsWith(),Replace()等
 
DataTime 静态方法

DataTime 实例方法

Math静态方法

Guid静态方法

例如：

context.Students.Where(t => t.StudentName.StartsWith("张"));
//被provider翻译为：
SELECT 
    [Extent1].[StudentId] AS [StudentId], 
    [Extent1].[StudentName] AS [StudentName], 
    [Extent1].[Gender] AS [Gender], 
    [Extent1].[Birthday] AS [Birthday], 
    [Extent1].[StudentIdNo] AS [StudentIdNo], 
    [Extent1].[Age] AS [Age], 
    [Extent1].[PhoneNumber] AS [PhoneNumber], 
    [Extent1].[StudentAddress] AS [StudentAddress], 
    [Extent1].[ClassId] AS [ClassId]
    FROM [dbo].[Students] AS [Extent1]
    WHERE [Extent1].[StudentName] LIKE '张%'

//如果是使用非规范函数的正则表达式，则抛出异常：
//其他信息: LINQ to Entities 不识别方法“Boolean IsMatch(System.String, System.String)”，因//此该方法无法转换为存储表达式。），因无法转译为SQL语句
context.Students.Where(t => System.Text.RegularExpressions.Regex.IsMatch(t.StudentName, "^张.*"));//抛出异常

4，查看Linq转换成的SQL语句。

        查询表达式.ToString()即可查看转换SQL语句。需要注意的是Linq查询是延迟查询，即使用时才进行查询。

5，数据的增删改查。

使用的数据

5.1，数据查询

MyDbContext context = new MyDbContext();
//普通查询
var result = context.Students.Where(s => s.StudentId > 100004);
Students ss=   context.Students.Find(100004);

//多表联合查询
var arr=   from a in context.ScoreList where a.StudentId>=100004
             select new
               {
                  a.StudentId,
                  a.SQLServerDB,
                  a.Students.StudentName,
                  a.Students.StudentClass.ClassName
               };

5.2，数据插入

 MyDbContext context = new MyDbContext();
//添加方式
 Students s = new Students
            {
                Age = 23,
                Birthday = DateTime.Parse("2001/4/23"),
                Gender = "女",
                PhoneNumber = "1234567",
                StudentAddress = "湖北武汉",
                StudentIdNo = 123456789987654321,
                StudentName = "李欣",
                ClassId = context.StudentClass.FirstOrDefault(t => t.ClassName.Equals("网络1班")).ClassId
            };
            context.Students.Add(s);
            context.SaveChanges();

//方式2
s.StudentIdNo = 123456789987654322;
            context.Entry(s).State = System.Data.Entity.EntityState.Added;
            context.SaveChanges();
//方式3
 Students stu = new Students
            {
                StudentId = 100010,
                Age = 20,
                Birthday = DateTime.Parse("1998/7/23"),
                Gender = "女",
                PhoneNumber = "13232224242",
                StudentIdNo = 111111110111111118,
                StudentAddress = "广东东莞",
                StudentName = "张三",

            };//注意这里无需指明classId

//这里获取StudentClass对象，该对象包含了classId同时也包含了导航属性students（导航属性students中的Studentid均相同，主外键一对多的关系）
(from c in context.StudentClass where c.ClassName.Equals("计算机2班") select c).FirstOrDefault().Students.Add(stu);
context.SaveChanges();

5.3，数据更新

//方式1
Students stu = context.Students.Find(100014);
stu.StudentName = "李2新";
context.SaveChanges();
//方式2
Students stu2 = new Students
{
     Age = 23,
    Birthday = DateTime.Parse("2001/4/23"),
    Gender = "女",
    PhoneNumber = "1234567",
    StudentAddress = "湖北武汉",
    StudentIdNo = 123456789987654323,
    StudentName = "李欣",
    ClassId = context.StudentClass.FirstOrDefault(t => t.ClassName.Equals("网络1班")).ClassId,
StudentId = 100015
};
 context.Entry(stu2).State = System.Data.Entity.EntityState.Modified;
context.SaveChanges();

5.4，数据删除

//方法1，查询出对象再删除
            Students stu1 = context.Students.Find(100014);
            context.Students.Remove(stu1);
            context.SaveChanges();
//方法2，创建对象直接删除
            Students stu2 = new Students { StudentId = 100013 };
            context.Entry(stu2).State = System.Data.Entity.EntityState.Deleted;
            context.SaveChanges();
//方法3，创建对象附加后再删除
            Students stu3 = new Students { StudentId = 100011 };
            context.Students.Attach(stu3);
            context.Students.Remove(stu3);
            context.SaveChanges();
//方法4
            Students stu3 = new Students { StudentId = 100011 };
             context.Entry(stu3).State = System.Data.Entity.EntityState.Unchanged;
            context.Students.Remove(stu3);
            context.SaveChanges();

6，使用SQL语句

6.1，无结果集返回时

            //增加
            string sqlcmd = "insert into StudentClass (ClassId,ClassName) values (7,\'软件10班\')";
            //删除
            string sqlcmd2 = "delete from StudentClass where ClassId>=7";
            context.Database.ExecuteSqlCommand(sqlcmd);

6.2，有结果集返回值时

 var arr = context.Database.SqlQuery<StudentClass>("select * from StudentClass");
            foreach (var item in arr)
            {
                Console.WriteLine(item.ClassId + "\t" + item.ClassName);
            }

6.3，调用存储过程

6.3.1,定义存储过程

--存储过程1
  if exists(select * from sysobjects where name='ups_procUpate')
      drop proc ups_procUpate
  go

  create proc ups_procUpate
     @studentName nvarchar(50),@id int = 100004
  as
      update Students set StudentName=@studentName where StudentId=@id
  go
 

 --存储过程2 
  if exists(select * from sysobjects where name ='ups_procRead')
      drop proc ups_procRead
  go

  create proc ups_procRead @id int
  as
	select * from Students where StudentId>=@id
  go
  

6.3.2，调用存储过程

 //调用存储过程
            context.Database.ExecuteSqlCommand("exec  ups_procUpate '猪八戒',100005");
//调用带参数的存储过程
            System.Data.SqlClient.SqlParameter[] paras = new System.Data.SqlClient.SqlParameter[]
            {
                   new System.Data.SqlClient.SqlParameter("@studentName","牛魔王"),
                   new System.Data.SqlClient.SqlParameter("@id",100006)
            };
            context.Database.ExecuteSqlCommand("exec ups_procUpate @studentName,@id ", paras);
//调用具有查询结果的存储过程
            var collect = context.Database.SqlQuery<Students>("exec ups_procRead 100004");
            foreach (var item in collect)
            {
                Console.WriteLine(item.StudentId + "\t" + item.StudentName);
            }

7，EF性能优化。

7.1，状态说明

EntityState
状态		    整数值		说明		            具备该状态的对象

Detached 	1	    对象存在但未被跟踪	    新创建的对象

UNchanged	2	    对象尚未经过修改	        使用DbContext读取的对象
						                    使用Attach()方法添加的对象
						                    执行SaveChange（）后的对象

Added		4	    对象为新对象，并已添加	    使用Add（）方法添加的对象
			        到上下文中

Deleted		8	    对象已从上下文中删除	    使用Remove()移除对象	

7.2，优化方法

        使用AsNoTracing()方法

        不进行状态跟踪添加AsNoTracing（）方法后，对象将不被状态管理，查询性能提高返回的实体将不再DbContext中缓存只适合纯粹的查询操作。

            //默认状态跟踪
            Students stu = (from a in context.Students where a.StudentId.Equals(100004) select a).FirstOrDefault();
            Console.WriteLine("该Student状态:{0}", context.Entry(stu).State);
            //不进行状态跟踪
            Students stu2 = context.Students.AsNoTracking().FirstOrDefault(a => a.StudentId.Equals(100004));
            Console.WriteLine("该Student状态:{0}", context.Entry(stu2).State);

        禁用自动跟踪（默认开启）DbContext.Configuration.AutoDetectChangesEnabled = false;

        关闭前，当执行Add()操作时耗费大量的性能，导致DbContext遍历所有缓存的Entity，比较原始值和当前值，非常耗时。
        关闭后使用Add()告知DbContext变化即可，如果是删除使用Remove()方法，以及通过State属性告知变化。
        适合于大批量操作数据（添加，删除，修改）

 System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            Console.WriteLine("启用状态自动跟踪");
            context.Configuration.AutoDetectChangesEnabled = true;
            for (int i = 17; i < 27; i++)
            {
                Console.WriteLine("第{0}次", i);
                StudentClass sc = new StudentClass { ClassName = $"网络{i}班", ClassId = i };
                Console.WriteLine("\t添加之前状态：{0}", context.Entry(sc).State);
                context.StudentClass.Add(sc);
                Console.WriteLine("\t添加之后状态：{0}", context.Entry(sc).State);
            }
            context.SaveChanges();
            Console.WriteLine("耗时：{0}", watch.Elapsed);
            Console.WriteLine("禁用状态自动跟踪");
            context.Configuration.AutoDetectChangesEnabled = false;

            watch.Restart();
            for (int i = 27; i < 37; i++)
            {
                Console.WriteLine("第{0}次", i);
                StudentClass sc = new StudentClass { ClassName = $"网络{i}班", ClassId = i };
                Console.WriteLine("\t添加之前状态：{0}", context.Entry(sc).State);
                context.StudentClass.Add(sc);
                Console.WriteLine("\t添加之后状态：{0}", context.Entry(sc).State);
            }
            context.SaveChanges();
            Console.WriteLine("耗时：{0}", watch.Elapsed);