c# - 将具有两列的SQL IN子句转换为LINQ

你们如何将这个查询转换为Linq？

SELECT *
FROM Test T1
WHERE  (convert(VARCHAR,T1.IdVersFirmFuente) + convert(VARCHAR,T1.IdVersLib))
IN
(
      Select TOP 1 convert(VARCHAR,T2.IdVersFirmFuente) + convert(VARCHAR,T2.IdVersLib)
      From Test T2
      Where T2.IdVersFirmFuente = T1.IdVersFirmFuente
      ORDER BY T2.CodVersion DESC
)

应该是这样的：

var Resul = (from u in nDT2.AsEnumerable()
            where (String.Concat(u.Field<int>("IdVersionLibreria").ToString(),u.Field<int>("IdVersionFirmwareFuente").ToString()))
                    .Contains(
                        (from y in nDT2.AsEnumerable()
                        where y.Field<int>("IdVersionFirmwareFuente") == u.Field<int>("IdVersionFirmwareFuente")
                        orderby y.Field<String>("CodVersion") descending
                        select String.Concat(y.Field<int>("IdVersionLibreria").ToString(),y.Field<int>("IdVersionFirmwareFuente").ToString())
                        ).Take(1))
            select u);

目的是获得具有最高CodVersion（如果有很多）的唯一“ IdVersFirmFuente”

IdVersFirmFuente IdVersLib IdVersion CodVersion
==
236              628       628       1.0.0.0
236              629       629       1.0.0.1
237              628       628       1.0.0.0
239              628       628       1.0.0.0

结果将是：

236              629       629       1.0.0.1
237              628       628       1.0.0.0
239              628       628       1.0.0.0

提前致谢。

最佳答案

我认为您正在使事情变得比实际复杂得多。从样本数据来看，您只需要按IdVersFirmFuente对行进行分组，按CodVersion对每个组进行排序，然后从每个组中选择第一个元素。像这样：

class Program
{
    public class Row
    {
        public int IdVersFirmFuente { get; set; }
        public int IdVersLib { get; set; }
        public int IdVersion { get; set; }
        public string CodVersion { get; set; }
    }

    static void Main()
    {
        var data = new[]
                       {
                           new Row { IdVersFirmFuente = 236, IdVersLib = 628, IdVersion = 628, CodVersion = "1.0.0.0" },
                           new Row { IdVersFirmFuente = 236, IdVersLib = 629, IdVersion = 629, CodVersion = "1.0.0.1" },
                           new Row { IdVersFirmFuente = 237, IdVersLib = 628, IdVersion = 628, CodVersion = "1.0.0.0" },
                           new Row { IdVersFirmFuente = 239, IdVersLib = 628, IdVersion = 628, CodVersion = "1.0.0.0" }
                       };

        var result = from u in data
                     group u by u.IdVersFirmFuente into g
                     select g.OrderByDescending(e => e.CodVersion).First();

        foreach (var row in result)
        {
            Console.WriteLine("{0,-5}{1,-5}{2,-5}{3,-10}", row.IdVersFirmFuente, row.IdVersLib, row.IdVersion, row.CodVersion);
        }
    }
}

关于c# - 将具有两列的SQL IN子句转换为LINQ，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7203912/