For an example, we want to find records where the Lookup Id is equal to a constant value or where clause is greater than for a specific date.
In this kind of cases, we can write very simple linq query.
But sometimes we have to join 2 (or more) entities and we want to filter records with the variables in between them.
Example
We have 1 to many relationship with contacts and visits
And we want to get contact Ids where Contact Agreement Date is greater than Visit Date. And hence Contact.AgreementDate and Visit.VisitDate are not constant.
We can write the query in SQL as below
select contact.Id from new_visit visit inner join Contact on visit.new_contactId = contact.ContactId
and visit.new_VisitDate >= contact.new_AgreementDate where visit.statecode = 0 and contact.StateCode = 0
If we write this in C#, we get below query
var query = from visit in context.CreateQuery<new_visit>()
join contact in context.CreateQuery<Contact>() on visit.new_contactId.Id equals contact.ContactId
where visit.new_VisitDate >= contact.new_AgreementDate
where visit.statecode.Value == new_visitState.Active
where contact.statecode.Value == ContactState.Active
select contact.Id
Check the right hand side of the below where clase
where visit.new_VisitDate >= contact.new_AgreementDatesince the right side is not constant, Linq cannot query the data we want.
But this can be done using anonymous types.
IOrganizationService service = new CRMInternal().GetService();
OrganizationServiceContext context = new OrganizationServiceContext(service);
var query = from visit in context.CreateQuery<new_visit>()
join contact in context.CreateQuery<Contact>() on visit.new_contactId.Id equals contact.ContactId
where visit.statecode.Value == new_visitState.Active
where contact.StateCode.Value == ContactState.Active
select new { contact.Id, visit.new_VisitDate, contact.new_AgreementDate };
var contacts = query.ToList().Where(p => p.new_VisitDate <= p.new_AgreementDate).Select(p => p.Id);
