How to query containing MAX and COUNT via LINQ

Asked

Viewed 1,028 times

2

I got 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 managed the controller and the views.

I changed the View Index for:

 <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:

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 the controller done:

// 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 the MAX, COUNT and the GROUP BY?

Update ref. the @Maniero response:

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

IDE mostrando o erro

Update ref. to @LINQ comments:

I changed the variable of Count for a variable of type int:

outro erro

c# asp.net-mvc .net entity-framework linq
  • 1

    How do you intend to apply a Count passing a string? You want to count the models that are not null?

  • @Linq, correct me if I’m wrong please, no sql, I can count, even if I’m the type Varchar Right? Why not here? I changed it to an INT variable and still the error continues.

  • 3

    No, you’re confusing things. You need to know what wants to count. The Count receives a predicate and not an element. And in SQL there is no "count even if it is varchar", if you put a column in Count it will be validated if that column is null.

2 answers

5


To query Are you sure? Is that what you want? Then in LINQ you will use Max(), Count() and GroupBy(). I can’t 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)};

I put in the Github for future reference.

  • Good morning, thanks for your help, but you’re returning a mistake.. I made an update on the question with the picture of the error, I also got a doubt, the enclosure Where has to come before the Group By right?

  • Answer what LINQ said to know what to do. I thought I would have this and even other problems. If that’s what he said I change the answer, just make sure it’s not null, although I find this count really weird.

  • I wanted to count, how many times the TAG repeats within the list, so the Count.. thank you.

3

The Count in SQL, when you receive a field, returns the count of all lines table least those where the field passed is null.

In the LINQ, the Count always receives a predicate and not an element, so to simulate the SQL effect, 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)
            };

  • In LINQ ... circular reference ( ° ʖ °) ...

Browser other questions tagged c# asp.net-mvc .net entity-framework linq

You are not signed in. Login or sign up in order to post.