• Frank Grimberg

Should I use FetchXML, QueryExpression, or LINQ?

A common question in our developer training course is: Should I use FetchXML, QueryExpression, or LINQ? The following table shows query capabilities by Query Type. I will dive into each area.



NoLock


NoLock should be a consideration for every query you write.


From docs.microsoft.com

"The benefit of setting NoLock to true is that it allows you to keep the system from issuing locks against the entities in your queries; this increases concurrency and performance because the database engine does not have to maintain the shared locks involved. The downside is that, because no locks are issued against the records being read, some "dirty” or uncommitted data could potentially be read. A "dirty" read is one in which the data being read is involved in a transaction from another connection. If that transaction rolls back its work, the data read from the query using NoLock will have read uncommitted data. This type of read makes processing inconsistent and can lead to problems."

Only QueryExpression and FetchXML implement NoLock. I always hear, LINQ has NoLock. Yes, LINQ does have NoLock but CDS LINQ does not implement NoLock.



Easy to see Mistakes


FetchXML is the easiest of all to read. Embarrassing story time. One of our training exercise solution using QueryExpression was incorrect for over a year. It is just difficult to see the overall “Select” statement. LINQ has its own syntax structure. This is a preference call to choose FetchXML over LINQ.


OOTB Tools to Generate


The Advanced Find provides a way to visual design a query then download the FetchXML. This enables a copy paste starting point for your query. What about the 3rd party tools to generate queries? I can never assume that 3rd party tools will be allowed in secure environments.


CDS SQL Server Reporting


Only FetchXML is supported.


Web API


Only FetchXML is supported.


Aggregates


Only FetchXML is supported.


Conclusion


The clear winner is FetchXML. I can’t stress enough the importance of NoLock in complex Plugin implementations.





  • RSS Social Icon
  • Twitter App Icon
  • LinkedIn App Icon
  • YouTube Classic

© 2015 by Prosoft Systems International       |    Privacy Policy