How can I do this Aggrigate, group by, in query in LINQ?

Question

Please do not give me a full working example, I want to know how this is done rather than to get some code I can copy paste

This is the query I need, and can't for the life of me create it in LINQ.

SELECT * FROM
dbo.Schedules s, dbo.Videos v
WHERE s.VideoID = v.ID
AND s.ID IN 
(
    SELECT MAX(ID) FROM dbo.Schedules
    WHERE ChannelID = 1
    GROUP BY VideoID
)
ORDER BY v.Rating DESC, s.StartTime DESC

I have the "IN" query in LINQ I think, it's something like this

var uniqueList =  from schedule in db.Schedules
                  where schedule.ChannelID == channelID
                  group schedule by schedule.VideoID into s
                  select new
                  {
                      id = s.Max(i => i.ID)
                  };

It is possibly wrong, but now I can not check in another query for this in a where clause uniqueList.Contains(schedule.ID)

There is possibly a better way to write this query, if you have any idea I would love some hints.

I get this error and it's not making much sense.

The type arguments for method 'System.Linq.Queryable.Contains(System.Linq.IQueryable, TSource)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

Answer 1

try with .Where(sInner=> s.ID == sInner.ID).Any().

I have only been able to use .Contains in linq expressions with simple lists. Although what you posted seems to be a compile time error, once you get around it I think you'll end up getting a runtime error like I have in the past.