llopez llopez - 1 month ago 8
SQL Question

Linq to Sql - Query list of distinct items with multiple joins

I'm trying to achieve a Linq query that is a little bit tricky.

DB structure :




  • Table A : idA, nameA

  • Table B : idB, idA, nameB

  • Table C : idC, idA, nameC

  • Table D : idD, idB, idC, nameD


    • (idB and idC are both nullable)




All fields are mandatory.

Structure Schema

Expected :



I'd like to retrieve :


  • a list of A items (distinct)


    • with the 10 first D items (from B OR C)


      • with a predicate applied on found D items




Answer

First, you need to LEFT JOIN tableD with tableB and tableC.

var augmentedD =
    from d in tableD
    join b in tableB on d.IdB equals b.IdB into bs
    join c in tableC on d.IdC equals c.IdC into cs
    from b in bs.DefaultIfEmpty()
    from c in cs.DefaultIfEmpty()
    let idAA = b != null ? b.IdA : c != null ? c.IdA : (int?) null
    where idAA != null
    let idA = idAA.Value
    select new
    {
        idA,
        d
    };

Then, GroupJoin tableA to that result.

var nestedA = from a in tableA
    join d in augmentedD on a.IdA equals d.idA into g
    select
    new
    {
        A = a,
        D = g.OrderBy(x => x.d.NameD)
            .Take(10)
            .Select(x => x.d)
            .ToList()
    };

Edit

If you want to put additional predicate, then put Where statement after tableD

var augmentedD =
    from d in tableD.Where(predicate)
    join b in tableB on d.IdB equals b.IdB into bs
    join c in tableC on d.IdC equals c.IdC into cs
    from b in bs.DefaultIfEmpty()
    from c in cs.DefaultIfEmpty()
    let idAA = b != null ? b.IdA : c != null ? c.IdA : (int?) null
    where idAA != null
    let idA = idAA.Value
    select new
    {
        idA,
        d
    };
Comments