NOT IN in linq using a subquery

Question

NOT IN in linq using a subquery

Navigation

#1 by (3 votes)
#2 by (1 votes)

1

I'm using the answer as the basis on: link

I need to make a query where it does 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:

    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));

Error:

Error CS1061 'int' does not contain a definition for 'ClientId' and no extension method 'ClientId' 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? is there a better way to be done than this my solution?

c# entity-framework linq asp.net-mvc

asked by anonymous 08.03.2016 / 16:45

2 answers

1

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

.Select(s => s.ClienteId)

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

So, in this line:

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

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

Then there are two ways to solve your problem:

Change the order of your query, like this:

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, by just changing the Where() :

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

08.03.2016 / 17:55

Is it possible to send email with xampp from a local server? Show occurrences as fields

score 3 · Accepted Answer

When doing select new {IDC = cli.ClienteId } the content of IdClientesSemBoleto is a list of an anonymous type, which only contains the IDC property. You do not need the creation of this type, you can only do select cli.ClienteId . This will make IdClientesSemBoleto 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);