Linq How to get the master record and a specific field of inner details record

Question

Given a DB structure of this type:

Auction =0..N=> Bidders =0..N=> Bids

where each entity has several fields (e.g. auction title, bidder.name, bid.date, bid.amount, etc.).

and given the auction id, I would like to run a LINQ query that extracts in one shot:

1. all fields of the auction (via its given id),
2. the id and the name of the best bidder (ignoring other bidder's fields)
3. the id and the amount of the best bid of the best bidder (ignoring other bid's fields)

    {
      AuctionId,
      AuctionTitle,
      AuctionStartDate,
      ...,

      IdOfTheBestBidder,
      NameOfTheBestBidder,

      IdOfTheBestBid,
      AmountOfTheBestBid
    }

All this in one shot and most efficient way. I.e. without loading all bidders and/or all bids for successive processing.

var qry = from auction in db.Auctions
          from bidder in auction.Bidders
          ...;

Answer 1

You can use two linq querys, first one to find the best bid as follows,

   var biddersList= (  from bidder in db.Bidders
                       join bid in db.Bids
                       on bidder.Id equals bid.bidderId
                       where //Here you can give the conditions to select best bid of a bidder
                       group  new { bidder, bid } by new { bidder.AuctionId } into bidandbidder
                       from grp in bidandbidder.DefaultIfEmpty()                            
                       select new { 
                                    grp.bidder.AuctionId, 
                                    grp.bidder.Id, 
                                    grp.bidder.Name, 
                                    grp.bid.Id, 
                                    grp.bid.Amount 
                                  }
                       );

In Second linq query use biddersList as follows to find the auction details with the best bid of the best bidder by the given given_auction_Id.

 var auctionList = ( from ac in db.Auction
                     join bd in biddersList
                     on ac.Id equals bd.AuctionId
                     where ac.Id == given_auction_Id and //here you can give the conditions to select best bidder
                     select new{
                                // select fields
                             }
                    );

Answer 2

Assuming the "best bid" is the bid with highest amount, you can build a LINQ to Entities query which orders the bids by amount in descending order and takes the first (with all related data), then project just the needed fields. It will be translated and executed as single SQL query (no auction, bidder or bid object will be loaded in the client memory).

For single auction, it would be something like this:

var result = (
    from auction in db.Auctions
    where auction.Id == auctionId
    from bidder in auction.Bidders
    from bid in bidder.Bids
    orderby bid.Amount descending
    select new
    {
        AuctionId = auction.Id,
        AuctionTitle = auction.Title,
        AuctionStartDate = auction.StartDate,
        ...,
        IdOfTheBestBidder = bidder.Id,
        NameOfTheBestBidder = bidder.Name,

        IdOfTheBestBid = bid.Id,
        AmountOfTheBestBid = bid.Amount,
    }).FirstOrDefault();

For all auctions it would be similar, but with subquery per each auction:

var result = (
    from auction in db.Auctions
    from best in (from bidder in auction.Bidders
                  from bid in bidder.Bids
                  orderby bid.Amount descending
                  select new { bidder, bid }).Take(1)
    select new
    {
        AuctionId = auction.Id,
        AuctionTitle = auction.Title,
        AuctionStartDate = auction.StartDate,
        ...,
        IdOfTheBestBidder = best.bidder.Id,
        NameOfTheBestBidder = best.bidder.Name,

        IdOfTheBestBid = best.bid.Id,
        AmountOfTheBestBid = best.bid.Amount,
    }).ToList();