c# Linq select join on select group by 收藏

Question

我有这个 MS-SQL 语句:

SELECT cv.id FROM ContactValue cv
INNER JOIN (
        SELECT mainId, max(version) as v
        FROM ContactValue
        WHERE version <= $Version(int)
        GROUP BY mainId
) 
AS t ON t.mainId = cv.mainId AND t.v = cv.version 
WHERE cv.contact_id = $ContactID(int) 
      AND cv.isActive = 1 
      ORDER BY sort'

并希望在 linq 中实现。我确实将上面的查询分为多个查询，但性能并不快。是否存在任何linq to linq连接

我的 C# 代码:

            var groupMax = from cv in db.ContactValue
                           where cv.contact_id == ContactID && cv.version <= Version
                           orderby cv.sort
                           group cv by cv.mainId into gcv
                           select new { mainID = gcv.Key, version = gcv.Max(cv => cv.version) };

            foreach (var data in groupMax.ToList())
            {
                var Query = from cv in db.ContactValue
                            where cv.contact_id == ContactID && cv.mainId == data.mainID && cv.version == data.version && cv.isActive == true
                            select cv;

                if (Query.Count() > 0)
                {
                    ContactValue tmp = Query.First();
                }
            }

我很乐意让所有联系人都有 1-2 个查询而不是 1 个查询，然后每个联系人都有另一个查询...

请帮帮我!

Answer 1

是的，Linq to SQL 确实实现了内部连接:

var groupMax =
    from cv in db.ContactValue
    where cv.contact_id == ContactID && cv.version <= Version
    orderby cv.sort
    group cv by cv.mainId into gcv
    select new { mainID = gcv.Key, version = gcv.Max(cv => cv.version) };

var res =
    from cv in db.ContactValue
    join gm in groupMax on cv.version equals gm.version
    where cv.contact_id == ContactID && cv.isActive
    orderby cv.version ascending /*for example*/
    select cv