Transform SQL Query to LINQ

Question

I would like if someone helps me to convert this SQL Query to LINQ syntax.

SELECT i.Id, i.Condomino as Condomino, i.Interno as Interno, 
    p.NomePiano as NomePiano, s.Nome as NomeCondominio,
    m.millesimi_fabbisogno_acs, m.millesimi_fabbisogno_riscaldamento
    FROM Interni i
    INNER JOIN Piani p ON i.IdPiano = p.Id
    INNER JOIN Stabili s ON i.IdStabile = s.Id
    LEFT JOIN MillesimiTabellaC m ON i.Id = m.idInterno
    WHERE s.IdCondominio = {0}

I tried using something like this, but is not working..

return _Db.Interni.Include("Piani").Where(x => x.Piani.IdCondominio == iidcond).ToList();

Checkout this answer: https://stackoverflow.com/a/2767742/1970317 — EvZ, Nov 23 '17 at 10:42

Peter Szekeli · Accepted Answer · 2017-11-23T11:31:36.053

I made it on-the-spot (so it's not tested), but perhaps it's enough to give you the idea. I'm also assuming that your DB model has foreign keys set up.

var result = _db.Interni
                .Where(i => i.Stabili.IdCondominio = [value])
                .Select(i => new
                             {
                                 i.Id,
                                 Condomino = i.Condomino, 
                                 Interno = i.Interno,
                                 NomePiano = i.Piani.NomePiano,
                                 NomeCondominio = i.Stabili.Nome,
                                 i.MillesimiTabellaC.millesimi_fabbisogno_acs, 
                                 i.MillesimiTabellaC.millesimi_fabbisogno_riscaldamento
                             })
                .ToList();

update

In case you don't have a foreign key between Interni and MillesimiTabellaC, try this:

var result = _db.Interni
                .Include(i => i.Piani)
                .Include(i => i.Stabili)
                .Where(i => i.Stabili.IdCondominio = [value])
                .Select(i => new
                             {
                                Interni = i,
                                MillesimiTabellaC = _db.MillesimiTabellaC.Where(m => i.Id = m.idInterno)
                             })
                .Select(x => new
                             {
                                 Id = x.Interni.Id,
                                 Condomino = x.Interni.Condomino, 
                                 Interno = x.Interni.Interno,
                                 NomePiano = x.Interni.Piani.NomePiano,
                                 NomeCondominio = x.Interni.Stabili.Nome,
                                 x.MillesimiTabellaC?.millesimi_fabbisogno_acs, 
                                 x.MillesimiTabellaC?.millesimi_fabbisogno_riscaldamento
                             })
                .ToList();

The syntax seems like what I'm looking for, I'm goint to test it right now, thanks! — Alessio R, Nov 23 '17 at 11:12
i.MillesimiTabellaC.millesimi_fabbisogno_riscaldamento cannot be found — Alessio R, Nov 23 '17 at 11:19
@AlessioR Is there a relation in your DB model between `Interni` and `MillesimiTabellaC` tables? — Peter Szekeli, Nov 23 '17 at 11:21
@AlessioR I added an alternative way, which should work without foreign keys (I made this again on-the-fly, so it's not tested, but maybe enough to put you on the right track) — Peter Szekeli, Nov 23 '17 at 11:32
Thanks a lot it works just by editing this fields : (i.MillesimiTabellaC.FirstOrDefault() == null) ? 0 : i.MillesimiTabellaC.FirstOrDefault().millesimi_fabbisogno_acs — Alessio R, Nov 23 '17 at 11:43

Transform SQL Query to LINQ

1 Answers1