CODEWITHSUNDEEP
c#sqllinq
Anthony Ovalles
Anthony Ovalles

Reputation: 11

How to convert SELECT TOP 1 WITH TIES query to LINQ C#?

I am trying to execute a select top 1 with ties, but I'm getting trouble to do so.

I need all the last records of orderdetails, some orders can have more than one article in the list.

var qry = (from ordd in db.OrderDetails
           join ord in db.Orders on ordd.OrderId equals ord.OrderId
           join cust in db.Salespersons on ord.SalespersonId equals cust.SalespersonId
           join comp in db.Companies on ord.CompanyId equals comp.CompanyId
           join pro in db.Products on ordd.ProductId equals pro.ProductId                              
           where (ord.OrderId == ordd.OrderId && pro.ProductId == ordd.ProductId)
           orderby ordd.OrderId descending
           select new { ordd })
           .Distinct()
           .ToList();

           foreach (var item in qry)
           {
               dt.Rows.Add(item.ordd.Reference, 
                           item.ordd.Quantity,
                           item.ordd.Description, 
                           item.ordd.Price, 
                           item.ordd.Price * item.ordd.Quantity);
           }

Upvotes: 1

Views: 1749

Answers (1)

Drag and Drop
Drag and Drop

Reputation: 2734

Here is a more generic answer, with a custom object it will be easier to explain.
GroupBy on the field you want order on. This way all the row with the same orderID will be together.
Order Desc on this key, and the first group will be all the row with the max orderID

var input = new Foo[] {
    new Foo{GroupId=1,Label="a" },
    new Foo{GroupId=2,Label="b" },
    new Foo{GroupId=3,Label="c" },
    new Foo{GroupId=3,Label="d" },
    new Foo{GroupId=2,Label="e" },
    new Foo{GroupId=4,Label="Bar" },
    new Foo{GroupId=4,Label="Bar" }
};

var result = input.Where(x => x.Label!="Bar")         //-> { a, b, c, d, e }
                    .GroupBy(x => x.GroupId)          //-> {1: a} {2: b, e} {3: c, d}
                    .OrderByDescending(x=> x.Key)     //-> {3: c, d} {2: b, e} {1: a} 
                    .First();

Upvotes: 1

Related Questions