Linq where clause finding for attribute on wrong entity

Question

Hi I have the following Linq query:

(from c in new_contactsubscriptionSet
join p in new_PaymentStatusSet 
    on c.new_PaymentStatusId.Id equals p.new_PaymentStatusId
    where (c.new_EndDate > DateTime.Now && 
           c.new_EndDate <= DateTime.Now.AddDays(14)) && 
    p.new_IsPaidStatus == false
select c)

It throws the following FaultException which means its checking attribute new_ispaidstatus on wrong entity. It should be checking on new_PaymentStatus rather then new_contactsubscription

FaultException

'new_contactsubscription' entity doesn't contain attribute with Name = 'new_ispaidstatus'.

If I use the following queries its working fine:

(from c in new_contactsubscriptionSet
join p in new_PaymentStatusSet 
    on c.new_PaymentStatusId.Id equals p.new_PaymentStatusId
    where p.new_IsPaidStatus == false
select c)

OR

(from c in new_contactsubscriptionSet
join p in new_PaymentStatusSet 
    on c.new_PaymentStatusId.Id equals p.new_PaymentStatusId
    where (c.new_EndDate > DateTime.Now && 
           c.new_EndDate <= DateTime.Now.AddDays(14))
select c)

Looks like something is wrong with the Where clause. Can anyone help me out to fix this query.

Thanks in Advance

Answer 1

You'll need a different where for each entity.

(from c in new_contactsubscriptionSet
 join p in new_PaymentStatusSet 
    on c.new_PaymentStatusId.Id equals p.new_PaymentStatusId
 where (c.new_EndDate > DateTime.Now && c.new_EndDate <= DateTime.Now.AddDays(14))
 where p.new_IsPaidStatus == false
 select c)

This is due to the way Microsoft maps the Linq query to a Query Expression. It attempts to map a where into it's only filter criteria, but these are applied on an entity to entity basis. So it is determining the names of all the attributes used, and creating a filter for it against the first expression it evaluates' entity.

With the multiple where's, it'll update the filter of the second Linked Entity rather than blindly adding it to the first.