SQL Query vs Entity Framework Syntax

เรามาเปรียบเทียบคำสั่ง SQL Query กับ Entity Framework Syntax (สำหรับคนที่ถนัด Query แต่เพิ่งเริ่มศึกษา Entity Framework) ว่าถ้าต้องการใช้ Query ประมาณนี้จะต้องเขียนยังไง

การดึงข้อมูลแบบง่าย

ตัวอย่างแรก ถ้าต้องการดึงข้อมูลจาก Table Employee โดยเอารหัสพนักงาน กับ ชื่อพนักงาน โดยกรองเฉพาะข้อมูลที่ไม่ถูกลบ และเรียงลำดับตามรหัสพนักงานจะต้องทำอย่างไร

SELECT TOP 10 EmpCode, EmpName
FROM Employee
WHERE IsDeleted=0
ORDER BY EmpCode

รูปแบบแรก จะเป็นการเขียนแบบ EF Syntax

var result = context.Employee
       .Where(e => e.IsDeleted == 0)
       .OrderBy(e => e.EmpCode)
       .Select(e => new { e.EmpCode, e.EmpName })
       .Take(10)
       .ToList();

รูปแบบที่สอง เป็นการเขียนแบบ LINQ Syntax

var result = (from e in context.Employee
              where e.IsDeleted == 0
              orderby e.EmpCode
              select new
              {
                  e.EmpCode,
                  e.EmpName
              }).Take(10);

---

การ JOIN

ต่อมาถ้าจะ Join Table แผนก (Division) โดยใช้ DivisionID เป็นคีย์ในการเชื่อมข้อมูลเพื่อไปดึงรหัสแผนกและชื่อแผนก จะต้องทำยังไง

SELECT e.EmpCode, e.EmpName, d.DivisionCode, d.DivisionName
FROM Employee e
JOIN Division d on e.DivisionID = d.DivisionID
WHERE IsDeleted=0
ORDER BY EmpCode

รูปแบบแรก จะใช้คำสั่ง Join ในการเชื่อมข้อมูล ดังนี้

var result = context.Employee
       .Join(context.Division,
             e => e.DivisionID,
             d => d.DivisionID,
             (e, d) => new { e.EmpCode, e.EmpName, d.DivisionCode, d.DivisionName })
       .Where(ed => ed.IsDeleted == 0)
       .OrderBy(ed => ed.EmpCode)
       .ToList();

รูปแบบ LINQ จะใช้คำสั่ง join … in … on col1 eq col2

var result = from e in context.Employee
             join d in context.Division on e.DivisionID equals d.DivisionID
             where e.IsDeleted == 0
             orderby e.EmpCode
             select new
             {
                 e.EmpCode,
                 e.EmpName,
                 d.DivisionCode,
                 d.DivisionName
             };

---

การ LEFT JOIN

แล้วถ้าจะใช้แบบ LEFT JOIN หล่ะ จะต้องทำยังไง

SELECT e.EmpCode, e.EmpName, d.DivisionCode, d.DivisionName
FROM Employee e
LEFT JOIN Division d on e.DivisionID = d.DivisionID
WHERE IsDeleted=0
ORDER BY EmpCode

— แบบแรก จะใช้เงื่อนไขประมาณนี้ (keycol != null ? valuecol : null)

var result = context.Employee
       .Where(e => e.IsDeleted == 0)
       .OrderBy(e => e.EmpCode)
       .Select(e => new {
            e.EmpCode,
            e.EmpName,
            DivisionCode = (e.Division != null ? e.Division.DivisionCode : null),
            DivisionName = (e.Division != null ? e.Division.DivisionName : null)
        })
        .ToList();

ส่วนของ LINQ จะเพิ่ม DefaultIfEmpty() เพิ่มเข้ามา

var result = (from e in context.Employee
              where e.IsDeleted == 0
              orderby e.EmpCode
              join d in context.Division on e.DivisionID equals d.DivisionID into divisions
              from d in divisions.DefaultIfEmpty()
              select new
              {
                  e.EmpCode,
                  e.EmpName,
                  DivisionCode = (d != null ? d.DivisionCode : null),
                  DivisionName = (d != null ? d.DivisionName : null)
              }).ToList();

---

การใช้ GROUP BY ร่วมกับ COUNT

แล้วถ้าจะทำ Group By หล่ะ

SELECT DivisionCode, DivisionName, COUNT(EmpCode) EmpCount
FROM Division d
LEFT JOIN Employee e ON d.DivisionID=e.DivisionID
WHERE d.IsDeleted=0 AND e.IsDeleted=0
GROUP BY DivisionCode, DivisionName
ORDER BY DivisionCode

แบบแรกจะใช้รูปแบบประมาณนี้ GroupJoin(...(tb1, tb2) => new {col1, col2})

var result = context.Division
    .Where(d => d.IsDeleted == 0)
    .GroupJoin(context.Employee
        .Where(e => e.IsDeleted == 0),
        d => d.DivisionID,
        e => e.DivisionID,
        (division, employees) => new
        {
            division.DivisionCode,
            division.DivisionName,
            EmpCount = employees.Count()
        })
    .OrderBy(result => result.DivisionCode)
    .ToList();

ส่วนแบบ LINQ จะใช้คำสั่งประมาณนี้ group x by new { x.col1, x.col2 } into newx select new { col1, col2 }

var result = (from d in context.Division
    where d.IsDeleted == 0
    join e in context.Employee
    on d.DivisionID equals e.DivisionID into empGroup
    from emp in empGroup.DefaultIfEmpty()
    where emp.IsDeleted == 0 || emp == null
    group d by new { d.DivisionCode, d.DivisionName } into grouped
    orderby grouped.Key.DivisionCode
    select new
    {
        grouped.Key.DivisionCode,
        grouped.Key.DivisionName,
        EmpCount = grouped.Count()
    })
.ToList();

หวังว่าบทความนี้จะเป็นประโยชน์กับใครที่กำลังเริ่มศึกษา EF แล้วไม่รู้ว่าจะเริ่มยังไงนะครับ