locked
Convert LINQ query to FetchXML SDK 2013 RRS feed

  • Question

  • Hello,

    We have some code that uses reflection in CRM2011 to convert our LINQ queries into QueryExpressions. We like this approach because it means we can write queries using LINQ (early bound) and still have the power of FetchXML (which allows us to insert paging info)

    Now, the code I have been using isn't anymore. The ArgumentException I get returns the message "Parameter cannot be null: Source".

    This is the method we use:

    http://stackoverflow.com/questions/6740179/convert-linq-expression-to-queryexpression-or-fetchxml

    Does anyone know if this is still possible using the CRM 2013 SDK?

    Friday, October 24, 2014 5:41 PM

Answers

  • Finally .. I've made some actual progress .. I have valid reflection code that calls the GetQueryExpression method on the CRM2013 SDK QueryProvider. This makes the solution I posted last night a bit silly .. but that just proves what a good night of sleep does to a man.

    The following extension method will convert your LINQ query into a QueryExpression that has PagingInfo and also allows you to convert the query into FetchXML.

    public static class QueryProviderExtensions { public static QueryExpression ToQueryExpression<T>(this IQueryable<T> items) { var queryProvider = items.Provider; var queryProviderType = queryProvider.GetType(); var listType = typeof(List<>); var projectionType = queryProviderType.Assembly.GetType("Microsoft.Xrm.Sdk.Linq.QueryProvider+Projection"); var navigationSourceType = queryProviderType.Assembly.GetType("Microsoft.Xrm.Sdk.Linq.QueryProvider+NavigationSource"); var linkLookupType = queryProviderType.Assembly.GetType("Microsoft.Xrm.Sdk.Linq.QueryProvider+LinkLookup"); var linkLookupListType = listType.MakeGenericType(linkLookupType); object projection = null; object source = Activator.CreateInstance(navigationSourceType, new object[] { null, null }); object linkLookups = Activator.CreateInstance(linkLookupListType); bool throwIfSequenceIsEmpty = false; bool throwIfSequenceNotSingle = false; object[] arguments = new object[6]; arguments[0] = items.Expression; arguments[1] = throwIfSequenceIsEmpty; arguments[2] = throwIfSequenceNotSingle; arguments[3] = projection; arguments[4] = source; arguments[5] = linkLookups; var getQueryExpressionMethod = queryProviderType.GetMethod("GetQueryExpression", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public, null, new[] { typeof(Expression), typeof(bool).MakeByRefType(), typeof(bool).MakeByRefType(), projectionType.MakeByRefType(), navigationSourceType.MakeByRefType(), linkLookupListType.MakeByRefType(), }, null); var queryExpression = (QueryExpression)getQueryExpressionMethod.Invoke(queryProvider, arguments); return queryExpression; }

    }

    This means I am no longer calling the Execute method, so nothing to intercept there (Thank god, that was dreadful). Additionally I don't need to visit the Skip and Take methods either, which was all part of the same train wreck.

    Just use it like so:

            // This can be any query
            var query = (from e in contactSet.Query()
                         where e.LastName.Contains("e")
                         select e);
    
            // This is where the magic happens
            var queryExpression = query.ToQueryExpression();
    
            // We can now add paging info
            queryExpression.PageInfo = new PagingInfo()
            {
                PageNumber = 1,
                Count = 50,
                ReturnTotalRecordCount = true
            };
    
            // This will create a QueryExpressionToFetchXmlRequest, not needed for paging.
            var xml = queryExpression.ToXml(service);
    
            // Perform the actual request 
            var collection = service.RetrieveMultiple(queryExpression);

    @Microsoft, please expose a method somewhere that allows us to convert LINQ Queries into QueryExpressions. This has been asked a lot of times and I understand the reasons for marking the QueryProvider as Internal. However, the QueryExpression class is public and therefore it only seems logical to expose it.


    • Marked as answer by Ericvf_ Saturday, October 25, 2014 2:29 PM
    • Edited by Ericvf_ Saturday, October 25, 2014 2:31 PM
    Saturday, October 25, 2014 2:12 PM

All replies

  • did you try the solution suggested inside the comment?

    http://www.hashtagcrm.com/?p=157


    My blog: www.crmanswers.net - Rockstar 365 Profile

    Friday, October 24, 2014 5:43 PM
  • Thanks for the reply, I did try that solution but it only allows me to view the fetchxml while executing requests generated by the SDK's QueryProvider. I can do that with Fiddler.

    I must say that this has been quite the ordeal and it took a few hours to come up with a (very hacky) solution. The real world problem we are having is that we prefer to use LINQ instead of FetchXML but FetchXML obviously has more features. The feature we rely on is having the TotalRecordCount on the PagingInfo.

    After thinking about it I decided to try to extend IOrganizationService as suggested and override the Execute method there. What I tried to accomplish was to always set TotalRecordCount to true and store the result into the Attributes collection of the first result Entity. The code looks like this:

    public override Microsoft.Xrm.Sdk.OrganizationResponse Execute(Microsoft.Xrm.Sdk.OrganizationRequest request)
    {
        var retrieveMultipleRequest = request as RetrieveMultipleRequest;
        if (retrieveMultipleRequest != null)
        {
            var queryExpression = retrieveMultipleRequest.Query as QueryExpression;
            if (queryExpression != null)
                queryExpression.PageInfo.ReturnTotalRecordCount = true;
        }
     
        var result = base.Execute(request);
     
        if (result is RetrieveMultipleResponse)
        {
            var e = result.Results["EntityCollection"] as EntityCollection;
            if (e != null)
                e.Entities[0].Attributes["TotalRecordCount"] = e.TotalRecordCount;
     
        }
     
        return result;
    }

    Unfortunately I was not getting the Attribute value back after enumerating the collection. None of the output entities had a TotalRecordCount key. After further inspection I also noticed that two calls were made to the OrganizationService. The first returning only a single result (with paging information), and the second call returns all the results.

    So what I ended up doing is actually a TERRIBLE solution. I do not recommend anyone to use it. However, if you need a QueryExpression from a LINQ Query you can use this snippet:

    public static class QueryProviderExtensions
    {
        public static QueryExpression ToQueryExpression<T>(thisIQueryable<T> items, IOrganizationService service)
        {
            var provider = items.Provider;
     
            var visitor = newSkipTakeVisitor().Visit(items.Expression);
     
            var itemsWithoutSkipAndTake = Expression.Lambda<Func<IQueryable<T>>>(visitor).Compile();
     
            IQueryable<T> expression = itemsWithoutSkipAndTake();
            expression = expression.Take(int.MaxValue);
     
            var executeMethod = provider.GetType().GetMethod("Execute");
            var genericExecuteMethod = executeMethod.MakeGenericMethod(typeof(T));
            var pagedItemCollection = genericExecuteMethod.Invoke(provider, newobject[] { expression.Expression });
     
            var pagedItemCollectionBaseType = provider.GetType().Assembly.GetType("Microsoft.Xrm.Sdk.Linq.PagedItemCollectionBase");
            var queryExpressionProperty = pagedItemCollectionBaseType.GetProperty("Query");
            var queryExpression = (QueryExpression)queryExpressionProperty.GetValue(pagedItemCollection);
     
            return queryExpression;
        }
     
        public static string ToXml(thisQueryExpression queryExpression, IOrganizationService service)
        {
            var request = newQueryExpressionToFetchXmlRequest { Query = queryExpression };
            var response = (QueryExpressionToFetchXmlResponse)service.Execute(request);
            return response.FetchXml;
        }
    }
    You will also need this
    public class SkipTakeVisitor : ExpressionVisitor
    {
        protected override Expression VisitMethodCall(MethodCallExpression node)
        {
            if (node.Method.DeclaringType != typeof(Queryable))
                return base.VisitMethodCall(node);
     
            if (node.Method.Name == "Skip" || node.Method.Name=="Take")
                return base.Visit(node.Arguments[0]);
     
            return base.VisitMethodCall(node);
        }
    }

    This code will return a QueryExpression which you can use to append your PagingInfo. If you make a call to RetrieveMultiple it will be done in a single call.

    The big problem with this code is that it calls Execute which makes a call to the OrganizationService again. I don't want this since I am only interested in the QueryExpression. I have tried all kinds of reflection calls to the GetQueryExpression and Translate methods we are using with the CRM2011 SDK but I always get an Exception. Even using MakeByRef parameters and creating instances of the internal SDK objects doesn't work.

    My solution to this was to intercept the call using the CustomOrganizationService class above. The final code now looks like this:

    public class CustomOrganizationService : OrganizationService
    {
        public CustomOrganizationService(CrmConnection connection)
            : base(connection)
       
        {
        }
     
        public override Microsoft.Xrm.Sdk.OrganizationResponse Execute(Microsoft.Xrm.Sdk.OrganizationRequest request)
        {
            var retrieveMultipleRequest = request as RetrieveMultipleRequest;
            if (retrieveMultipleRequest != null)
            {
                var x = retrieveMultipleRequest.Query as QueryExpression;
                if (x != null)
                {
                    if (x.PageInfo.Count == int.MaxValue)
                        return CreateFakeResponse();
                }
            }
     
            var result = base.Execute(request);
            return result;
        }
     
        private OrganizationResponse CreateFakeResponse()
        {
            Debug.WriteLine("Intercepting request");
     
            var parameters = new ParameterCollection();
            var items = new EntityCollection();
     
            parameters.Add("EntityCollection", items);
            var response = new RetrieveMultipleResponse()
            {
                ResponseName = "RetrieveMultiple",
                Results = parameters,
            };
     
            return response;
        }
    }

    As you can see, the reason I use a Visitor to remove Skip and Take parameters is because I don't need them in the QueryExpression. This way I can use a special value (int.MaxValue) to signal the CustomOrganizationService that it should incept the call.

    This is how you can use it:

            var query = (from e in contactSet.Query()
                         where e.LastName.Contains("E")
                         select e);
     
            var queryExpression = query.ToQueryExpression(service);
     
     
            queryExpression.PageInfo = new PagingInfo()
            {
                PageNumber = 1,
                Count = 50,
                ReturnTotalRecordCount = true
            };
     
            // This will create a QueryExpressionToFetchXmlRequest 
            // Don't do this for paging unless you need the XML
            var xml = queryExpression.ToXml(service);
     
            var collection = service.RetrieveMultiple(queryExpression);

    I will not be using this solution, but it sure was fun.

    • Edited by Ericvf_ Saturday, October 25, 2014 2:19 PM C# formatting
    Friday, October 24, 2014 11:36 PM
  • Hi Eric,

    thank you for sharing your solution, very interesting piece of code.


    My blog: www.crmanswers.net - Rockstar 365 Profile

    Saturday, October 25, 2014 1:18 AM
  • Finally .. I've made some actual progress .. I have valid reflection code that calls the GetQueryExpression method on the CRM2013 SDK QueryProvider. This makes the solution I posted last night a bit silly .. but that just proves what a good night of sleep does to a man.

    The following extension method will convert your LINQ query into a QueryExpression that has PagingInfo and also allows you to convert the query into FetchXML.

    public static class QueryProviderExtensions { public static QueryExpression ToQueryExpression<T>(this IQueryable<T> items) { var queryProvider = items.Provider; var queryProviderType = queryProvider.GetType(); var listType = typeof(List<>); var projectionType = queryProviderType.Assembly.GetType("Microsoft.Xrm.Sdk.Linq.QueryProvider+Projection"); var navigationSourceType = queryProviderType.Assembly.GetType("Microsoft.Xrm.Sdk.Linq.QueryProvider+NavigationSource"); var linkLookupType = queryProviderType.Assembly.GetType("Microsoft.Xrm.Sdk.Linq.QueryProvider+LinkLookup"); var linkLookupListType = listType.MakeGenericType(linkLookupType); object projection = null; object source = Activator.CreateInstance(navigationSourceType, new object[] { null, null }); object linkLookups = Activator.CreateInstance(linkLookupListType); bool throwIfSequenceIsEmpty = false; bool throwIfSequenceNotSingle = false; object[] arguments = new object[6]; arguments[0] = items.Expression; arguments[1] = throwIfSequenceIsEmpty; arguments[2] = throwIfSequenceNotSingle; arguments[3] = projection; arguments[4] = source; arguments[5] = linkLookups; var getQueryExpressionMethod = queryProviderType.GetMethod("GetQueryExpression", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public, null, new[] { typeof(Expression), typeof(bool).MakeByRefType(), typeof(bool).MakeByRefType(), projectionType.MakeByRefType(), navigationSourceType.MakeByRefType(), linkLookupListType.MakeByRefType(), }, null); var queryExpression = (QueryExpression)getQueryExpressionMethod.Invoke(queryProvider, arguments); return queryExpression; }

    }

    This means I am no longer calling the Execute method, so nothing to intercept there (Thank god, that was dreadful). Additionally I don't need to visit the Skip and Take methods either, which was all part of the same train wreck.

    Just use it like so:

            // This can be any query
            var query = (from e in contactSet.Query()
                         where e.LastName.Contains("e")
                         select e);
    
            // This is where the magic happens
            var queryExpression = query.ToQueryExpression();
    
            // We can now add paging info
            queryExpression.PageInfo = new PagingInfo()
            {
                PageNumber = 1,
                Count = 50,
                ReturnTotalRecordCount = true
            };
    
            // This will create a QueryExpressionToFetchXmlRequest, not needed for paging.
            var xml = queryExpression.ToXml(service);
    
            // Perform the actual request 
            var collection = service.RetrieveMultiple(queryExpression);

    @Microsoft, please expose a method somewhere that allows us to convert LINQ Queries into QueryExpressions. This has been asked a lot of times and I understand the reasons for marking the QueryProvider as Internal. However, the QueryExpression class is public and therefore it only seems logical to expose it.


    • Marked as answer by Ericvf_ Saturday, October 25, 2014 2:29 PM
    • Edited by Ericvf_ Saturday, October 25, 2014 2:31 PM
    Saturday, October 25, 2014 2:12 PM