QueryExpression vs. FetchXml CRM2011

Question

We found out that Linq for CRM 2011 is horribly broken - it seems to have gotten in without any QA performed on it. An indicator as how badly broken the provider is a query like .Where(x => x== "b") works but this .Where(x => "b" == x) might not depending on some preceding condition like a join statement. I have actually had to rewrite parts of the query provider and am enjoying better luck with the crap I put together.

However this can't go on, there are still other issues and I'm not paid to to work for MS, so I'm looking at alternatives. These 2 came up QueryExpression & FetchXml as detailed here: http://msdn.microsoft.com/en-us/library/gg334607.aspx

Can anyone give me an honest, real life pros and cons of using QueryExpression vs. FetchXml? I would like to know how they compare in terms of performance, development speed, robustness and flexibility.

Shortly

Answer 1

In my opinion, I usually go for Linq or FetchXml depending for the requirements.

For the Linq: In case for early-bound, I like using Linq because it's strongly typed and It helps much for development speed, but as you stated above, it has its disadvantages.

For the FetchXML: I really love using this magic statement:

EntityCollection result = _serviceProxy.RetrieveMultiple(new FetchExpression(fetch2));

foreach (var c in result.Entities)
{
   System.Console.WriteLine(c.Attributes["name"]);
}

Why? Because it's very similar of using the QueryExpression in addition of the aggregation and grouping. The only thing I hate about FetxhXML is that it's hard to build, unlike the Linq.

For building FetchXML queries, I have to open the Advanced-Find then add columns then put my criteria and so on, finally I download it and copy it into my code, and so on.

Finally, the FetchXML has the least limitations among the others.

Regarding the performance I've tried to benchmark between Linq and FetchXML for same query using StopWatch, the result was FetchXML is faster than the Linq.

Noman

Anwar, why did you say that's a magic statement? It looks like you still need to deserialize the result into objects which isn't easy. I like the idea of downloading the fetchxml from the CRM web app directly though. – Shortly

Because previously in CRM 2004 I had to load the returned XML into an XMLDocument then check for every node and so on, which results for an awful code. – Noman

Answer 2

To build on Anwar's excellent answer focusing on LINQ vs. FetchXml, I'll add I never use QueryExpression. Why?

LINQ: Queries are built using standard language, but internally uses QueryExpression so is limited to the features of QueryExpression.

QueryExpression: Queries are built as an object model. Supports all the features in FetchXML except for aggregates and grouping.

So it's worse in querying power than FetchXml without the Advanced Find code generation, and it offers the same functionality as the LINQ provider while offering a completely non-standard querying interface (unlike LINQ).

As for LINQ (non)functionality, the limitations of the LINQ provider are clearly, and I think fairly well, documented. Your .Where(x => "b" == x) snippet, for instance, violates the where clause restriction:

where: The left side of the clause must be an attribute name and the right side of the clause must be a value. You cannot set the left side to a constant. Both the sides of the clause cannot be constants.

Not defending Microsoft: they need to put in a lot of work on the LINQ provider (read: direct-to-SQL provider) before the LINQ provider is professional grade, but hey, at least they've got a great disclaimer.

Outstation

You mention that QueryExpression supports all features in FetchXML, but it cannot do nested joins, it cannot do multiple joins, it cannot do outer joins, it cannot do joins with multiple conditions. All these are, however, possible with FetchXml. – Weismann

@Abel: I'll take your word on that, since I don't use QueryExpression. I must add that those are Microsoft's words, not mine (see link above), so I can't correct their documentation. – Outstation

Well, more precisely, the QueryExpression class itself supports them, but when you try to run the expression against the CrmService, it will always fail with a SOAPException. The internet is full of complaints about this. – Weismann

@Abel: I see - too bad! Here's to hoping Microsoft gives users complete access to their own data. – Outstation

Answer 3

I've been asked specifically by a client to use the Query Expression model, so in order to make my life easier, I've resorted to adding a lot of extension methods to IOrganizationService. Examples Include:

public static List<T> GetEntities<T>(
    this IOrganizationService service, 
    params object[] columnNameAndValuePairs
) where T : Entity

which converts the params object[] and T entity type into a query expression, and automatically returns the results to an entity list. So it is used like so:

foreach(var c in service.GetEntities<Contact>("lastname", "Doe", "firstname", "Smith"))
{
    ... 
}

I also use this one a lot too:

public static T GetFirstOrDefault<T>(
    this IOrganizationService service,
    params object[] columnNameAndValuePairs
) where T : Entity

var c = service.GetFirstOrDefault<Contact>("owner", id);

These type of extension methods make working with query expressions a lot easier, giving you a much more LINQ type style, without weird linq restriction traps that are easy to fall into.

Villalpando

@Eccountable, check out the DLaB.Xrm.Common library in the Unit testing framework for more examples: github.com/daryllabar/XrmUnitTest – Villalpando

Answer 4

I would advocate in favor of FetchXML because I can use it in my JavaScript or C# code, unlike LINQ or QueryExpression...therefore one less thing to learn and maintain. As for stuff like Intellisense, there is a great tool that plugs into XrmToolbox called FetchXML Builder that is much more sophisticated in designing complex queries than you will ever see using Advanced Find. I have been using it now for a month for a CRM Online client and it is as close to using SQL as you can get in this environment. It can also generate QueryExpression code for me. I have turned over this tool to my business analysts and they are going to town using it to make sophisticated data sets for the dashboard - a big win for the clients.

I do lament the loss of detecting errors with early binding, but I enjoy

Killer