A newbie asks...

Part 1

Suppose I have 3 classes (and their equivalent SQL tables) :

Product 
{
     int Id;
     List<Keyword> Keywords;
     List<Serial> Serials;
}

Keyword
{
  int Id;
  int ProductId; // FK to Product
  string Name;
}

Serial
{
   int Id;
   int ProductId; // FK to Product
   string SerialNumber;
}

When loading PRODUCT == 123, the we could do this:

   item = db.Products.FirstOrDefault(p => p.Id == 123);
   item.Keywords  = db.Keywords.Where(p => p.ProductId == 123).ToList();
   item.Serials   = db.Serials.Where(p => p.ProductId == 123).ToList();

which is 3 SQL statements.

Or we could do this:

    from product in db.Products.AsNoTracking()
        join link1 in Db.Keywords.AsNoTracking()
        on product.Id equals link1.ProductId into kwJoin
    from keyword  in kwJoin.DefaultIfEmpty() 
        join link2 in Db.Serials.AsNoTracking()
        on product.Id equals link2.ProductId into serJoin
    from serial   in serJoin.DefaultIfEmpty() 
        where product.Id == 123
    select new { product, keyword, serial };

which gives 1 SQL statement but produces far too many rows (number of keywords x number of serials) that need to be coalesced together

Both seem less than efficient. Is there a better way?

Part 2

As another question, but using the same example, when we have a join like so:

    from product in db.Products.AsNoTracking()
        join link1 in Db.Keywords.AsNoTracking()
        on product.Id equals link1.ProductId into kwJoin
    from keyword  in kwJoin.DefaultIfEmpty() 
    select new { product, keyword };

Is there a way to assign the keywords directly in the product, in select statement?

    select new { product, product.Keywords = keyword };

Thanks for any help!

CodePudding user response：

If the FKs exist, depending on how you have setup your DB context, the properties will automatically be fetched. No joins required. Part 1 query is simple as it has a filter. Part 2 might have issues depending on how many records needs to be fetched from the database. You can map the fields to anonymous objects(or DTOs) after the fact that you have keyword objects for each product in the list.

Part 1

item = db.Products
          .Include(p=>p.Keywords)
          .Include(s=>s.Serials)
          .Where(p => p.Id == 123)
          .FirstOrDefault();

Part 2

products = db.Products.Include(p=>p.Keywords).ToList();