Performing complex query with Dynamics CRM 4.0

Question

Hi, I have two custom entites, Product and ProductType, linked together in many-to-one relationship. Product has a lookup field to ProductType.

I'm trying to write a query to fetch Type1 products with a price over 100, and Type2 products with a price lower than 100.

Here's how I would do it in SQL :

select *
  from Product P
 inner join ProductType T on T.Id = P.TypeId
 where (T.Code = 'Type1' and P.Price >= 100)
    or (T.Code = 'Type2' and P.Price < 100)

I can't figure out a way to build a QueryExpression to do exactly that. I know I could do it with two queries, but I'd like to minimize roundtrips to the server.

Is there a way to perform that query in only one operation ?

Thanks!

Answer 1

On the QueryExpression object, there is a property called LinkEntities. You can create LinkEntity objects that specify the "join" information, and then add that to your link entity. For example:

QueryExpression q = new QueryExpression();
LinkEntity l = new LinkEntity();
l.LinkFromAttributeName = "fromatt";
l.LinkToAttributeName = "toatt";
l.LinkFromEntityName = "product";
l.LinkToEntityName = "producttype";

FilterExpression f = new FilterExpression();
f.AddCondition(new ConditionExpression("code", ConditionOperator.Equal, "type1"));
l.LinkCriteria = f;
q.LinkEntities.Add(l);

Note that you can't actually get to any of the attributes on your ProductType entity from a QueryExpression that's retrieving Products. For that you'd have to use Fetch XML.

Answer 2

You could also give LinqtoCRM a look. It converts queries to FetchXML so you can get attributes on joined entities.

Answer 3

Unfortunately, no, it is not possible to do that with a QueryExpression or FetchXML. At least, not with Dynamics CRM 4. Let's hope they include this feature in version 5 (2nd half 2010)