如何使用LINQ获取关联合同的采购对应的全部History记录？

问题原因 #

你的原查询直接将Purchase与History做关联查询，这会产生Purchase和History的交叉结果：同一个采购记录会和对应合同的每条History各生成一条结果，且你在select中仅返回单条histories，导致最终结果看起来只拿到了单条History，而非合同对应的全部记录。

解决方案一：使用EF导航属性（推荐） #

利用EF的实体导航属性可以更简洁地加载关联数据，首先需要给Contract实体添加History集合的导航属性：

public class Contract
{
    [Key]
    public UInt64 ContractId { get; set; }
    public string ContractNumber { get; set; }
    public string InitialContractNumber { get; set; }
    [ForeignKey("ClienteId")]
    public UInt64 ClienteId { get; set; }
    [ForeignKey("ContactId")]
    public UInt64? ContactId { get; set; }
    [ForeignKey("ComercialEmployeeId")]
    public UInt64 ComercialEmployeeId { get; set; }
    [ForeignKey("TechnicalEmployeeId")]
    public UInt64? TechnicalEmployeeId { get; set; }
    public ulong OfferId { get; set; }
    public string Brand { get; set; }
    public string Type { get; set; }
    public string SerialNumber { get; set; }

    // 添加History集合导航属性，EF会自动关联ContractId外键
    public virtual ICollection<History> Histories { get; set; } = new List<History>();
}

修改LINQ查询，使用Include方法加载合同对应的全部History：

var purchases = from purchase in _context.Purchases
                join component in _context.Components on purchase.ComponentId equals component.ComponentId
                join supplier in _context.Suppliers on component.SupplierId equals supplier.SupplierId
                join contractDb in _context.Contracts.Include(c => c.Histories) 
                    on purchase.ContractId equals contractDb.ContractId into contract
                from contractDb in contract.DefaultIfEmpty()
                where purchase.Step == 0 && contractDb != null // 过滤出关联有合同的采购
                select new 
                { 
                    component.ComponentId, 
                    purchase.SupplierId, 
                    supplier.Name, 
                    contractDb.ContractId, 
                    contractDb.ContractNumber, 
                    ContractHistories = contractDb.Histories 
                };

解决方案二：使用子查询（无需修改实体） #

如果不想修改实体模型，可以在select中通过子查询直接获取当前合同的全部History记录：

var purchases = from purchase in _context.Purchases
                join component in _context.Components on purchase.ComponentId equals component.ComponentId
                join supplier in _context.Suppliers on component.SupplierId equals supplier.SupplierId
                join contractDb in _context.Contracts on purchase.ContractId equals contractDb.ContractId into contract
                from contractDb in contract.DefaultIfEmpty()
                where purchase.Step == 0 && contractDb != null
                select new 
                { 
                    component.ComponentId, 
                    purchase.SupplierId, 
                    supplier.Name, 
                    contractDb.ContractId, 
                    contractDb.ContractNumber, 
                    ContractHistories = _context.Histories.Where(h => h.ContractId == contractDb.ContractId).ToList()
                };

两种方案最终都会在结果的ContractHistories字段中返回对应合同的所有History记录，而非单条。

内容的提问来源于stack exchange，提问作者J.C