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

Question

I have a Linq that presents data listed according to the required filters (date and unit) by the user and another filter to validate a code with another table (which is not the case). I wonder how I can from a "reconcile" button, change the status (NFBP_STA_LANCTO) of all the 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 as clear as possible what is being done(front-end):

Answer 1

First of all, do not use these two commands:

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

Entity Framework closes the connection when necessary. You don’t have to do this manually.

This selection is good for grid, but not for the 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, becomes 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();

Answer 2

In order for you to change the value of a record of a particular Entityframework entity, you need to search for the object you want to change. In this list "_s", it is not possible to 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 you select via LINQ.

You can return in this your object anonymously the key needed to search for a record of the table "Nfbp_conciliacao_braspags". 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 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". Then you may have to make a query exactly the same, but 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();