LINQ用法总结

之前一直用sql和拉姆达表达式，一直感觉linq不好用。用熟练了感觉期功能好强大，查询性能例外考究。这里讲讲基本用法。

内联查询：

            var list2 = (from a in db.Role where a.IsDel==true
                         join b in db.UserRole on a.Id equals b.RoleId where b.Id>0
                         where a.Id > 0 
                         select new
                         {
                             a.Id,
                             b.RoleId
                         }
                        ).ToList();

跟踪sql得到：

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent2].[RoleId] AS [RoleId]
    FROM  [dbo].[Role] AS [Extent1]
    INNER JOIN [dbo].[UserRole] AS [Extent2] ON [Extent1].[Id] = [Extent2].[RoleId]
    WHERE (1 = [Extent1].[IsDel]) AND ([Extent2].[Id] > 0) AND ([Extent1].[Id] > 0)

左连表查询：

            var list3 = (from a in db.Role
                         join b in db.UserRole on a.Id equals b.RoleId into temp
                         from ur in temp.DefaultIfEmpty()
                         select new
                         {
                             a.Id,
                             ur.UserId
                         }).ToList();

sql：

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent2].[UserId] AS [UserId]
    FROM  [dbo].[Role] AS [Extent1]
    LEFT OUTER JOIN [dbo].[UserRole] AS [Extent2] ON [Extent1].[Id] = [Extent2].[RoleId]

order by:

            var list4 = (from a in db.Role
                         join b in db.UserRole on a.Id equals b.RoleId 
                         group a by a.Id into g
                         select new
                         {
                             g.Key,
                             Name=g.Max(x=>x.Name)
                         }).ToList();

sql:

SELECT 
    [GroupBy1].[K1] AS [Id], 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        [Extent1].[Id] AS [K1], 
        MAX([Extent1].[Name]) AS [A1]
        FROM  [dbo].[Role] AS [Extent1]
        INNER JOIN [dbo].[UserRole] AS [Extent2] ON [Extent1].[Id] = [Extent2].[RoleId]
        GROUP BY [Extent1].[Id]
    )  AS [GroupBy1]

in 查询：like的查询和in写法差不多，都是用Contains

            int[] arr = { 1, 2 };
            var list1 = (from a in db.Role
                         where arr.Contains(a.Id)
                         select new Test
                         {
                             Id = a.Id,
                             list = db.UserRole.Where(x => x.RoleId == a.Id).ToList()
                         }
                       ).ToList();

sql：

SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[C1] AS [C1], 
    [Project1].[Id1] AS [Id1], 
    [Project1].[UserId] AS [UserId], 
    [Project1].[RoleId] AS [RoleId]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent2].[Id] AS [Id1], 
        [Extent2].[UserId] AS [UserId], 
        [Extent2].[RoleId] AS [RoleId], 
        CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[Role] AS [Extent1]
        LEFT OUTER JOIN [dbo].[UserRole] AS [Extent2] ON [Extent2].[RoleId] = [Extent1].[Id]
        WHERE [Extent1].[Id] IN (1, 2)
    )  AS [Project1]
    ORDER BY [Project1].[Id] ASC, [Project1].[C1] ASC

上面in查询，我研究稍微深入了点，单独写查询看linq是做一次连接还是多长，结果验证是内部做处理，一次链接完成，直接颠覆了我以前的观念。先记录到这里