整理一下Entity Framework的查询

Entity Framework是个好东西，虽然没有Hibernate功能强大，但使用更简便。今天整理一下常见SQL如何用EF来表达，Func形式和Linq形式都会列出来（本人更喜欢Func形式）。

1、简单查询：

SQL：

SELECT * FROM [Clients] WHERE Type=1 AND Deleted=0 ORDER BY ID

EF：

//Func形式
    var clients = (c => == 1 && == 0)
                .OrderBy(c => )
                .ToList();

    //Linq形式
    var clients = from c in
                  where == 1 && ==0
                  orderby
                  select c;

2、查询部分字段：

SQL：

SELECT ID, Name FROM [Clients] WHERE Status=1

EF：

//Func形式
    var clients = (c => == 1)
                .Select(c => new { , Name = })
                .ToList();

    //Linq形式
    var clients = from c in
                  where == 1
                  select new { , Name = }; ：

3、查询单一记录：

SQL:

SELECT * FROM [Clients] WHERE ID=100

EF：

//Func形式
    var client = (c => == 100);

    //Linq形式
    var client = ( from c in
                where = 100
                select c).FirstOrDefault();

4、LEFT JOIN 连接查询

SQL：

SELECT  ,
        ,
        g. Name GroupName
FROM    [Clients] c
        LEFT JOIN [Groups] g ON =
WHERE   = 1

EF：

//Func形式
    var clients = (c => == 1)
                .Select(c => new
                {
                    ,
                    ,
                    GroupName = (g => == ).Name
                })
                .ToList();

    //Linq形式
    var clients = from c in
                where == 1
                select new
                {
                    ,
                    ,
                    GroupName = ( from g in
                                where ==
                                select ).FirstOrDefault()
                };

5、INNER JOIN 连接查询：

SQL：

SELECT  ,
        ,
        g. Name GroupName
FROM    [Clients] c
        INNER JOIN [Groups] g ON =
WHERE   = 1
ORDER BY g. Name

EF：

//Func形式
    var clients = (c => == 1)
                .Join(, c => , g => , (c,g) =>
                {
                    ,
                    ,
                    GroupName =
                })
                .OrderBy(item => )
                .ToList();


    //Linq形式1
    var clients = from c in
                from g in
                where ==
                orderby
                select new
                {
                    ,
                    ,
                    GroupName =
                };

    //Linq形式2
    var clients = from c in
                where == 1
                join g in
                on equals into result
                from r in result
                order by
                select new
                {
                    ,
                    ,
                    GroupName =
                };

6、分页

SQL：

-- 方案1
SELECT TOP 10
        *
FROM    [Clients]
WHERE   Status = 1
        AND ID NOT IN ( SELECT TOP 20
                                ID
                        FROM    [Clients]
                        WHERE   Status = 1
                        ORDER BY ComputerName )
ORDER BY ComputerName

--方案2
SELECT  *
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( ORDER BY ComputerName ) AS RowNo
          FROM      [Clients]
          WHERE     Status = 1
        ) t
WHERE   RowNo >= 20
        AND RowNo < 30

EF：

//Func形式
    var clients = (c => =1)
                .OrderBy(c => )
                .Skip(20)
                .Take(10)
                .ToList();

    //Linq形式
    var clients = ( from c in
                orderby
                select c).Skip(20).Take(10);

7、分组统计：

SQL：

SELECT  Status ,
        COUNT (*) AS Cnt
FROM    [Clients]
GROUP BY Status
ORDER BY COUNT (*) DESC

EF：

//Func形式
    var result = (c => )
                .Select(s => new
                {
                    Status = ,
                    Cnt = ()
                })
                .OrderByDescending(r => );

    //Linq形式
    var result = from c in
                group c by into r
                orderby () descending
                select new
                {
                    Status = ,
                    Cnt = ()
                };