LINQ inner join

sql语句：

　　

select guid from Project_TaskVersion a
inner join
(
    select max(Version) maxversion,ProjectId from Project_TaskVersion
    group by ProjectId
) b
on a.Version=b.maxversion and a.ProjectId=b.ProjectId

 

对应的linq 语句（基于方法的查询语法）：

DbContext:

public DbSet<Project_TaskVersion> ProjectTaskVersion { get; set; }//表

 

查询：

　　　　　　　var maxVersions = ProjectVersion.GroupBy(g => g.ProjectId).Select(s => new
            {
                Version = s.Max(m => m.Version),
                ProjectId = s.Key
            });
            var result = ProjectVersion.Join(
                maxVersions,//join对象
                outer => new { outer.Version, outer.ProjectId },//外部key
                inner => new { inner.Version, inner.ProjectId },//内部key
                (outer, inner) => new //结果
                {
                    LatestVersion = outer.Guid
                })
                .Select(s => s.LatestVersion);
            return result.ToList();

 

对应的linq语句（基于表达式的查询语法）：

 

 　　　　　　var taskVersions = ProjectTaskVersion;

            //使用Group
            var maxVersions = from item in taskVersions
                              group item by item.ProjectId into grouping
                              select new
                              {
                                  ProjectId = grouping.Key,
                                  Version = grouping.Max(m => m.Version)
                              };
            //join查询语句
            var result = from taskVersion in taskVersions
                         join maxVersion in maxVersions
                         on new { taskVersion.Version, taskVersion.ProjectId }
                         equals new { maxVersion.Version, maxVersion.ProjectId }
                         select new
                         {
                             LatestVersion = taskVersion.Guid
                         }.LatestVersion;
            return result.ToList();