c# - 在内部使用 where 子句包含生成错误 "The Include path expression must refer to a navigation property defined on the type"

Question

我想在获取账单时过滤附件:

var billing = db.Billings
                .Include(b => b.Client)
                .Include(b => b.Attachments.Where(a => a.WorkflowStateID == workflowStateID))
                .Where(b => b.BillingID == id)
                .FirstOrDefault();

账单实体:

public partial class Billing
    {
        public Billing()
        {
            this.Attachments = new HashSet<Attachment>();
        }

        public long BillingID { get; set; }
        public int ClientID { get; set; }

        public virtual ICollection<Attachment> Attachments { get; set; }
        public virtual Client Client { get; set; }
    }

但是报错

The Include path expression must refer to a navigation property defined on the type.
Use dotted paths for reference navigation properties and the Select operator for 
collection navigation properties

如何在include中使用where子句？我想要实现的是，如果我在查询 sql 中进行翻译:

select * 
from Billing b
    inner join Client c on b.ClientID = c.ClientID
    inner join (select * from Attachment a where a.WorkflowStateID = @workflowStateID) t on b.BillingID = t.BillingID
where b.BillingID = @billingID

Answer 1

如前所述，不允许在 Include 方法中使用 Where。据我所知，不可能像这样过滤导航属性。你可以做的是使用投影

var billing = db.Billings
    .Where(b => b.BillingID == id)
    .Select(b => new {
        Billing = b,
        BillingClient = b.Client
        FilteredAttachments = b.Attachments.Where(a => a.WorkflowStateID == workflowStateID)
    })
    .FirstOrDefault();