概念
LINQ to Entities |
一种 LINQ 技术,使开发人员可以使用 LINQ 表达式和 LINQ 标准查询运算符,针对实体数据模型 (EDM) 对象上下文创建灵活的强类型化查询。 |
ESQL |
Entity SQL 一种与 SQL 类似的、独立于存储的语言。该语言直接操作概念实体架构,并支持实体数据模型功能,如继承和关系。 |
EF |
实体框架ADO.NET Entity Framework 一套支持面向数据的软件应用程序开发的技术,可让开发人员使用映射到数据源中的逻辑架构的概念模型。 |
EDM |
实体数据模型(Entity Data Model) 一个数据模型,用于将应用程序数据定义为公共语言运行时类型和存储结构可以映射到的实体和关系集。 |
Entity |
实体 ADO.NET Entity Framework 应用程序域中的一个概念,数据类型在该域中定义 |
csdl |
概念架构定义语言 Conceptual schema definition language 一种基于 XML 的语言,可用于定义概念模型的实体类型、关联、实体容器、实体集和关联集 |
ssdl |
存储架构定义语言 Store schema definition language 一种基于 XML 的语言,用于定义存储模型的实体类型、关联、实体容器、实体集和关联集,经常对应于数据库架构。 |
msl |
映射规范语言 Mapping specification language 一种基于 XML 的语言,可用于将概念模型中定义的项映射到存储模型中的项 |
ADO.NET Entity Framework
架构
ADO.NET Entity Framework分Storage Provider ,Mapping Layer ,Object Services,LINQ to Entities 四层。
Storage Provider |
负责直接和数据源通讯,支持的数据库Sql Server |
Mapping Layer |
数据库概念层和逻辑层的映射。 通过EDM模型和mapping provider,应用程序将构建在更高层次的EDM模型抽象层次上。同时,在应用程序中将不再使用本地数据库的查询语言比如(T-sql),取而代之的将是Entity SQL。 |
Object Services |
Object Services 的目标是消除数据和应用程序代码风格的不匹配 ADO.NET允许将查询结果呈现为行和列记录,同时也可以呈现为.NET对象。 该层还包括了更多被O/R mapping框架支持的高级的服务,比如身份认证,跟踪对象状态变化,并行性检查以及处理更新。 |
LINQ to Entities |
将Entity Framework与LINQ项目集成,以提供面向对象编程语言适合自己特点的查询功能。 LINQ to Entities这一层依赖于object services和mapping layer这两层。 |
说明
ADO.NET Entity Framework 的数据访问方式与ADO.NET 有类似之处
ADO.NET |
ADO.NET Entity Framework |
SqlConnection |
EntityConnection |
SqlCommand |
EntityCommand |
SqlDataReader. |
EntityDataReader |
SqlDataAdapter |
ObjectContext |
DataSet |
ObjectQuery<T> |
Entity |
|
csdl,ssdl,msl |
注意:ADO.NET与 ADO.NET Entity Framework是不同的技术,这个对比只是一个帮助理解的比效
EntityConnection
EntityCommand
通过EntityDataReader 方式的数据访问
string econString = @" metadata=res://*/myModel.csdl |res://*/myModel.ssdl |res://*/myModel.msl ; provider=System.Data.SqlClient; provider connection string="" Data Source=.; Initial Catalog=LingTestDB; Integrated Security=True; MultipleActiveResultSets=True; "" "; EntityConnection econ = new EntityConnection(); econ.ConnectionString = econString; EntityCommand ecmd = new EntityCommand(); ecmd.CommandText = "select it.ItemID,it.ItemMatter from myContext.DBItem as it";//esql //或 // cmd.CommandText = "select value it from myContext.DBItem as it"; //esql // to SQL : "select * from DBItem" econ.Open(); EntityDataReader eReader = ecmd.ExecuteReader(CommandBehavior.SequentialAccess); while (eReader.Read()) { Console.WriteLine("{0},{1}", eReader[0].ToString(), eReader[1].ToString()); } |
通过ObjectContext返回ObjectQuery<T> 方式的数据访问
ObjectContext提供了管理数据的功能
string econString = @" metadata=res://*/myModel.csdl |res://*/myModel.ssdl |res://*/myModel.msl ; provider=System.Data.SqlClient; provider connection string="" Data Source=.; Initial Catalog=LingTestDB; Integrated Security=True; MultipleActiveResultSets=True; "" "; EntityConnection econ = new EntityConnection(econString); context.DefaultContainerName = "myContext"; { System.Console.WriteLine("{0},{1}", r.ItemID, r.ItemMatter); } |
string econString = @" metadata=res://*/myModel.csdl |res://*/myModel.ssdl |res://*/myModel.msl ; provider=System.Data.SqlClient; provider connection string="" Data Source=.; Initial Catalog=LingTestDB; Integrated Security=True; MultipleActiveResultSets=True; "" "; EntityConnection econ = new EntityConnection(econString); ObjectQuery<DBItem> queryTab = context.CreateQuery<DBItem>("select value it from myContext.DBItem as it where it.ItemID='a'"); { System.Console.WriteLine("{0},{1}",r.ItemID,r.ItemMatter); } |
模型结构
[概念模型]中要有[实体键], [实体键]要与表中的 [主键] 对应,也就是说表必须要有主键.
表中的[唯一键]不会在[概念模型]中体现
在[概念模型]中默认是不允许修改[实体键]的值的
联合主健可以正常映射
如果为属性赋值超过字段长度保存时,会向数据库提交,数据库会返回错误
联合主健的主外关系可以正常映射
只有基于主健的主外关系可以在模型向导中自动建立
Conceptual Model |
概念模型 用于描述实体(Entity)类型及其关系 |
|
Storage Model |
存储模型 用于描述数据库实际存储架构 |
|
Mapping Specification |
映射规范 将概念模型和存储模型连接起来,以便进行操作转换 |
|
Entity Class |
实体类 用于描述实体的属性,每一个实体类都会定义一个属性或多个属性为一个键属性(Key Properties),用于唯一标识一个实体 实体类型可以通过继承关系加以扩展 |
|
Entity Set |
实体集 实体(Entity)存在于实体集(Entity Set)中,就像表格中的行存在于表格中的一样 |
|
Entity Containe |
实体容器, 实体集定义在实体容器(Entity Container)中 |
|
关联 |
关联 定义了实体之间的关系,可以直接通过关联,来访问相关联的对象,关联分为一对一、一对多、多对多 关联通过Association Type来定义,过实体类中的Navigation属性就可以访问与实体相关联的实体 |
模型关系说明
模型设计器结构说明
EDM
EF 没有采取 LINQ to SQL 基于Attribute映射的做法。
为了适应变化和提供更多的数据库类型扩展,EF 提供了专门的定义语言来完成模型设置
Conceptual schema definition language (.csdl) |
|
Store schema definition language (.ssdl) |
|
Mapping specification language (.msl) |
默认,Model设计器将(.csdl)(.ssdl)(.msl)存放在一个名为(.edmx)的XML 格式定义文件,并会根据设计自动生成对应的Context与实体类
Model设计器
数据库中的表 |
|
模型 |
|
模型生成选项 |
|
edmx文件
<?xml version="1.0" encoding="utf-8"?> <edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx"> <!-- EF Runtime content --> |
|
<edmx:Runtime> |
|
存储模型 |
<!-- SSDL content --> <edmx:StorageModels> <Schema Namespace="myModel.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/2006/04/edm/ssdl"> <EntityContainer Name="myModelStoreContainer"> <EntitySet Name="myTab" EntityType="myModel.Store.myTab" store:Type="Tables"Schema="dbo" /> </EntityContainer> <EntityType Name="myTab"> <Key> <PropertyRef Name="a" /> </Key> <Property Name="a" Type="nchar" Nullable="false" MaxLength="10" /> <Property Name="b" Type="nchar" Nullable="false" MaxLength="10" /> <Property Name="c" Type="nchar" MaxLength="10" /> <Property Name="d" Type="nchar" MaxLength="10" /> </EntityType> </Schema> </edmx:StorageModels> |
概念模型 |
<!-- CSDL content --> <edmx:ConceptualModels> <Schema Namespace="myModel" Alias="Self"xmlns="http://schemas.microsoft.com/ado/2006/04/edm"> <EntityContainer Name="mySets"> <EntitySet Name="myTab" EntityType="myModel.myTab" /> </EntityContainer> <EntityType Name="myTab"> <Key> <PropertyRef Name="aa" /> </Key> <Property Name="aa" Type="String" Nullable="false" MaxLength="10" Unicode="true"FixedLength="true" /> <Property Name="bb" Type="String" Nullable="false" MaxLength="10" Unicode="true"FixedLength="true" /> <Property Name="cc" Type="String" MaxLength="10" Unicode="true" FixedLength="true" /> <Property Name="dd" Type="String" MaxLength="10" Unicode="true" FixedLength="true" /> </EntityType> </Schema> </edmx:ConceptualModels> |
映射 |
<!-- C-S mapping content --> <edmx:Mappings> <Mapping Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS"> <EntityContainerMapping StorageEntityContainer="myModelStoreContainer"CdmEntityContainer="mySets"> <EntitySetMapping Name="myTab"> <EntityTypeMapping TypeName="IsTypeOf(myModel.myTab)"> <MappingFragment StoreEntitySet="myTab"> <ScalarProperty Name="aa" ColumnName="a" /> <ScalarProperty Name="bb" ColumnName="b" /> <ScalarProperty Name="cc" ColumnName="d" /> <ScalarProperty Name="dd" ColumnName="c" /> </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/2007/06/edmx"> <edmx:Connection> <DesignerInfoPropertySet> <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" /> </DesignerInfoPropertySet> </edmx:Connection> <edmx:Options> <DesignerInfoPropertySet> <DesignerProperty Name="ValidateOnBuild" Value="true" /> </DesignerInfoPropertySet> </edmx:Options> <!-- Diagram content (shape and connector positions) --> <edmx:Diagrams> <Diagram Name="myModel"> <EntityTypeShape EntityType="myModel.myTab" Width="1.5" PointX="0.75" PointY="0.75"Height="1.7" IsExpanded="true" /> </Diagram> </edmx:Diagrams> </edmx:Designer> |
</edmx:Edmx> |
Context
public class myContext :ObjectContext { public myContext(EntityConnection connection) : base(connection, "mySets") { } public ObjectQuery<myTab> myTab { get { if ((this._myTab == null)) { this._myTab = base.CreateQuery<myTab>("[myTab]"); } return this._myTab; } } private ObjectQuery<myTab> _myTab; public void AddTomyTab(myTab myTab) { base.AddObject("myTab", myTab); } } |
实体类
[EdmEntityType(NamespaceName = "myModel", Name = "myTab")] [DataContract(IsReference = true)] [Serializable()] public class myTab :EntityObject { public static myTab CreatemyTab(string aa, string bb) { myTab myTab = new myTab(); myTab.aa = aa; myTab.bb = bb; return myTab; } private string _aa; [EdmScalarProperty(EntityKeyProperty = true, IsNullable = false)] [DataMember()] public string aa { get { return this._aa; } set { this.ReportPropertyChanging("aa"); this._aa = StructuralObject.SetValidValue(value, false); this.ReportPropertyChanged("aa"); } } private string _bb; [EdmScalarPropertyAttribute(IsNullable = false)] [DataMemberAttribute()] public string bb { get { return this._bb; } set { this.ReportPropertyChanging("bb"); this._bb = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(value,false); this.ReportPropertyChanged("bb"); } } // private string _cc; [EdmScalarProperty()] [DataMember()] public string cc { get { return this._cc; } set { this.ReportPropertyChanging("cc"); this._cc =StructuralObject.SetValidValue(value, true); this.ReportPropertyChanged("cc"); } } // private string _dd; [EdmScalarProperty()] [DataMember()] public string dd { get { return this._dd; } set { this.ReportPropertyChanging("dd"); this._dd = StructuralObject.SetValidValue(value, true); this.ReportPropertyChanged("dd"); } } } |
使用
myContext cn; EntityConnection econ = new EntityConnection(); string s = @" metadata=res://*/myModel.csdl |res://*/myModel.ssdl |res://*/myModel.msl ; provider=System.Data.SqlClient; provider connection string="" Data Source=.; Initial Catalog=LingTestDB; Integrated Security=True; MultipleActiveResultSets=True; "" "; econ.ConnectionString = s; cn = new myDate.myContext(econ); this.dataGridView1.DataSource = cn.myTab; |
使用向导创建模型
映射基本规则
1.[概念模型]的[数据类型],[键],[非空]约束要与[存储模型]对应
2.[概念模型]的属性名与[存储模型]的字段名不必一致
3.[存储模型]的字段如果有[键],[非空]约束, [概念模型]必需要有对应的属性与之映射
4. [概念模型]中出现的属性,在[存储模型]中必须要有对应的字段映射
5. [存储模型]的多个字段可以映射到[概念模型]的同一个属性上
加载时, [概念模型]的属性加载[存储模型]的第一个字段值
保存时, [概念模型]的属性会保存到[存储模型]的多个字段上
映射条件
- 一个字段,如果要作为映射条件,就不能映射到属性上
- 加载数据时,只会加载符合条件的数据
- 添加数据时, 映射条件字段不可见, 射条件字段的字直接使用设计时的设定值
继承
1. 要实现继承,要使用映射条件进行筛选
2. [概念模型]与 [子概念模型] 所有的筛选条件不能有相的
3. [子概念模型] 自动得到 [概念模型] 的属性
4. [子概念模型] 中可以没有属性
5. 所的[子概念模型] 的筛选条件将以[Or]的方式影响[概念模型]的筛选,也就是说[概念模型]的记录将包括[子概念模型] 记录
6. [概念模型]与 [子概念模型] 共用同一个 [实体集], [实体集]的数据类型为 ObjectQuery<概念模型>
7. 可以使用类型转换的方式从[实体集]得[子概念模型]的数据
myContext context = new myContext(); ObjectQuery<myTab> a = context.myTabSets; ObjectQuery<myTabEx1> b = context.myTabSets.OfType<myTabEx1>(); ObjectQuery<myTabEx2> c = context.myTabSets.OfType<myTabEx2>(); |
多表联合
1. 不要求在数据库中有关系,最好是主键对主键
2. 使用对联的方式,只筛选出有对应键的记录
3. 插入时,会在两个表中同时插入键相同的记录
4. 删除时,会同时在两个表中删除键相同的记录
关联
[(1)—(*)] 关联
1. [(1)—(*)]为主外关系,主键表为(1),外键表为(*)
2. 外键字段不能出现在[概念模型]中
3. 可以在向导中直接跟据数据库中的关系直接生成,也可以在设计[概念模型]时任意添加
4. 关联要绑定到外键表上
5. 要删除主键时,要保证内存中没有对应的外键数据
6. 插入外键数据,可以插入到主键的外键集合,也可创建带主键信息的外键对象后直接插入到Context中
表
设置
操作数据
从主键表访问数据 |
myContext context = new myContext(); ObjectQuery<tab1> tab1_List = context.tab1; foreach (tab1 tab1_Entity in tab1_List) { Console.WriteLine("{0},{1}", tab1_Entity.a, tab1_Entity.b); EntityCollection<tab2> tab2_List = tab1_Entity.Nvtab2; tab2_List.Load(); //加载外键数据 foreach (tab2 tab2_Entity in tab2_List) { Console.WriteLine("{0},{1},{2}", tab2_Entity.ID,tab2_Entity.x,tab2_Entity.y); } } |
lzm ,456 3 ,5 ,6 4 ,7 ,8 wxd ,123 1 ,1 ,2 2 ,3 ,4 |
|
从外键表访问数据 |
myContext context = new myContext(); ObjectQuery<tab2> tab2_List = context.tab2; foreach (tab2 tab2_Entity in tab2_List) { Console.WriteLine("{0},{1},{2}", tab2_Entity.ID, tab2_Entity.x, tab2_Entity.y); tab2_Entity.NVtab1Reference.Load(); //加载主键数据 tab1 tab1 = tab2_Entity.NVtab1; Console.WriteLine("{0},{1}", tab1.a, tab1.b); } |
1 ,1 ,2 wxd ,123 2 ,3 ,4 wxd ,123 3 ,5 ,6 lzm ,456 4 ,7 ,8 lzm ,456 |
|
删除主键 |
myContext context = new myContext(); var v = context.tab1.First(p => p.a == "wxd"); // 要删除主键时,要保证内存中对应的外键数据为空 //v.Nvtab2.Load(); //if (v.Nvtab2.Count != 0) //{ // List<tab2> list = new List<tab2>(); // foreach (var w in v.Nvtab2) // { // list.Add(w); // } // foreach (var ww in list) // { // context.DeleteObject(ww); // } //} context.DeleteObject(v); context.SaveChanges(); |
直接插入外键数据 |
myContext context = new myContext(); tab2 tab2 = new tab2(); tab2.ID = "101"; tab2.x = "555"; tab2.y = "666"; tab2.Nvtab1 = context.tab1.First(p => p.a == "wxd"); //要有主键信息 context.AddTotab2(tab2); context.SaveChanges(); |
通过主键插入外键数据 |
myContext context = new myContext(); var tab1 = context.tab1.First(p => p.a == "wxd"); //第1条外键记录 tab2 r1 = new tab2(); r1.ID = "103"; r1.x = "r1"; r1.y = "r1"; tab1.Nvtab2.Add(r1); //第2条外键记录 tab2 r2 = new tab2(); r2.ID = "104"; r2.x = "r1"; r2.y = "r1"; tab1.Nvtab2.Add(r2); context.SaveChanges(); |
[(1)—(1)]关联
1. [(1)—(1)]为主主关系,既两个表的主键关联
2. 两个表的地位是平等的
3. 主键字段不能删除
4. 可以在向导中直接跟据数据库中的关系直接生成,也可以在设计[概念模型]时任意添加
5. 关联可绑定到任意一个表上
6. 删除时,要先将关联数据删除
7. 插入时,要有与之关联的数据
表
设置
操作数据
读取 |
myContext context = new myContext(); ObjectQuery<tab1> tab1_List = context.tab1; foreach (tab1 tab1_Entity in tab1_List) { Console.WriteLine("{0},{1}", tab1_Entity.a, tab1_Entity.b); tab1_Entity.NVtab3Reference.Load(); //加载关联数据 tab3 tab3 = tab1_Entity.NVtab3; Console.WriteLine("{0},{1},{2}", tab3.a, tab3.m, tab3.n); } |
lzm ,456 lzm ,mmmm ,nnnn wxd ,123 wxd ,nnn ,nnn |
|
myContext context = new myContext(); ObjectQuery<tab3> tab3_List = context.tab3; foreach (tab3 tab3_Entity in tab3_List) { Console.WriteLine("{0},{1},{2}", tab3_Entity.a, tab3_Entity.m,tab3_Entity.n ); tab3_Entity.NVtab1Reference.Load(); //加载关联数据 tab1 tab1 = tab3_Entity.NVtab1; Console.WriteLine("{0},{1}", tab1.a, tab1.b ); } |
|
lzm ,mmmm ,nnnn lzm ,456 wxd ,nnn ,nnn wxd ,123 |
|
删除 |
myContext context = new myContext(); var tab3 = context.tab3.First(p => p.a == "wxd"); //-要按顺序 tab3.NVtab1Reference.Load(); // 加载关联数据 context.DeleteObject(tab3.NVtab1); //删除关联数据 context.DeleteObject(tab3); // 删除 // context.SaveChanges(); |
插入 |
myContext context = new myContext(); tab1 tab1 = new tab1(); tab1.a = "wxwinter"; tab1.b = "789"; //关联 tab1.NVtab3 = new tab3(); tab1.NVtab3.a = "wxwinter"; tab1.NVtab3.m = "mm"; tab1.NVtab3.n = "mm"; context.AddTotab1(tab1); context.SaveChanges(); |
[(1)—(0..1)]关联
设计与[(1)—(0..1)]相同.
1. 插入(1)时,可以不插入(0..1)
2. 删除(0..1) 时,可以不删除(1)
3. 插入(0..1)时,需要插入(1)
4. 删除(1) 时, 需要删除(0..1)
[(*)—(*)]关联
表
显示中转方式
隐示中转方式
1. 要实现这个关联,要将关联绑定到 [中转表(安排表)]上
2. 所用的[中转表(安排表)]不能以[概念模型]的方式出现
3. 两个表的地位是平等的
4. 添加一端的(*)时,不受另一端(*)约束
5. 可以在添加一端的(*)时,通过[关联集合]将数据也添加到[关联表]与[中转表]中
6. 如果加载关联数据,[中转表]中的关联数据也会被删除.
如果不加载关联数据,将不会删除[中转表]中的关联数据
操作数据
得到数据 |
myContext context = new myContext(); ObjectQuery<部门表> bList = context.部门表; foreach (部门表 b in bList) { Console.WriteLine("{0},{1}", b.部门, b.部门说明); b.场地表.Load(); EntityCollection<场地表> cList = b.场地表; foreach (场地表 c in cList) { Console.WriteLine("{0},{1}", c.场地, c.场地说明); } } |
丙 ,1 a ,11 b ,22 c ,33 甲 ,2 a ,11 乙 ,3 a ,11 b ,22 |
|
myContext context = new myContext(); ObjectQuery<场地表> cList = context.场地表; foreach (场地表 c in cList) { Console.WriteLine("{0},{1}", c.场地, c.场地说明); c.部门表.Load(); EntityCollection<部门表> bList = c.部门表; foreach (部门表 b in bList) { Console.WriteLine("{0},{1}", b.部门, b.部门说明); } } |
|
a ,11 丙 ,1 甲 ,2 乙 ,3 b ,22 丙 ,1 乙 ,3 c ,33 丙 ,1 |
|
删除数据 |
myContext context = new myContext(); 场地表 c = context.场地表.First(p => p.场地 == "a"); //1.如果加载关联数据,[中转表]中的关联数据也会被删除, //2.如果不加载关联数据,将不会删除[中转表]中的关联数据 //c.部门表.Load(); // <-------[是/否] context.DeleteObject(c); context.SaveChanges(); |
添加数据 |
myContext context = new myContext(); 场地表 c = new 场地表(); c.场地 = "d"; c.场地说明 = "44"; context.AddTo场地表(c); context.SaveChanges(); |
同时添加数据到 [源表], [关联表], [中转表] |
//可以将数据同时添加[源表],[关联表],[中转表] myContext context = new myContext(); 场地表 c = new 场地表(); c.场地 = "e"; c.场地说明 = "55"; c.部门表.Add(new 部门表() { 部门 = "vv", 部门说明 = "vv" }); context.AddTo场地表(c); context.SaveChanges(); |
映射存储过程与函数
返回表型存储过程
存储过程
查询 PRSelectDBItem |
CREATE PROCEDURE PRSelectDBItem @v nvarchar(50) as select * from DBItem where ItemID=@v |
使用向导生成映射
myContext context = new myContext(); ObjectResult<DBItem> dbitemList= context.PRSelectDBITem("a"); foreach (var r in dbitemList) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemMatter); } |
代码实现
public class Context :ObjectContext { public Context() : base("name=myContext", "myContext") { } public ObjectResult<DBItem> PRSelectDBITem(string v) { return base.ExecuteFunction<DBItem>("PRSelectDBITem", new ObjectParameter("v", v)); } } |
Context context = new Context(); ObjectResult<DBItem> dbitemList = context.PRSelectDBITem("a"); foreach (var r in dbitemList) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemMatter); } |
命令型存储过程
存储过程
插入 PRinsertDBItem |
CREATE PROCEDURE PRinsertDBItem @ItemID nvarchar(50), @ItemMatter nvarchar(50) as insert into DBItem (ItemID,ItemMatter) values (@ItemID,@ItemMatter) |
使用向导生成映射
代码实现
string econString = @" metadata=res://*/myModel.csdl |res://*/myModel.ssdl |res://*/myModel.msl ; provider=System.Data.SqlClient; provider connection string="" Data Source=.; Initial Catalog=LingTestDB; Integrated Security=True; MultipleActiveResultSets=True; "" "; EntityConnection econ = new EntityConnection(); econ.ConnectionString = econString; EntityCommand ecmd = new EntityCommand(); ecmd.CommandType = CommandType.Text; ecmd.Connection = econ; ecmd.CommandText = "myContext.PRinsertDBItem"; ecmd.CommandType = CommandType.StoredProcedure; EntityParameter p1 = new EntityParameter("ItemID", System.Data.DbType.String); p1.Value = "aaa"; EntityParameter p2 = new EntityParameter("ItemMatter", System.Data.DbType.String); p2.Value = "bbb"; ecmd.Parameters.Add(p1); ecmd.Parameters.Add(p2); econ.Open(); ecmd.ExecuteNonQuery(); |
参数返回值型存储过程
存储过程
算加法 PRadd |
CREATE PROCEDURE PRadd @x int = 0, @y int = 0, @s int = 0 output as set @s= @x * @y |
使用向导生成映射
代码实现
string econString = @" metadata=res://*/myModel.csdl |res://*/myModel.ssdl |res://*/myModel.msl ; provider=System.Data.SqlClient; provider connection string="" Data Source=.; Initial Catalog=LingTestDB; Integrated Security=True; MultipleActiveResultSets=True; "" "; EntityConnection econ = new EntityConnection(); econ.ConnectionString = econString; EntityCommand ecmd = new EntityCommand(); ecmd.CommandType = CommandType.Text; ecmd.Connection = econ; ecmd.CommandText = "myContext.PRadd"; ecmd.CommandType = CommandType.StoredProcedure; EntityParameter p1 = new EntityParameter("x", System.Data.DbType.Int32); p1.Value = 123; EntityParameter p2 = new EntityParameter("y", System.Data.DbType.Int32); p2.Value = 456; EntityParameter rt = new EntityParameter("s", System.Data.DbType.Int32, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Current, 0); ecmd.Parameters.Add(p1); ecmd.Parameters.Add(p2); ecmd.Parameters.Add(rt); econ.Open(); ecmd.ExecuteNonQuery(); Console.WriteLine(rt.Value); //579 |
说明
ObjectContext提供了管理数据的功能
Context操作数据
AddObject 添加实体
将实体添加到集合中, 创建实体时,状态为EntityState.Detached 当调用AddObject将实体添加到Context时,状态为EntityState.Added |
myContext context = new myContext(); myTab r = new myTab(); r.ID = 10; r.a = "wxwinter"; Console.WriteLine(r.EntityState); //print:Detached context.AddTomyTab(r); Console.WriteLine(r.EntityState); //print:Added context.SaveChanges(); |
myContext context = new myContext(); myTab newrow = new myTab() { a = "wxd", b = "lzm", c = "wxwinter" }; context.AddObject("myTab",newrow); context.SaveChanges(); |
DeleteObject 删除实体
将集合中的实体添标记为删除 当调用Context.DeleteObject时,并不是将实体移除集合,而是将实体添标记为EntityState.Deleted ,在下次调用SaveChanges()方法时跟新数据库 |
myContext context = new myContext(); myTab r = context.myTab.First(p=>p.ID==1); Console.WriteLine(r.EntityState); //print:Unchanged context.DeleteObject(r); Console.WriteLine(r.EntityState); //print:Deleted context.SaveChanges(); |
Detach 分离实体
将实体从Context中分离,将状态标记为EntityState.Detached 。 |
myContext context = new myContext(); myTab r = myTab.CreatemyTab(22); Console.WriteLine(r.EntityState); //print:Detached context.AddTomyTab(r); Console.WriteLine(r.EntityState); //print:Added context.Detach(r); Console.WriteLine(r.EntityState); //print: Detached |
修改实体
可以直接修在实体对象上修改 当修改在Context中的实体时,会将实体的状态标记为EntityState.Modified |
myContext context = new myContext(); myTab r = context.myTab.First(p=>p.ID==1); Console.WriteLine(r.EntityState); //print:Unchanged r.a = "wxwinter"; Console.WriteLine(r.EntityState); //print:Modified context.SaveChanges(); |
ApplyPropertyChanges 修改实体
使用ApplyPropertyChanges,可以使用不在集合中的实体覆盖到集合中主键对应用实体上,如果内存中没有主键对应的记录,会报错:“ObjectStateManager 不包含具有对“XXX”类型的对象的引用的 ObjectStateEntry。”该方法还有一个特点就是,会拿内存中的对象(新对象)和context中的对象(旧对象)对比,自动生成对应字段修改的Update语句,如果内存中的对象与context中的对象完全相等(每个字段的值都相等),将不生成响应的Update |
myContext context = new myContext(); myTab r1 = context.myTab.First(p => p.ID == 1); myTab nr = myTab.CreatemyTab(1); nr.a = "wxwinter"; Console.WriteLine(nr.EntityState); //print:Detached Console.WriteLine(r1.EntityState); //print:Unchanged context.ApplyPropertyChanges("myTab", nr); myTab r2 = context.myTab.First(p => p.ID == 1); Console.WriteLine(nr.EntityState); //print:Detached Console.WriteLine(r2.EntityState); //print:Modified context.SaveChanges(); |
Orders order; |
Attach / AttachTo 附加实体
使用Attach方法可将[外部实体]附加到Context集合中
在使用 服务器/客户端模式,或要将[实体]从Context集合中分离,修改后要用Context更新回数据库时,可用这种方式
Attach与ApplyPropertyChanges有类似之处,都是将Context集合外的[实体]与Context集合内的[实体]同步.
- ApplyPropertyChanges调用时,要求对应的[实体]在内存中,Attach不要求
- ApplyPropertyChanges调用后,集合内的实体状态会标记为EntityState.Modified
Attach调用后不会修改合内的实体状态,如要SaveChanges(),要手动标记EntityState.Modified - ApplyPropertyChanges是用[外部实体]全覆盖Context集合中的[实体],
Attach方式,通过SetModifiedProperty()方法,可在调用SaveChanges()时,只修改只定有字段值
myContext context = new myContext(); |
修改前 修改后 |
CreateEntityKey 创建EntityKey
myContext context = new myContext(); myTab nr = myTab.CreatemyTab(1); EntityKey ek= context.CreateEntityKey("myTab", nr); |
EntityKey
EntityContainerName 属性 |
|
EntityKeyValues 集合 |
|
EntitySetName 属性 |
|
IsTemporary 属性 |
|
GetEntitySet(System.Data.Metadata.Edm.MetadataWorkspace) 方法 |
|
OnDeserialized(System.Runtime.Serialization.StreamingContext) 方法 |
|
OnDeserializing(System.Runtime.Serialization.StreamingContext) 方法 |
GetObjectByKey/TryGetObjectByKey 通过EntityKey得到实体
myContext context = new myContext(); myTab nr = myTab.CreatemyTab(1); EntityKey ek= context.CreateEntityKey("myTab", nr); myTab r = context.GetObjectByKey(ek) as myTab ; Console.WriteLine("{0},{1},{2},{3}", r.ID, r.a, r.b, r.c); |
myContext context = new myContext(); myTab nr = myTab.CreatemyTab(1); EntityKey ek= context.CreateEntityKey("myTab", nr); if (context.TryGetObjectByKey(ek,out obj)) { myTab r = obj as myTab; Console.WriteLine("{0},{1},{2},{3}", r.ID, r.a, r.b, r.c); } |
CreateQuery 创建查询
更多见esql |
myContext context = new myContext(); string esql = "SELECT VALUE DBItemList FROM myContext.DBItemList"; // ObjectQuery<DBItemList> query = new ObjectQuery<DBItemList>(esql, context); ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql); foreach (DBItemList r in query) { Console.WriteLine(r.NameID); } |
状态管理
EntityState 状态枚举
EntityState.Added 已通过AddObject方法加到集合中,AcceptChanges 尚未调用。 EntityState.Deleted 已通过 DeleteObject 方法被删除。 EntityState.Detached 已被创建,但不属于任何集合。在以下情况下立即处于此状态:创建之后添加到集合中之前;或从集合中移除之后。 EntityState.Modified 已被修改,AcceptChanges 尚未调用。 EntityState.Unchanged 自上次调用 AcceptChanges 以来尚未更改 |
Context.ObjectStateManager 管理记录的状态
GetObjectStateEntry 根据实体对象或实体主键得到状态实体
ObjectStateManager 不包含具有对“context ”类型的对象的引用的 ObjectStateEntry
也就是该方法无法获取已分离的实体对象状态
ObjectStateEntry = GetObjectStateEntry(实体对像/EntityKey) 得到所指定的[实体对像]或EntityKey的 ObjectStateEntry |
myContext context = new myContext(); myTab r = myTab.CreatemyTab(22); context.AddTomyTab(r); // ObjectStateEntry ose= context.ObjectStateManager.GetObjectStateEntry(r); ObjectStateEntry ose = context.ObjectStateManager.GetObjectStateEntry(r.EntityKey); Console.WriteLine(ose.State); //print:Added |
TryGetObjectStateEntry 根据实体对象或实体主键得到状态实体
bool = TryGetObjectStateEntry(实体对像/EntityKey,out ObjectStateEntry) 得到所指定的[实体对像]或EntityKey的 ObjectStateEntry |
myContext context = new myContext(); myTab r = myTab.CreatemyTab(22); context.AddTomyTab(r); ObjectStateEntry ose; if( context.ObjectStateManager.TryGetObjectStateEntry(r,out ose)) { Console.WriteLine(ose.State); //print:Added } |
GetObjectStateEntries 根据状态类型得到状态实体集合
IEnumerable<ObjectStateEntry> = GetObjectStateEntries(EntityState枚举) 返回IEnumerable<ObjectStateEntry>,得到EntityState枚举所指定的某种状态的列表 |
myContext context = new myContext(); myTab r = myTab.CreatemyTab(22); context.AddTomyTab(r); IEnumerable<ObjectStateEntry> oseList = context.ObjectStateManager.GetObjectStateEntries(EntityState.Added); foreach (ObjectStateEntry v in oseList) { Console.WriteLine("{0},{1},{2}", v.State, v.CurrentValues["ID"], v.EntitySet.Name); } //print:Added,22,myTab |
ObjectStateManagerChanged 事件
CollectionChangeEventHandler(object sender, CollectionChangeEventArgs e) e.Action : 集合操作行为 System.ComponentModel.CollectionChangeAction.Add System.ComponentModel.CollectionChangeAction.Refresh System.ComponentModel.CollectionChangeAction.Remove e.Element : 操作的实体对象 |
void ObjectStateManager_ObjectStateManagerChanged(object sender, CollectionChangeEventArgse) { Console.WriteLine(e.Action); Console.WriteLine("{0}",v.ID); } //=================================== myContext context = new myContext(); context.ObjectStateManager.ObjectStateManagerChanged+=newCollectionChangeEventHandler(ObjectStateManager_ObjectStateManagerChanged); myTab r = myTab.CreatemyTab(22); context.AddTomyTab(r); /* *print: Add 22 */ |
ObjectStateEntry 对象
基本属性
IsRelationship 属性 |
|
Entity 属性 |
|
EntityKey 属性 |
|
EntitySet 属性 |
State 状态属性
EntityState 枚举 |
myContext context = new myContext(); myTab r = myTab.CreatemyTab(22); context.AddTomyTab(r); ObjectStateEntry ose= context.ObjectStateManager.GetObjectStateEntry(r); Console.WriteLine(ose.State); //print:Added |
CurrentValues 当前值
处于 deleted 或 detached 状态的对象没有当前值。 |
myContext context = new myContext(); myTab r = new myTab() { ID = 22, a = "wxwinter" }; context.AddTomyTab(r); ObjectStateEntry ose= context.ObjectStateManager.GetObjectStateEntry(r); Console.WriteLine("{0},{1}",ose.CurrentValues["ID"],ose.CurrentValues["a"]); //print: 22,wxwinter |
OriginalValues 原始值
处于 added 或 detached 状态的对象没有原始值 |
myContext context = new myContext(); myTab r = context.myTab.First(p => p.ID == 1); r.a = "wxwinter"; ObjectStateEntry ose= context.ObjectStateManager.GetObjectStateEntry(r); Console.WriteLine(ose.State); Console.WriteLine("CurrentValues :{0},{1}", ose.CurrentValues["ID"], ose.CurrentValues["a"]); Console.WriteLine("OriginalValues:{0},{1}", ose.OriginalValues["ID"], ose.OriginalValues["a"]); /* * print: Modified CurrentValues :1,wxwinter OriginalValues:1,aa */ |
GetModifiedProperties 得到被修改的属性
返回IEnumerable<string> 得到被修改的属性集合 |
myContext context = new myContext(); myTab r = context.myTab.First(p => p.ID == 1); r.a = "wxwinter"; r.b = "wxd"; ObjectStateEntry ose= context.ObjectStateManager.GetObjectStateEntry(r); IEnumerable<string> list = ose.GetModifiedProperties(); foreach (string pr in list) { Console.WriteLine(pr); } /* * print: a b */ |
SetModified,SetModifiedProperty 标记为修改
SetModified() 方法将记录标记为 EntityState.Modified 只是这样,调用Context.SaveChanges方法是无法保存修改到数据库中的,Context.SaveChanges方法要查找被修改过的属性, 可用SetModifiedProperty方法标记被修改过的属性 |
myContext context = new myContext(); myTab r = context.myTab.First(p => p.ID == 1); r.a = "wxwinter"; ObjectStateEntry ose= context.ObjectStateManager.GetObjectStateEntry(r); Console.WriteLine(ose.State); Console.WriteLine("CurrentValues :{0},{1}", ose.CurrentValues["ID"], ose.CurrentValues["a"]); Console.WriteLine("OriginalValues:{0},{1}", ose.OriginalValues["ID"], ose.OriginalValues["a"]); /* * print: Unchanged CurrentValues :1,wxwinter OriginalValues:1,wxwinter */ ose.SetModified(); ose.SetModifiedProperty("a"); Console.WriteLine(ose.State); Console.WriteLine("CurrentValues :{0},{1}", ose.CurrentValues["ID"], ose.CurrentValues["a"]); Console.WriteLine("OriginalValues:{0},{1}", ose.OriginalValues["ID"], ose.OriginalValues["a"]); /* * print: Modified CurrentValues :1,wxwinter OriginalValues:1,wxwinter */ context.SaveChanges(); |
Delete 标记为删除
标记为EntityState.Deleted |
myContext context = new myContext(); myTab r = context.myTab.First(p => p.ID == 1); ObjectStateEntry ose= context.ObjectStateManager.GetObjectStateEntry(r); ose.Delete(); Console.WriteLine(ose.State); //print: Detached Console.WriteLine("OriginalValues:{0},{1}", ose.OriginalValues["ID"], ose.OriginalValues["a"]); //print:OriginalValues:1,wxwinter |
用 context.DeleteObject方法的效果与上例一样 myContext context = new myContext(); myTab r = context.myTab.First(p => p.ID == 1); //print:OriginalValues:1,wxwinter |
AcceptChanges 方法
将记录的状态置为EntityState.Unchanged 用[CurrentValues 当前值]替换[OriginalValues 原始值], 使用[ Context.AcceptAllChanges 方法]也有同样效果 注意:状态为[EntityState.Deleted ]的记录,会被[Detach] |
myContext context = new myContext(); myTab r = context.myTab.First(p => p.ID == 1); context.AcceptAllChanges(); ose.AcceptChanges(); Console.WriteLine(ose.State); Console.WriteLine("CurrentValues :{0},{1}", ose.CurrentValues["ID"], ose.CurrentValues["a"]); Console.WriteLine("OriginalValues:{0},{1}", ose.OriginalValues["ID"], ose.OriginalValues["a"]); /* * print: Unchanged CurrentValues :1,wxwinter OriginalValues:1,wxwinter */ |
当调用AcceptChanges时,如果对像处于[EntityState.Deleted ],会将对象移除集合,这时对像的状态为[EntityState.Detached ] myContext context = new myContext(); myTab r = context.myTab.First(p => p.ID == 1); ObjectStateEntry ose= context.ObjectStateManager.GetObjectStateEntry(r); ose.Delete(); ose.AcceptChanges(); Console.WriteLine(ose.State); //print: Detached |
保存修改到数据库
Context.SaveChanges 方法
如果集合中有状态为EntityState.Added的记录,用[CurrentValues 当前值]添加到数据库中
如果集合中有状态为EntityState.Deleted的记录,从数据库是删除与之对应的数据库记录
如果集合中有状态为EntityState.Modified的记录,用[OriginalValues 原始值]与对应的数据库记录比效,查看并发, 用[CurrentValues 当前值]更新与之对应的数据库记录
SaveChanges(true) |
将数据保存到数据库后 将所有记录状态标记为EntityState.Unchanged ,(调用Context.AcceptAllChanges ) |
myContext context = new myContext(); myTab r = context.myTab.First(p => p.ID == 1); r.a = "wxwinter"; ObjectStateEntry ose = context.ObjectStateManager.GetObjectStateEntry(r); context.SaveChanges(true); Console.WriteLine(ose.State); Console.WriteLine("CurrentValues :{0},{1}", ose.CurrentValues["ID"], ose.CurrentValues["a"]); Console.WriteLine("OriginalValues:{0},{1}", ose.OriginalValues["ID"], ose.OriginalValues["a"]); /* * print: Unchanged CurrentValues :1,wxwinter OriginalValues:1,wxwinter */ |
|
SaveChanges() |
与SaveChanges(true)相同 |
SaveChanges(false) |
将数据保存到数据库, 但并不改变记录状态 |
myContext context = new myContext(); myTab r = context.myTab.First(p => p.ID == 1); r.a = "wxwinter"; ObjectStateEntry ose = context.ObjectStateManager.GetObjectStateEntry(r); context.SaveChanges(false); Console.WriteLine(ose.State); Console.WriteLine("CurrentValues :{0},{1}", ose.CurrentValues["ID"], ose.CurrentValues["a"]); Console.WriteLine("OriginalValues:{0},{1}", ose.OriginalValues["ID"], ose.OriginalValues["a"]); /* * print: Modified CurrentValues :1,wxwinter OriginalValues:1,aa */ |
Context.SavingChanges 事件
myContext context = new myContext(); context.SavingChanges+=new EventHandler(context_SavingChanges); myTab r = context.myTab.First(p => p.ID == 1); |
void context_SavingChanges(object sender, EventArgs e) { myContext context = sender as myContext; Console.WriteLine(context.DefaultContainerName); } |
Context.AcceptAllChanges 方法
将所有记录的状态置为EntityState.Unchanged 用[CurrentValues 当前值]替换[OriginalValues 原始值] 效果与对所在记录的ObjectStateEntry上调用AcceptAllChanges一样 注意:状态为[EntityState.Deleted ]的记录,会被[Detach] |
myContext context = new myContext(); myTab r = context.myTab.First(p => p.ID == 1); r.a = "wxwinter"; context.AcceptAllChanges(); ObjectStateEntry ose= context.ObjectStateManager.GetObjectStateEntry(r); Console.WriteLine(ose.State); Console.WriteLine("CurrentValues :{0},{1}", ose.CurrentValues["ID"], ose.CurrentValues["a"]); Console.WriteLine("OriginalValues:{0},{1}", ose.OriginalValues["ID"], ose.OriginalValues["a"]); /* * print: Unchanged CurrentValues :1,wxwinter OriginalValues:1,wxwinter */ |
连接属性
Context.DefaultContainerName 属性
Context.Connection 属性
Context.CommandTimeout 属性
Context.MetadataWorkspace
数据刷新与并发
EF提供了两种并发冲突处理方式:放任不管方式和开放式并发。默认采用放任不管的方式处理。
如果要使用开放式并发,必须设置相应属性上的[并发模式]值[Fixed]
后修改数据的ObjectContext缓存了旧版本的数据时,当提交修改后系统就会抛出"OptimisticConcurrencyException"(开放式并发异常)。
当程序捕获到异常以后,可以使用ObjectContext的Refresh方法对异常采取处理。
缓存数据不会自动更新
公共 |
myContext context1 = new myContext(); myContext context2 = new myContext(); |
查询 |
foreach (var r in context1.DBItem) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemMatter); } Console.WriteLine("---------------------"); foreach (var r in context2.DBItem) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemMatter); } |
a,this is a b,this is b c,this is c --------------------- a,this is a b,this is b c,this is c |
|
修改 |
DBItem dbitem1 = context1.DBItem.First(p => p.ItemID == "a"); dbitem1.ItemMatter = "hello"; context1.SaveChanges(); |
再查询 |
foreach (var r in context1.DBItem) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemMatter); } Console.WriteLine("---------------------"); foreach (var r in context2.DBItem) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemMatter); } |
a,hello b,this is b c,this is c --------------------- a,this is a b,this is b c,this is c |
[并发模式]值为[Fixed]的并发异常
注意,只有后修改数据的ObjectContext缓存了旧版本的数据时,长会产生异常
DBItem dbitem1 = context1.DBItem.First(p => p.ItemID == "a"); dbitem1.ItemMatter = "hello"; context1.SaveChanges(); DBItem dbitem2 = context2.DBItem.First(p => p.ItemID == "a"); dbitem2.ItemMatter = "wxwinter"; context2.SaveChanges(); |
|
ObjectContext.Refresh()
Refresh的第一个参数RefreshMode枚举,RefreshMode.StoreWins,RefreshMode.ClientWins
StoreWins
StoreWins : Refresh以后,用数据库的值回写,当前的修改值被放弃
公共 |
myContext context1 = new myContext(); myContext context2 = new myContext(); |
查询 |
foreach (var r in context1.DBItem) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemMatter); } Console.WriteLine("---------------------"); foreach (var r in context2.DBItem) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemMatter); } |
a,this is a b,this is b c,this is c --------------------- a,this is a b,this is b c,this is c |
|
修改 |
DBItem dbitem1 = context1.DBItem.First(p => p.ItemID == "a"); dbitem1.ItemMatter = "hello"; context1.SaveChanges(); DBItem dbitem2 = context2.DBItem.First(p => p.ItemID == "a"); dbitem2.ItemMatter = "wxwinter"; try { context2.SaveChanges(); } catch { context2.Refresh( RefreshMode.StoreWins , dbitem2); } |
在System.Data.OptimisticConcurrencyException 中第一次偶然出现的"System.Data.Entity.dll"类型的异常 |
|
再查询 |
foreach (var r in context1.DBItem) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemMatter); } Console.WriteLine("---------------------"); foreach (var r in context2.DBItem) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemMatter); } |
a,hello b,this is b c,this is c --------------------- a,hello b,this is b c,this is c |
ClientWins
StoreWins: Refresh以后,当前的修改值仍存在,只是告诉ObjectContext知到的并发问题了,这时再调用 ObjectContext.SaveChanges()时,ObjectContext就不会报[开放式并发异常]
DBItem dbitem1 = context1.DBItem.First(p => p.ItemID == "a"); dbitem1.ItemMatter = "hello"; context1.SaveChanges(); DBItem dbitem2 = context2.DBItem.First(p => p.ItemID == "a"); dbitem2.ItemMatter = "wxwinter"; try { context2.SaveChanges(); } catch { context2.Refresh(RefreshMode.ClientWins, dbitem2); context2.SaveChanges(); } |
|
也可以先Refresh()再SaveChanges(),而不用异常捕获
DBItem dbitem1 = context1.DBItem.First(p => p.ItemID == "a"); dbitem1.ItemMatter = "hello"; context1.SaveChanges(); DBItem dbitem2 = context2.DBItem.First(p => p.ItemID == "a"); dbitem2.ItemMatter = "wxwinter"; context2.Refresh(RefreshMode.ClientWins, dbitem2); context2.SaveChanges(); |
事务处理
同一SubmitChanges 会做默认的事务处理
下例由于ItemID主键冲突,两条数据都不会被插入
myContext context1 = new myContext(); DBItem item1 = new DBItem(); item1.ItemID = "w"; item1.ItemMatter = "wxwinter"; context1.AddObject("DBItem", item1); DBItem item2 = new DBItem(); item2.ItemID = "w"; item2.ItemMatter = "wxd"; context1.AddObject("DBItem", item2); context1.SaveChanges(); |
不同SubmitChanges 不会做事务处理
下例由于ItemID主键冲突,后一条数据都不会被插入
myContext context1 = new myContext(); DBItem item1 = new DBItem(); item1.ItemID = "w"; item1.ItemMatter = "wxwinter"; context1.AddObject("DBItem", item1); context1.SaveChanges(); myContext context2 = new myContext(); DBItem item2 = new DBItem(); item2.ItemID = "w"; item2.ItemMatter = "wxd"; context2.AddObject("DBItem", item2); context2.SaveChanges(); |
System.Data.Common.DbTransaction
下例由于ItemID主键冲突,两条数据都不会被插入
myContext context1 = new myContext(); DBItem item1 = new DBItem(); item1.ItemID = "w"; item1.ItemMatter = "wxwinter"; context1.AddObject("DBItem", item1); if (context1.Connection.State != ConnectionState.Open) { context1.Connection.Open(); } System.Data.Common.DbTransaction tran = context1.Connection.BeginTransaction(); context1.SaveChanges(); try { DBItem item2 = new DBItem(); item2.ItemID = "w"; item2.ItemMatter = "wxd"; context1.AddObject("DBItem", item2); context1.SaveChanges(); tran.Commit(); } catch { tran.Rollback(); } |
死锁(两个Context使用DbTransaction)
myContext context1 = new myContext(); DBItem item1 = new DBItem(); item1.ItemID = "w"; item1.ItemMatter = "wxwinter"; context1.AddObject("DBItem", item1); if (context1.Connection.State != ConnectionState.Open) { context1.Connection.Open(); } System.Data.Common.DbTransaction tran = context1.Connection.BeginTransaction(); context1.SaveChanges(); try { myContext context2 = new myContext(); DBItem item2 = new DBItem(); item2.ItemID = "w"; item2.ItemMatter = "wxd"; context2.AddObject("DBItem", item2); context2.SaveChanges(); tran.Commit(); } catch { tran.Rollback(); } |
TransactionScope 事务(两个Context)
System.Transactions.TransactionScope
可解决[死锁(两个Context使用DbTransaction)]
下例由于ItemID主键冲突,两条数据都不会被插入
using (System.Transactions.TransactionScope tc = new TransactionScope()) { try { myContext context1 = new myContext(); DBItem item1 = new DBItem(); item1.ItemID = "w"; item1.ItemMatter = "wxwinter"; context1.AddObject("DBItem", item1); context1.SaveChanges(); myContext context2 = new myContext(); DBItem item2 = new DBItem(); item2.ItemID = "w"; item2.ItemMatter = "wxd"; context2.AddObject("DBItem", item2); context2.SaveChanges(); tc.Complete(); } catch { } } |
ObjectQuery<T>数据加载方式
1. ObjectQuery<T> 提供了一个管理[实体对像]集合
2. ObjectQuery<T>继承System.Data.Objects.ObjectQuery, ObjectQuery对ObjectContext进行了封装,
3.可以通过ObjectContext.CreateQuery<T>("esql")的方式创建ObjectQuery<T>
4.可以通过new ObjectQuery<T>(ObjectContext,"esql")的方式创建ObjectQuery<T>,跟据SQL字串的不同,会得到具体的ObjectQuery<值类型>,或ObjectQuery<DbDataRecord>或ObjectQuery<实体>
访问方式
Context.CreateQuery<T>()
string econString = @" metadata=res://*/myModel.csdl |res://*/myModel.ssdl |res://*/myModel.msl ; provider=System.Data.SqlClient; provider connection string="" Data Source=.; Initial Catalog=LingTestDB; Integrated Security=True; MultipleActiveResultSets=True; "" ";
EntityConnection econ = new EntityConnection(econString); foreach (var r in queryTab) { System.Console.WriteLine("{0},{1}", r.ItemID, r.ItemMatter); } |
string econString = @" metadata=res://*/myModel.csdl |res://*/myModel.ssdl |res://*/myModel.msl ;
provider=System.Data.SqlClient;
provider connection string="" Data Source=.; Initial Catalog=LingTestDB; Integrated Security=True; MultipleActiveResultSets=True; "" "; EntityConnection econ = new EntityConnection(econString); ObjectContext context = new ObjectContext(econ); ObjectQuery<DBItem> queryTab = context.CreateQuery<DBItem>("select value it from myContext.DBItem as it where it.ItemID='a'"); foreach (var r in queryTab) { System.Console.WriteLine("{0},{1}",r.ItemID,r.ItemMatter); } |
ObjectQuery<DbDataRecord>
string econString = @" metadata=res://*/myModel.csdl |res://*/myModel.ssdl |res://*/myModel.msl ;
provider=System.Data.SqlClient;
provider connection string="" Data Source=.; Initial Catalog=LingTestDB; Integrated Security=True; MultipleActiveResultSets=True; "" ";
EntityConnection econ = new EntityConnection(econString); ObjectContext context = new ObjectContext(econ); ObjectQuery<DbDataRecord> queryTab = new ObjectQuery<DbDataRecord>("select it.ItemID,it.ItemMatter from myContext.DBItem as it", context) foreach (var r in queryTab) { System.Console.WriteLine("{0},{1}",r[0].ToString(),r[1].ToString()); } |
ObjectQuery<简单类型>
string econString = @" metadata=res://*/myModel.csdl |res://*/myModel.ssdl |res://*/myModel.msl ;
provider=System.Data.SqlClient; provider connection string="" Data Source=.; Initial Catalog=LingTestDB; Integrated Security=True; MultipleActiveResultSets=True; "" ";
EntityConnection econ = new EntityConnection(econString); ObjectContext context = new ObjectContext(econ); ObjectQuery<int> queryTab = new ObjectQuery<int>("select value Count(it.ItemID) from myContext.DBItem as it", context); foreach (var r in queryTab) { System.Console.WriteLine("个数:{0}", r.ToString() ); } |
Execute方法与ObjectResult
Execute方法
string econString = @" metadata=res://*/myModel.csdl |res://*/myModel.ssdl |res://*/myModel.msl ;
provider=System.Data.SqlClient;
provider connection string="" Data Source=.; Initial Catalog=LingTestDB; Integrated Security=True; MultipleActiveResultSets=True; "" ";
EntityConnection econ = new EntityConnection(econString); ObjectContext context = new ObjectContext(econ); context.DefaultContainerName = "myContext";
ObjectQuery<DBItem> queryTab = context.CreateQuery<DBItem>("DBItem"); ObjectResult<DBItem> resultTab = queryTab.Execute(MergeOption.NoTracking);
foreach (var r in resultTab) { System.Console.WriteLine("{0},{1}",r.ItemID,r.ItemMatter); } |
ObjectResult<T> 结果集
ObjectQuery<T>.Execute()方法返回ObjectResult<T>对象
类型转换
OfType(TResultType)
ObjectQuery<TResultType> OfType<TResultType>(); |
myContext context = new myContext(); ObjectQuery<DBItemEx> v = context.DBItem.OfType<DBItemEx>(); |
Linq方法
All
判断集合中是否所有元素都满足某一条件 |
myContext context = new myContext(); bool b= context.DBItemList.All(p => p.ItemValue >= 0); |
SELECT CASE WHEN ( NOT EXISTS (SELECT cast(1 as bit) AS [C1] FROM [dbo].[DBItemList] AS [Extent1] WHERE ( NOT ([Extent1].[ItemValue] >= 1)) OR (CASE WHEN ([Extent1].[ItemValue] >= 0) THEN cast(1 as bit) WHEN ( NOT([Extent1].[ItemValue] >= 0)) THEN cast(0 as bit) END IS NULL) )) THEN cast(1 as bit) WHEN ( EXISTS (SELECT cast(1 as bit) AS [C1] FROM [dbo].[DBItemList] AS [Extent2] WHERE ( NOT ([Extent2].[ItemValue] >= 0)) OR (CASE WHEN ([Extent2].[ItemValue] >= 0) THEN cast(1 as bit) WHEN ( NOT([Extent2].[ItemValue] >= 0)) THEN cast(0 as bit) END IS NULL) )) THEN cast(0 as bit) END AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] |
Any
判断集合中是否有元素满足某一条件 |
myContext context = new myContext(); bool b = context.DBItemList.Any(p => p.ItemValue == 4); |
SELECT CASE WHEN ( EXISTS (SELECT cast(1 as bit) AS [C1] FROM [dbo].[DBItemList] AS [Extent1] WHERE 4 = [Extent1].[ItemValue] )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT cast(1 as bit) AS [C1] FROM [dbo].[DBItemList] AS [Extent2] WHERE 4 = [Extent2].[ItemValue] )) THEN cast(0 as bit) END AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] |
Take
获取集合的前n个元素 |
myContext context = new myContext(); IQueryable<DBItemList> list = context.DBItemList.Take(3); |
SELECT TOP (3) [c].[AutoId] AS [AutoId], [c].[NameID] AS [NameID], [c].[ItemID] AS [ItemID], [c].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [c] |
Skip
跳过集合的前n个元素, Linq To Entity 要求必须先OrderBy |
myContext context = new myContext(); IQueryable<DBItemList> list = context.DBItemList.OrderBy(p=>p.ItemValue).Skip(5); |
SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM ( SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID],[Extent1].[ItemValue] AS [ItemValue], row_number() OVER (ORDER BY [Extent1].[ItemValue] ASC) AS [row_number] FROM [dbo].[DBItemList] AS [Extent1] ) AS [Extent1] WHERE [Extent1].[row_number] > 5 ORDER BY [Extent1].[ItemValue] ASC |
First
集合的第一个元素,集合中没有会报错, |
myContext context = new myContext();
DBItemList f1 = context.DBItemList.First();
DBItemList fi = context.DBItemList.First(p => p.ItemValue == 5);
|
SELECT TOP (1) [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] WHERE 5 = [Extent1].[ItemValue] |
FirstOrDefault
集合中的第一个元素,没有则返回类型默认值,对象型默认值为null |
myContext context = new myContext();
DBItemList fi = context.DBItemList.FirstOrDefault(p => p.ItemValue ==5);
if (fi != null) { Console.WriteLine(fi.ItemValue); } |
Where
用LinqExpressions为条件进行查询 |
myContext context = new myContext(); IQueryable<DBItemList> list= context.DBItemList.Where(p => p.ItemValue == 5); |
SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] WHERE 5 = [Extent1].[ItemValue] |
Distinct
过滤集合中的相同项
ObjectQuery<T> Distinct() |
myContext context = new myContext();
ObjectQuery<DbDataRecord> list = context.DBItemList.Select("it.ItemValue");
ObjectQuery<DbDataRecord> dlist= list.Distinct(); |
SELECT [Distinct1].[C1] AS [C1], [Distinct1].[ItemValue] AS [ItemValue] FROM ( SELECT DISTINCT [Extent1].[ItemValue] AS [ItemValue], 1 AS [C1] FROM [dbo].[DBItemList] AS [Extent1] ) AS [Distinct1] |
OrderBy,OrderByDescending
排序升,排序降 |
myContext context = new myContext(); IQueryable<DBItemList> list = context.DBItemList.OrderBy(p=>p.ItemValue); IQueryable<DBItemList> list = context.DBItemList.OrderByDescending(p=>p.ItemValue); |
SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] ORDER BY [Extent1].[ItemValue] ASC |
SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] ORDER BY [Extent1].[ItemValue] DESC |
ThenBy,ThenByDescending
ThenBy,ThenByDescending 方法必须跟在 OrderBy 方法或对 ThenBy 方法的另一次调用之后 当用OrderBy,OrderByDescending指定主排序字段后,可用ThenBy呀ThenByDescending指定次排序字段 |
myContext context = new myContext(); IQueryable<DBItemList> query = context.DBItemList.OrderBy(p=>p.ItemValue).ThenByDescending(p => p.ItemID); foreach (var r in query) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue);
} |
SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] ORDER BY [Extent1].[ItemValue] ASC, [Extent1].[ItemID] DESC |
Average,Sum
平均值,求和 |
myContext context = new myContext(); double d = context.DBItemList.Average(p => p.ItemValue); double s = context.DBItemList.Sum(p => p.ItemValue); |
SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] LEFT OUTER JOIN (SELECT AVG( CAST( [Extent1].[ItemValue] AS float)) AS [A1] FROM [dbo].[DBItemList] AS [Extent1] ) AS [GroupBy1] ON 1 = 1 |
SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] LEFT OUTER JOIN (SELECT SUM([Extent1].[ItemValue]) AS [A1] FROM [dbo].[DBItemList] AS [Extent1] ) AS [GroupBy1] ON 1 = 1 |
Max,Min
集合最大值,最小值 |
myContext context = new myContext();
var mx = context.DBItemList.Max(p => p.ItemValue); var mi = context.DBItemList.Min(p => p.ItemValue); |
SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] LEFT OUTER JOIN (SELECT MAX([Extent1].[ItemValue]) AS [A1] FROM [dbo].[DBItemList] AS [Extent1] ) AS [GroupBy1] ON 1 = 1 |
SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] LEFT OUTER JOIN (SELECT MIN([Extent1].[ItemValue]) AS [A1] FROM [dbo].[DBItemList] AS [Extent1] ) AS [GroupBy1] ON 1 = 1 |
Count,LongCount
集合中的元素个数 |
myContext context = new myContext();
int n = context.DBItemList.Count();
int ni = context.DBItemList.Count(p => p.ItemValue == 5);
long ln = context.DBItemList.LongCount(); |
SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] LEFT OUTER JOIN (SELECT COUNT(cast(1 as bit)) AS [A1] FROM [dbo].[DBItemList] AS [Extent1] WHERE 5 = [Extent1].[ItemValue] ) AS [GroupBy1] ON 1 = 1 |
Concat
连接不同集合,不会自动过滤相同项,两个集合可以不是同一个Context |
myContext context1 = new myContext(); myContext context2 = new myContext();
IQueryable<DBItemList> list1 = context1.DBItemList.Where(p => p.ItemValue == 1); IQueryable<DBItemList> list2 = context1.DBItemList.Where(p => p.ItemValue == 2);
IQueryable<DBItemList> list = list1.Concat(list2); |
SELECT [UnionAll1].[AutoId] AS [C1], [UnionAll1].[NameID] AS [C2], [UnionAll1].[ItemID] AS [C3], [UnionAll1].[ItemValue] AS [C4] FROM (SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] WHERE 1 = [Extent1].[ItemValue] UNION ALL SELECT [Extent2].[AutoId] AS [AutoId], [Extent2].[NameID] AS [NameID], [Extent2].[ItemID] AS [ItemID], [Extent2].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent2] WHERE 2 = [Extent2].[ItemValue]) AS [UnionAll1] |
Union
连接不同集合,自动过滤相同项,两个集合要是同一个Context
ObjectQuery<T> Union(ObjectQuery<T> query) IQueryable<T> Union( IQueryable<T> query) IQueryable<T> Uniont( IQueryable<T> query,IEqualityComparer<T>) |
myContext context1 = new myContext();
IQueryable<DBItemList> query1 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "b");
IQueryable<DBItemList> query2 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "a");
IQueryable<DBItemList> v = query2.Union(query1);
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
myContext context1 = new myContext();
ObjectQuery<DBItemList> query1 = context1.CreateQuery<DBItemList>("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' ");
ObjectQuery<DBItemList> query2 = context1.CreateQuery<DBItemList>("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ");
ObjectQuery<DBItemList> v = query2.Union(query1);
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
SELECT [Distinct1].[C1] AS [C1], [Distinct1].[C2] AS [C2], [Distinct1].[C3] AS [C3], [Distinct1].[C4] AS [C4] FROM ( SELECT DISTINCT [UnionAll1].[AutoId] AS [C1], [UnionAll1].[NameID] AS [C2], [UnionAll1].[ItemID] AS [C3], [UnionAll1].[ItemValue] AS [C4] FROM (SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] WHERE (N'c' = [Extent1].[ItemID]) OR (N'a' = [Extent1].[ItemID]) UNION ALL SELECT [Extent2].[AutoId] AS [AutoId], [Extent2].[NameID] AS [NameID], [Extent2].[ItemID] AS [ItemID], [Extent2].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent2] WHERE (N'c' = [Extent2].[ItemID]) OR (N'b' = [Extent2].[ItemID])) AS [UnionAll1] ) AS [Distinct1] |
UnionAll
两个集合的相同项都会返回,两个集合要是同一个Context ObjectQuery<T> UnionAll(ObjectQuery<T> query); |
myContext context1 = new myContext();
ObjectQuery<DBItemList> query1 = context1.CreateQuery<DBItemList>("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' ");
ObjectQuery<DBItemList> query2 = context1.CreateQuery<DBItemList>("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ");
ObjectQuery<DBItemList> v = query2.UnionAll(query1);
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
SELECT [UnionAll1].[AutoId] AS [C1], [UnionAll1].[NameID] AS [C2], [UnionAll1].[ItemID] AS [C3], [UnionAll1].[ItemValue] AS [C4] FROM (SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] WHERE ([Extent1].[ItemID] = 'c') OR ([Extent1].[ItemID] = 'a') UNION ALL SELECT [Extent2].[AutoId] AS [AutoId], [Extent2].[NameID] AS [NameID], [Extent2].[ItemID] AS [ItemID], [Extent2].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent2] WHERE ([Extent2].[ItemID] = 'c') OR ([Extent2].[ItemID] = 'b')) AS [UnionAll1] |
Except
从某集合中删除其与另一个集合中相同的项,两个集合要是同一个Context
ObjectQuery<T> Except(ObjectQuery<T> query) IQueryable<T> Except( IQueryable<T> query) IQueryable<T> Except( IQueryable<T> query,IEqualityComparer<T>) |
myContext context1 = new myContext();
IQueryable<DBItemList> query1 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "b");
IQueryable<DBItemList> query2 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "a");
IQueryable<DBItemList> v = query2.Except(query1);
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
myContext context1 = new myContext();
ObjectQuery<DBItemList> query1 = context1.CreateQuery<DBItemList>("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' ");
ObjectQuery<DBItemList> query2 = context1.CreateQuery<DBItemList>("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ");
ObjectQuery<DBItemList> v = query2.Except(query1);
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
SELECT [Except1].[AutoId] AS [C1], [Except1].[NameID] AS [C2], [Except1].[ItemID] AS [C3], [Except1].[ItemValue] AS [C4] FROM (SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] WHERE (N'c' = [Extent1].[ItemID]) OR (N'a' = [Extent1].[ItemID]) EXCEPT SELECT [Extent2].[AutoId] AS [AutoId], [Extent2].[NameID] AS [NameID], [Extent2].[ItemID] AS [ItemID], [Extent2].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent2] WHERE (N'c' = [Extent2].[ItemID]) OR (N'b' = [Extent2].[ItemID])) AS [Except1] |
Intersect
获取不同集合的相同项(交集),两个集合要是同一个Context
ObjectQuery<T> Intersect(ObjectQuery<T> query) IQueryable<T> Intersect( IQueryable<T> query) IQueryable<T> Intersect( IQueryable<T> query,IEqualityComparer<T>) |
myContext context1 = new myContext();
IQueryable<DBItemList> query1 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "b");
IQueryable<DBItemList> query2 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "a");
IQueryable<DBItemList> v = query2.Intersect(query1);
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
myContext context1 = new myContext();
ObjectQuery<DBItemList> query1 = context1.CreateQuery<DBItemList>("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' ");
ObjectQuery<DBItemList> query2 = context1.CreateQuery<DBItemList>("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ");
ObjectQuery<DBItemList> v = query2.Intersect(query1);
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
SELECT [Intersect1].[AutoId] AS [C1], [Intersect1].[NameID] AS [C2], [Intersect1].[ItemID] AS [C3], [Intersect1].[ItemValue] AS [C4] FROM (SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] WHERE (N'c' = [Extent1].[ItemID]) OR (N'a' = [Extent1].[ItemID]) INTERSECT SELECT [Extent2].[AutoId] AS [AutoId], [Extent2].[NameID] AS [NameID], [Extent2].[ItemID] AS [ItemID], [Extent2].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent2] WHERE (N'c' = [Extent2].[ItemID]) OR (N'b' = [Extent2].[ItemID])) AS [Intersect1] |
Select
射影 |
myContext context = new myContext();
var list = context.DBItemList.Select(p => new {a= p.ItemValue,p.NameID }); |
SELECT 1 AS [C1], [Extent1].[ItemValue] AS [ItemValue], [Extent1].[NameID] AS [NameID] FROM [dbo].[DBItemList] AS [Extent1] |
GroupBy
分组,该方法分组结果集合
System.Collections.Generic.IEnumerable<System.Linq.IGrouping<TKey,TElement>>
myContext context = new myContext(); var query = context.DBItemList.GroupBy(p => p.ItemID); foreach (var g in query) { Console.WriteLine(g.Key);
foreach (var r in g) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); }
} /* a 23,a,n01,4 24,a,n01,5 25,a,n02,2 26,a,n02,3 27,a,n02,6 28,a,n03,3 b 11,b,n03,5 14,b,n01,2 16,b,n01,1 c 5,c,n01,4 7,c,n01,5 9,c,n02,2 10,c,n02,3 12,c,n02,6 17,c,n03,3 */ |
SELECT [Project2].[ItemID] AS [ItemID], [Project2].[C1] AS [C1], [Project2].[C2] AS [C2], [Project2].[AutoId] AS [AutoId], [Project2].[NameID] AS [NameID], [Project2].[ItemID1] AS [ItemID1], [Project2].[ItemValue] AS [ItemValue] FROM ( SELECT [Distinct1].[ItemID] AS [ItemID], 1 AS [C1], [Extent2].[AutoId] AS [AutoId], [Extent2].[NameID] AS [NameID], [Extent2].[ItemID] AS [ItemID1], [Extent2].[ItemValue] AS [ItemValue], CASE WHEN ([Extent2].[AutoId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2] FROM (SELECT DISTINCT [Extent1].[ItemID] AS [ItemID] FROM [dbo].[DBItemList] AS [Extent1] ) AS [Distinct1] LEFT OUTER JOIN [dbo].[DBItemList] AS [Extent2] ON ([Extent2].[ItemID] = [Distinct1].[ItemID]) OR (([Extent2].[ItemID] ISNULL) AND ([Distinct1].[ItemID] IS NULL)) ) AS [Project2] ORDER BY [Project2].[ItemID] ASC, [Project2].[C2] ASC
|
Join
联合查询 |
myContext context1 = new myContext();
ObjectQuery<DBItem> query1 = context1.DBItem;
ObjectQuery<DBItemList> query2 = context1.DBItemList;
var v = query1.Join(query2, temp1 => temp1.ItemID, temp2 => temp2.ItemID, (temp1, temp2) => new { temp1.ItemID, temp1.ItemMatter, temp2.ItemValue, temp2.AutoId, temp2.NameID });
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3},{4}", r.AutoId, r.ItemID, r.NameID, r.ItemValue,r.ItemMatter); }
|
myContext context1 = new myContext();
ObjectQuery<DBItem> query1 = context1.DBItem;
ObjectQuery<DBItemList> query2 = context1.DBItemList;
var v = from temp1 in query1 join temp2 in query2 on temp1.ItemID equals temp2.ItemID select new { temp1.ItemID, temp1.ItemMatter, temp2.ItemValue, temp2.AutoId,temp2.NameID };
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3},{4}", r.AutoId, r.ItemID, r.NameID, r.ItemValue,r.ItemMatter); } /* 5,c,n01,4,this is c 7,c,n01,5,this is c 9,c,n02,2,this is c 10,c,n02,3,this is c 11,b,n03,5,this is b 12,c,n02,6,this is c 14,b,n01,2,this is b 16,b,n01,1,this is b 17,c,n03,3,this is c 23,a,n01,4,this is a 24,a,n01,5,this is a 25,a,n02,2,this is a 26,a,n02,3,this is a 27,a,n02,6,this is a 28,a,n03,3,this is a */ |
SELECT 1 AS [C1], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemMatter] AS [ItemMatter], [Extent2].[ItemValue] AS [ItemValue], [Extent2].[AutoId] AS [AutoId], [Extent2].[NameID] AS [NameID] FROM [dbo].[DBItem] AS [Extent1] INNER JOIN [dbo].[DBItemList] AS [Extent2] ON ([Extent1].[ItemID] = [Extent2].[ItemID]) OR (([Extent1].[ItemID] IS NULL) AND([Extent2].[ItemID] IS NULL)) |
GroupJoin
myContext context1 = new myContext();
ObjectQuery<DBItem> query1 = context1.DBItem;
ObjectQuery<DBItemList> query2 = context1.DBItemList;
var v = query1.GroupJoin(query2, temp1 => temp1.ItemID, temp2 => temp2.ItemID, (temp1, temp2) => new { temp1.ItemID, temp1.ItemMatter, 个数 = temp2.Count() });
foreach (var r in v) { Console.WriteLine("{0},{1},{2}", r.ItemID, r.ItemMatter,r.个数); }
|
myContext context1 = new myContext();
ObjectQuery<DBItem> query1 = context1.DBItem;
ObjectQuery<DBItemList> query2 = context1.DBItemList;
var v = from temp1 in query1 join temp2 in query2 on temp1.ItemID equals temp2.ItemID into newtab select new { temp1.ItemID, temp1.ItemMatter, 个数 = newtab.Count() };
foreach (var r in v) { Console.WriteLine("{0},{1},{2}", r.ItemID, r.ItemMatter,r.个数); } /* a,this is a,6 b,this is b,3 c,this is c,6 */ |
SELECT 1 AS [C1], [Project1].[ItemID] AS [ItemID], [Project1].[ItemMatter] AS [ItemMatter], [Project1].[C1] AS [C2] FROM ( SELECT [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemMatter] AS [ItemMatter], (SELECT COUNT(cast(1 as bit)) AS [A1] FROM [dbo].[DBItemList] AS [Extent2] WHERE ([Extent1].[ItemID] = [Extent2].[ItemID]) OR (([Extent1].[ItemID] IS NULL) AND ([Extent2].[ItemID] IS NULL)))AS [C1] FROM [dbo].[DBItem] AS [Extent1] ) AS [Project1]
|
无效的Linq方法
可将ObjectQuery<T>转换为List<T>后使用 List的对应方法
Aggregate
据输入的表达式获取一个聚合值 |
myContext context = new myContext();
List<DBItemList> list = context.DBItemList.ToList();
DBItemList r = list.Aggregate((x, y) => new DBItemList() { ItemValue = x.ItemValue + y.ItemValue });
Console.WriteLine("ItemValue合计为:{0}", r.ItemValue); //print: ItemValue合计为:54 |
TakeWhile
条件第一次不成立就跳出循环 |
myContext context = new myContext();
List<DBItemList> list = context.DBItemList.ToList();
IEnumerable<DBItemList> v = list.TakeWhile(p => p.ItemValue >= 2);
foreach (var r in v) {
Console.WriteLine(r.ItemValue); } /* 4 5 2 3 5 6 2 */ |
SkipWhile
条件第一次不成立就失效,将后面的数据全取 |
myContext context = new myContext();
List<DBItemList> list = context.DBItemList.ToList();
IEnumerable<DBItemList> v = list.SkipWhile(p => p.ItemValue >= 2);
foreach (var r in v) {
Console.WriteLine(r.ItemValue); } /* 1 3 4 5 2 3 6 3 */ |
Reverse
顺序返转 |
myContext context = new myContext();
IEnumerable<DBItemList> list = context.DBItemList.AsEnumerable();
IEnumerable<DBItemList> v = list.Reverse(); |
Last,LastOrDefault
集合的最后一个元素,集合中没有会报错, 集合中的最后一个元素,没有则返回类型默认值,对象型默认值为null |
myContext context = new myContext();
List<DBItemList> list = context.DBItemList.ToList();
DBItemList l1 = list.Last();
DBItemList li = list.Last(p=>p.ItemValue==5);
DBItemList lid = list.LastOrDefault(p => p.ItemValue == 15);
if (lid != null) { Console.WriteLine(lid.ItemValue); } |
Single,SingleOrDefault
集合中符合条件的唯一元素,集合中没有会报错,集合中有两个以上会报错 集合中符合条件的唯一元素,集合中有两个以上会报错,集合中没有则返回类型默认值,对象型默认值为null |
myContext context = new myContext();
List<DBItemList> list = context.DBItemList.ToList();
DBItemList di = list.Single(p=>p.ItemValue==5);
DBItemList did = list.SingleOrDefault(p => p.ItemValue == 15);
if (did != null) { Console.WriteLine(did.ItemValue); } |
Contains
判断集合中是否包含有某一元素 |
myContext context = new myContext(); List<DBItemList> list = context.DBItemList.ToList();
DBItemList r = new DBItemList(); r.ItemValue = 3;
bool b = list.Contains(r,new c()); |
class c : IEqualityComparer<DBItemList> { public bool Equals(DBItemList x, DBItemList y) { if (x.ItemValue == y.ItemValue) { return true; } else { return false; } }
public int GetHashCode(DBItemList obj) { return 0; } } |
Distinct
过滤集合中的相同项 |
myContext context = new myContext(); List<DBItemList> list = context.DBItemList.ToList(); IEnumerable<DBItemList> v = list.Distinct(new c()); |
class c : IEqualityComparer<DBItemList> { public bool Equals(DBItemList x, DBItemList y) { if (x.ItemValue == y.ItemValue) { return true; } else { return false; } }
public int GetHashCode(DBItemList obj) { return 0; } } |
ElementAt,ElementAtOrDefault
集合中指定索引的元素 集合中指定索引的元素,没有则返回类型默认值,对象型默认值为null |
myContext context = new myContext();
List<DBItemList> list = context.DBItemList.ToList();
DBItemList r = list.ElementAt(5);
DBItemList rd = list.ElementAtOrDefault(50); |
DefaultIfEmpty
如果集合是的无素为空(count==0),就向集合中插入一个默认元素 |
DBItemList r=new DBItemList(); r.ItemValue=100;
List<DBItemList> list = new List<DBItemList>(); var pp = list.DefaultIfEmpty(r); |
SelectMany
myContext context = new myContext(); List<DBItemList> query = context.DBItemList.ToList();
IEnumerable<string> ls=query.SelectMany(p => p.NameID.Split('0')); foreach (string r in ls) { Console.WriteLine(r); }
/* n 1 n 1 n 2 n 2 n 3 n 2 n 1 n 1 n 3 n 1 n 1 n 2 n 2 n 2 n 3 */ |
|
Lambda表达式 |
List<string> ls = new List<string>() { "wxd/1", "lzm/2", "wxwinter/3" }; var li = ls.SelectMany(p => p.Split('/')); foreach (var s in li) { Console.WriteLine(s); }
|
对应Select效果 |
var ll = ls.Select(p => p.Split('/')); foreach (var s in ll) { foreach (var ss in s) { Console.WriteLine(ss); } } |
SequenceEqual
比较两个序列是否相等,可以指定相等比较方法 |
myContext context = new myContext(); List<DBItemList> list1 = context.DBItemList.Where(p => p.ItemID == "a" ).ToList(); List<DBItemList> list2 = context.DBItemList.Where(p => p.ItemID == "a").ToList(); bool b = list1.SequenceEqual(list2);
Console.WriteLine(b); //print:True |
比起 LINQ to SQL,EF 除了提供 LINQ 查询方式, 还提供了 Entity SQL language
ESQL 类似 Hibernate 的 HSQL,ESQL 与SQL 语言的语法相似,以字符串的方式执行
esql的查询结果集 ObjectQuery
ObjectQuery<实体>
myContext context = new myContext(); string esql = "SELECT VALUE DBItemList FROM myContext.DBItemList"; // ObjectQuery<DBItemList> query = new ObjectQuery<DBItemList>(esql, context); ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql); foreach (DBItemList r in query) { Console.WriteLine(r.NameID); } |
myContext context = new myContext(); string esql = "SELECT VALUE it FROM myContext.DBItemList as it"; // ObjectQuery<DBItemList> query = new ObjectQuery<DBItemList>(esql, context); ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql); foreach (DBItemList r in query) { Console.WriteLine(r.NameID); } |
ObjectQuery<DbDataRecord>
myContext context = new myContext(); string esql = "SELECT it.NameID FROM myContext.DBItemList as it"; //ObjectQuery<DbDataRecord> query = new ObjectQuery<DbDataRecord>(esql, context); ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in query) { Console.WriteLine(r["NameID"].ToString()); } |
ObjectQuery<简单类型>
myContext context = new myContext(); string esql = "SELECT value count(it.NameID) FROM myContext.DBItemList as it"; // ObjectQuery<int> query = new ObjectQuery<int>(esql, context); ObjectQuery<int> query = context.CreateQuery<int>(esql); foreach (int n in query) { Console.WriteLine(n); } |
myContext context = new myContext(); string esql = "SELECT value it.NameID FROM myContext.DBItemList as it"; // ObjectQuery<int> query = new ObjectQuery<int>(esql, context); ObjectQuery<string> query = context.CreateQuery<string>(esql); foreach (string n in query) { Console.WriteLine(n); } |
esql的使用
可以在
- ObjectQuery的Linq方法,
- 构造ObjectQuery,
- context.CreateQuery返方法,
中使用esql,并得到返回的榄查询结果ObjectQuery
it关键字
[it] 出现在 ESQL 中, 由 ObjectQuery<T>.Name 属性设定,用于标示源查询对象(ObjectQuery)的名称,
类似于 "SELECT * FROM Tab as it WHERE it.ItemValue =14" 。
可以将这个默认值 "it" 改成其他字符串。
myContext context = new myContext(); context.DBItemList.Name = "wxd"; ObjectQuery<DBItemList> list = context.DBItemList.Where("wxd.ItemValue=5"); |
myContext context = new myContext(); var sql = "SELECT VALUE DBItemList FROM myContext.DBItemList"; var query = new ObjectQuery<DBItemList>(sql, context); query.Name = "wxd"; ObjectQuery<DBItemList> list = query.Where("wxd.ItemValue=@v", newObjectParameter("v", 5)); |
value 关键字
value 后只能返回一个成员
myContext context = new myContext(); string esql = "SELECT value AVG(it.ItemValue) FROM myContext.DBItemList as it"; ObjectQuery<int> query = context.CreateQuery<int>(esql); foreach (int n in query) { Console.WriteLine(n); } /* print: 3 */ |
string esql = "select value it.ItemID from myContext.DBItemList as it"; ObjectQuery<string> query = context.CreateQuery<string>(esql); foreach (string r in query) { Console.WriteLine(r); } |
myContext context = new myContext(); string esql = "select value row( it.ItemValue ,it.NameID,'wxd' as wxwinter) from myContext.DBItemList as it"; ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in query) { Console.WriteLine("{0},{1},{2}", r["ItemValue"], r["NameID"], r["wxwinter"]); } |
查询参数的使用
myContext context = new myContext(); string esql = "SELECT VALUE it FROM myContext.DBItemList as it where it.ItemValue=@v1 or it.NameID=@v2"; ObjectParameter v1 = new ObjectParameter("v1", 3); ObjectParameter v2 = new ObjectParameter("v2", "n01"); ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql,v1,v2); foreach (DBItemList r in query) { Console.WriteLine("{0},{1}",r.NameID,r.ItemValue); } |
中文字段
使用[]将字段括起来
myContext context = new myContext(); ObjectQuery<typeTest> query = context.typeTest.Where("it.值 ==22.22"); System.Console.WriteLine(query.CommandText); foreach (typeTest r in query) { Console.WriteLine("{0},{1},{2},{3},{4},{5}", r.a, r.b, r.c, r.d, r.e, r.值); } |
myContext context = new myContext(); ObjectQuery<typeTest> query = context.typeTest.Where("it.[值] ==22.22"); System.Console.WriteLine(query.CommandText); foreach (typeTest r in query) { Console.WriteLine("{0},{1},{2},{3},{4},{5}", r.a, r.b, r.c, r.d, r.e, r.值); } |
得到esql与sql字串
myContext context = new myContext(); string esql = "SELECT VALUE it FROM myContext.DBItemList as it"; ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql); Console.WriteLine(query.CommandText); Console.WriteLine(query.ToTraceString()) |
SELECT VALUE it FROM myContext.DBItemList as it |
SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] |
CommandText属性
得到esql字串
ToTraceString方法
得到sql字串
ObjectQuery的Linq方法
Where
用字符串为条件进行查询 ObjectQuery<T> Where(string predicate, params ObjectParameter[] parameters); |
myContext context = new myContext(); ObjectQuery<DBItemList> list = context.DBItemList.Where("(it.ItemValue=5 or it .ItemValue=5) and it.NameID='n01'"); |
SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] WHERE (([Extent1].[ItemValue] = 5) OR ([Extent1].[ItemValue] = 5)) AND([Extent1].[NameID] = 'n01') |
OrderBy
排序 ObjectQuery<T> OrderBy(string keys, params ObjectParameter[] parameters); |
myContext context = new myContext(); ObjectQuery<DBItemList> query = context.DBItemList.OrderBy("it.ItemValue,it.ItemID desc"); foreach (var r in query) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] ORDER BY [Extent1].[ItemValue] ASC, [Extent1].[ItemID] DESC |
Select
射影 ObjectQuery<DbDataRecord> Select(string projection, params ObjectParameter[] parameters); |
myContext context = new myContext(); ObjectQuery<DbDataRecord> list = context.DBItemList.Select(" it.ItemValue as a,it.NameID "); |
SELECT 1 AS [C1], [Extent1].[ItemValue] AS [ItemValue], [Extent1].[NameID] AS [NameID] FROM [dbo].[DBItemList] AS [Extent1] |
SelectValue(projection)
返回只有一组字段的数组 ObjectQuery<TResultType> SelectValue<TResultType>(string projection, paramsObjectParameter[] parameters); |
myContext context = new myContext(); ObjectQuery<int> query = context.DBItemList.SelectValue<int>("it.ItemValue + it.AutoID"); foreach (var r in query) { Console.WriteLine(r); } |
SELECT [Extent1].[ItemValue] + [Extent1].[AutoId] AS [C1] FROM [dbo].[DBItemList] AS [Extent1] |
Top(count)
集合的前n个元素 count : 前n个元素 ObjectQuery<T> Top(string count, params ObjectParameter[] parameters); |
myContext context = new myContext(); ObjectQuery<DBItemList> query = context.DBItemList.Top("3"); ; foreach (var r in query) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
SELECT TOP (3) [c].[AutoId] AS [AutoId], [c].[NameID] AS [NameID], [c].[ItemID] AS [ItemID], [c].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [c] |
Skip(keys,count)
跳过集合的前n个元素, keys : 用于排序的字段 count : 要跳过的记录个数 ObjectQuery<T> Skip(string keys, string count, params ObjectParameter[] parameters); |
myContext context = new myContext(); ObjectQuery<DBItemList> query = context.DBItemList.Skip("it.ItemValue", "5"); foreach (var r in query) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM ( SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS[NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS[ItemValue], row_number() OVER (ORDER BY [Extent1].[ItemValue] ASC) AS[row_number] FROM [dbo].[DBItemList] AS [Extent1] ) AS [Extent1] WHERE [Extent1].[row_number] > 5 ORDER BY [Extent1].[ItemValue] ASC |
分页 Skip Top
Skip与Top一起使用 |
myContext context = new myContext(); ObjectQuery<DBItemList> query = context.DBItemList.Skip("it.ItemValue", "5").Top("3"); ; foreach (var r in query) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
SELECT TOP (3) [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM ( SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS[NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS[ItemValue], row_number() OVER (ORDER BY [Extent1].[ItemValue] ASC) AS[row_number] FROM [dbo].[DBItemList] AS [Extent1] ) AS [Extent1] WHERE [Extent1].[row_number] > 5 ORDER BY [Extent1].[ItemValue] ASC |
GroupBy(keys,projection)
分组 keys: GROUP BY的字段 projection : Select 的内容 ObjectQuery<DbDataRecord> GroupBy(string keys, string projection, paramsObjectParameter[] parameters); |
myContext context = new myContext(); ObjectQuery<DbDataRecord> query = context.DBItemList.GroupBy("it.ItemID","it.ItemID,Sum(it.ItemValue) as ValueSum"); foreach (var r in query) { Console.WriteLine("{0},{1}", r["ItemID"], r["ValueSum"]); } /* a,23 b,8 c,23 */ |
SELECT 1 AS [C1], [GroupBy1].[K1] AS [ItemID], [GroupBy1].[A1] AS [C2] FROM ( SELECT [Extent1].[ItemID] AS [K1], SUM([Extent1].[ItemValue]) AS [A1] FROM [dbo].[DBItemList] AS [Extent1] GROUP BY [Extent1].[ItemID] ) AS [GroupBy1] |
SELECT it.ItemID,Sum(it.ItemValue) as ValueSum FROM ( [DBItemList] ) AS it GROUP BY it.ItemID |
Include(path)
加载关联数据,参数为实体的[导航属性]的字串,调用Include("导航属性")后,关联数据会加载,这样就不用在[实体.导航属性]上调用Load()方法
ObjectQuery<T> Include(string path); |
myContext context = new myContext(); var r = context.DBItem.Include("DBItemList"); foreach (var dbitem in r) { foreach (var dbitemlist in dbitem.DBItemList) { Console.WriteLine("{0},{1}", dbitemlist.NameID, dbitemlist.ItemValue); } } |
效果与下例相同 myContext context = new myContext(); var r = context.DBItem; foreach (var dbitem in r) { dbitem.DBItemList.Load(); foreach (var dbitemlist in dbitem.DBItemList) { Console.WriteLine("{0},{1}", dbitemlist.NameID, dbitemlist.ItemValue); } } |
esql注释,成员访问,分行
注释 |
-- |
成员访问 |
. |
分行 |
; |
esql运算符
算术运算符
加 |
+ |
减 |
- |
乘 |
* |
除 |
/ |
模 |
% |
负 |
- |
比效运算符
等于 |
= |
大于 |
> |
大于等于 |
>= |
空判断 |
IS NOT NULL IS NULL |
小于 |
< |
小天等于 |
<= |
不等于 |
!= <> |
字符比效 |
LIKE '' NOT LIKE '' |
% : _ : [ ] : [^] : |
逻辑运算符
与 |
AND && |
非 |
NOT ! |
或 |
OR || |
区间
BETWEEN 之间 |
BETWEEN n AND m NOT BETWEEN n AND m |
myContext context = new myContext(); string esql = "SELECT VALUE it FROM [DBItemList] AS it WHERE it.ItemValue BETWEEN 2 and 4"; ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql); foreach (DBItemList r in query) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue); } |
|
myContext context = new myContext(); ObjectQuery<DBItemList> query = context.DBItemList.Where(" it.ItemValue not BETWEEN 2 and 4"); foreach (DBItemList r in query) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue); } |
|
IN 在集合中 |
IN {v,v} NOT IN{v,v} |
myContext context = new myContext(); string esql = "SELECT VALUE it FROM [DBItemList] AS it WHERE it.ItemValue IN {1,2,3}"; ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql); foreach (DBItemList r in query) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue); } |
|
myContext context = new myContext(); ObjectQuery<DBItemList> query = context.DBItemList.Where("it.ItemValue not IN {1,2,3}"); foreach (DBItemList r in query) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue); } |
|
EXISTS 存在 |
EXISTS(select from) NOT EXISTS(select from) |
myContext context = new myContext(); string esql = "SELECT VALUE it FROM [DBItemList] AS it WHERE exists(Select VALUE it2 From DBItem as it2 Where it2.ItemID=it.ItemID )"; ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql); foreach (DBItemList r in query) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue); } |
|
myContext context = new myContext(); ObjectQuery<DBItemList> query = context.DBItemList.Where("exists(Select VALUE it2 From DBItem as it2 Where it2.ItemID=it.ItemID )"); foreach (DBItemList r in query) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue); } |
|
分页 |
SELECT VALUE it FROM ( [DBItemList] ) AS it ORDER BY it.ItemValue SKIP 5 LIMIT 3 |
集合运算
Union (合集) 连接不同集合 |
UNION --自动过滤相同项 UNION ALL --两个集合的相同项都会返回 |
myContext context = new myContext(); string esql = "(select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ) UNION (select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' )"; ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql); foreach (DBItemList r in query) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue); } |
|
EXCEPT (左并集) 从集合中删除其与另一个集合中相同的项 |
myContext context = new myContext(); string esql = "(select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ) EXCEPT (select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' )"; ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql); foreach (DBItemList r in query) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue); } |
INTERSECT (交集) 获取不同集合的相同项 |
myContext context = new myContext(); string esql = "(select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ) INTERSECT (select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' )"; ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql); foreach (DBItemList r in query) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue); } |
ANYELEMENT 集合中的第一个 |
myContext context = new myContext(); string esql = "ANYELEMENT(select value it from myContext.DBItemList as it where it.ItemID == 'a') "; ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql); //如果集合中有两个以上,只返回第一个到集合中 foreach (DBItemList r in query) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue); } |
SELECT [Element1].[AutoId] AS [AutoId], [Element1].[NameID] AS [NameID], [Element1].[ItemID] AS [ItemID], [Element1].[ItemValue] AS [ItemValue] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] LEFT OUTER JOIN (SELECT TOP (1) [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] WHERE [Extent1].[ItemID] = 'a' ) AS [Element1] ON 1 =1 |
|
OVERLAPS 两个集合是否有相交部份 |
myContext context = new myContext(); string esql = "(select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID=='b' ) OVERLAPS (select value it from myContext.DBItemList as it where it.ItemID == 'a' || it.ItemID=='b')"; ObjectQuery<bool> query = context.CreateQuery<bool>(esql); foreach (bool r in query) { Console.WriteLine(r); } //print: True |
SELECT CASE WHEN ( EXISTS (SELECT cast(1 as bit) AS [C1] FROM (SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] WHERE ([Extent1].[ItemID] = 'c') OR([Extent1].[ItemID] = 'b') INTERSECT SELECT [Extent2].[AutoId] AS [AutoId], [Extent2].[NameID] AS [NameID], [Extent2].[ItemID] AS [ItemID], [Extent2].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent2] WHERE ([Extent2].[ItemID] = 'a') OR([Extent2].[ItemID] = 'b')) AS [Intersect1] )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT cast(1 as bit) AS [C1] FROM (SELECT [Extent3].[AutoId] AS [AutoId], [Extent3].[NameID] AS [NameID], [Extent3].[ItemID] AS [ItemID], [Extent3].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent3] WHERE ([Extent3].[ItemID] = 'c') OR([Extent3].[ItemID] = 'b') INTERSECT SELECT [Extent4].[AutoId] AS [AutoId], [Extent4].[NameID] AS [NameID], [Extent4].[ItemID] AS [ItemID], [Extent4].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent4] WHERE ([Extent4].[ItemID] = 'a') OR([Extent4].[ItemID] = 'b')) AS [Intersect2] )) THEN cast(0 as bit) END AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] |
|
Set 去掉重复项 |
myContext context = new myContext(); string esql = "set(select value it.ItemID from myContext.DBItemList as it)"; ObjectQuery<string> query = context.CreateQuery<string>(esql); foreach (string r in query) { Console.WriteLine(r); } //去掉了重复项 |
SELECT [Distinct1].[ItemID] AS [ItemID] FROM ( SELECT DISTINCT [Extent1].[ItemID] AS [ItemID] FROM [dbo].[DBItemList] AS [Extent1] ) AS [Distinct1] |
esql函数
统计类
Avg 平均值 |
myContext context = new myContext(); string esql = "SELECT value AVG(it.ItemValue) FROM myContext.DBItemList as it"; ObjectQuery<int> query = context.CreateQuery<int>(esql); foreach (int n in query) { Console.WriteLine(n); } /* print: 3 */ |
BigCount 个数(long) |
myContext context = new myContext(); string esql = "SELECT value BigCount(it.ItemValue) FROM myContext.DBItemList as it"; ObjectQuery<long> query = context.CreateQuery<long>(esql); foreach (long n in query) { Console.WriteLine(n); } /* print: 15 */ |
Count 个数(int) |
myContext context = new myContext(); string esql = "SELECT value Count(it.ItemValue) FROM myContext.DBItemList as it"; ObjectQuery<int> query = context.CreateQuery<int>(esql); foreach (int n in query) { Console.WriteLine(n); } /* print: 15 */ |
Max 最大值 |
myContext context = new myContext(); string esql = "SELECT value Max(it.ItemValue) FROM myContext.DBItemList as it"; ObjectQuery<int> query = context.CreateQuery<int>(esql); foreach (int n in query) { Console.WriteLine(n); } /* print: 6 */ |
Min 最小值 |
myContext context = new myContext(); string esql = "SELECT value Min(it.ItemValue) FROM myContext.DBItemList as it"; ObjectQuery<int> query = context.CreateQuery<int>(esql); foreach (int n in query) { Console.WriteLine(n); } /* print: 1 */ |
Sum 合计 |
myContext context = new myContext(); string esql = "SELECT value Sum(it.ItemValue) FROM myContext.DBItemList as it"; ObjectQuery<int> query = context.CreateQuery<int>(esql); foreach (int n in query) { Console.WriteLine(n); } /* print: 54 */ |
联合使用
myContext context = new myContext(); string esql = "SELECT Max(it.ItemValue) as Max , Min(it.ItemValue) as Min FROM myContext.DBItemList as it"; ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in query) { Console.WriteLine("Max:{0},Min:{1}", r["Max"], r["Min"]); } /* print: Max:6,Min:1 */ |
与group by一起使用
myContext context = new myContext(); string esql = "SELECT ID as ItemID , Max(it.ItemValue) as Max , Min(it.ItemValue) as Min FROM myContext.DBItemList as it group by it.ItemID as ID"; ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in query) { Console.WriteLine("ItemID:{0}, Max:{1},Min:{2}", r["ItemID"], r["Max"], r["Min"]); } /* print: ItemID:a, Max:6,Min:2 ItemID:b, Max:5,Min:1 ItemID:c, Max:6,Min:2 */ |
数学类
Abs 绝对值 |
Abs(-2) |
Round 随机数 |
Round(748.58) |
日期
CurrentDateTime() |
|
CurrentDateTimeOffset() |
|
CurrentUtcDateTime() |
|
Day( expression ) |
Day(cast('03/12/1998' as DateTime)) --返回:12 |
GetTotalOffsetMinutes |
--返回: SQL Server 2008 only |
Hour ( expression ) |
Hour(cast('22:35:5' as DateTime)) --返回:22 |
Minute( expression ) |
Minute(cast('22:35:5' as DateTime)) --返回:35 |
Month (expression) |
Month(cast('03/12/1998' as DateTime)) --返回:3 |
Second( expression ) |
Second(cast('22:35:5' as DateTime)) --返回:5 |
Year( expression ) |
Year(cast('03/12/1998' as DateTime)) --返回:1998 |
字符
Concat ( string1, string2) 字符串连接 |
Concat('abc', 'xyz') --返回:abcxyz |
IndexOf( string1, string2) 字符串位置查找 |
IndexOf('xyz', 'abcxyz') --返回:4 |
Length ( string ) 字符串长度 |
Legth('abcxyz') --返回:6 |
Reverse ( string ) 字符串反转 |
Reverse('abcd') --返回:dcba |
ToLower( string ) 大写转小写 |
ToLower('ABC') --返回:abc |
ToUpper( string ) 小写转大写 |
ToUpper('abc') --返回:ABC |
Trim( string ) 去两端空格 |
Trim(' abc ') --返回:abc |
LTrim( string ) 去左端空格 |
LTrim(' abc') --返回:abc |
RTrim( string ) 去右端空格 |
|
Left ( string, length) 左端截取 |
Left('abcxyz', 3) --返回:abc |
Right ( string, length) 右端截取 |
Right('abcxyz', 3) --返回:xyz |
Substring ( string, start, length) 两端截取 |
Substring('abcxyz', 4, 3) --返回:xyz |
esql语句
查询语句
SELECT |
myContext context = new myContext(); string esql = "SELECT it.ItemValue as a,it.NameID FROM [DBItemList] AS it"; ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in query) { Console.WriteLine("{0},{1}", r["a"], r["NameID"]); } |
SELECT 1 AS [C1], [Extent1].[ItemValue] AS [ItemValue], [Extent1].[NameID] AS [NameID] FROM [dbo].[DBItemList] AS [Extent1] |
|
WHERE |
myContext context = new myContext(); string esql = "SELECT VALUE it FROM [DBItemList] AS it WHERE (it.ItemValue=5 or it .ItemValue=5) and it.NameID='n01' "; ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql); foreach (DBItemList r in query) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue); } |
SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] WHERE (([Extent1].[ItemValue] = 5) OR ([Extent1].[ItemValue]= 5)) AND ([Extent1].[NameID] = 'n01') |
|
GROUP BY |
myContext context = new myContext(); string esql = "SELECT it.ItemID,Sum(it.ItemValue) as ValueSum FROM [DBItemList] AS it GROUP BY it.ItemID "; ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in query) { Console.WriteLine("{0},{1}", r["ItemID"], r["ValueSum"]); } |
SELECT 1 AS [C1], [GroupBy1].[K1] AS [ItemID], [GroupBy1].[A1] AS [C2] FROM ( SELECT [Extent1].[ItemID] AS [K1], SUM([Extent1].[ItemValue]) AS [A1] FROM [dbo].[DBItemList] AS [Extent1] GROUP BY [Extent1].[ItemID] ) AS [GroupBy1] |
|
ORDER BY |
myContext context = new myContext(); string esql = "SELECT VALUE it FROM [DBItemList] AS it ORDER BY it.ItemValue,it.ItemID desc "; ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql); foreach (DBItemList r in query) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue); } |
SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] ORDER BY [Extent1].[ItemValue] ASC, [Extent1].[ItemID] DESC |
|
HAVING |
myContext context = new myContext(); string esql = "SELECT it.ItemID,Count(it.ItemValue) as ValueSum FROM DBItemList AS it GROUP BY it.ItemID HAVING SUM(it.ItemValue) > 5"; ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in query) { Console.WriteLine("{0},{1}", r["ItemID"], r["ValueSum"]); } |
SELECT 1 AS [C1], [GroupBy1].[K1] AS [ItemID], [GroupBy1].[A2] AS [C2] FROM ( SELECT [Extent1].[ItemID] AS [K1], SUM([Extent1].[ItemValue]) AS [A1], COUNT([Extent1].[ItemValue]) AS [A2] FROM [dbo].[DBItemList] AS [Extent1] GROUP BY [Extent1].[ItemID] ) AS [GroupBy1] WHERE [GroupBy1].[A1] > 5 |
|
JOIN |
Cross Joins Inner Joins Left Outer Joins Right Outer Joins Full Outer Joins |
CASE语句
CASE WHEN THEN ELSE END |
myContext context = new myContext(); string esql = "select it.ItemID, it.ItemValue ,(Case when it.ItemValue =1 then '差' when it.ItemValue between 2 and 4 then '好' else '其他' end) as ItemValueRemarks from myContext.DBItemList as it"; ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in query) { Console.WriteLine("{0},{1},{2}", r["ItemID"], r["ItemValue"], r["ItemValueRemarks"]); } /* c,4,好 c,5,其他 c,2,好 c,3,好 b,5,其他 c,6,其他 b,2,好 b,1,差 c,3,好 a,4,好 a,5,其他 a,2,好 a,3,好 a,6,其他 a,3,好 */ |
esql 类型
简单类型
Null |
is Null is not Null |
myContext context = new myContext(); ObjectQuery<typeTest> query = context.typeTest.Where("it.b is not Null"); foreach (typeTest r in query) { Console.WriteLine("{0},{1},{2},{3},{4},{5}",r.a,r.b,r.c,r.d,r.e,r.值); } |
|
Boolean |
True,False |
myContext context = new myContext(); ObjectQuery<typeTest> query = context.typeTest.Where("it.e==True"); foreach (typeTest r in query) { Console.WriteLine("{0},{1},{2},{3},{4},{5}",r.a,r.b,r.c,r.d,r.e,r.值); } |
|
Integer Float,Double Decimal |
123 123.456 23.34 |
myContext context = new myContext(); ObjectQuery<typeTest> query = context.typeTest.Where("it.c==123"); foreach (typeTest r in query) { Console.WriteLine("{0},{1},{2},{3},{4},{5}",r.a,r.b,r.c,r.d,r.e,r.值); } |
|
String |
"abcd" N"U字符" 'abcd' |
myContext context = new myContext(); // ObjectQuery<typeTest> query = context.typeTest.Where("it.b==N'冬冬'"); ObjectQuery<typeTest> query = context.typeTest.Where("it.b=="冬冬" "); // ObjectQuery<typeTest> query = context.typeTest.Where("it.b=='冬冬'"); foreach (typeTest r in query) { Console.WriteLine("{0},{1},{2},{3},{4},{5}",r.a,r.b,r.c,r.d,r.e,r.值); } |
|
DateTime |
DATETIME'2007-11-11 22:22' DATETIME'2007-11-11 01:01:00.0000000' |
myContext context = new myContext(); // ObjectQuery<typeTest> query = context.typeTest.Where("it.d==DATETIME'1977-11-11 00:00:00.000'"); ObjectQuery<typeTest> query = context.typeTest.Where("it.d==cast('1977-11-11' as System.DateTime)"); foreach (typeTest r in query) { Console.WriteLine("{0},{1},{2},{3},{4},{5}",r.a,r.b,r.c,r.d,r.e,r.值); } |
|
Time |
TIME'22:11' TIME'01:02:03.1234567' |
DateTimeOffset |
DATETIMEOFFSET'2007-11-11 22:11 +02:00' DATETIMEOFFSET'2007-11-11 01:01:00.0000000 -02:00' |
Binary |
Binary'00ffaabb' X'ABCabc' BINARY '0f0f0f0F0F0F0F0F0F0F' X'' –空 |
Guid |
Guid'0321AF86-0AA5-4a86-A086-1D789FA54AA3' GUID '0321AF86-0AA5-4a86-A086-1D789FA54AA3' |
myContext context = new myContext(); ObjectQuery<typeTest> query = context.typeTest.Where("it.a==Guid'0321af86-0aa5-4a86-a086-1d789fa54aa3'"); foreach (typeTest r in query) { Console.WriteLine("{0},{1},{2},{3},{4},{5}",r.a,r.b,r.c,r.d,r.e,r.值); } |
REF
myContext context = new myContext(); //string esql = "SELECT it.ItemID FROM DBItem as it"; string esql = "SELECT REF(it).ItemID FROM DBItem as it"; ObjectQuery<DbDataRecord> v = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in v) { Console.WriteLine("{0}", r[0]); } |
ROW
myContext context = new myContext(); string esql = "select value row( it.ItemValue ,it.NameID,'wxd' as wxwinter) from myContext.DBItemList as it"; ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in query) { Console.WriteLine("{0},{1},{2}", r["ItemValue"], r["NameID"], r["wxwinter"]); } |
myContext context = new myContext(); string esql = "select row( it.ItemValue ,it.NameID) as wxd ,it.ItemID from myContext.DBItemList as it"; ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in query) { DbDataRecord v = r["wxd"] as DbDataRecord; Console.WriteLine("{0},{1},{2}", r["ItemID"],v["ItemValue"],v["NameID"]); } |
集合
MULTISET(1,2,3,4) {1,2,3,4} |
SELECT VALUE it FROM [DBItemList] AS it WHERE it.ItemValue IN {1,2,3} |
myContext context = new myContext(); string esql = "{1,2,3} "; ObjectQuery<int> query = context.CreateQuery<int>(esql); foreach (int r in query) { System.Console.WriteLine(r); } |
SELECT [UnionAll2].[C1] AS [C1] FROM (SELECT [UnionAll1].[C1] AS [C1] FROM (SELECT 1 AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] UNION ALL SELECT 2 AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable2]) AS[UnionAll1] UNION ALL SELECT 3 AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable3]) AS[UnionAll2] |
myContext context = new myContext(); string esql = "{row(1 as a,'wxd' as wxwinter),row(2 as a,'lzm' as wxwinter),row(3 as a,'wxwinter' as wxwinter)} "; ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in query) { System.Console.WriteLine("{0},{1}",r["a"],r["wxwinter"]); } /* 1,wxd 2,lzm 3,wxwinter */ |
SELECT 1 AS [C1], CASE WHEN ([UnionAll2].[C1] = 0) THEN 1 WHEN ([UnionAll2].[C1] = 1)THEN 2 ELSE 3 END AS [C2], CASE WHEN ([UnionAll2].[C1] = 0) THEN 'wxd' WHEN ([UnionAll2].[C1] = 1)THEN 'lzm' ELSE 'wxwinter' END AS [C3] FROM (SELECT [UnionAll1].[C1] AS [C1] FROM (SELECT 0 AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] UNION ALL SELECT 1 AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable2]) AS[UnionAll1] UNION ALL SELECT 2 AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable3]) AS[UnionAll2] |
Object 返回对像
Select 选出的值可以直接创建为对像后,将对像放入字段中返回 |
myContext context = new myContext(); string esql = "SELECT [WindowsFormsApplication8].[DBItemEx](it.ItemID + 'b') as myObject FROM DBItem as it"; ObjectQuery<DbDataRecord> v = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in v) { DBItemEx obj = r["myObject"] as DBItemEx; Console.WriteLine("{0}", obj.ItemID); } |
CAST 类型转换
myContext context = new myContext(); string esql = "select value CAST(it.ItemValue as System.String) from myContext.DBItemList as it"; ObjectQuery<string> query = context.CreateQuery<string>(esql); foreach (string r in query) { Console.WriteLine(r); } |
myContext context = new myContext(); string esql = "using System;select value CAST(it.ItemValue as String) from myContext.DBItemList as it"; ObjectQuery<string> query = context.CreateQuery<string>(esql); foreach (string r in query) { Console.WriteLine(r); } |
CAST( d as Edm.Decimal(16, 2) ) |
OFTYPE
OFTYPE ( expression, [ONLY] test_type ) |
myContext context = new myContext(); string esql = "OFTYPE(((SELECT VALUE it FROM DBItem as it) ),[WindowsFormsApplication8].[DBItemEx])"; ObjectQuery<DBItemEx> v = context.CreateQuery<DBItemEx>(esql); |
与如下效果相同 myContext context = new myContext(); ObjectQuery<DBItemEx> v = context.DBItem.OfType<DBItemEx>(); |
TREAT
myContext context = new myContext(); string esql = "SELECT TREAT(it AS [WindowsFormsApplication8].[DBItemEx]) FROM DBItem AS it"; ObjectQuery<DbDataRecord> v = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in v) { DBItemEx obj = r[0] as DBItemEx; if (obj != null) { Console.WriteLine("{0}", obj.ItemID); } } |
与如下效果类似 myContext context = new myContext(); ObjectQuery<DBItemEx> v = context.DBItem.OfType<DBItemEx>(); |
IS 类型判断
myContext context = new myContext(); string esql = "SELECT TREAT(it AS [WindowsFormsApplication8].[DBItemEx]) FROM DBItem AS it WHERE it IS OF ([WindowsFormsApplication8].[DBItemEx])"; ObjectQuery<DbDataRecord> v = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in v) { DBItemEx obj = r[0] as DBItemEx; // if (obj != null) { Console.WriteLine("{0}", obj.ItemID); } } |
与如下效果类似 myContext context = new myContext(); ObjectQuery<DBItemEx> v = context.DBItem.OfType<DBItemEx>(); |
esql Namespace
使用SqlServer函数
using SqlServer; |
myContext context = new myContext(); string esql = "using SqlServer;select it.ItemValue ,LEN(it.NameID) as NameIDLEN from myContext.DBItemList as it"; ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in query) { Console.WriteLine("{0},{1}",r["ItemValue"],r["NameIDLEN"]); } |
SqlServer.函数 |
myContext context = new myContext(); string esql = "select it.ItemValue ,SqlServer.LEN(it.NameID) as NameIDLEN from myContext.DBItemList as it"; ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql); foreach (DbDataRecord r in query) { Console.WriteLine("{0},{1}",r["ItemValue"],r["NameIDLEN"]); } |
SELECT 1 AS [C1], [Extent1].[ItemValue] AS [ItemValue], LEN([Extent1].[NameID]) AS [C2] FROM [dbo].[DBItemList] AS [Extent1] |
使用NET的数据类型
myContext context = new myContext(); string esql = "using System;select value CAST(it.ItemValue as String) from myContext.DBItemList as it"; ObjectQuery<string> query = context.CreateQuery<string>(esql); foreach (string r in query) { Console.WriteLine(r); } |
myContext context = new myContext(); // ObjectQuery<typeTest> query = context.typeTest.Where("it.d==DATETIME'1977-11-11 00:00:00.000'"); ObjectQuery<typeTest> query = context.typeTest.Where("it.d==cast('1977-11-11' as System.DateTime)"); foreach (typeTest r in query) { Console.WriteLine("{0},{1},{2},{3},{4},{5}",r.a,r.b,r.c,r.d,r.e,r.值); } |
esql关系,导航
KEY
myContext context = new myContext(); string esql="SELECT VALUE [TargetEntity] FROM (SELECT VALUE x FROM [myContext].[FK_DBItemList_DBItem] AS x WHERE Key(x.[DBItem]) = ROW(@EntityKeyValue1 AS EntityKeyValue1)) AS [AssociationEntry] INNER JOIN [myContext].[DBItemList] AS [TargetEntity] ON Key([AssociationEntry].[DBItemList]) = Key(Ref([TargetEntity]))"; ObjectQuery<DBItemList> dbitemlist = context.CreateQuery<DBItemList>(esql, newObjectParameter("EntityKeyValue1", "a")); foreach (DBItemList r in dbitemlist) { Console.WriteLine("{0},{1},{2}", r.AutoId, r.ItemValue, r.NameID); } |
效果同下 myContext context = new myContext(); ObjectQuery<DBItemList> dbitemlist = context.DBItem.First(p => p.ItemID =="a").DBItemList.CreateSourceQuery(); foreach (DBItemList r in dbitemlist) { Console.WriteLine("{0},{1},{2}", r.AutoId, r.ItemValue, r.NameID); } |