当前位置： 首页 > news >正文

EF 常见语句以及sql语句简单 后续继续添加

news 来源：原创 2024/5/14 20:44:59

1.注意级联删除的时候数据库的外键要设置为开启级联删除，（数据库里sqlserver的外键修改的时候，可以看到级联删除和级联更新）

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebApplication3.Models;

namespace WebApplication3.Controllers
{
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{

 

 //EF 批量添加10万数据，虽然表格只有4个列，但是耗时却大概只有1分钟，足见效率已经提高了很多

Model1Container context=new Model1Container();
Class c = context.Classes.First(x => x.Id == 1);
Student s = null;
List<Student> stuList=new List<Student>();
for (int i = 10; i < 100000; i++)
{
s = new Student() { Address = i.ToString(), Class = c, Name = i.ToString() + "N" };
stuList.Add(s);
}

//这里使用AddRange经过我测试效率会比直接在循环内context.Students.Add(s)的效率高非常多

context.Students.AddRange(stuList);
context.SaveChanges();

Class c2 = context.Classes.First(x=>x.Id==1);

//ef的修改

Menu m2=new Menu() {mId = 119,mName = "151"};
dbContext.Menus.Attach(m2);
dbContext.Entry(m2).State=EntityState.Modified;
dbContext.SaveChanges();

//ef的修改

 //如果只想针对性的修改某几列提高效率就这样

Menu m2=new Menu() {mId = 119,mName = "1511"};
dbContext.Menus.Attach(m2);
dbContext.Entry(m2).State=EntityState.Unchanged;
dbContext.Entry(m2).Property("mName").IsModified = true;
dbContext.SaveChanges();


#region EF的添加代码，这里是一个一对多的关系
//Model1Container DbContext = new Model1Container();
//User u = new User() { UserName = "zhangsan" };
//Role r = new Role() { RoleName = "juese1" };
//u.Role = new List<Role>();
//DbContext.Entry(r).State = System.Data.Entity.EntityState.Added;
//u.Role.Add(r);
//DbContext.Users.Add(u);
//DbContext.SaveChanges();
#endregion

#region 级联删除
// < Association Name = "UserRole" >
// < End Type = "Model1.User" Role = "User" Multiplicity = "1" >
// < OnDelete Action = "Cascade" /> 注意如果是1对1的话在配置文件edmx中设置为级联删除开启，1对多，多对多默认是开启的
// </ End >
// < End Type = "Model1.Role" Role = "Role" Multiplicity = "*" />
// </ Association >
#region 非官方推荐的删除
//官方推荐的删除方式是先查询再删除，而这里的是通过附加ef上下文去删除，有个问题如果当前的对象id不存在就会报错
//这就是为什么官方推荐先查询再删除的原因，查询之后你可以判断对象是不是为空
//Model1Container DbContext = new Model1Container();
//User u = new User() { Id = 2 };
//DbContext.Users.Attach(u);
//DbContext.Users.Remove(u);
//DbContext.SaveChanges();
#endregion

#region 官方推荐的删除方式
//官方推荐的删除方式
//Model1Container DbContext = new Model1Container();
//User u = DbContext.Users.FirstOrDefault(x => x.Id == 2);
//if (u != null)
// DbContext.Users.Remove(u);
//DbContext.SaveChanges();
#endregion

#endregion

#region EF-SQL语句
Model1Container DbContext=new Model1Container();
int i=DbContext.Database.ExecuteSqlCommand(TransactionalBehavior.EnsureTransaction,
"update users set username='李四' where id=3");
#endregion

return View();
}
}
}

 

 

2.顺带的生成的类我也一起贴进来吧


namespace WebApplication3.Models
{
using System;
using System.Collections.Generic;

public partial class Role
{
public int Id { get; set; }
public string RoleName { get; set; }

public virtual User User { get; set; }
}
}

 

//------------------------------------------------------------------------------
// <auto-generated>
// 此代码已从模板生成。
//
// 手动更改此文件可能导致应用程序出现意外的行为。
// 如果重新生成代码，将覆盖对此文件的手动更改。
// </auto-generated>
//------------------------------------------------------------------------------

namespace WebApplication3.Models
{
using System;
using System.Collections.Generic;

public partial class User
{
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
public User()
{
this.Role = new HashSet<Role>();
}

public int Id { get; set; }
public string UserName { get; set; }

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<Role> Role { get; set; }
}
}

3.我把配置文件一起贴这里，其实可以根据图形界面操作生成，这里也必须知道原理，不然无法更改

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
<!-- EF Runtime content -->
<edmx:Runtime>
<!-- SSDL content -->
<edmx:StorageModels>
<Schema Namespace="Model1.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
<EntityContainer Name="Model1StoreContainer">
<EntitySet Name="Users" EntityType="Model1.Store.Users" store:Type="Tables" Schema="dbo" />
<EntitySet Name="Roles" EntityType="Model1.Store.Roles" store:Type="Tables" Schema="dbo" />
<AssociationSet Name="UserRole" Association="Model1.Store.UserRole">
<End Role="User" EntitySet="Users" />
<End Role="Role" EntitySet="Roles" />
</AssociationSet>
</EntityContainer>
<EntityType Name="Users">
<Key>
<PropertyRef Name="Id" />
</Key>
<Property Name="Id" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
<Property Name="UserName" Type="nvarchar(max)" Nullable="false" />
</EntityType>
<EntityType Name="Roles">
<Key>
<PropertyRef Name="Id" />
</Key>
<Property Name="Id" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
<Property Name="RoleName" Type="nvarchar(max)" Nullable="false" />
<Property Name="Uid" Type="int" Nullable="false" />
</EntityType>
<Association Name="UserRole">
<End Role="User" Type="Model1.Store.Users" Multiplicity="1" />
<End Role="Role" Type="Model1.Store.Roles" Multiplicity="*" />
<ReferentialConstraint>
<Principal Role="User">
<PropertyRef Name="Id" />
</Principal>
<Dependent Role="Role">
<PropertyRef Name="Uid" />
</Dependent>
</ReferentialConstraint>
</Association>
</Schema></edmx:StorageModels>
<!-- CSDL content -->
<edmx:ConceptualModels>
<Schema xmlns="http://schemas.microsoft.com/ado/2009/11/edm" xmlns:cg="http://schemas.microsoft.com/ado/2006/04/codegeneration" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" Namespace="Model1" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" annotation:UseStrongSpatialTypes="false">
<EntityContainer Name="Model1Container" annotation:LazyLoadingEnabled="true">
<EntitySet Name="Users" EntityType="Model1.User" />
<EntitySet Name="Roles" EntityType="Model1.Role" />
<AssociationSet Name="UserRole" Association="Model1.UserRole">
<End Role="User" EntitySet="Users" />
<End Role="Role" EntitySet="Roles" />
</AssociationSet>
</EntityContainer>
<EntityType Name="User">
<Key>
<PropertyRef Name="Id" />
</Key>
<Property Name="Id" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
<Property Name="UserName" Type="String" Nullable="false" />
<NavigationProperty Name="Role" Relationship="Model1.UserRole" FromRole="User" ToRole="Role" />
</EntityType>
<EntityType Name="Role">
<Key>
<PropertyRef Name="Id" />
</Key>
<Property Name="Id" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
<Property Name="RoleName" Type="String" Nullable="false" />
<NavigationProperty Name="User" Relationship="Model1.UserRole" FromRole="Role" ToRole="User" />
</EntityType>
<Association Name="UserRole">
<End Type="Model1.User" Role="User" Multiplicity="1" >
<!--<OnDelete Action="Cascade"/>-->

</End>
<End Type="Model1.Role" Role="Role" Multiplicity="*" />

</Association>
</Schema>
</edmx:ConceptualModels>
<!-- C-S mapping content -->
<edmx:Mappings>
<Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
<EntityContainerMapping StorageEntityContainer="Model1StoreContainer" CdmEntityContainer="Model1Container">
<EntitySetMapping Name="Users">
<EntityTypeMapping TypeName="IsTypeOf(Model1.User)">
<MappingFragment StoreEntitySet="Users">
<ScalarProperty Name="Id" ColumnName="Id" />
<ScalarProperty Name="UserName" ColumnName="UserName" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
<EntitySetMapping Name="Roles">
<EntityTypeMapping TypeName="IsTypeOf(Model1.Role)">
<MappingFragment StoreEntitySet="Roles">
<ScalarProperty Name="Id" ColumnName="Id" />
<ScalarProperty Name="RoleName" ColumnName="RoleName" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
<AssociationSetMapping Name="UserRole" TypeName="Model1.UserRole" StoreEntitySet="Roles">
<EndProperty Name="User">
<ScalarProperty Name="Id" ColumnName="Uid" />
</EndProperty>
<EndProperty Name="Role">
<ScalarProperty Name="Id" ColumnName="Id" />
</EndProperty>
</AssociationSetMapping>
</EntityContainerMapping>
</Mapping></edmx:Mappings>
</edmx:Runtime>
<!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
<edmx:Designer xmlns="http://schemas.microsoft.com/ado/2009/11/edmx">
<edmx:Connection>
<DesignerInfoPropertySet>
<DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
</DesignerInfoPropertySet>
</edmx:Connection>
<edmx:Options>
<DesignerInfoPropertySet>
<DesignerProperty Name="ValidateOnBuild" Value="true" />
<DesignerProperty Name="EnablePluralization" Value="False" />
<DesignerProperty Name="CodeGenerationStrategy" Value="无" />
<DesignerProperty Name="UseLegacyProvider" Value="False" />
</DesignerInfoPropertySet>
</edmx:Options>
<!-- Diagram content (shape and connector positions) -->
<edmx:Diagrams>
</edmx:Diagrams>
</edmx:Designer>
</edmx:Edmx>

3.更能说明问题的edmx

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
<!-- EF Runtime content -->
<edmx:Runtime>
<!-- SSDL content -->
<edmx:StorageModels>
<Schema Namespace="Model1.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
<EntityContainer Name="Model1StoreContainer">
<EntitySet Name="Classes" EntityType="Model1.Store.Classes" store:Type="Tables" Schema="dbo" />
<EntitySet Name="Students" EntityType="Model1.Store.Students" store:Type="Tables" Schema="dbo" />
<AssociationSet Name="ClassStudent" Association="Model1.Store.ClassStudent">
<End Role="Class" EntitySet="Classes" />
<End Role="Student" EntitySet="Students" />
</AssociationSet>
</EntityContainer>
<EntityType Name="Classes">
<Key>
<PropertyRef Name="Id" />
</Key>
<Property Name="Id" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
<Property Name="ClassName" Type="nvarchar(max)" Nullable="false" />
<Property Name="ClassNo" Type="nvarchar(max)" Nullable="false" />
<Property Name="DeptNo" Type="nvarchar(max)" Nullable="false" />
</EntityType>
<EntityType Name="Students">
<Key>
<PropertyRef Name="Id" />
</Key>
<Property Name="Id" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
<Property Name="Name" Type="nvarchar(max)" Nullable="false" />
<Property Name="Address" Type="nvarchar(max)" Nullable="false" />
<Property Name="ClassId" Type="int" Nullable="false" />
</EntityType>
<Association Name="ClassStudent">
<End Role="Class" Type="Model1.Store.Classes" Multiplicity="1" />
<End Role="Student" Type="Model1.Store.Students" Multiplicity="*" />
<ReferentialConstraint>
<Principal Role="Class">
<PropertyRef Name="Id" />
</Principal>
<Dependent Role="Student">
<PropertyRef Name="ClassId" />
</Dependent>
</ReferentialConstraint>
</Association>
</Schema></edmx:StorageModels>
<!-- CSDL content -->
<edmx:ConceptualModels>
<Schema xmlns="http://schemas.microsoft.com/ado/2009/11/edm" xmlns:cg="http://schemas.microsoft.com/ado/2006/04/codegeneration" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" Namespace="Model1" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" annotation:UseStrongSpatialTypes="false">
<EntityContainer Name="Model1Container" annotation:LazyLoadingEnabled="true">
<EntitySet Name="Classes2" EntityType="Model1.Class2" />
<EntitySet Name="Students2" EntityType="Model1.Student2" />
<AssociationSet Name="ClassStudent" Association="Model1.ClassStudent">
<End Role="Class" EntitySet="Classes2" />
<End Role="Student" EntitySet="Students2" />
</AssociationSet>
</EntityContainer>
<EntityType Name="Class2">
<Key>
<PropertyRef Name="Id2" />
</Key>
<Property Name="Id2" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
<Property Name="ClassName2" Type="String" Nullable="false" />
<Property Name="ClassNo2" Type="String" Nullable="false" />
<Property Name="DeptNo2" Type="String" Nullable="false" />
<NavigationProperty Name="DaoStudent" Relationship="Model1.ClassStudent" FromRole="Class" ToRole="Student" />
</EntityType>
<EntityType Name="Student2">
<Key>
<PropertyRef Name="Id2" />
</Key>
<Property Name="Id2" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
<Property Name="StuName2" Type="String" Nullable="false" />
<Property Name="Address2" Type="String" Nullable="false" />
<NavigationProperty Name="DaoClass" Relationship="Model1.ClassStudent" FromRole="Student" ToRole="Class" />
<Property Name="ClassId" Type="Int32" Nullable="false" />
</EntityType>
<Association Name="ClassStudent">
<End Type="Model1.Class2" Role="Class" Multiplicity="1" />
<End Type="Model1.Student2" Role="Student" Multiplicity="*" />
<ReferentialConstraint>
<Principal Role="Class">
<PropertyRef Name="Id2" />
</Principal>
<Dependent Role="Student">
<PropertyRef Name="ClassId" />
</Dependent>
</ReferentialConstraint>
</Association>
</Schema>
</edmx:ConceptualModels>
<!-- C-S mapping content -->
<edmx:Mappings>
<Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
<EntityContainerMapping StorageEntityContainer="Model1StoreContainer" CdmEntityContainer="Model1Container">
<EntitySetMapping Name="Classes2">
<EntityTypeMapping TypeName="IsTypeOf(Model1.Class2)">
<MappingFragment StoreEntitySet="Classes">
<ScalarProperty Name="Id2" ColumnName="Id" />
<ScalarProperty Name="ClassName2" ColumnName="ClassName" />
<ScalarProperty Name="ClassNo2" ColumnName="ClassNo" />
<ScalarProperty Name="DeptNo2" ColumnName="DeptNo" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
<EntitySetMapping Name="Students2">
<EntityTypeMapping TypeName="IsTypeOf(Model1.Student2)">
<MappingFragment StoreEntitySet="Students">
<ScalarProperty Name="Id2" ColumnName="Id" />
<ScalarProperty Name="StuName2" ColumnName="Name" />
<ScalarProperty Name="Address2" ColumnName="Address" />
<ScalarProperty Name="ClassId" ColumnName="ClassId" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
</EntityContainerMapping>
</Mapping></edmx:Mappings>
</edmx:Runtime>
<!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
<edmx:Designer xmlns="http://schemas.microsoft.com/ado/2009/11/edmx">
<edmx:Connection>
<DesignerInfoPropertySet>
<DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
</DesignerInfoPropertySet>
</edmx:Connection>
<edmx:Options>
<DesignerInfoPropertySet>
<DesignerProperty Name="ValidateOnBuild" Value="true" />
<DesignerProperty Name="EnablePluralization" Value="False" />
<DesignerProperty Name="CodeGenerationStrategy" Value="无" />
<DesignerProperty Name="UseLegacyProvider" Value="False" />
</DesignerInfoPropertySet>
</edmx:Options>
<!-- Diagram content (shape and connector positions) -->
<edmx:Diagrams>
</edmx:Diagrams>
</edmx:Designer>
</edmx:Edmx>

转载于:https://www.cnblogs.com/kexb/p/4782897.html

相关文章：

  • Javascript -- 精通Math对象
  • 基于TLS（线程局部存储）的高效timelog实现
  • 当一个java程序员到底有多难
  • [纯干货] MySQL索引背后的数据结构及算法原理
  • traffic and backfill
  • 简单的转义字符
  • Eclipse自动补全设置
  • ${factoryList }后面有空格不影响
  • 再看BP神经网络
  • JavaScript深浅拷贝
  • 节约内存：Instagram的Redis实践
  • 智云大咖秀：怎样的稳定器才是摄影师的“灵魂辅助”？
  • Bootstrap vs Foundation如何选择靠谱前端框架
  • 双管齐下，VMware的容器新战略
  • 退役前的记录(2018.10.14-NOIP2018)
  • 【Leetcode】101. 对称二叉树
  • python3.6+scrapy+mysql 爬虫实战
  • Android组件 - 收藏集 - 掘金
  • AngularJS指令开发（1）——参数详解
  • Bootstrap JS插件Alert源码分析
  • IP路由与转发
  • JavaWeb(学习笔记二)
  • Java多线程（4）：使用线程池执行定时任务
  • Redis字符串类型内部编码剖析
  • SegmentFault 社区上线小程序开发频道，助力小程序开发者生态
  • SOFAMosn配置模型
  • 反思总结然后整装待发
  • 项目管理碎碎念系列之一：干系人管理
  • 原生 js 实现移动端 Touch 滑动反弹
  • 在Mac OS X上安装 Ruby运行环境
  • Prometheus VS InfluxDB
  • shell使用lftp连接ftp和sftp,并可以指定私钥
  • 智能情侣枕Pillow Talk，倾听彼此的心跳
  • 昨天1024程序员节，我故意写了个死循环~
  • ​软考-高级-信息系统项目管理师教程 第四版【第19章-配置与变更管理-思维导图】​
  • #pragma multi_compile #pragma shader_feature
  • %@ page import=%的用法
  • （003）SlickEdit Unity的补全
  • （1）虚拟机的安装与使用,linux系统安装
  • （6）添加vue-cookie
  • （solr系列:一）使用tomcat部署solr服务
  • （已更新）关于Visual Studio 2019安装时VS installer无法下载文件，进度条为0，显示网络有问题的解决办法
  • (最完美)小米手机6X的Usb调试模式在哪里打开的流程
  • .Family_物联网
  • .Net 8.0 新的变化
  • .NET BackgroundWorker
  • .NET DevOps 接入指南 | 1. GitLab 安装
  • .NET Framework 和 .NET Core 在默认情况下垃圾回收（GC）机制的不同（局部变量部分）
  • .net 提取注释生成API文档 帮助文档
  • .NET4.0并行计算技术基础（1）
  • .NET6使用MiniExcel根据数据源横向导出头部标题及数据
  • .NET企业级应用架构设计系列之开场白
  • .NET业务框架的构建
  • .net专家（高海东的专栏）
  • /dev/sda2 is mounted; will not make a filesystem here!