LINQ update of all listed items (changing the status of all listed items)

1

I have a Linq that displays data listed according to the required filters (date and unit) by the user and another filter to validate a code with another table (which are not the case). I would like to know how I can from a "reconcile" button, change the status (NFBP_STA_LANCTO) of all items listed in this table.

            int _idUnidade = int.Parse(cboUnidade.SelectedValue);
            DateTime _dtCred = DateTime.Parse(txtCred.Text, new CultureInfo("pt-br"));

            using (NFSeDataContext context = new NFSeDataContext(ConnNFSe))
            {
                context.Connection.Open();

                var _s = (from p in context.NFS_Parcelas
                          join q in context.NFBP_CONCILIACAO_BRASPAGs on p.NFPA_NR_PARCELA equals q.NFBP_NR_PARCELA
                          where
                            _idUnidade == p.NFPA_CD_UNIDADE
                           && _dtCred == p.NFPA_DT_MOVIMENTO
                           && SqlMethods.Like(p.NFPA_TX_COMPLEMENTO, "%" + q.NFBP_TID + "%")
                          orderby p.NFPA_NR_PARCELA
                          select new
                          {
                              q.NFBP_STA_LANCTO,
                              p.NFPA_CD_UNIDADE,
                              p.NFPA_DT_MOVIMENTO,
                              q.NFBP_COD_AUTORIZACAO,
                              q.NFBP_TID,
                              q.NFBP_VL_BRUTO_TRANSACAO,
                              q.NFBP_VL_LIQUIDO_TRANSACAO,
                              p.NFPA_NR_PARCELA,
                              q.NFBP_BANDEIRA
                          }).ToList();

                context.Connection.Close();
                grvConciliacaoBraspag.DataSource = _s;
                grvConciliacaoBraspag.DataBind();

Image to make the front-end as clear as possible:

    
asked by anonymous 29.02.2016 / 21:31

2 answers

2

First of all, do not use these two commands:

context.Connection.Open();
context.Connection.Close();

The Entity Framework closes the connection when necessary. You do not have to do this manually.

This selection is good for grid, but not for rest, so do the following:

var _s = (from p in context.NFS_Parcelas
                      join q in context.NFBP_CONCILIACAO_BRASPAGs on p.NFPA_NR_PARCELA equals q.NFBP_NR_PARCELA
                      where
                        _idUnidade == p.NFPA_CD_UNIDADE
                       && _dtCred == p.NFPA_DT_MOVIMENTO
                       && SqlMethods.Like(p.NFPA_TX_COMPLEMENTO, "%" + q.NFBP_TID + "%")
                      orderby p.NFPA_NR_PARCELA
                      select new {
                          Parcela = p, ItemConciliacao = q
                      }).ToList();

When sending to Grid, you select the fields you need:

var dsGrid = _s.Select(new { 
                          ItemConciliacao.NFBP_STA_LANCTO,
                          Parcela.NFPA_CD_UNIDADE,
                          Parcela.NFPA_DT_MOVIMENTO,
                          ItemConciliacao.NFBP_COD_AUTORIZACAO,
                          ItemConciliacao.NFBP_TID,
                          ItemConciliacao.NFBP_VL_BRUTO_TRANSACAO,
                          ItemConciliacao.NFBP_VL_LIQUIDO_TRANSACAO,
                          Parcela.NFPA_NR_PARCELA,
                          ItemConciliacao.NFBP_BANDEIRA
                      }).ToList();

Having _s , it's simple:

foreach (var parcela in _s.Select(s => s.Parcela).ToList())
{
    parcela.NFBP_STA_LANCTO = /* Coloque aqui o status novo */
    context.Entry(parcela).State = EntityState.Modified;
}

context.SaveChanges();
    
29.02.2016 / 22:47
0

For you to change the value of a record of a given EntityFramework entity, you need to fetch the object you want to change. In this list "_s", you can not change the value of anything because it is not an object of the entity "NFBP_CONCILIACAO_BRASPAGs", "_s" is just a list of a new anonymous object that you create using the data of the entities that you select via LINQ.

You can return in this your anonymous object the key needed to fetch a record from the "NFBP_CONCILIACAO_BRASPAGs" table. This way you can select the exact object to update the value.

Example:

foreach (var record in _s) 
{
    var nfpb = context.NFBP_CONCILIACAO_BRASPAGs.Single(a => a.chave == record.chave && a.chave2 == record.chave2); // Se for chave composta, se for um id pode ignorar o &&
    nfpb.NFBP_STA_LANCTO = novo_valor;
}

context.SaveChanges();

Another option would be for you to return instead of an anonymous object with the values, return an anonymous object with the objects of the entities, but this solution will impact the view that uses this list "_s". So maybe you have to do an exactly the same query, but by changing what is selected.

            var _s = (from p in context.NFS_Parcelas
                      join q in context.NFBP_CONCILIACAO_BRASPAGs on p.NFPA_NR_PARCELA equals q.NFBP_NR_PARCELA
                      where
                        _idUnidade == p.NFPA_CD_UNIDADE
                       && _dtCred == p.NFPA_DT_MOVIMENTO
                       && SqlMethods.Like(p.NFPA_TX_COMPLEMENTO, "%" + q.NFBP_TID + "%")
                      orderby p.NFPA_NR_PARCELA
                      select new
                      {
                          Parcela = p,
                          Conciliacao = q
                      }).ToList();

foreach (var record in _s) 
{      
    record.Conciliacao.NFBP_STA_LANCTO = novo_valor;
}

context.SaveChanges();
    
29.02.2016 / 23:02