Linq + Lambda, subquery, distinct and not in, how to improve this query

Question

Linq + Lambda, subquery, distinct and not in, how to improve this query

Navigation

#1 by (0 votes)

2

I had the following syntax in SQL

SELECT  tbl_boleto.int_IDC, tbl_Admin.int_STATUS, tbl_Admin.int_CONVENIO, tbl_Admin.int_PLANO, tbl_Admin.int_RESTRICAO, tbl_Admin.str_URL
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 = '20160501'))) AND (tbl_Admin.int_STATUS IN (4, 5))
ORDER BY tbl_Admin.int_STATUS, tbl_Admin.str_URL

I tried doing linq or lambda. Original question with NOT IN inquiries

At the end of it all I got it done like this First creating the integer list that will be part of not in After doing the query and the third block was just to make a distinct !

Can you improve this game?

    var IdClientesSemBoleto = (from cli in db.Clientes
                               join bol in db.Boletos on cli.ClienteId equals bol.ClienteId
                               where bol.DataReferencia == DataRef
                               select cli.ClienteId).Distinct();

    var condicaoStatus = new[] { 4, 5 };
    var clientes = db.Boletos
        .Include(i => i.Cliente)
        .Where(s => !IdClientesSemBoleto.Distinct().Contains(s.ClienteId))
        .Where(i => condicaoStatus.Contains(i.Cliente.Status))
        .OrderBy(o => o.Cliente.Status) //status == byte
        .OrderBy(o => o.Cliente.Url); //url == string

    IEnumerable<boleto> clientesFiltrados= clientes
      .GroupBy(customer => customer.ClienteId)
      .Select(group => group.FirstOrDefault()).ToList();

c# entity-framework linq asp.net-mvc

asked by anonymous 08.03.2016 / 20:30

1 answer

How to implement Actionbar and ViewPager as in Tinder View SplashScreen with progress while JFrame loads

score 0 · Accepted Answer

I wrote this query through the Tablet, so maybe there are basic syntax errors that you can easily adapt.

Here is an example of a query using left join:

var clientes = (from cliente in db.Clientes
join boleto in db.Boletos on cliente.ClienteId equals boleto.ClienteId 
and boleto.DataReferencia equals DataRef into boletos
from boleto in boletos.DefaultIfEmpty()
select new { cliente, boleto })
.Where(c=> c.boleto == null)
.Select(c => c.cliente);