How do you handle fetchxml result data?

Question

I avoided working with fetchxml since I'm not sure what the best way to process the result data after calling crmService.Fetch (fetchXml). In several situations, I used an XDocument with LINQ to extract data from this data structure, for example:

XDocument resultset = XDocument.Parse(_service.Fetch(fetchXml)); if (resultset.Root == null || !resultset.Root.Elements("result").Any()) { return; } foreach (var displayItem in resultset.Root.Elements("result").Select(item => item.Element(displayAttributeName)).Distinct()) { if (displayItem!= null && displayItem.Value != null) { dropDownList.Items.Add(displayItem.Value); } } 

What is the best way to process fetchxml result data so that it can be easily used. Applications such as passing these records to an ASP.NET datagrid will be very useful.

Answer 1

I usually avoid FetchXML for this reason. You can use RetrieveMultiple to get strongly typed BusinessEntity objects and basically do the same.

But if you want to use FetchXML, this sample should cover you:

http://msdn.microsoft.com/en-us/library/ms914457.aspx

Answer 2

I like the flexibility of FetchXML, so I developed the following function that returns a datatable for use in binding to grids and repeaters, etc.

  /// <summary> /// Takes a CRM FetchXML query and returns a DataTable /// </summary> /// <param name="fetchXml">The FetchXML query</param> /// <param name="requiredFields">A array of columns you'd expect returned. This is required as if there is no data for a field/column CRM will not return it which could impact databinding</param> /// <returns>A datatable containing the results of the FetchXML</returns> public static DataTable FetchXML2DataTable(string fetchXml, string[] requiredFields) { CrmService tomService = new CrmService(); tomService = CrmWebService; string result = tomService.Fetch(fetchXml); DataSet ds = new DataSet(); System.IO.StringReader reader = new System.IO.StringReader(result); ds.ReadXml(reader); DataTable dt = ds.Tables[1]; //check all required columns are present otherwise add them to make life easier for databinding at the top level //caused by CRM not returning fields if they contain no data foreach (string field in requiredFields) { //Check for column names and nested tables if ((dt.Columns.IndexOf(field) < 0) && (dt.DataSet.Tables.IndexOf(field) <0)) { //Add column to datatable even though it is empty for reason stated above dt.Columns.Add(new DataColumn(field)); } } return dt; } 

A required array of requiredFields is required because columns are not returned if your result set does not contain data with this column, however I may need a column for the exact reason for binding to datagrids, etc.

CrmService is a singleton class that runs a web service.

I hope this will be useful to you.

Answer 3

With QueryExpression, you cannot query a many-to-many object and cannot extract attributes from multiple objects at the same time, so you should use FetchXML.

Unfortunately, the LinqToCRM codeplex project is deprecated (1 year without a new version, but it seems to be a good implementation, better than the microsoft version) with the release 4.0.12 CRM SDK, which contains the linq provider for crm dynamics, but I read some article about this new version and its not very good, it seems to be a "poor implementation" with many restrictions (forced cache, etc.).

I see a lot of people using LinqToXML and a DataSet to maintain FetchXML results, but I could not say what the best way to deal with it. What do you think about this?

Christoph Trevisani Chavi.

Answer 4

You can also go for LinqtoCRM, which will handle the XML parsing for you: http://codeplex.com/linqtocrm

Answer 5

If you want to use fetchxml and get a return in BusinessEntityType, you can use FetchXmlToQueryExpression to get the query expression from fetchxml and then apply the query expression in the RetrieveMultiple method, as in

 FetchXmlToQueryExpressionResponse qe = (FetchXmlToQueryExpressionResponse) service.Execute(fetch); 

Note that there is also a QueryExpressiontoFetchXml inverse method method