How to write this query in LINQ?

Question

Following query is used for Getting Categories and one news for each category. How can I write this query using LINQ

SELECT * FROM News n where n.NewsID IN 
(SELECT TOP 1 NewsID FROM News v
WHERE v.CategoryID = n.CategoryID 
ORDER BY CreatedOn DESC)

Thanks in advance.

Answer 1

Not tested, but try something like this:

using (var db = new YourDataContext())
{
   var results = from n in db.News 
                 let v = db.News
                 where n.NewsId == v.Where(c=>c.CategoryId == n.CategoryId)
                    .OrderByDescending(o=>o.CreatedOn).First()
                 select n;
}

Answer 2

var q = from n in dc.News
        group n by n.CategoryId into g
        let ti = g.OrderByDescending(x => x.CreatedOn).FirstOrDefault()
        where ti != null
        select ti;

Answer 3

Here it is in VB:

Using db = New YourDataContext()
   Dim results = From n In db.News _
       Let v = db.News _
       Where n.NewsId = v.Where(Function(c) c.CategoryId = n.CategoryId).OrderByDescending(Function(o) o.CreatedOn).First() _
       Select n
End Using

Converted with: http://www.developerfusion.com/tools/convert/csharp-to-vb/