ASP.NET EF(LINQ/Lambda查询)-白红宇

ASP.NET EF(LINQ/Lambda查询)

阅读量：4659 次

发布时间：2019-06-09

本文共 17742 字，大约阅读时间需要 59 分钟。

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 on12 (13     name='MyFirstEF_log.ldf',14     --修改为自己电脑上SQL DB路径15     filename='E:\ProgramMSSQLServerDB\MyFirstEF_log.ldf',16     size=2mb,17     maxsize=100mb,18     filegrowth=5mb19 )20 go21 22 use MyFirstEF23 go24 25 create table CustomerInfo26 (27     id int identity(1,1) primary key,28     customerName nvarchar(100) not null,29     customerDate datetime30 )31 go32 33 insert into CustomerInfo values('aaaaa',getdate())34 go35 36 select * from CustomerInfo37 go38 39 create table OrderInfo40 (41   id int identity(1,1) primary key,42   orderName nvarchar(100),43   customerId int44 )45 go46 47 48 alter table OrderInfo49 add constraint FK_OrderInfo_CustomerInfo50 foreign key(customerId) references CustomerInfo(id)51 on delete cascade52 on update cascade53 54 go55 56 57 select * from CustomerInfo58 select * from OrderInfo

create SQL

1：LINQ[Language Integrated Query]/Lambda 基本查询

DbContext context = new MyFirstEFEntities();// LINQ 基本查询var rows = from c in context.Set
     
      ()           select c;//返回的是SQL文Console.WriteLine(rows);Console.WriteLine(rows.Count());//Lambda 基本查询var rows1 = context.Set
      
       ().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
     
      ()           where c.id > 2           select c;Console.WriteLine(rows);Console.WriteLine(rows.Count());//Lambda 单条件查询var rows1 = context.Set
      
       ().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
     
      ()           where c.id > 2 && c.customerName.Contains("today")           select c;Console.WriteLine(rows);Console.WriteLine(rows.Count());//Lambda 多条件查询var rows1 = context.Set
      
       ().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
     
      ()           join o in context.Set
      
       () on c.id equals o.customerId           select c;//连接查询相当于是Inner joinConsole.WriteLine(rows);Console.WriteLine(rows.Count());//Lambda 连接查询var rows1 = context.Set
       
        ().Join(context.Set
        
         (), 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 CustomerInfovar rows = from c in context.Set
     
      ()           from o in c.OrderInfoes           where c.id > 2 && o.orderName.Contains("car")           select c;Console.WriteLine(rows);Console.WriteLine(rows.Count());//select OrderInfovar rows1 = from c in context.Set
      
       ()           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 CustomerInfoSELECT[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 OrderInfoSELECT[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
     
      ()           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
      
       ().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
     
      ()           join o in context.Set
      
       ()           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
       
        ().Join(context.Set
        
         (), 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 和 EndedIndexvar rows = context.Set
     
      ().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] > 2ORDER BY [Extent1].[id] ASC

9：Lambda：封装分页查询

DbContext context1 = new MyFirstEFEntities();int pageSize = 2;int pageIndex = 2;//var rows = context1.Set
     
      ().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
      
       (context1, pageSize, pageIndex, c => c.id > 1, c => c.id);Console.WriteLine(rows1);Console.WriteLine(rows1.Count());

//泛型 委托 Lambda表达式public static IQueryable
     
       GetPageList
      
       (DbContext context, int pageSize, int pageIndex, Expression
       
        
         > where, Expression
         
          
           > orderBy) where T : class{ var rows = context.Set
           
            () //泛型约束，因为上下文的Set
            
             ()方法，对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] > 2ORDER 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
       
        ().Add(customerState);            //contextState.SaveChanges();            ////add 2            //CustomerInfo customerState = new CustomerInfo() {             //     customerName="stateTest111",            //     customerDate=DateTime.Now            //};            //contextState.Entry
        
         (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
         
          (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
          
            entry = contextState.Entry
           
            (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
            
             ().Attach(customerState);///直接针对属性进行状态设置，但是当前对象并没有被上下文跟踪 //contextState.Entry
             
              (customerState).Property("customerName").IsModified = true; //contextState.SaveChanges(); ////delete //CustomerInfo customerState = new CustomerInfo() //{ // id = 2 //}; //contextState.Entry
              
               (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
               
                ().Add(customerInfo); //OrderInfo orderInfo1 = new OrderInfo() //{ // orderName = "bike1", // customerId = customerInfo.id //}; //dbContext.Set
                
                 ().Add(orderInfo1); //OrderInfo orderInfo2 = new OrderInfo() //{ // orderName = "bike2", // customerId = customerInfo.id //}; //dbContext.Set
                 
                  ().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
                  
                   ().Add(customerInfo); //dbContext.SaveChanges(); #endregion #region 查询LINQ Lambda表达式 EF //DbContext context = new MyFirstEFEntities(); //// LINQ 基本查询 //var rows = from c in context.Set
                   
                    () // select c; ////返回的是SQL文 //Console.WriteLine(rows); //Console.WriteLine(rows.Count()); ////Lambda 基本查询 //var rows1 = context.Set
                    
                     ().Select(c => c); //Console.WriteLine(rows1); //Console.WriteLine(rows1.Count()); ////LINQ 单条件查询 //var rows = from c in context.Set
                     
                      () // where c.id > 2 // select c; //Console.WriteLine(rows); //Console.WriteLine(rows.Count()); ////Lambda 单条件查询 //var rows1 = context.Set
                      
                       ().Where(c => (c.id > 2)); //Console.WriteLine(rows1); //Console.WriteLine(rows1.Count()); ////LINQ 多条件查询 //var rows = from c in context.Set
                       
                        () // where c.id > 2 && c.customerName.Contains("today") // select c; //Console.WriteLine(rows); //Console.WriteLine(rows.Count()); ////Lambda 多条件查询 //var rows1 = context.Set
                        
                         ().Where(c => (c.id > 2) && (c.customerName.Contains("today"))); //Console.WriteLine(rows1); //Console.WriteLine(rows1.Count()); ////LINQ 连接查询 //var rows = from c in context.Set
                         
                          () // join o in context.Set
                          
                           () on c.id equals o.customerId // select c; ////连接查询相当于是Inner join //Console.WriteLine(rows); //Console.WriteLine(rows.Count()); ////Lambda 连接查询 //var rows1 = context.Set
                           
                            ().Join(context.Set
                            
                             (), 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
                             
                              () // 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
                              
                               () // 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
                               
                                () // 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
                                
                                 ().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
                                 
                                  () // join o in context.Set
                                  
                                   () // 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
                                   
                                    ().Join(context.Set
                                    
                                     (), 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
                                     
                                      ().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
                                      
                                       ().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
                                       
                                        (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
                                        
                                          GetPageList
                                         
                                          (DbContext context, int pageSize, int pageIndex, Expression
                                          
                                           
                                            > where, Expression
                                            
                                             
                                              > orderBy) where T : class { var rows = context.Set
                                              
                                               () //泛型约束，因为上下文的Set
                                               
                                                ()方法，对T有要求 .Where(where) //语法糖 将Lambda表达式封装为Expresson对象 .OrderBy(orderBy) .Skip(pageSize * (pageIndex - 1)) .Take(pageSize); return rows; } }}