How do you handle the fetchxml result data?

Question

I have avoided working with fetchxml as I have been unsure the best way to handle the result data after calling crmService.Fetch(fetchXml). In a couple of situations, I have used an XDocument with LINQ to retrieve the data from this data structure, such as:

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 handle fetchxml result data, so that it can be easily used. Applications such as passing these records into an ASP.NET datagrid would be quite useful.

Answer 1

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

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

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

Answer 2

You could also go for LinqtoCRM, that'll handle the XML parsing for you: http://codeplex.com/linqtocrm

Answer 3

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

        /// <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;
    }

The requiredFields string array is there because columns aren't returned if your result set contains no data with that column, however I might want the column in place for the exact reason of binding to datagrids etc.

CrmService is a singleton class that initates the webservice.

Hopefully this is of use to you.

Answer 4

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

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

Note that the reverse method QueryExpressiontoFetchXml exists as well

Answer 5

Thank you all for the support

Answer 6

With QueryExpression you can't query many-to-many entity and can't retrieve attributes from more than one entity at once, so you must use FetchXML.

Unfortunately LinqToCRM's codeplex project has been turned obsolete (1year without new release, but it seems to be a good implementation, better than the microsoft's release) with the release of 4.0.12 of CRM's SDK that contained a linq provider for dynamics crm, but I read some article about this new release and its not very good, seems to be a "poor implementation" with lot of limitations (forced cache etc.).

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

Christophe Trevisani Chavey.