Problem - Linq SQL Server: Unwanted query in database when assigning value in a database derived field

Question

All right, guys

I have the following problem:

I have the method below that only has the job of updating a field in the database, the class t_sap_log_jobs that is passed as parameter was created automatically when LINQ was configured.

public void UpdateReprocessar(List<t_sap_log_jobs> lstJob, int intStatus)
{
    try
    {
        DataContext db = new DataContext(entity.Connection);
        var transacao = new Transacao(db, () =>
        {
            foreach (var item in lstJob)
            {
                // Consulta job
                var query = (from t in entity.t_sap_log_jobs
                                     where t.cd_log_job == item.cd_log_job
                                     select t).FirstOrDefault();

                // quando atribuio o intStatus no campo query.cd_status (que é uma FK na base de dados) uma consulta // é disparada no banco de dados.
                query.cd_status = intStatus;

                // Salva alteracao no banco de dados
                entity.SaveChanges();
            }
        });

        transacao.Executar();
    }

    catch (Exception ex)
    {
        throw ex;
    }
}

After checking the database for slowness, I was able to detect through the profiler that every time I assign value to the field cd_status the following query is executed in the database. Remembering that the cd_status field is a FK of another table.

exec sp_executesql N'SELECT [Extent1].[cd_log_job] AS [cd_log_job], 
                   [Extent1].[cd_tipo_operacao] AS [cd_tipo_operacao], 
                   [Extent1].[cd_chave_atividade] AS [cd_chave_atividade], 
                   [Extent1].[dt_processamento] AS [dt_processamento], 
                   [Extent1].[dc_erro] AS [dc_erro], 
                   [Extent1].[qt_tentativa] AS [qt_tentativa], 
                   [Extent1].[dt_ultimo_processamento] AS [dt_ultimo_processamento],                                                               [Extent1].[cd_status] AS [cd_status] 
                   FROM [dbo].[t_sap_log_jobs] AS [Extent1] 
                   WHERE [Extent1].[cd_status] = @EntityKeyValue1',
    N'@EntityKeyValue1 int',
    @EntityKeyValue1=3 
    go

There must be some configuration so that LINQ does not behave this way, some idea?

Answer 1

There are a lot of weird things in your code. This is one of them:

DataContext db = new DataContext(entity.Connection);

It’s not clear what it is entity in your code. I believe it’s another context. Anyway, db is used only for the transactional scope, which is counterproductive.

The change is not in the assignment. It’s at this point here:

// Salva alteracao no banco de dados
entity.SaveChanges();

If lstJob is very large (more than 1000 records, for example), will be slow even. In this other answer I gave there are some strategies to improve performance.

Another thing that can be done is to call the entity.SaveChanges() only once, at the end of all its amendments:

        foreach (var item in lstJob)
        {
            // Consulta job
            var query = (from t in entity.t_sap_log_jobs
                                 where t.cd_log_job == item.cd_log_job
                                 select t).FirstOrDefault();

            // quando atribuio o intStatus no campo query.cd_status (que é uma FK na base de dados) uma consulta // é disparada no banco de dados.
            query.cd_status = intStatus;
        }

        // Salva alteracao no banco de dados
        entity.SaveChanges();

In practice, performance is noticeably lower because the context is optimized to work with updating multiple records at once.