NOT IN in Inline using a sub-query

Question

I am using as a basis the answer in: https://stackoverflow.com/questions/432954/not-in-clause-in-linq-to-entities

I need to make a query where do not contain (NOT IN) the Ids coming from another query.

The SQL Query is this:

SELECT DISTINCT tbl_boleto.int_IDC, tbl_Admin.* //redução do código
FROM            tbl_boleto INNER JOIN
                         tbl_Admin ON tbl_boleto.int_IDC = tbl_Admin.intid
WHERE        (tbl_boleto.int_IDC NOT IN
                             (SELECT DISTINCT int_IDC
                               FROM            tbl_boleto 
                               WHERE        (sdt_DataReferencia = '20160401'))) AND (tbl_Admin.int_STATUS IN (4, 5))
ORDER BY tbl_Admin.int_STATUS, tbl_Admin.str_URL

I’m trying to:

    var condicaoStatus = new[] { 4, 5 };
    var IdClientesSemBoleto = (from cli in db.Clientes
                             join bol in db.Boletos on cli.ClienteId equals bol.ClienteId
                             where bol.DataReferencia == DataRef && condicaoStatus.Contains(cli.Status)
                             select new {IDC = cli.ClienteId });

        var clientes = db.Boletos
            .Include(i => i.Cliente)
            .Select(s => s.ClienteId)
             .Where(s => !IdClientesSemBoleto.Contains(s.ClienteId));

But the line:

 .Where(s => !IdClientesSemBoleto.Contains(s.ClienteId));

Of error:

Error CS1061 'int' does not contain a Definition for 'Clienteid' and no Extension method 'Clienteid' Accepting a first argument of type 'int' could be found (are you Missing a using Directive or an Assembly Reference?)

What am I doing wrong?

How to do it? There is a better way to be done than this solution of mine?

Answer 1

In doing select new {IDC = cli.ClienteId } the content of IdClientesSemBoleto is a list of an anonymous type, which only contains the property IDC. You don’t need the creation of this kind, one can just do select cli.ClienteId. This will make it IdClientesSemBoleto be a list of int's, which will facilitate the select below.

This code snippet

var clientes = db.Boletos
    .Include(i => i.Cliente)
    .Select(s => s.ClienteId)
    .Where(s => !IdClientesSemBoleto.Contains(s.ClienteId));

Switch to

var clientes = db.Boletos
    .Include(i => i.Cliente)
    .Where(s => !IdClientesSemBoleto.Contains(s.ClienteId))
    .Select(s => s.ClienteId);

Answer 2

The problem in the order in which you are doing things. First, in this line:

.Select(s => s.ClienteId)

Here you are turning your entire LINQ query into a IEnumerable<int>, because you are selecting only the ClienteId

So, on this line:

.Where(s => !IdClientesSemBoleto.Contains(s.ClienteId));

The s does not contain property ClienteId because the very s is the ClienteId! It is the integer you selected in the previous row.

So there are two ways to solve your problem:

Change the order of your query thus:

var clientes = db.Boletos
    .Include(i => i.Cliente)
    .Where(s => !IdClientesSemBoleto.Contains(s.ClienteId));
    .Select(s => s.ClienteId)

Or keep the query the way it is, only making changes in the Where():

var clientes = db.Boletos
    .Include(i => i.Cliente)
    .Select(s => s.ClienteId)
    .Where(s => !IdClientesSemBoleto.Contains(s));