EF基础入门

骗你学计算机

已于 2023-05-23 16:55:43 修改

阅读量1.2k

点赞数 2

文章标签：数据库 sql mysql linq

于 2023-04-26 14:42:26 首次发布

本文链接：https://blog.csdn.net/wangwengrui40/article/details/130379392

版权

查询聚合操作符（如 Count、Sum、 Min、Max、Average、Aggregate）不返回一个序列，而返回一个值。

---------------------实际业务中--------没那么简单，下列举个比较复杂的例子

linqA表左连B表,再B表内连C表

待解决问题，左连+分组无法生成 Group By sql语句，全表提交了

基础查询

基本单表Select查询

LINQ本身支持四种不同的集合生成方式，包含生成数组的ToArray()、生成列表的ToList、生成字典集合的ToDictionary

        //基本查询
        public async Task GetSelet()
        {
            //select * from BasBloodVariety
            var varietyList = _varietyRepository.GetAll();
            //select Top 1 * from BasBloodVariety
            var varietyMessage = await _varietyRepository.GetAll().FirstOrDefaultAsync();
            //select * from BasBloodVariety where Id=1
            var varietyWhere = await _varietyRepository.GetAll().Where(t => t.Id==1).ToListAsync();

            //select * from BasBloodVariety where Id=1 and BasXyzl=1
            var varietyWhereAnd = await  _varietyRepository.GetAll().Where(t => t.Id==1 && t.BasXyzl==1).ToListAsync();
            //select * from BasBloodVariety where Id=1 || BasXyzl=1
            var varietyWhereOr = await _varietyRepository.GetAll().Where(t => t.Id==1 || t.BasXyzl==1).ToListAsync();
       
        }

基本单表Select查询wherIf

        //基本查询
        public async Task GetSeletWhereIf(int? Id)
        {
            /*
             * WhereIf就是封装了一层if判断的 Where方法
            public static IQueryable<T> WhereIf<T>(this IQueryable<T> query, bool condition, Expression<Func<T, bool>> predicate)
           {
               if (!condition)
               {
                   return query;
               }

               return query.Where(predicate);
           }
           */

            var varietyWhere = await _varietyRepository.GetAll().WhereIf(Id!=null, t => t.Id==Id).ToListAsync();
        }

基本单表SelectMany查询

会按照list1 内的元素个数调用它的selector，并组装集合输出。例如，list1有2个，list2 的元素就会重复出现2次

        public async Task<List<int>> SelectMany() {
            List<int> list1 = new List<int>() { 1, 2 };
            List<int> list2 = new List<int>() {9 };

            var query = list1.SelectMany(o => list2).ToList();
            _varietyRepository.GetAll().SelectMany(o => _productRepository.GetAll()).ToList();
            return query;

        }

对应数据库的CROSS JOIN 方法

数据排序

        //数据排序：OrderBy() 与ThenBy() 优先级 OrderBy>ThenBy 倒序OrderByDescending>ThenByDescending
        //基本查询
        public async Task GetOrderBy(int? Id)
        {
           
            var varietyWhere = await _varietyRepository.GetAll().OrderBy(t=>t.Id).ThenBy(t=>t.BasXyzl).ToListAsync();

        }

分页Skip()、 Take()

        public async Task<List<BasBloodVariety>> GetSkipToList()
        {
            var varietySkipList = await _varietyRepository.GetAll().Skip(0).Take(13).ToListAsync();
            return varietySkipList;
        }

查询聚合操作符（如 Count、Sum、 Min、Max、Average、Aggregate）不返回一个序列，而返回一个值。

        //聚合操作符
        public async Task GetSeletFun(int? Id)
        {

            var varietyCount = await _varietyRepository.GetAll().CountAsync();
            var varietySum = await _varietyRepository.GetAll().SumAsync(t=>t.Id);
            var varietyAver = await _varietyRepository.GetAll().AverageAsync(t => t.Id);
        }

其他函数包SqlFunctions

        var result = from b in_varietyRepository.GetAll()
                         where SqlFunctions.StringConvert((double)b.Id) == "1"
                         select b;

基本单表分页

  var varietyWhereOrPage =await _varietyRepository.GetAll().Where(t => t.Id==1 || t.BasXyzl==1).Skip(0).Take(13).ToListAsync();

基本单表 in / not in

        //not in  / in
        public async Task<List<BasBloodVariety>> GetSeletNotIn()
        {
            //千万不要tolist！！！不然拼不成一整句sql
            var productList = _productRepository.GetAll().Where(s => s.Code!="1");
            var varietyList =await _varietyRepository.GetAll().Where(t=>(productList.Select(tb => tb.BasBloodVarietyId).Contains(t.Id))).ToListAsync();
            return varietyList;
        }

基本单表 in / not in错误写法
//千万不要tolist！！！不然拼不成一整句sql 例如这句就分成2个sql查询；1

       //not in 错误写法
        public async Task<List<BasBloodVariety>> GetSeletNotInToList()
        {
            //千万不要tolist！！！不然拼不成一整句sql 例如这句就分成2个sql查询；1
            var productList = _productRepository.GetAll().Where(s => s.Code!="1").ToList();
            var varietyList = await _varietyRepository.GetAll().Where(t => (productList.Select(tb => tb.BasBloodVarietyId).Contains(t.Id))).ToListAsync();
            return varietyList;
        }

内连接Join

        //内连
        public async Task<List<LeftJoinDto>> GetInnerJoin()
        {
            /*
             select * from  BasBloodProduct JOIN BasBloodVariety on  BasBloodProduct.BasBloodVarietyId = BasBloodVariety.Id
             */
            // 内连接示例
            /*
            var innerJoinQuery =
                from c in _varietyRepository.GetAll()
                join o in _productRepository.GetAll() on c.Id equals o.BasBloodVarietyId
                select new LeftJoinDto { varietyId = c.Id, productName = c.Name };
            */
            var innerJoinQuery = await _varietyRepository.GetAll()
                .Join(_productRepository.GetAll(), c => c.Id, o => o.BasBloodVarietyId, (c, o) => new LeftJoinDto { varietyId = c.Id, productName = c.Name }).Skip(0).Take(13).ToListAsync();
            return innerJoinQuery;
        }

左连 GroupJoin

方法查询

        //左连
        public async Task<List<LeftJoinDto>> GetLeftJoin()
        {
            /*
             select * from  BasBloodProduct LEFT JOIN BasBloodVariety  on  BasBloodProduct.BasBloodVarietyId = BasBloodVariety.Id
             */
            // 左连接示例 以左表为主
            /*
  */

            var leftJoinQuery = await _varietyRepository.GetAll().GroupJoin(_productRepository.GetAll(), c => c.Id, o => o.BasBloodVarietyId,
                (l, tmp) => new LeftJoinDto { varietyId = l.Id, productName = tmp.DefaultIfEmpty().FirstOrDefault()==null ? "" : tmp.DefaultIfEmpty().FirstOrDefault().Name })

                .ToListAsync();

            return leftJoinQuery;

        }

语法查询多了into coGroup from o in coGroup.DefaultIfEmpty()

           var leftJoinQuery =
                from c in _varietyRepository.GetAll()
                join o in _productRepository.GetAll() on c.Id equals o.BasBloodVarietyId into coGroup
                from o in coGroup.DefaultIfEmpty()
                select new LeftJoinDto { varietyId = c.Id,productName= 0.Name };

分组查询Group By

分组容易出性能问题，写分组后一定再三检查生成的语句

对应Group By来说还有一个 ToLookup，效果是一样的，但是会让数据分组在系统内存进行就不演示了

        //分组
        public async Task<List<string>> GetGroupByJoin()
        {
            var product = await _productRepository.GetAll().GroupBy(tt => tt.Code).Select(ta => ta.Key).ToListAsync();
            return product;
        }

注意分组查询只能出现 Count 和 Sum 聚合函数和 key，其他函数会做全表查询

内连+分组查询Group By

        public List<LeftJoinDto> GetGroupByJoinOne()
        {
            // var product =await _productRepository.GetAll().GroupBy(tt => tt.Code).Select(ta => ta.Key).ToListAsync();


            /*
             并且使用 Count 和 Sum 聚合函数来计算每个城市的客户数量和订单总数。当我们执行以上 LINQ 查询时，ORM 会生成包含 Group By 的 SQL 语句。
             */

            var innerJoinQuery = from variety in _varietyRepository.GetAll()
                                 join product in _productRepository.GetAll() on variety.Id equals product.BasBloodVarietyId
                                 group product by product.Code into product2
                                 select new LeftJoinDto
                                 {
                                     varietyId = product2.Min(a => a.BasBloodVarietyId),
                                     productName = product2.Key,
                                 };

            return innerJoinQuery.ToList();


        }

---------------------实际业务中--------没那么简单，下列举个比较复杂的例子

linqA表左连B表,再B表内连C表

        public async Task<List<LeftJoinDto>> Get3LeftJoin()
        {
            /*
             sql实现表A表B内连表C左连
             */
            //问题写法 分开写，但第一个查询对象不明确
            //var innerJoin = from a in _bloodRepository.GetAll()
            //                join b in _productRepository.GetAll() on a.BasBloodProductId equals b.Code
            //                select new { A = a, B = b };

            //var leftJoin = from ab in innerJoin
            //               join c in _varietyRepository.GetAll() on ab.B.BasBloodVarietyId equals c.Id into tmp
            //               select new LeftJoinDto {  varietyId = ab.B.BasBloodVarietyId};

            var result = await (from a in _bloodRepository.GetAll()
                                join b in _productRepository.GetAll() on a.BasBloodProductId equals b.Code into leftJoinGroup
                                from c in leftJoinGroup.DefaultIfEmpty()
                                join d in _varietyRepository.GetAll() on c.BasBloodVarietyId equals d.Id
                                select new LeftJoinDto { varietyId = d.Id, productName=c.Name }).Skip(1).Take(100).ToListAsync();

            return result;
        }

待解决问题，左连+分组无法生成 Group By sql语句，全表提交了

为什么要纠结这个问题，因为随着业务的复杂，很多人喜欢分组后再where，因为无法生成 Group By sql语句 Group By是在内存做的，就容易造成数据量过大，出现性能问题

        /*
 linq实现A表左连B表，过滤掉B表再A表不存在的，B表存在多条时只保留一条。返回A表与B表信息 = 逻辑不成立 直接内连分组就好了
 linq实现A表左连B表，B表存在多条时只保留一条 （没解决 左连的时候，怎么同时做分组）
 */
        public async Task<List<LeftJoinDto>> GetLeftGroupByJoinOne()
        {
            /*
             select BasBloodVariety.Id,Min(BasBloodProduct.Name) as Name from  BasBloodVariety  
left JOIN BasBloodProduct on  BasBloodProduct.BasBloodVarietyId = BasBloodVariety.Id
GROUP BY BasBloodVariety.Id,BasBloodProduct.Code
HAVING BasBloodProduct.Code Is NOT NULL
             */
            // 左连接示例 以左表为主  

            var result = from variety in _varietyRepository.GetAll()
                         join product in _productRepository.GetAll() on variety.Id equals product.BasBloodVarietyId into productGroup
                         from productb in productGroup.DefaultIfEmpty()
                         group productb by new { variety.Id, productb.Code } into g
                         select new LeftJoinDto { varietyId = g.Key.Id, productName=g.Key.Code };

            var innerJoinQuery2 = result.Where(t => t.varietyId>10);

            return innerJoinQuery2.ToList();


        }

目前解决方案

        //方案一：组合开发
        public async Task<TraApplyBillPatient> GetLeftGroupByJoinOne1()
        {
            /*
             SELECT TOP(1) xxxx  FROM (
    SELECT HospitalNumber as HospitalNumber, MAX(BillNo) as BillNo,
    '' as ManualNo, '' as BillType, 0 as BillStatus, '' as AuditInfo, '' as Remark, GETDATE() as CreationTime, convert(BIGINT, '1') as LastModifierUserId, convert(BIGINT, '1') as CreatorUserId, convert(BIGINT, '1') as AuditUserId,
    GETDATE() as AuditTime, GETDATE() as LastModificationTime
     FROM TraApplyBillPatient 
    GROUP BY HospitalNumber 
) AS [sa]
INNER JOIN [TraApplyBillPatient] AS [se] ON [sa].[BillNo] = [se].[BillNo]
             */
            var patientG = _dbContextProvider.GetDbContext().Set<BillInfoEntity>()
                   .FromSql(string.Format(@"SELECT HospitalNumber as HospitalNumber,MAX(BillNo) as BillNo,
'' as ManualNo,'' as BillType,0 as BillStatus,'' as AuditInfo,'' as Remark,GETDATE() as CreationTime,convert(BIGINT,'1') as LastModifierUserId,convert(BIGINT,'1') as CreatorUserId, convert(BIGINT,'1') as AuditUserId,
GETDATE() as AuditTime,GETDATE() as LastModificationTime
 FROM TraApplyBillPatient 
GROUP BY HospitalNumber 
"));
            var patientGroupBy = await patientG.Join(_TraApplyPatientRepository.GetAll(), sa => sa.BillNo, se => se.BillNo, (sa, se) => se).FirstOrDefaultAsync();

            return patientGroupBy;

        }
        //方案二：分开查询
        public async Task<List<LeftJoinDto>> GetLeftGroupByJoinOne2()
        {
            var result = await _varietyRepository.GetAll().Select(t => new LeftJoinDto { varietyId=t.Id }).ToListAsync();
            var resultIdList = result.Select(t => t.varietyId).ToList();
            var productList = await _productRepository.GetAll().Where(t => resultIdList.Contains(t.BasBloodVarietyId)).ToListAsync();
            foreach (var resultMessage in result)
            {

                resultMessage.productName=productList.Where(t => t.BasBloodVarietyId==resultMessage.varietyId).Select(t => t.Name).FirstOrDefault();
            }
            return result;
        }

增

        public async Task<BasBloodIndicationReturnDto> InsertData(BasBloodIndicationEditDto input)
        {
            var entity = input.MapTo<BasBloodIndication>();
// await _entityRepository.InsertAsync(entity); 不返回id版本
            input.Id = await _entityRepository.InsertAndGetIdAsync(entity);
          return input.MapTo<BasBloodIndicationReturnDto>(); ;
        }

批量操作

建议用 EFCore.BulkExtensions，原因免费。但是用ABP框架的要注意，批量操作是不包含框架的赋值的，用户id，修改时间，修改人，创建时间，创建人是不会的自动赋值

 _entityCheckRuleDetailRepository.GetDbContext().BulkInsertAsync(entityL);

改

      public async Task<BasBloodIndicationReturnDto> UpdateData(BasBloodIndicationEditDto input)
        {
            var entity = await _entityRepository.UpdateAsync(input.MapTo<BasBloodIndication>());
            return entity.MapTo<BasBloodIndicationReturnDto>();
        }

批量操作

        public async Task<List<BasBloodIndicationReturnDto>> BacthUpdateData(List<BasBloodIndicationEditDto> input)
        {
            var updateList = _entityRepository.GetAll().Where(t => input.Select(a => a.Id).Contains(t.Id));
            //批量修改要判断数量，否则容易数量为0是报错
           if (updateList.Count()>0) {
                await updateList.BatchUpdateAsync(a => new BasBloodIndication { BasXyzl=1 });
            }
          
            return input.MapTo<List<BasBloodIndicationReturnDto>>();
        }

删

原生的就是批量的，所以不建议使用 EFCore.BulkExtensions的删除方法

        public async Task BacthDelete()
        {

            await _entityRepository.DeleteAsync(t => t.BasXyzl==1&&t.Id==0);
           
        }

其他数据库函数

.net 5以上版本

      //其他数据库函数
        //public async Task GetSeletCast()
        //{
        //    var result = from b in_varietyRepository.GetAll()
        //                 where SqlFunctions.StringConvert((double)b.Id) == "1"
        //                 select b;
        //}

一个方便映射的方法

添加一个方法方便映射_骗你学计算机的博客-CSDN博客

骗你学计算机

关注

2
点赞
踩
5

收藏

觉得还不错? 一键收藏
2
评论
EF基础入门

为什么要纠结这个问题，因为随着业务的复杂，很多人喜欢分组后再where，因为无法生成 Group By sql语句 Group By是在内存做的，就容易造成数据量过大，出现性能问题。但是用ABP框架的要注意，批量操作是不包含框架的赋值的，用户id，修改时间，修改人，创建时间，创建人是不会的自动赋值。例如，list1有2个，list2 的元素就会重复出现2次。//千万不要tolist！原生的就是批量的，所以不建议使用 EFCore.BulkExtensions的删除方法。.net 5以上版本。
复制链接

扫一扫