2

I'm pretty sure there's an example of this somewhere on here, I just can't find it as I don't know the exact terms. So please, be kind.

The problem should be a simple one:

I have an Azure SQL database, somewhat badly designed (ie missing foreign keys and such). As I cannot redesign the database at this point, I have to handle relations manually via queries. As the database is also the bottleneck of our software, I must try and complete queries with as little database hits as possible.

I have entities A, B, C and D. An entity A has several entities B (connected with an unregistered foreign key), an entity B has several entities C and an entity C has several entities D.

I have the ID of entity A, and would like to return a tree of all connected entities up to Ds, as optimally as possible.

Starting off:

from A in dbA.Where(e=>e.Id==IDParameter)
join B in dbB on A.Id equals B.AId into Bs

Now I'd need to do this with every B in Bs for C and again with Cs for D. If I go the obvoius way, and join Ds and Cs first, and then Bs and Cs, and finally the As and Bs, this means joining all Ds with Cs, then all... you get the point, until I finally get to the IDParameter filter. It just seems ineffective.

Is there a proper C# way of doing this in a single query?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JasonX
  • 503
  • 7
  • 21

1 Answers1

4

You can simulate with manual joins what EF does when you have relationships defined. All you need is to use Group Joins and projections. Something like this:

var result =
    (from a in db.A
     where a.Id == IDParameter
     join b in db.B on a.Id equals b.AId into Bs
     select new
     {
         a,
         Bs =
            (from b in Bs
             join c in db.C on b.Id equals c.BId into Cs
             select new
             {
                 b,
                 Cs =
                    (from c in Cs
                     join d in db.D on c.Id equals d.CId into Ds
                     select new
                     {
                         c,
                         Ds = Ds.ToList()
                     }).ToList() 
             }).ToList()
     }).ToList();
Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343
  • 1
    Well duh, projections... It was right under my nose the whole time :D I'm ashamed to tell you how much time I've been staring at my screen now... Thank you :) – JasonX Aug 23 '16 at 08:39
  • Additional question - how would I go about only selecting the Bs that actually have at least one C? – JasonX Aug 23 '16 at 09:11
  • Unfortunately the group joins are basically left outer joins. But you could insert after the join and before the projection `where Cs.Any()`. – Ivan Stoev Aug 23 '16 at 09:17
  • That did the trick. I've removed the .ToList() calls and added an await - .AsNoTracking().FirstOrDefault() to the query - works like a charm :) – JasonX Aug 23 '16 at 10:20
  • Cool! What about your first comment, no problem, it happens to all of us sometime :) – Ivan Stoev Aug 23 '16 at 10:23
  • 1
    looks even more complicated than the query you'd type yourself in mssm – phil123456 Oct 08 '20 at 03:52