前言

由于项目变动，最近接手了一个新的团队，发现代码常出现SQL性能问题，使用到EF Core技术，故针对常用的EF Core使用场景做了一些培训，同时也亲测这些代码。

---

一、Initiate tables

示例：主要使用到以下几个表，DB：PG。

var table1 = _dbContext.Sysusers;     //主表
var table2 = _dbContext.Sysuserroles; //从表
var table3 = _dbContext.Sysroles;     //从表

二、两表Join

1.Linq

2.Lambda

代码如下： 

#region 两表 join
        // a. Linq
        var query = from m in table1
                    join d in table2 on m.Userid equals d.Userid
                    select new { UserID = m.Userid, RoleID = d.Roleid };
        query.ToList();

        // b. Lambda
        query = table1.Join(table2, m => m.Userid, d => d.Userid, (m, d) => new { UserID = m.Userid, RoleID = d.Roleid });
        query.ToList();

 #endregion

生成SQL：

SELECT s.userid AS "UserID", s0.roleid AS "RoleID"
FROM sysuser AS s
INNER JOIN sysuserrole AS s0 ON s.userid = s0.userid

SELECT s.userid AS "UserID", s0.roleid AS "RoleID"
FROM sysuser AS s
INNER JOIN sysuserrole AS s0 ON s.userid = s0.userid 

---

 三、两表Left Join

1.Linq

2.Lambda

代码如下： 

#region 两表left join
        // a. Linq
        query = from m in table1
                join d in table2 on m.Userid equals d.Userid into jtemp
                from leftjoin in jtemp.DefaultIfEmpty()
                select new
                {
                    UserID = m.Userid,
                    RoleID = leftjoin.Roleid
                };
        query.ToList();

        // b. Lambda
        // GroupJoin：用于查询一对多的关系
        query = table1.GroupJoin(table2, a => a.Userid, b => b.Userid, (a, b) => new
        {
            userid = a.Userid,
            userrole = b
        }).SelectMany(a => a.userrole.DefaultIfEmpty(), (m, n) => new
        {
            UserID = m.userid,
            RoleID = n.Roleid
        });
        query.ToList();

        query = table1
            .SelectMany(a => table2.Where(b => b.Userid == a.Userid).DefaultIfEmpty(),
                        (m, n) => new { UserID = m.Userid, RoleID = n.Roleid });
        query.ToList();

#endregion

生成SQL：

SELECT s.userid AS "UserID", s0.roleid AS "RoleID"
FROM sysuser AS s
LEFT JOIN sysuserrole AS s0 ON s.userid = s0.userid

SELECT s.userid AS "UserID", s0.roleid AS "RoleID"
FROM sysuser AS s
LEFT JOIN sysuserrole AS s0 ON s.userid = s0.userid

SELECT s.userid AS "UserID", s0.roleid AS "RoleID"
FROM sysuser AS s
LEFT JOIN sysuserrole AS s0 ON s.userid = s0.userid 

---

 四、三表Join

1.Linq

2.Lambda

代码如下： 

#region 三表 join

        // a. Linq
        var query2 = from m in table1
                     join d in table2 on m.Userid equals d.Userid
                     join d2 in table3 on d.Roleid equals d2.Id
                     select new
                     {
                         UserID = m.Userid,
                         RoleID = d.Roleid,
                         RoleName = d2.Rolename
                     };
        query2.ToList();

        // b. Lambda
        query2 = table1.Join(table2, m => m.Userid, d => d.Userid, (m, d) => new
        {
            UserID = m.Userid,
            RoleID = d.Roleid
        }).Join(table3, m => m.RoleID, d => d.Id, (m, d) => new
        {
            UserID = m.UserID,
            RoleID = m.RoleID,
            RoleName = d.Rolename
        });
        query2.ToList();

#endregion

生成SQL：

SELECT s.userid AS "UserID", s0.roleid AS "RoleID", s1.rolename AS "RoleName"
FROM sysuser AS s
INNER JOIN sysuserrole AS s0 ON s.userid = s0.userid
INNER JOIN sysrole AS s1 ON s0.roleid = s1.id

SELECT s.userid AS "UserID", s0.roleid AS "RoleID", s1.rolename AS "RoleName"
FROM sysuser AS s
INNER JOIN sysuserrole AS s0 ON s.userid = s0.userid
INNER JOIN sysrole AS s1 ON s0.roleid = s1.id

---

 五、三表Left Join

1.Linq

2.Lambda

代码如下： 

#region 三表 left join
        // a. Linq
        query2 = from m in table1
                 join t1 in table2 on m.Userid equals t1.Userid into t1group
                 from d in t1group.DefaultIfEmpty()
                 join t2 in table3 on d.Roleid equals t2.Id into t2group
                 from d2 in t2group.DefaultIfEmpty()
                 select new
                 {
                     UserID = m.Userid,
                     RoleID = d.Roleid,
                     RoleName = d2.Rolename
                 };
        query2.ToList();

        // b. Lambda
        query2 = table1.GroupJoin(table2, a => a.Userid, b => b.Userid, (a, b) => new
        { userid = a.Userid, userrole = b }).SelectMany(a => a.userrole.DefaultIfEmpty(), (m, n) => new
        {
            userid = m.userid,
            roleid = n.Roleid
        }).GroupJoin(table3, a => a.roleid, b => b.Id, (m, n) => new
        {
            userid = m.userid,
            roleid = m.roleid,
            role = n
        }).SelectMany(a => a.role.DefaultIfEmpty(), (m, n) => new
        { UserID = m.userid, RoleID = m.roleid, RoleName = n.Rolename });
        query2.ToList();

        query2 = table1
            .SelectMany(a => table2.Where(b => b.Userid == a.Userid).DefaultIfEmpty(),
                        (m, n) => new { userid = m.Userid, roleid = n.Roleid })
            .SelectMany(a => table3.Where(b => b.Id == a.roleid).DefaultIfEmpty(),
                        (m, n) => new { UserID = m.userid, RoleID = m.roleid, RoleName = n.Rolename });
        query2.ToList();

#endregion

生成SQL：

SELECT s.userid AS "UserID", s0.roleid AS "RoleID", s1.rolename AS "RoleName"
FROM sysuser AS s
LEFT JOIN sysuserrole AS s0 ON s.userid = s0.userid
LEFT JOIN sysrole AS s1 ON s0.roleid = s1.id

SELECT s.userid AS "UserID", s0.roleid AS "RoleID", s1.rolename AS "RoleName"
FROM sysuser AS s
LEFT JOIN sysuserrole AS s0 ON s.userid = s0.userid
LEFT JOIN sysrole AS s1 ON s0.roleid = s1.id

SELECT s.userid AS "UserID", s0.roleid AS "RoleID", s1.rolename AS "RoleName"
FROM sysuser AS s
LEFT JOIN sysuserrole AS s0 ON s.userid = s0.userid
LEFT JOIN sysrole AS s1 ON s0.roleid = s1.id

---

 六、单表分组(include where)

1.Linq

2.Lambda

代码如下： 

#region 单表分组
        // a. Linq
        var queryGroup = from user in table1
                         group user by new { user.Userlang } into grouptemp
                         where grouptemp.Sum(a => a.Usertz) > 0
                         select new
                         {
                             grpby = grouptemp.Key,
                             sum = grouptemp.Sum(a => a.Usertz),
                             max = grouptemp.Max(a => a.Usertz),
                             min = grouptemp.Min(a => a.Usertz),
                             avg = grouptemp.Average(a => a.Usertz),
                             cnt = grouptemp.Count()
                         };
        queryGroup.ToList();

        // b. Lambda
        queryGroup = table1.GroupBy(a => new { a.Userlang }).Select(grouptemp => new
        {
            grpby = grouptemp.Key,
            sum = grouptemp.Sum(a => a.Usertz),
            max = grouptemp.Max(a => a.Usertz),
            min = grouptemp.Min(a => a.Usertz),
            avg = grouptemp.Average(a => a.Usertz),
            cnt = grouptemp.Count()
        }).Where(a => a.sum > 0);

        queryGroup.ToList();

#endregion

生成SQL：

SELECT s.userlang AS "Userlang", COALESCE(sum(s.usertz), 0.0) AS sum, max(s.usertz) AS max, min(s.usertz) AS min, avg(s.usertz) AS avg, count(*)::int AS cnt
FROM sysuser AS s
GROUP BY s.userlang
HAVING COALESCE(sum(s.usertz), 0.0) > 0.0

SELECT s.userlang AS "Userlang", COALESCE(sum(s.usertz), 0.0) AS sum, max(s.usertz) AS max, min(s.usertz) AS min, avg(s.usertz) AS avg, count(*)::int AS cnt
FROM sysuser AS s
GROUP BY s.userlang
HAVING COALESCE(sum(s.usertz), 0.0) > 0.0 

---

 七、WHERE IN

1.Linq

2.Lambda

代码如下： 

#region WHERE IN 
        // a. Linq
        var ids = new List<string> { "x", "xx", "xxx" }; // "x,xx,xxx".Split(',').ToList()
        var whereIn = from t in table1 where ids.Contains(t.Userid) select t;
        whereIn.ToList();

        // b. Lambda
        whereIn = table1.Where(t => ids.Contains(t.Userid));
        whereIn.ToList();

#endregion

生成SQL：

SELECT s.userid, s.cdate, s.cuser, s.mdate, s.muser, s.useravatar, s.userlang, s.usertz
FROM sysuser AS s
WHERE s.userid = ANY (@__ids_0)

SELECT s.userid, s.cdate, s.cuser, s.mdate, s.muser, s.useravatar, s.userlang, s.usertz
FROM sysuser AS s
WHERE s.userid = ANY (@__ids_0) 

---

 八、WHERE EXISTS

1.Linq

2.Lambda

代码如下： 

#region WHERE EXISTS
        // a. Linq
        var whereExists = from t1 in table1
                          where table2.Any(t2 => t2.Userid == t1.Userid && t2.Roleid == "1")
                          select t1;
        whereExists.ToList();

        // b. Lambda
        whereExists = table1.Where(t1 => table2.Any(t2 => t2.Userid == t1.Userid && t2.Roleid == "1"));
        whereExists.ToList();

#endregion

生成SQL：

SELECT s.userid, s.cdate, s.cuser, s.mdate, s.muser, s.useravatar, s.userlang, s.usertz
FROM sysuser AS s
WHERE EXISTS (
    SELECT 1
    FROM sysuserrole AS s0
    WHERE s0.userid = s.userid AND s0.roleid = '1')

SELECT s.userid, s.cdate, s.cuser, s.mdate, s.muser, s.useravatar, s.userlang, s.usertz
FROM sysuser AS s
WHERE EXISTS (
    SELECT 1
    FROM sysuserrole AS s0
    WHERE s0.userid = s.userid AND s0.roleid = '1') 

---

 九、WHERE LIKE

1.Linq

2.Lambda

代码如下： 

#region WHERE LIKE
        // a. Linq
        var whereLike = from p in table1
                        where p.Userid.Contains("Z") || p.Userlang.IndexOf("en") > 0 || p.Cuser.StartsWith("start") || p.Muser.EndsWith("end")
                        select p;
        whereLike.ToList();

        // b. Lambda
        whereLike = table1.Where(p => p.Userid.Contains("Z") || p.Userlang.IndexOf("en") > 0 || p.Cuser.StartsWith("start") || p.Muser.EndsWith("end"));
        whereLike.ToList();

#endregion

生成SQL：

SELECT s.userid, s.cdate, s.cuser, s.mdate, s.muser, s.useravatar, s.userlang, s.usertz
FROM sysuser AS s
WHERE strpos(s.userid, 'Z') > 0 OR (strpos(s.userlang, 'en') - 1) > 0 OR (s.cuser LIKE 'start%') OR ((s.muser IS NOT NULL) AND (s.muser LIKE '%end'))

SELECT s.userid, s.cdate, s.cuser, s.mdate, s.muser, s.useravatar, s.userlang, s.usertz
FROM sysuser AS s
WHERE strpos(s.userid, 'Z') > 0 OR (strpos(s.userlang, 'en') - 1) > 0 OR (s.cuser LIKE 'start%') OR ((s.muser IS NOT NULL) AND (s.muser LIKE '%end')) 

---

 十、Table record Row to Col

1.Linq

2.Lambda

代码如下： 

#region RowToCol
        // a. Linq
        var rowToCol = from m in table1
                       join d in table2 on m.Userid equals d.Userid
                       join d2 in table3 on d.Roleid equals d2.Id
                       group d2 by new { m.Userid } into g
                       select new
                       {
                           UserID = g.Key.Userid,
                           RoleNames = string.Join(",", g.Select(p => p.Rolename))
                       };
        rowToCol.ToList();

        // b. Lambda
        rowToCol = table1.Join(table2, m => m.Userid, d => d.Userid, (m, d) => new
        {
            Userid = m.Userid,
            Roleid = d.Roleid
        }).Join(table3, m => m.Roleid, d => d.Id, (m, d) => new
        {
            Userid = m.Userid,
            Roleid = m.Roleid,
            Rolename = d.Rolename
        }).GroupBy(a => new { a.Userid }).Select(g => new
        {
            UserID = g.Key.Userid,
            RoleNames = string.Join(",", g.Select(p => p.Rolename))
        });
        rowToCol.ToList();

#endregion

生成SQL：

SELECT s.userid AS "UserID", COALESCE(string_agg(s1.rolename, ','), '') AS "RoleNames"
FROM sysuser AS s
INNER JOIN sysuserrole AS s0 ON s.userid = s0.userid
INNER JOIN sysrole AS s1 ON s0.roleid = s1.id
GROUP BY s.userid

SELECT s.userid AS "UserID", COALESCE(string_agg(s1.rolename, ','), '') AS "RoleNames"
FROM sysuser AS s
INNER JOIN sysuserrole AS s0 ON s.userid = s0.userid
INNER JOIN sysrole AS s1 ON s0.roleid = s1.id
GROUP BY s.userid 

---

 十一、Table record Row to Col left join

1.Linq

2.Lambda

代码如下： 

#region RowToCol left join
        // a. Linq
        rowToCol = from m in table1
                   join t1 in table2 on m.Userid equals t1.Userid into t1group
                   from d in t1group.DefaultIfEmpty()
                   join t2 in table3 on d.Roleid equals t2.Id into t2group
                   from d2 in t2group.DefaultIfEmpty()
                   group d2 by new { m.Userid } into g
                   select new
                   {
                       UserID = g.Key.Userid,
                       RoleNames = string.Join(",", g.Select(p => p.Rolename))
                   };
        rowToCol.ToList();

        // b. Lambda
        rowToCol = table1.SelectMany(a => table2.Where(b => b.Userid == a.Userid).DefaultIfEmpty(),
                        (m, n) => new { Userid = m.Userid, roleid = n.Roleid })
            .SelectMany(a => table3.Where(b => b.Id == a.roleid).DefaultIfEmpty(),
                        (m, n) => new { Userid = m.Userid, RoleID = m.roleid, Rolename = n.Rolename })
            .GroupBy(a => new { a.Userid }).Select(g => new
            {
                UserID = g.Key.Userid,
                RoleNames = string.Join(",", g.Select(p => p.Rolename))
            });
        rowToCol.ToList();

#endregion

生成SQL：

SELECT s.userid AS "UserID", COALESCE(string_agg(COALESCE(s1.rolename, ''), ','), '') AS "RoleNames"
FROM sysuser AS s
LEFT JOIN sysuserrole AS s0 ON s.userid = s0.userid
LEFT JOIN sysrole AS s1 ON s0.roleid = s1.id
GROUP BY s.userid

SELECT s.userid AS "UserID", COALESCE(string_agg(COALESCE(s1.rolename, ''), ','), '') AS "RoleNames"
FROM sysuser AS s
LEFT JOIN sysuserrole AS s0 ON s.userid = s0.userid
LEFT JOIN sysrole AS s1 ON s0.roleid = s1.id
GROUP BY s.userid

---