HOW TO: Multiple where Clause across Multiple Entities Using LINQ

When performing complex LINQ queries against CRM you might find that you need to perform a join across several entities. As soon as you introduce a join you might need to set up a where clause that dependants on certain conditions across your join. By this I simply mean you might need to perform the where query across multiple entities. Using a single where clause you cannot achieve this! For each entity you need where clauses, you need to add another where keyword.

I can back that statement up as the SDK help pretty much mentions the same thing.

The where clause applies a filter to the results, often using a Boolean expression. The filter specifies which elements to exclude from the source sequence. Each where clause can only contain conditions against a single entity type. A composite condition involving multiple entities is not valid. Instead, each entity should be filtered in separate where clauses.

Let me give you an example of how to add multiple where clauses across a join. I’ll show you an invalid statement and then a valid statement so you can see the difference.

Our scenario is that we want to bring back all contacts that are primary contacts for their associated accounts. We want the results to contain only active contacts and accounts.

The following snippet of code is invalid. Here we have one ‘where’ that represents two entities, account and contact

///
/// Gets a collection of contacts that are linked to accounts as primary contacts.
///The state the accounts and contacts should be in. ///

public IList GetContacts(int statecode)
{
var query = from c in OrgService.CreateQuery("contact")
join a in OrgService.CreateQuery("account")
on c["contactid"] equals a["primarycontactid"]
where c["statecode"].Equals(statecode) &&
a["statecode"].Equals(statecode)
select c;

return query.ToList();
}

The following snippet of code is valid. For each entity we want to perform a where clause against we have an associated where keyword.


///
/// Gets a collection of contacts that are linked to accounts as primary contacts.
///The state the accounts and contacts should be in. ///

public IList GetContacts(int statecode)
{
var query = from c in OrgService.CreateQuery("contact")
join a in OrgService.CreateQuery("account")
on c["contactid"] equals a["primarycontactid"]
where c["statecode"].Equals(statecode)
where a["statecode"].Equals(statecode)
select c;

return query.ToList();
}

Leave a Reply

Your email address will not be published. Required fields are marked *