CODEWITHSUNDEEP
c#.netsqllinq
GoRoS
GoRoS

Reputation: 5375

Convert SQL IN clause with two columns into LINQ

how would you guys transform this query into 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
)

It should be something like this:

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);

The aim is to get those unique "IdVersFirmFuente" with the highest CodVersion (if there are many)

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

The result would be:

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

Thanks in advance.

Upvotes: 2

Views: 366

Answers (1)

andriys
andriys

Reputation: 2222

I think you're making things a little more complicated than they really are. Judging by your sample data you simply need to group rows by IdVersFirmFuente, sort each group by CodVersion and select first elements from each group. Something like this:

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);
        }
    }
}

Upvotes: 4

Related Questions