Convert a SQL Server Query to C# LINQ having ROW_NUMBER() and OVER

Question

i need to convert this query to C# LINQ but i have no idea how start. thank you for your time.

SELECT s.TextId, s.Title, s.CategoryId, s.Name, s.DateSent, Row
FROM 
   (SELECT t.TextId, t.Title, t.CategoryId, c.Name, t.DateSent, 
        ROW_NUMBER() OVER (PARTITION BY t.CategoryId ORDER BY t.datesent DESC) AS Row
    FROM Concept_Text t 
    JOIN Concept_Text_Categories c 
      ON t.CategoryId = c.CategoryId 
    JOIN Concept_Text_CategoryToPlugin cp 
      ON c.CategoryId = cp.CategoryId 
    JOIN Concept_Text_Plugins p 
      ON cp.PluginId = p.PluginId
    WHERE p.type = 12 AND (t.IsPublished = 'True') AND (Visible = 'True')
    GROUP BY t.TextId, t.Title, t.CategoryId, c.Name, t.DateSent) s
WHERE Row <=12

with help, so far, i got this

(from t in Concept_Text
   join c in Concept_Text_Categories on t.CategoryId equals c.CategoryId
   join cp in Concept_Text_CategoryToPlugin on c.CategoryId equals cp.CategoryID
   join p in Concept_Text_Plugins on cp.PluginID equals p.PluginID
   where p.Type == 12 && t.IsPublished && t.Visible
   group cp by new { t.TextId, t.Title, t.CategoryId, c.Name, t.DateSent } into gr
   orderby gr.Key.DateSent descending
   select new
   {
       gr.Key.TextId,
       gr.Key.Title,
       gr.Key.CategoryId,
       gr.Key.Name,
       gr.Key.DateSent
   })

the only problem now is the we need to get 12 enteries of each category.

Answer 1

(from c in Concept_Text_Categories
                            join cp in Concept_Text_CategoryToPlugin
                            on c.CategoryId equals CategoryID
                            join p in Concept_Text_Plugins
                            on PluginID equals p.PluginID
                            where p.Type == 12
                            && c.Enabled
                            && p.Enabled
                            orderby c.Name ascending
                            select new ()
                            {
                                CategoryId = c.CategoryId,
                                Name = c.Name,
                                Materias = (from t in Concept_Text
                                        where t.CategoryId == c.CategoryId
                                        && t.IsPublished
                                        && t.Visible
                                        orderby t.DateSent
                                        select new ()
                                        {
                                            TextId = t.TextId,
                                            Title = t.Title
                                        }).Take(12)
                            })

Answer 2

I think something like this:

var query = 
(
   from t in Concept_Texts
   join c in Concept_Text_Categories on t.CategoryId equals c.CategoryId
   join cp in Concept_Text_CategoryToPlugin on c.CategoryId equals cp.CategoryId
   join p in Concept_Text_Plugins on cp.CategoryId equals p.CategoryId

   where p.type = 12 && t.IsPublished == "True" AND t.Visible == "True"
   group cp by new {t.TextId, t.Title, t.CategoryId, c.Name, t.DateSent} into gr
   select new {
              gr.Key.TextId, 
              gr.Key.Title, 
              gr.Key.CategoryId, 
              gr.Key.Name, 
              gr.Key.DateSent,
              MinC = gr.Min(gcp=>gcp.CategoryId },
              MaxC = gr.Max(gcp=>gcp.CategoryId }
).Where(c=>c.CategoryId >= c.MinC && c.CategoryId <= c.MaxC)
 .OrderByDescending(c=>c.DateSent)
 .Skip(0).Take(12);

Can you test this with LINQ Pad? see what SQL produces?