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

Question

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 to do it in English or lick. Original question with doubts in NOT IN

At the end of everything I managed doing so First creating the list of integers that will be part of the not in Then making the appointment and the third block was just to make a distinct!

There’s a way to make this up?

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

Answer 1

I wrote this query by Tablet, so maybe there are basic syntax errors which 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);