How to do query containing MAX and COUNT via LINQ

Navigation
2

I have the following model : 

 public class Crm_Analise
{
    [Key]
    public int id { get; set; }
    public string cod_item_CRM { get; set; }
    public string TAG { get; set; }
    public string data_creat { get; set; }
    public string modelo { get; set; }   
    public int cliente_CRM { get; set; }
}

Via Scaffolding generated controller and Views ..

I changed the View Index to: 

 <table class="table">
    <tr>          
        <th>
            @Html.ActionLink("TAG", "Index")
        </th>
        <th>
            @Html.ActionLink("ATUALIZAÇÃO", "Index")
        </th>
        <th>
            @Html.ActionLink("RELATÓRIOS", "Index")
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.TAG)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.data_creat)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.modelo)
        </td>
        <td>
            @Html.ActionLink("Visualizar", "Details") 
        </td>
    </tr>
}
</table>

So I wanted to group by TAG , being MAX(data_creat) and COUNT(modelo) . If it were via SQL, I would do: 

SELECT TAG, MAX(data_creat) AS 'ATUALIZAÇÃO', COUNT(modelo) AS 'RELATÓRIOS'
FROM Crm_Analise
WHERE cliente_CRM = @cliente_CRM
GROUP BY TAG

Then in controller did: 

// GET: Crm_Analise
        [Authorize]
        public async Task<ActionResult> Index()
        {

        if (Session["cod_cli"] != null)
        {
            int cod_cli = Convert.ToInt32(Session["cod_cli"]);

            var query = from s in db.Crm_Analise
                        select s;

            query = query.Where(s => s.cliente_CRM == cod_cli);

            return View(await query.ToListAsync());
        }
        else
        {
            return RedirectToAction("Login", "Account");
        }
    }

But how do I apply MAX , COUNT , and GROUP BY ?

Update ref. the answer from @bigown:

When trying to execute according to the response, the error was generated:

Updateref.@LINQcommentedon:

IchangedthevariablefromCounttoavariableoftypeInt:

    
asked by anonymous 14.07.2017 / 13:26

2 answers

4

Is query right? Is that what you want? So in LINQ you will use Max() , Count() and GroupBy() . I do not guarantee the exact way, but it's something like this: 

var query = from s in db.Crm_Analise
                group s by s.TAG into g
                where s.cliente_CRM == cod_cli
                select new {TAG = g.TAG, ATUALIZAÇÂO = g.Max(t => t.data_creat), RELATÓRIOS = g.Count(t => t.modelo != null)};
    
14.07.2017 / 13:55
2

The Count in SQL, when given a field, returns the count of all rows of the table minus those where the field passed is null. >

In LINQ, Count always receives a predicate and not an element, so to simulate the effect of SQL, you need to make a condition with the field. 

var query = from s in db.Crm_Analise
            where s.cliente_CRM == cod_cli
            group s by s.TAG into g
            select new 
            {
                TAG = g.TAG, 
                ATUALIZAÇÂO = g.Max(t => t.data_creat), 
                RELATÓRIOS = g.Count(t => t.modelo != null)
            };
    
14.07.2017 / 14:36
How to get data typed with angular and save in an array of objects? [closed] How to check if it already exists in bd?