How can I do this Aggrigate, group by, in query in LINQ?
- by Ólafur Waage
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.
