Grouping query by date

Question

I am developing a query with C#, but I have a problem to accomplish it. I need to group by date.

Code:

public void BuscarVendaDeProdurtos_Saidas()
{
    var data1 = Convert.ToDateTime(dateINI_Saida.Text);
    var data2 = Convert.ToDateTime(dateFINI_Saida.Text);
    var cod = Convert.ToInt64(textBox8.Text);

    var pesquisa = from p in dc.ITEMVENDA
    orderby p.CD_PRODUTO descending
    where p.CD_PRODUTO == cod && p.Data_Venda >= data1 && p.Data_Venda <= data2
    select new
    {

        p.NM_PROD,
        p.Data_Venda,
        p.QT_PRODV,
    };

    dataGridView2.DataSource = pesquisa.ToList();

    Soma = 0;
    foreach (DataGridViewRow dr in dataGridView2.Rows)
    Soma += Convert.ToDecimal(dr.Cells[2].Value);
    textBox12.Text = Convert.ToString(string.Format("{0:n}", Soma));
}

This research returns this:

Data Produto      Qt
20/10/2017……………….  3
20/10/2017 …………….. 1

I need something like that:

Data Produto     Qt
20/10/2017………………. 4

A single date already with its amounts summed.

Answer 1

Try group by with a date formatted with dd/mm/yyyy

public void BuscarVendaDeProdurtos_Saidas()
{
    var data1 = Convert.ToDateTime(dateINI_Saida.Text);
    var data2 = Convert.ToDateTime(dateFINI_Saida.Text);
    var cod = Convert.ToInt64(textBox8.Text);

    var pesquisa = from p in dc.ITEMVENDA
    orderby p.CD_PRODUTO descending
    where p.CD_PRODUTO == cod && p.Data_Venda >= data1 && p.Data_Venda <= data2
    select new
    {

        p.NM_PROD,
        p.Data_Venda,
        p.QT_PRODV,
    };

    dataGridView2.DataSource = pesquisa // .ToList() 
    //.OrderBy(x => DateTime.ParseExact(x.Data_Venda, "dd/MM/yyyy", null))
        .OrderBy(x => x.Data_Venda.ToString("dd/mm/yyyy"))
        .ToList();
    ;

    Soma = 0;
    foreach (DataGridViewRow dr in dataGridView2.Rows)
    Soma += Convert.ToDecimal(dr.Cells[2].Value);
    textBox12.Text = Convert.ToString(string.Format("{0:n}", Soma));
}

Answer 2

You can use the Groupby from the LIN to group.

I don’t know what the attributes name of your object is but try to change this line:

dataGridView2.DataSource = pesquisa.ToList();

For something like this:

dataGridView2.DataSource = pesquisa.GroupBy(x => x.Data_Venda, x => x.Quantidade).ToList();

I believe this function will group the sales dates adding up the quantities