Include null cells in Linq query from DB in C# code-CodePudding

I just want to know if its possible to still retrieve null cells from a linq query, because i need these rows even when a cell is 'empty'.

The context is that i have a version control based by customer contracts, where i update their applications and update the database with lastest version build and update date. This report returns all contracts with its lastest update build and update date for each contract.

I have this query in SQL that i use to generate a report:

SELECT c.contrato, b.build, MAX(p.data_atualizacao) AS [data_atualizacao] 
FROM
(
    SELECT id_contrato, MAX(id_build_verus) AS id_build_verus FROM tbl_controle_atualizacao_contrato
    GROUP BY id_contrato
) d
JOIN tbl_controle_atualizacao_contrato P ON p.id_contrato = d.id_contrato AND p.id_build_verus = d.id_build_verus
RIGHT JOIN tbl_contrato c WITH(NOLOCK) ON p.id_contrato = c.id_contrato
LEFT JOIN tbl_build_verus b WITH(NOLOCK) ON p.id_build_verus = b.id_build_verus
WHERE c.contrato <> 0 AND c.id_empresa = 1 AND c.ativo = 1
GROUP BY c.contrato, b.build

The output is something like this:

Then in my code I've set up a linq expression that returns a query for me with the same results:

var listaTotalContratos = db.tbl_controle_atualizacao_contrato.Include(i => i.tbl_contrato).OrderByDescending(d => d.data_atualizacao).ThenByDescending(b => b.tbl_build_verus.build)
                .Where(c => c.tbl_contrato.contrato != 0 && c.tbl_contrato.ativo == true && c.tbl_contrato.id_empresa == 1)
                .Select(r => new
                {
                    contrato = r.tbl_contrato.contrato.ToString(),
                    UF = r.tbl_contrato.tbl_cliente.tbl_pessoa.tbl_endereco.FirstOrDefault().uf,
                    build = r.tbl_build_verus.build,
                    data_atualizacao = r.tbl_contrato.tbl_controle_atualizacao_contrato.Max(x => x.data_atualizacao)
                }).GroupBy(c => c.contrato).Select(s => new { contrato = s.Key, listaItens = s.ToList() }).ToList();

I've made a custom select to get values from cells because i was getting duplicate results.

The linq code works but i think it ignores the rows with empty cells.

Thank you!

CodePudding user response：

In the SQL query you are doing a LEFT JOIN on tbl_build_verus, that not exists on the linq query, thats why it is ignoring null fields. You need to translate the exact query to a linq query, with the LEFT JOIN in it, otherwise it will be doing a normal JOIN.

Your result should be something like this:

var query = 
    from d in tbl_controle_atualizacao_contrato
    join p in tbl_controle_atualizacao_contrato 
        on p.id_contrato = d.id_contrato 
        AND p.id_build_verus = d.id_build_verus
    join c in tbl_contrato 
        on p.id_contrato = c.id_contrato
    left join b in tbl_build_verus 
        ON p.id_build_verus = b.id_build_verus
        select new 
        {
            c.contrato, 
            b.build, 
            data_atualizacao = MAX(p.data_atualizacao) 
        }
    where c.contrato <> 0 AND c.id_empresa = 1 AND c.ativo = 1
    GROUP BY c.contrato, b.build;

I recommend using some type of interceptor/profiler on your development enviroment, that way you can see what query is being executed by EF in real time. See this

Interceptor example:

public class CommandInterceptor : DbCommandInterceptor
    {
        public override InterceptionResult DataReaderDisposing(DbCommand command, DataReaderDisposingEventData eventData, InterceptionResult result)
        {
            Debug.WriteLine(command.CommandText);
            return base.DataReaderDisposing(command, eventData, result);
        }

        public override int NonQueryExecuted(DbCommand command, CommandExecutedEventData eventData, int result)
        {
            Debug.WriteLine(command.CommandText);
            return base.NonQueryExecuted(command, eventData, result);
        }

        public override DbDataReader ReaderExecuted(DbCommand command, CommandExecutedEventData eventData, DbDataReader result)
        {
            Debug.WriteLine(command.CommandText);
            return base.ReaderExecuted(command, eventData, result);
        }

        public override object ScalarExecuted(DbCommand command, CommandExecutedEventData eventData, object result)
        {
            Debug.WriteLine(command.CommandText);
            return base.ScalarExecuted(command, eventData, result);
        }
    }

Interceptor registration

services.AddDbContext<Context>(contextOptions =>
            {
                contextOptions                    
                    .AddInterceptors(new CommandInterceptor())                
            });