将包含两列的SQL IN子句转换为LINQ

本文关键字：子句 转换 LINQ IN SQL 包含两 | 更新日期: 2023-09-27 18:06:39

你们怎么把这个查询转换成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

将包含两列的SQL IN子句转换为LINQ

我觉得你把事情弄得比实际情况更复杂了。根据您的示例数据，您只需要按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);
        }
    }
}
相关文章：