CODEWITHSUNDEEP
c#linqentity-framework
Willy
Willy

Reputation: 1729

using where clause inside include generate error "The Include path expression must refer to a navigation property defined on the type"

I want to filter attachment when get a billing:

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

Billing Entity:

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; }
    }

but it gives an error 

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

How to use where clause on include? What I want to achieve is if I translate in query 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

Upvotes: 2

Views: 2865

Answers (1)

Vladimir Sachek
Vladimir Sachek

Reputation: 1124

As stated it is not allowed to use Where inside Include method. As I know it is not possible to filter navigation properties like that. What you could do is using projection

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

Upvotes: 3

Related Questions