Convert SQL to LINQ, nested select, top, "distinct" using group by and multiple order bys

Question

I have the following SQL query, which I'm struggling to convert to LINQ.

Purpose: Get the top 10 coupons from the table, ordered by the date they expire (i.e. list the ones that are about to expire first) and then randomly choosing one of those for publication.

Notes: Because of the way the database is structured, there maybe duplicate Codes in the Coupon table. Therefore, I am using a GROUP BY to enforce distinction, because I can't use DISTINCT in the sub select query (which I think is correct). The SQL query works.

SELECT TOP 1
    c1.*
FROM
    Coupon c1
WHERE
    Code IN (
        SELECT TOP 10
            c2.Code
        FROM
            Coupon c2
        WHERE
            c2.Published = 0
        GROUP BY
            c2.Code,
            c2.Expires
        ORDER BY
            c2.Expires
    )
ORDER BY NEWID()

Update: This is as close as I have got, but in two queries:

var result1 = (from c in Coupons
        where c.Published == false
        orderby c.Expires
        group c by new { c.Code, c.Expires } into coupon
        select coupon.FirstOrDefault()).Take(10);

var result2 = (from c in result1
        orderby Guid.NewGuid()
        select c).Take(1);

score 0 · Accepted Answer · answered Mar 02 '11 at 15:52

Here's one possible way:

from c in Coupons
from cs in 
    ((from c in coupons
        where c.published == false
        select c).Distinct()
        ).Take(10)
where cs.ID == c.ID
select c

Keep in mind that LINQ creates a strongly-typed data set, so an IN statement has no general equivalent. I understand trying to keep the SQL tight, but LINQ may not be the best answer for this. If you are using MS SQL Server (not SQL Server Compact) you might want to consider doing this as a Stored Procedure.

score 0 · Answer 2 · edited May 23 '17 at 11:55

Using MercurioJ's slightly buggy response, in combination with another SO suggested random row solution my solution was:

var result3 = (from c in _dataContext.Coupons
                from cs in
                    ((from c1 in _dataContext.Coupons
                        where
                        c1.IsPublished == false
                        select c1).Distinct()
                        ).Take(10)
                where cs.CouponId == c.CouponId
                orderby _dataContext.NewId()
                select c).Take(1);

Convert SQL to LINQ, nested select, top, "distinct" using group by and multiple order bys

2 Answers2