16、EF操作_多表联查lambda和linq

单表

sql

List<UserInfo> userList=fHZMEntities.Database.SqlQuery<UserInfo>("select * from UserInfo").ToList();

Linq

List<UserInfo> userList = (from a in fHZMEntities.UserInfo
where (string.IsNullOrEmpty(userinfo.UserName) || a.UserName == userinfo.UserName)
select a).ToList();

Lambda

List<UserInfo> userList = fHZMEntities.UserInfo.Where(a => (string.IsNullOrEmpty(userinfo.UserName) || a.UserName == userinfo.UserName)
&& (string.IsNullOrEmpty(userinfo.Number) || a.Number == userinfo.Number)
).ToList();

两表join

sql

List<UserParentViewModel> UserParentViewModelList = fHZMEntities.Database.SqlQuery<UserParentViewModel>("select *from UserInfo join UserParent on UserInfo.UserParent =UserParent.id").ToList();

Linq

var jointemp = from a in fHZMEntities.UserInfo
              join b in fHZMEntities.UserParent on a.UserParent equals b.id
              select new
               {
                  a.Id,
                   a.UserName,
                 b.FatherName
             };

Lambda

第一个参数：你要join的表
第二，三个参数：join连接条件
第四个参数：返回的结果

var UserParentViewModelList = fHZMEntities.UserInfo.Join(fHZMEntities.UserParent, a => a.UserParent, b => b.id, (a, b) => new
             {
                 a.Id,
                 a.UserName,
                 b.FatherName
             }).ToList();

两表leftjoin

Linq

var jointemp = from a in fHZMEntities.UserInfo
    join p in fHZMEntities.UserParent on a.UserParentId equals p.id into leftjointemp
    from leftjon in leftjointemp.DefaultIfEmpty()
    select new
{
    a.Id,
    a.UserName,
    leftjon.FatherName
};
var result = jointemp.ToList();

Lambda

//GroupJoin一对多
            var jointemp = fHZMEntities.UserInfo.GroupJoin(fHZMEntities.UserParent, a => a.UserParentId, b => b.id, (user, parent) => new
            {
                user = user,
                Father = parent
            }).SelectMany(a => a.Father.DefaultIfEmpty(), (last, parent) => new UserParentViewModel
            {
                Id = last.user.Id,
                UserName = last.user.UserName,
                FatherName = parent.FatherName
            });
            //SelectMany一对一

三表join

Linq

//var jointemp = from u in fHZMEntities.UserInfo
//               join p in fHZMEntities.UserParent on u.UserParentId equals p.id
//               join s in fHZMEntities.Score on u.Id equals s.UserId
//               select new
//               {
//                   u.Id,
//                   u.UserName,
//                   p.FatherName,
//                   s.sub,
//                   s.userScore
//               };
//var result = jointemp.ToList();

Lambda

      var jointemp = fHZMEntities1.UserInfo.Join(fHZMEntities1.UserParent, a => a.UserParentId, b => b.id, (a, b) => new
            {
                a.Id,
                a.UserName,
                a.Number,
                a.UClass,
                b.FatherName
            }).Join(fHZMEntities1.Score, a => a.Id, b => b.UserId, (a, b) => new
            {
                a.Id,
                a.UserName,
                a.Number,
                a.UClass,
                b.sub,
                b.userScore,
                a.FatherName
            });
            var result = jointemp.ToList();

三表leftjoin

Linq

var list = from u in fHZMEntities1.UserInfo
    join p in fHZMEntities1.UserParent on u.UserParentId equals p.id into leftjointemp
    from leftjointemp2 in leftjointemp.DefaultIfEmpty()
    join s in fHZMEntities1.Score on u.Id equals s.UserId into scoretemp
    from leftscore in scoretemp.DefaultIfEmpty()
    select new
{
    u.Id,
    u.UserName,
    u.Number,
    u.UClass,
    leftscore.sub,
    leftscore.userScore,
    leftjointemp2.FatherName
};
var result = list.ToList();

Lambda

var result = fHZMEntities1.UserInfo.GroupJoin(fHZMEntities1.UserParent, a => a.UserParentId, b => b.id, (a, b) => new
            {
                a.Id,
                a.UserName,
                a.Number,
                a.UClass,
                UserParent = b
            }).GroupJoin(fHZMEntities1.Score, a => a.Id, b => b.UserId, (a, b) => new
            {
                a.Id,
                a.UserName,
                a.Number,
                a.UClass,
                a.UserParent,
                Score = b
            }).SelectMany(a => a.UserParent.DefaultIfEmpty(), (m, n) => new
            {
                m.Id,
                m.UserName,
                m.Number,
                m.UClass,
                m.Score,
                n.FatherName
            }).SelectMany(a => a.Score.DefaultIfEmpty(), (m, n) => new
            {
                m.Id,
                m.UserName,
                m.Number,
                m.UClass,
                n.sub,
                n.userScore,
                m.FatherName,
            });
            var list = result.ToList();

join的连接之后的参数的理解

leftjoin参数理解