.NET CORE【EF】

宇翔苦涩

已于 2022-02-27 09:46:05 修改

阅读量662

点赞数

分类专栏： EF 文章标签： EF

于 2021-08-18 11:55:36 首次发布

本文链接：https://blog.csdn.net/qq_40110781/article/details/119776867

版权

EF 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

本文详细介绍了使用 Entity Framework Core 进行数据库查询的多种方法，包括简单查询、条件查询、分组查询、多表联接（内连接、左连接）以及分组函数的应用。示例涵盖了 Lambda 表达式和 LINQ 查询的不同场景，展示了如何高效地从数据库获取和处理数据。

摘要由CSDN通过智能技术生成

一、简单查询

//根据id查询
var transPlan = await _context.TransPlans.FindAsync(detailParam.TransPlanId);

//条件查询  返回一条
 var query = _context.TransPlans.Where(q => q.Id == inputParam.TransPlanId).FirstOrDefault();

//条件查询  返回集合
 var signAccessoryList = await _context.MortgageAccessorys
                .Where(t => detailParam.TransPlanId.Equals(t.TransPlanId) && t.TransPlanType == 0
                && t.IsDeleted == false).ToListAsync();
//分组查询
var result = new List<MortgageMainBrokerVo>();
            result = _context.DeliverCases.Where(d => d.SigningCompanyId == companyId && !string.IsNullOrWhiteSpace(d.MainBrokerId))
                .AsEnumerable()
                .GroupBy(g => g.MainBrokerId)
                .Select(s =>
                new MortgageMainBrokerVo() { MainBrokerId = s.FirstOrDefault().MainBrokerId})
                .ToList();

//根据某字段升序
signAccessoryList.Sort((l, r) => l.TransPlanType.CompareTo(r.TransPlanType));

//直接执行sql
//全表查询
 List<Users> ulist = se.Database.SqlQuery<Users>("select * from users").ToList();
 //接条件查询
 List<Users> ulist = se.Database.SqlQuery<Users>("select * from users where number=@number", new SqlParameter("number", "NS001")).ToList();

二、多表查询

//EF连表
mortgageInfoList = _context.DeliverCases
                    .Join(_context.TransPlans, d => d.JYCode
                    , t => t.JYCode, (d, t) => new MortgageInfo() {
                        JYCode = d.JYCode,
                        MortgageStatusCd = d.StatusCd,
                        MortgageStatusName = d.StatusNm,
                        OnlineSignTime = t.OnlineSignTime,
                        TransPlanStopReason = d.TransPlanStopReason,
                        TransStopTypeValue = d.TransStopTypeValue,
                        TransStopTypeName = d.TransStopTypeName
                    }).Where(w => input.JYCodeList.Contains(w.JYCode)).ToList();

//两表join linq写法
var query = from u in oae.Users
        join p in oae.Parent on u.Id equals p.ParentId
        select new
        {
           username = u.UserName,
           father = p.Father
        };

//两表left join linq写法
var query = from u in oae.Users
        join p in oae.Parent on u.Id equals p.ParentId into jtemp
        from leftjoin in jtemp.DefaultIfEmpty()
        select new
        {
         username = u.UserName,
         father = leftjoin.Father
        };
//两表left join lamdba写法
var query = oae.Users.GroupJoin(oae.Parent, a => a.Id, b => b.ParentId, (a, b) => new
            {
                username = a.UserName,
                parent = b
            }).SelectMany(a => a.parent, (m, n) => new
            {
                username = m.username,
                father = n.Father
            });
//lamdba的写法主要用到了groupjoin与SelectMany,这里简单解释一下：

//groupjoin：    用于查询一对多的关系很方便，所以得数据格式就是1对多的关系

//SelectMany:    可以解析集合中含有集合的情况(也就是1对多的表现)为单一对象

//三表join linq写法
var queru = from u in oae.Users
        join p in oae.Parent on u.Id equals p.ParentId
        join s in oae.Score on u.Id equals s.UsersId
        select new
        {
         username = u.UserName,
         fahter = p.Father,
         sub = s.Sub,
         score = s.Score1
        };
//三表join lamdba写法
var query = oae.Users.Join(oae.Parent, a => a.Id, b => b.ParentId, (a, b) => new
       {
          uid = a.Id,
          username = a.UserName,
          father = b.Father
        }).Join(oae.Score, a => a.uid, b => b.UsersId, (m, n) => new
         {
          username = m.username,
          father = m.father,
          sub = n.Sub,
          score = n.Score1
        });
//三表left join linq写法
var query = from u in oae.Users
        join p in oae.Parent on u.Id equals p.ParentId into ptemp
        join s in oae.Score on u.Id equals s.UsersId into stemp
        from leftp in ptemp.DefaultIfEmpty()
        from lefts in stemp.DefaultIfEmpty()
        select new
        {
           username = u.UserName,
           father = leftp.Father,
           sub = lefts.Sub,
           score = lefts.Score1
         };

//三表left join lamdba写法
var query = oae.Users.GroupJoin(oae.Parent, a => a.Id, b => b.ParentId, (a, b) => new
        {
            uid = a.Id,
            username = a.UserName,
            parent = b
        }).GroupJoin(oae.Score, a => a.uid, b => b.UsersId, (m, n) => new
        {
            username = m.username,
            uid = m.uid,
            score = n,
            parent = m.parent
        }).SelectMany(a => a.parent.DefaultIfEmpty(), (m, n) => new
        {
            username = m.username,
            fahter = n.Father,
            score = m.score
        }).SelectMany(a => a.score.DefaultIfEmpty(), (m, n) => new
        {
            usernaem = m.username,
            father = m.fahter,
            sub = n.Sub,
            score = n.Score1
        });
//lamdba写法2：上面是现join完在selectmany，也可以先selectmany了在join第三张表
//三表left join lamdba写法2
var query = oae.Users.GroupJoin(oae.Parent, a => a.Id, b => b.ParentId, (a, b) => new
        {
            uid = a.Id,
            username = a.UserName,
            parent = b
        }).SelectMany(a => a.parent.DefaultIfEmpty(), (m, n) => new
        {
            uid = m.uid,
            username = m.username,
            father = n.Father
        }).GroupJoin(oae.Score, a => a.uid, b => b.UsersId, (m, n) => new
        {
            username = m.username,
            father = m.father,
            score = n
        }).SelectMany(a => a.score, (m, n) => new
        {
            username = m.username,
            father = m.father,
            sub = n.Sub,
            score = n.Score1
        });

三、分组函数

//单表分组函数
//linq
var query = from score in oae.Score
            group score by score.Sub into grouptemp
            select new
            {
                sub = grouptemp.Key,
                sum = grouptemp.Sum(a => a.Score1),
                max = grouptemp.Max(a => a.Score1),
                min = grouptemp.Min(a => a.Score1),
                avg = grouptemp.Average(a => a.Score1)
            };
//lamdba
var query = oae.Score.GroupBy(a => a.Sub).Select(grouptemp => new
{
    sub = grouptemp.Key,
    sum = grouptemp.Sum(a => a.Score1),
    max = grouptemp.Max(a => a.Score1),
    min = grouptemp.Min(a => a.Score1),
    avg = grouptemp.Average(a => a.Score1)
}).Where(a => a.max > 60);

var result = query.ToList();

//分组函数后接一点条件

//linq
var query = from score in oae.Score
            group score by score.Sub into grouptemp
            where grouptemp.Sum(a=>a.Score1)>60
            select new
            {
                sub = grouptemp.Key,
                sum = grouptemp.Sum(a => a.Score1),
                max = grouptemp.Max(a => a.Score1),
                min = grouptemp.Min(a => a.Score1),
                avg = grouptemp.Average(a => a.Score1)
            };
//linq 写法二
var query = from score in oae.Score
            group score by score.Sub into grouptemp
            select new
            {
                sub = grouptemp.Key,
                sum = grouptemp.Sum(a => a.Score1),
                max = grouptemp.Max(a => a.Score1),
                min = grouptemp.Min(a => a.Score1),
                avg = grouptemp.Average(a => a.Score1)
            } into temp
            where temp.max > 60
            select new
            {
                sub = temp.sub,
                sum = temp.sum
            };
var result = query.ToList();

在这里插入图片描述
linq形式：

var query = from u in oae.Users
            join s in oae.Score on u.Id equals s.UsersId
            select new
            {
                UserName = u.UserName,
                Score1 = s.Score1
            } into jointemp
            group jointemp by jointemp.UserName into a
            select new ScoreViewModel
            {
                UserName = a.Key,
                Count = a.Count(),
                Max = a.Max(b => b.Score1),
                Min = a.Min(b => b.Score1),
                Sum = a.Sum(b => b.Score1),
                Avg = a.Average(b => b.Score1)
            };
List<ScoreViewModel> result = query.ToList();

在这里插入图片描述
linq形式

var query = from u in oae.Users
            join p in oae.User_Parent on u.Id equals p.UsersId into upjointemp
            from leftjoin in upjointemp.DefaultIfEmpty()
            join s in oae.Score on u.Id equals s.UsersId
            select new
            {
                UserName = u.UserName,
                Father = leftjoin.Father,
                Score1 = s.Score1
            } into jointemp
            group jointemp by new { jointemp.UserName, jointemp.Father } into a
            select new ScoreViewModel
            {
                UserName = a.Key.UserName,
                Father = a.Key.Father ?? "孤儿",
                Count = a.Count(),
                Max = a.Max(b => b.Score1),
                Min = a.Min(b => b.Score1),
                Sum = a.Sum(b => b.Score1),
                Avg = a.Average(b => b.Score1)
            };
List<ScoreViewModel> result = query.ToList();

四、简单插入修改

//有则修改，无则插入
 var transPlan = await _context.TransPlans.FindAsync(deliverCase.TransPlan.Id);
  //if (!string.IsNullOrEmpty(transPlan.JYCode)) {
  //    throw new DomainException("该案子已录过签约。");
  //}
  transPlan.JYCode = signParam.JYCode;
  transPlan.OnlineSignTime = signParam.OnlineSignTime ?? DateTimeOffset.Now;
  transPlan.LastTransferDate = signParam.LastTransferDate;
  transPlan.SigningHandleBrokerId = broker.Id;
  await _context.SaveEntitiesAsync();


//执行添加，删除，修改等操作
int count =Database.ExecuteSqlRaw(sql,params);
//执行添加，删除，修改等操作 方法2：
context.Database.ExecuteSqlInterpolated()
//直接执行存储过程语句
//执行存储过程并取得返回值
int prlr = myc.Database.SqlQuery<int>("exec [ProSelectCount] '1'").SingleOrDefault();