之前一直用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是做一次连接还是多长，结果验证是内部做处理，一次链接完成，直接颠覆了我以前的观念。先记录到这里