c# - Linq如何获取主记录和内部明细记录的特定字段

Question

给定这种类型的数据库结构:

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

其中每个实体都有多个字段(例如拍卖名称、出价人姓名、出价日期、出价金额等)。

并给定拍卖 ID，我想运行一次提取的 LINQ 查询:

1. 拍卖的所有领域(通过其给定的 id)，
2. 最佳投标人的 ID 和姓名(忽略其他投标人的字段)
3. 最佳出价人的id和出价金额(忽略其他出价字段)

    {      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

假设“最佳出价”是金额最高的出价，您可以构建一个 LINQ to Entities 查询，该查询按金额降序排列出价并取第一个(包含所有相关数据)，然后只转换所需的字段.它将作为单个 SQL 查询进行翻译和执行(不会在客户端内存中加载拍卖、投标人或投标对象)。

对于单一拍卖，它会是这样的:

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();

对于所有拍卖，它都是相似的，但每次拍卖都有子查询:

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();