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

Question

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

Check out this question http://stackoverflow.com/questions/19386501/linq-to-entities-include-where-method — cost, May 12 '14 at 06:14

Vladimir Sachek · Accepted Answer · 2014-05-12T06:44:07.407

3

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

edited May 12 '14 at 06:44

answered May 12 '14 at 06:20

Vladimir Sachek

1,126
1
7
20

ok i see, but why i cannot access Client in this way billing.Client? – Willy May 12 '14 at 06:37
2

This answer is wrong, in exactly the way that leads to @Willy's comment: `Include` has no effect, because of the projection. `db.Billings.Where(b => b.BillingID == id).Select(b => new { Billing = b, BillingClient = b.Client, FilteredAttachments = ... }).FirstOrDefault()` should work (and should make sure both `billing.BillingClient` and `billing.Billing.Client` get set) – May 12 '14 at 06:42
@hvd thanks for your correction, so it doesn't need .Include(b => b.Client) and as replacement use BillingClient = b.Client, am I right? – Willy May 12 '14 at 06:51
1

Yes, but as I noted in the comment, you don't have to actually use `BillingClient`, the mere fact that you're retrieving it like that should be enough to ensure that the `Client` property gets set too. – May 12 '14 at 06:53

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

1 Answers1