Linq select from CRM public view

Question

I have searching for a while now, but couldn't find how to query from public view. For example, I have predefined public view called Active Accounts and I want data from it.

So far I only know this way, but that not include any views:

using (var xrm = new XrmServiceContext("Xrm"))
{
    var activeAccounts = from a in xrm.AccountSet
                   where a.StateCode == 0
                   select new { a.Id, a.Name };

    // TODO ...
}

But I would like to do it like this (not working, ActiveAccountsView not exist, it's pseudo):

using (var xrm = new XrmServiceContext("Xrm"))
{
    var activeAccounts = from a in xrm.ActiveAccountsView
                         select new { a.Id, a.Name };

    // TODO ...
}

Is this even possible?

Answer 1

The query definitions of public views are stored in the savedquery entity, that can be queried using common techniques.

Out-of-the-box views are stored with a fixed ID, so querying Active Accounts on the OrganizationServiceContext object could be done in the following way:

private static IEnumerable<Entity> GetActiveAccounts(OrganizationServiceContext serviceContext)
{
    string fetchXml = serviceContext
        .CreateQuery("savedquery")
        .Where(sq =>
            sq.GetAttributeValue<Guid>("savedqueryid") == new Guid("00000000-0000-0000-00AA-000010001002"))
        .Select(sq => sq.GetAttributeValue<string>("fetchxml"))
        .First();

    var request = new RetrieveMultipleRequest
    {
        Query = new FetchExpression(fetchXml)
    };

    var response = (RetrieveMultipleResponse) serviceContext.Execute(request);

    return response.EntityCollection.Entities;
}

It is not possible to use LINQ here. LINQ relies on the QueryExpression class, but does not implement all its capabilities (OUTER JOIN is a painful omission for example). So, while it is possible to convert a LINQ query to a QueryExpression, the other way around is not.

Paging can be applied by editing the Fetch XML string, but if that is too much hassle, you can also consider to convert the Fetch XML to a QueryExpression and apply paging on that object:

private IEnumerable<Entity> GetActiveAccounts(int pageNumber)
{
    string fetchXml = _serviceContext
        .CreateQuery("savedquery")
        .Where(sq =>
            sq.GetAttributeValue<Guid>("savedqueryid") == new Guid("00000000-0000-0000-00AA-000010001002"))
        .Select(sq => sq.GetAttributeValue<string>("fetchxml"))
        .First();

    var conversionRequest = new FetchXmlToQueryExpressionRequest
    {
        FetchXml = fetchXml
    };

    var response = (FetchXmlToQueryExpressionResponse)_serviceContext.Execute(conversionRequest);
    response.Query.PageInfo = new PagingInfo { Count = 1, PageNumber = pageNumber };

    var queryRequest = new RetrieveMultipleRequest
    {
        Query = response.Query
    };

    var result = (RetrieveMultipleResponse) _serviceContext.Execute(queryRequest);
    return result.EntityCollection.Entities;
}

Additional advantage of the QueryExpression vs. Fetch XML is that it is processed in a bit more efficient way.

The very same can be done with user defined views; these views are stored in the userquery entity. The only difference here is you cannot rely on a fixed view ID. Instead you would need to filter your query on querytype, name, returnedtypecode, ownerid and/or other criteria.

Dynamics CRM also has an OrganizationRequest that allows you to execute the savedquery immediately. However, it returns its result as a resultset XML string, so you would still need to deserialize the response. (A nice example can be found here.) Also, I am not sure if it is possible to limit the result set to a specific page when using the ExecuteByIdSavedQueryRequest:

var request = new ExecuteByIdSavedQueryRequest
{
    EntityId = new Guid("00000000-0000-0000-00AA-000010001002")
};

var response = (ExecuteByIdSavedQueryResponse)serviceContext.Execute(request);

string resultset = response.String;