Convert this SQL query to LINQ using .GroupBy() methods

Question

I run this query in SQL Server:

SELECT 
    COUNT(SERIAL) AS [Cantidad de computadoras entregadas], 
    FechaEntrega AS [Fecha de Entrega]
FROM
    Inventary
WHERE  
    Modelo = 0 AND FechaEntrega > '2001-01-01 10:48:47.0000000' 
GROUP BY 
    FechaEntrega
ORDER BY 
    FechaEntrega

I try to make that table with the C# methods but I don't get it, apparently, I am misusing the GroupBy() method

<table>  
      <thead>
            <tr>
                <th>Fecha</th>
            </tr>
        </thead>
        <tbody>
            @foreach(
             var item in Model.Inventary 
                         .Where(m => m.Modelo == 0)
                         .Where(f  => f.FechaEntrega.Year > 2005)
                         .GroupBy( e => e.FechaEntrega)          
                    )
                   {
                     <tr>
                         <td>
                            @Html.DisplayNameFor(modelItem => item.FechaEntrega)
                        </td>
                     </tr>
                   }

          </tbody>
 </table>

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. Note that your Razor is not running the same query as your SQL (where is the `Count`?) — NetMage, Nov 26 '19 at 20:35

Wamiq Rehman · Accepted Answer · 2019-11-26T23:07:11.443

The conversion of your sql query to linq would be something like this. You can do this in your controller and then return your result to the view
Please see the working fiddle

var model =  Inventary.Where(m => m.Modelo == 0)
                         .Where(f  => f.FechaEntrega.Year > 2005)
                         .GroupBy( e => e.FechaEntrega.Date)
                         .Select(p=> new YourCustomModelClass 
                          {
                              Fecha  =  p.Key,
                              Count  = p.Count()
                          });


public class YourCustomModelClass 
{
   public Datetime Fecha {set; get;}
   public int Count {set; get;}
}

Convert this SQL query to LINQ using .GroupBy() methods

1 Answers1