C# LINQ Join multiple tables into one object

Question

Main problem to solve:

How do I get "2 levels down" to get Owner.Pet[n].Toys and put it all in one object right away.

There are three entities in the system: Owner, Pet and Toy.

I am trying to write a query (using LINQ's SQL-like notation) that will bring me single object of class Owner with its inner properties filled = all pets assigned to that owner + each pet having all its toys.

This is what I have so far, it's not working as expected. SQL feelings tell me that I am missing GROUP JOIN somewhere...

var singleOwnerQuery =
    from o in owners
    join p in pets on o.FirstName = p.OwnerName
    join t in toys on p.PetId = t.ToyUniqueId // each toy is unique - there are not "re-usable" toys 
    where o.Name == "..." && o.LastName == "..."
    select new Owner
    {
        Pets = pets // this should already assign all the toys for each of the pets
    };

Any help will be appreciated.

I skipped lot of other properties inside each of the classes to make it simpler

The relationship between pets and owners is confusing. You're joining on a string (Name) yet pets has a PK (PetId). Why not join pets and owners by the PKs (PetId, OwnerId)? — GH DevOps, Dec 13 '22 at 15:05
This might help: https://stackoverflow.com/questions/16677871/c-sharp-joining-3-lists-with-linq — sr28, Dec 13 '22 at 15:13
There is an example for owners and pets doing exactly this here: [Example - Group join](https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform-grouped-joins#example---group-join). — Olivier Jacot-Descombes, Dec 13 '22 at 16:46
You say it's not working as expected, but what is not working? Is it not filling the `Pets` correctly? Have you tested the base query to make sure it correctly retrieves the data you are looking for? — floating_camel, Dec 13 '22 at 17:49
If you expect correct query as an answer, ALWAYS show your model with navigation properties. — Svyatoslav Danyliv, Dec 13 '22 at 17:52
There are no navigation properties, because there are no FK in a db. If there would be FKs - I would mention it. I could also use EF Core and all of its fency stuff. but sadly - "ain't no navigation properties" — Kamil Turowski, Dec 13 '22 at 18:16
@GHDevOps - it's irrelevant in my question - I did not state my problem as not being able to join two tables — Kamil Turowski, Dec 13 '22 at 18:19

score 0 · Answer 1 · answered Dec 13 '22 at 19:06

Try the following query. Without model, this is just direction how to do Eager Loading query as in your case.

var singleOwnerQuery =
    from o in owners
    where o.Name == "..." && o.LastName == "..."
    select new Owner
    {
        Pets = pets.Where(p => o.FirstName == p.OwnerName)
          .Select(p => new 
          {
              p.PetId,
              Toys = toys.Where(t => p.PetId == t.ToyUniqueId).ToList()
          })
          .tolist()
    };

C# LINQ Join multiple tables into one object

1 Answers1