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.

Bamford

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

Dank

Yeah, I have been mostly using RetrieveMultiple, but in this case I need to retrieve some attributes and add some conditions based on joined entities, which fetchXml will allow me to do, and the QueryExpression object will not allow. – Bamford

Luke, are you sure? Retrieval by QueryExpression also has ways to define joins. – Latoshalatouche

Yes, you can define joins, but I'm fairly sure you can't return attributes from a joined entity. – Bamford

Answer 2

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.

Oconner

+1 Magnificent, thanks for stating the clearest answer (turning the returned XML into a table). – Lainelainey

HI Fishcake, this looks brilliant - i'm getting an error though on the line tomService = CrmWebService; "The name 'CrmWebService' does not exist in the current context" - any ideas please? Thanks! – Cogon

Note: string result = tomService.Fetch(fetchXml); not working in CRM 2016, and deprecated in CRM 2011 – Thanatos

Answer 3

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.

Marlowe

It's actually default caching, you can turn it off if you add some configuration options to the config file. Just FYI – Asymmetry

Answer 4

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

Thetisa

Answer 5

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

Mezzorilievo