EF(EntityFrameWork) ORM(对象关系映射框架/数据持久化框架),根据实体对象操作数据表中数据的一种面向对象的操作框架,底层也是调用ADO.NET
ASP.NET MVC 项目会自动导入MVC程序集,因为默认.NET环境(GAC)中没有这个程序集
1 create database MyFirstEF 2 on primary 3 ( 4 name='MyFirstEF.mdf', 5 --修改为自己电脑上SQL DB路径 6 filename='E:\ProgramMSSQLServerDB\MyFirstEF.mdf', 7 size=5mb, 8 maxsize=100mb, 9 filegrowth=10% 10 ) 11 log on 12 ( 13 name='MyFirstEF_log.ldf', 14 --修改为自己电脑上SQL DB路径 15 filename='E:\ProgramMSSQLServerDB\MyFirstEF_log.ldf', 16 size=2mb, 17 maxsize=100mb, 18 filegrowth=5mb 19 ) 20 go 21 22 use MyFirstEF 23 go 24 25 create table CustomerInfo 26 ( 27 id int identity(1,1) primary key, 28 customerName nvarchar(100) not null, 29 customerDate datetime 30 ) 31 go 32 33 insert into CustomerInfo values('aaaaa',getdate()) 34 go 35 36 select * from CustomerInfo 37 go 38 39 create table OrderInfo 40 ( 41 id int identity(1,1) primary key, 42 orderName nvarchar(100), 43 customerId int 44 ) 45 go 46 47 48 alter table OrderInfo 49 add constraint FK_OrderInfo_CustomerInfo 50 foreign key(customerId) references CustomerInfo(id) 51 on delete cascade 52 on update cascade 53 54 go 55 56 57 select * from CustomerInfo 58 select * from OrderInfo
1:LINQ[Language Integrated Query]/Lambda 基本查询
DbContext context = new MyFirstEFEntities(); // LINQ 基本查询 var rows = from c in context.Set<CustomerInfo>() select c; //返回的是SQL文 Console.WriteLine(rows); Console.WriteLine(rows.Count()); //Lambda 基本查询 var rows1 = context.Set<CustomerInfo>().Select(c => c); Console.WriteLine(rows1); Console.WriteLine(rows1.Count());
对应SQL为:
--基本查询 SELECT [Extent1].[id] AS [id], [Extent1].[customerName] AS [customerName], [Extent1].[customerDate] AS [customerDate] FROM [dbo].[CustomerInfo] AS [Extent1]
2:LINQ/Lambda 单条件查询
DbContext context = new MyFirstEFEntities(); //LINQ 单条件查询 var rows = from c in context.Set<CustomerInfo>() where c.id > 2 select c; Console.WriteLine(rows); Console.WriteLine(rows.Count()); //Lambda 单条件查询 var rows1 = context.Set<CustomerInfo>().Where(c => (c.id > 2)); Console.WriteLine(rows1); Console.WriteLine(rows1.Count());
对应SQL为:
--单条件查询 SELECT [Extent1].[id] AS [id], [Extent1].[customerName] AS [customerName], [Extent1].[customerDate] AS [customerDate] FROM [dbo].[CustomerInfo] AS [Extent1] WHERE [Extent1].[id] > 2
3:LINQ/Lambda 多条件查询
DbContext context = new MyFirstEFEntities(); //LINQ 多条件查询 var rows = from c in context.Set<CustomerInfo>() where c.id > 2 && c.customerName.Contains("today") select c; Console.WriteLine(rows); Console.WriteLine(rows.Count()); //Lambda 多条件查询 var rows1 = context.Set<CustomerInfo>().Where(c => (c.id > 2) && (c.customerName.Contains("today"))); Console.WriteLine(rows1); Console.WriteLine(rows1.Count());
对应SQL为:
--多条件查询 SELECT [Extent1].[id] AS [id], [Extent1].[customerName] AS [customerName], [Extent1].[customerDate] AS [customerDate] FROM [dbo].[CustomerInfo] AS [Extent1] WHERE ([Extent1].[id] > 2) AND ([Extent1].[customerName] LIKE N'%today%')
4:LINQ/Lambda 连接查询
DbContext context = new MyFirstEFEntities(); //LINQ 连接查询 var rows = from c in context.Set<CustomerInfo>() join o in context.Set<OrderInfo>() on c.id equals o.customerId select c; //连接查询相当于是Inner join Console.WriteLine(rows); Console.WriteLine(rows.Count()); //Lambda 连接查询 var rows1 = context.Set<CustomerInfo>().Join(context.Set<OrderInfo>(), c => c.id, o => o.customerId, (c, o) => c); Console.WriteLine(rows1); Console.WriteLine(rows1.Count());
对应SQL为:
--连接查询 SELECT [Extent1].[id] AS [id], [Extent1].[customerName] AS [customerName], [Extent1].[customerDate] AS [customerDate] FROM [dbo].[CustomerInfo] AS [Extent1] INNER JOIN [dbo].[OrderInfo] AS [Extent2] ON [Extent1].[id] = [Extent2].[customerId]
5:LINQ:多from查询:专用于有导航属性的查询(LINQ特有)
DbContext context = new MyFirstEFEntities(); //多from查询:专用于有导航属性的查询(LINQ特有) //select CustomerInfo var rows = from c in context.Set<CustomerInfo>() from o in c.OrderInfoes where c.id > 2 && o.orderName.Contains("car") select c; Console.WriteLine(rows); Console.WriteLine(rows.Count()); //select OrderInfo var rows1 = from c in context.Set<CustomerInfo>() from o in c.OrderInfoes where c.id > 2 && o.orderName.Contains("car") select o; Console.WriteLine(rows1); Console.WriteLine(rows1.Count());
对应SQL为:
--多from查询:专用于有导航属性的查询(LINQ特有) --select CustomerInfo SELECT [Extent1].[id] AS [id], [Extent1].[customerName] AS [customerName], [Extent1].[customerDate] AS [customerDate] FROM [dbo].[CustomerInfo] AS [Extent1] INNER JOIN [dbo].[OrderInfo] AS [Extent2] ON [Extent1].[id] = [Extent2].[customerId] WHERE ([Extent1].[id] > 2) AND ([Extent2].[orderName] LIKE N'%car%') --select OrderInfo SELECT [Extent1].[id] AS [id], [Extent1].[orderName] AS [orderName], [Extent1].[customerId] AS [customerId] FROM [dbo].[OrderInfo] AS [Extent1] WHERE ([Extent1].[customerId] IS NOT NULL) AND ([Extent1].[customerId] > 2) AND ([Extent1].[orderName] LIKE N'%car%')
6:LINQ/Lambda 查询部分列(指定列)
DbContext context = new MyFirstEFEntities(); //LINQ 查询部分列(指定列) var rows = from c in context.Set<CustomerInfo>() select new { Id = c.id, Name = c.customerName }; Console.WriteLine(rows); foreach (var row in rows) { Console.WriteLine(row.Id + ":" + row.Name); } //Lambda 查询部分列(指定列) var rows1 = context.Set<CustomerInfo>().Select(c => (new { Id = c.id, Name = c.customerName })); foreach (var row in rows1) { Console.WriteLine(row.Id + ":" + row.Name); }
对应SQL为:
--查询部分列(指定列) SELECT [Extent1].[id] AS [id], [Extent1].[customerName] AS [customerName] FROM [dbo].[CustomerInfo] AS [Extent1]
7:LINQ/Lambda 查询多个表格部分列(指定列)
DbContext context = new MyFirstEFEntities(); //LINQ 查询多个表格部分列 var rows = from c in context.Set<CustomerInfo>() join o in context.Set<OrderInfo>() on c.id equals o.customerId select new { CustomerName = c.customerName, OrderName = o.orderName }; Console.WriteLine(rows); Console.WriteLine(rows.Count()); var rows1 = context.Set<CustomerInfo>().Join(context.Set<OrderInfo>(), c => c.id, o => o.customerId, (c, o) => (new { CustomerName = c.customerName, OrderName = o.orderName })); Console.WriteLine(rows1); Console.WriteLine(rows1.Count());
对应SQL为:
--查询多个表格部分列(指定列) SELECT [Extent1].[id] AS [id], [Extent1].[customerName] AS [customerName], [Extent2].[orderName] AS [orderName] FROM [dbo].[CustomerInfo] AS [Extent1] INNER JOIN [dbo].[OrderInfo] AS [Extent2] ON [Extent1].[id] = [Extent2].[customerId]
8:Lambda:分页查询 lambda特有 OrderBy/Skip/Take
DbContext context = new MyFirstEFEntities(); int pageSize = 2; int pageIndex = 2; //需要构建出startIndex 和 EndedIndex var rows = context.Set<CustomerInfo>().OrderBy(c => c.id).Skip(pageSize * (pageIndex - 1)).Take(2); Console.WriteLine(rows); Console.WriteLine(rows.Count());
对应SQL为:
--分页查询 lambda特有 SELECT TOP (2) [Extent1].[id] AS [id], [Extent1].[customerName] AS [customerName], [Extent1].[customerDate] AS [customerDate] FROM ( SELECT [Extent1].[id] AS [id], [Extent1].[customerName] AS [customerName], [Extent1].[customerDate] AS [customerDate], row_number() OVER (ORDER BY [Extent1].[id] ASC) AS [row_number] FROM [dbo].[CustomerInfo] AS [Extent1] ) AS [Extent1] WHERE [Extent1].[row_number] > 2 ORDER BY [Extent1].[id] ASC
9:Lambda:封装分页查询
DbContext context1 = new MyFirstEFEntities(); int pageSize = 2; int pageIndex = 2; //var rows = context1.Set<CustomerInfo>().Where(c => c.id > 1).OrderBy(c => c.id).Skip(pageSize * (pageIndex - 1)).Take(pageSize); //Console.WriteLine(rows); //Console.WriteLine(rows.Count()); var rows1 = GetPageList<CustomerInfo, int>(context1, pageSize, pageIndex, c => c.id > 1, c => c.id); Console.WriteLine(rows1); Console.WriteLine(rows1.Count());
//泛型 委托 Lambda表达式 public static IQueryable<T> GetPageList<T, Tkey>(DbContext context, int pageSize, int pageIndex, Expression<Func<T, bool>> where, Expression<Func<T, Tkey>> orderBy) where T : class { var rows = context.Set<T>() //泛型约束,因为上下文的Set<T>()方法,对T有要求 .Where(where) //语法糖 将Lambda表达式封装为Expresson对象 .OrderBy(orderBy) .Skip(pageSize * (pageIndex - 1)) .Take(pageSize); return rows; }
对应SQL为:
SELECT TOP (2) [Filter1].[id] AS [id], [Filter1].[customerName] AS [customerName], [Filter1].[customerDate] AS [customerDate] FROM ( SELECT [Extent1].[id] AS [id], [Extent1].[customerName] AS [customerName], [Extent1].[customerDate] AS [customerDate], row_number() OVER (ORDER BY [Extent1].[id] ASC) AS [row_number] FROM [dbo].[CustomerInfo] AS [Extent1] WHERE [Extent1].[id] > 1 ) AS [Filter1] WHERE [Filter1].[row_number] > 2 ORDER BY [Filter1].[id] ASC
整体EF 查询Demo源代码:
using System; using System.Collections.Generic; using System.Data.Entity; using System.Data.Entity.Infrastructure; using System.Linq; using System.Linq.Expressions; using System.Text; using System.Threading.Tasks; namespace _20160403_MyFirstEFDemo { class Program { static void Main(string[] args) { #region 方法 ////EF context 对象 //MyFirstEFEntities context = new MyFirstEFEntities(); ////add //CustomerInfo customer = new CustomerInfo(); //customer.customerName = "Test1"; //customer.customerDate = DateTime.Now; //context.CustomerInfoes.Add(customer); //context.SaveChanges(); ////update ////1:先查询要修改的原数据 //CustomerInfo customer = context.CustomerInfoes.Find(1); ////2:设置修改后的值 //customer.customerDate = DateTime.Now; ////3:更新到数据库 //context.SaveChanges(); ////Read //CustomerInfo customer = context.CustomerInfoes.Find(1); //if (customer != null) //{ // string strCustomerInfo = string.Format("name:{0},date:{1}", customer.customerName, customer.customerDate); // Console.WriteLine(strCustomerInfo); //} ////delete //CustomerInfo customer = new CustomerInfo(); //customer.id = 1; //context.CustomerInfoes.Attach(customer); //context.CustomerInfoes.Remove(customer); //context.SaveChanges(); #endregion #region 状态 //EF context 对象 //DbContext contextState = new MyFirstEFEntities(); ////add 1 //CustomerInfo customerState = new CustomerInfo(); //customerState.customerName = "testState1"; //customerState.customerDate = DateTime.Now; //contextState.Set<CustomerInfo>().Add(customerState); //contextState.SaveChanges(); ////add 2 //CustomerInfo customerState = new CustomerInfo() { // customerName="stateTest111", // customerDate=DateTime.Now //}; //contextState.Entry<CustomerInfo>(customerState).State = System.Data.EntityState.Added; //contextState.SaveChanges(); ////update 1 //CustomerInfo customerState = new CustomerInfo(); //customerState.id = 1; //customerState.customerDate = DateTime.Now; //customerState.customerName = "bbb"; ////1: 标记当前对象,必须把必填字段都填写,否则会报错:System.Data.Entity.Validation.DbEntityValidationException ////1: 若此时未更新 非必填字段,则数据库会把非必填字段更新为null //contextState.Entry<CustomerInfo>(customerState).State = System.Data.EntityState.Modified; //contextState.SaveChanges(); ////update 2 //CustomerInfo customerState = new CustomerInfo(); //customerState.id = 1; //customerState.customerName = "dfdfdfdf"; ////2: 针对某个属性,进行状态跟踪设置 ////** 2.1: 如果使用 Entry 附加 实体对象到数据容器中,则需要手动 设置 实体包装类的对象 的 状态为 Unchanged** ////** 2.1: entry.State = System.Data.EntityState.Unchanged; //DbEntityEntry<CustomerInfo> entry = contextState.Entry<CustomerInfo>(customerState); //entry.State = System.Data.EntityState.Unchanged; //entry.Property("customerName").IsModified = true; //contextState.SaveChanges(); ////update 3 //CustomerInfo customerState = new CustomerInfo(); //customerState.id = 1; //customerState.customerName = "aaaaa"; ////** 2.2: 如果使用 Attach 就不需要这句 ////** 2.2: entry.State = System.Data.EntityState.Unchanged; //contextState.Set<CustomerInfo>().Attach(customerState);///直接针对属性进行状态设置,但是当前对象并没有被上下文跟踪 //contextState.Entry<CustomerInfo>(customerState).Property("customerName").IsModified = true; //contextState.SaveChanges(); ////delete //CustomerInfo customerState = new CustomerInfo() //{ // id = 2 //}; //contextState.Entry<CustomerInfo>(customerState).State = System.Data.EntityState.Deleted; //contextState.SaveChanges(); #endregion #region 多表增加操作 //DbContext dbContext = new MyFirstEFEntities(); //CustomerInfo customerInfo = new CustomerInfo() //{ // customerName = "duobiaocaozuo", // customerDate = DateTime.Now //}; //dbContext.Set<CustomerInfo>().Add(customerInfo); //OrderInfo orderInfo1 = new OrderInfo() //{ // orderName = "bike1", // customerId = customerInfo.id //}; //dbContext.Set<OrderInfo>().Add(orderInfo1); //OrderInfo orderInfo2 = new OrderInfo() //{ // orderName = "bike2", // customerId = customerInfo.id //}; //dbContext.Set<OrderInfo>().Add(orderInfo2); //dbContext.SaveChanges(); #endregion #region 导航属性 //DbContext dbContext = new MyFirstEFEntities(); //CustomerInfo customerInfo = new CustomerInfo() //{ // customerName = "daohangshuxing", // customerDate = DateTime.Now //}; //customerInfo.OrderInfoes.Add(new OrderInfo() //{ // orderName = "car1", //}); //customerInfo.OrderInfoes.Add(new OrderInfo() //{ // orderName = "car2" //}); //dbContext.Set<CustomerInfo>().Add(customerInfo); //dbContext.SaveChanges(); #endregion #region 查询LINQ Lambda表达式 EF //DbContext context = new MyFirstEFEntities(); //// LINQ 基本查询 //var rows = from c in context.Set<CustomerInfo>() // select c; ////返回的是SQL文 //Console.WriteLine(rows); //Console.WriteLine(rows.Count()); ////Lambda 基本查询 //var rows1 = context.Set<CustomerInfo>().Select(c => c); //Console.WriteLine(rows1); //Console.WriteLine(rows1.Count()); ////LINQ 单条件查询 //var rows = from c in context.Set<CustomerInfo>() // where c.id > 2 // select c; //Console.WriteLine(rows); //Console.WriteLine(rows.Count()); ////Lambda 单条件查询 //var rows1 = context.Set<CustomerInfo>().Where(c => (c.id > 2)); //Console.WriteLine(rows1); //Console.WriteLine(rows1.Count()); ////LINQ 多条件查询 //var rows = from c in context.Set<CustomerInfo>() // where c.id > 2 && c.customerName.Contains("today") // select c; //Console.WriteLine(rows); //Console.WriteLine(rows.Count()); ////Lambda 多条件查询 //var rows1 = context.Set<CustomerInfo>().Where(c => (c.id > 2) && (c.customerName.Contains("today"))); //Console.WriteLine(rows1); //Console.WriteLine(rows1.Count()); ////LINQ 连接查询 //var rows = from c in context.Set<CustomerInfo>() // join o in context.Set<OrderInfo>() on c.id equals o.customerId // select c; ////连接查询相当于是Inner join //Console.WriteLine(rows); //Console.WriteLine(rows.Count()); ////Lambda 连接查询 //var rows1 = context.Set<CustomerInfo>().Join(context.Set<OrderInfo>(), c => c.id, o => o.customerId, (c, o) => c); //Console.WriteLine(rows1); //Console.WriteLine(rows1.Count()); ////多from查询:专用于有导航属性的查询(LINQ特有) ////select CustomerInfo //var rows = from c in context.Set<CustomerInfo>() // from o in c.OrderInfoes // where c.id > 2 && o.orderName.Contains("car") // select c; //Console.WriteLine(rows); //Console.WriteLine(rows.Count()); ////select OrderInfo //var rows1 = from c in context.Set<CustomerInfo>() // from o in c.OrderInfoes // where c.id > 2 && o.orderName.Contains("car") // select o; //Console.WriteLine(rows1); //Console.WriteLine(rows1.Count()); ////LINQ 查询部分列(指定列) //var rows = from c in context.Set<CustomerInfo>() // select new { Id = c.id, Name = c.customerName }; //Console.WriteLine(rows); //foreach (var row in rows) //{ // Console.WriteLine(row.Id + ":" + row.Name); //} ////Lambda 查询部分列(指定列) //var rows1 = context.Set<CustomerInfo>().Select(c => (new { Id = c.id, Name = c.customerName })); //foreach (var row in rows1) //{ // Console.WriteLine(row.Id + ":" + row.Name); //} ////LINQ 查询多个表格部分列 //var rows = from c in context.Set<CustomerInfo>() // join o in context.Set<OrderInfo>() // on c.id equals o.customerId // select new { CustomerName = c.customerName, OrderName = o.orderName }; //Console.WriteLine(rows); //Console.WriteLine(rows.Count()); //var rows1 = context.Set<CustomerInfo>().Join(context.Set<OrderInfo>(), c => c.id, o => o.customerId, (c, o) => (new { CustomerName = c.customerName, OrderName = o.orderName })); //Console.WriteLine(rows1); //Console.WriteLine(rows1.Count()); ////分页查询 lambda特有 //int pageSize = 2; //int pageIndex = 2; ////需要构建出startIndex 和 EndedIndex //var rows = context.Set<CustomerInfo>().OrderBy(c => c.id).Skip(pageSize * (pageIndex - 1)).Take(2); //Console.WriteLine(rows); //Console.WriteLine(rows.Count()); #endregion #region Lambda 分页封装 //DbContext context1 = new MyFirstEFEntities(); //int pageSize = 2; //int pageIndex = 2; ////var rows = context1.Set<CustomerInfo>().Where(c => c.id > 1).OrderBy(c => c.id).Skip(pageSize * (pageIndex - 1)).Take(pageSize); ////Console.WriteLine(rows); ////Console.WriteLine(rows.Count()); //var rows1 = GetPageList<CustomerInfo, int>(context1, pageSize, pageIndex, c => c.id > 1, c => c.id); //Console.WriteLine(rows1); //Console.WriteLine(rows1.Count()); #endregion Console.WriteLine("OK"); Console.ReadKey(); } //泛型 委托 Lambda表达式 public static IQueryable<T> GetPageList<T, Tkey>(DbContext context, int pageSize, int pageIndex, Expression<Func<T, bool>> where, Expression<Func<T, Tkey>> orderBy) where T : class { var rows = context.Set<T>() //泛型约束,因为上下文的Set<T>()方法,对T有要求 .Where(where) //语法糖 将Lambda表达式封装为Expresson对象 .OrderBy(orderBy) .Skip(pageSize * (pageIndex - 1)) .Take(pageSize); return rows; } } }
参考链接:
ASP.NET MVC EF直接更新数据(不需查询):http://www.cnblogs.com/Dr-Hao/p/5255630.html
ASP.NET EF 使用LinqPad 快速学习Linq:http://www.cnblogs.com/Dr-Hao/p/5357112.html