Optimize Linq query with PredicateBuilder with N-N join

Question

I'm using Linq to query MS CRM 2011 Web Services. I've got a query that results in very poor SQL, it fetches too much intermediary data and its performance is horrible!! I'm new to it, so it may very well be the way I'm using it...

I've got two entities linked via an N-N relationship: Product and SalesLink. I want to recover a bunch of Product from their SerialNumber along with all SalesLink associated to them.

This is the query I have using PredicateBuilder:

// Build inner OR predicate on Serial Number list
var innerPredicate = PredicateBuilder.False<Xrm.c_product>();
foreach (string sn in serialNumbers) {
   string temp = sn; // This temp assignement is important!
   innerPredicate = innerPredicate.Or(p => p.c_SerialNumber == temp);
}

// Combine predicate with outer AND predicate
var predicate = PredicateBuilder.True<Xrm.c_product>();
predicate = predicate.And(innerPredicate);
predicate = predicate.And(p => p.statecode == (int)CrmStateValueType.Active);

// Inner Join Query
var prodAndLinks = from p in orgContext.CreateQuery<Xrm.c_product>().AsExpandable()
                                                                    .Where(predicate)
                                                                    .AsEnumerable()
                   join link in orgContext.CreateQuery<Xrm.c_saleslink>()
                        on p.Id equals link.c_ProductSalesLinkId.Id
                   where link.statecode == (int)CrmStateValueType.Active
                   select new {
                         productId = p.Id
                       , productSerialNumber = p.c_SerialNumber
                       , accountId = link.c_Account.Id
                       , accountName = link.c_Account.Name
                   };
...

Using SQL profiler, I saw that it causes an intermediate SQL query that has no WHERE clause, looking like this:

select 
top 5001 "c_saleslink0".statecode as "statecode"
  ...
, "c_saleslink0".ModifiedOnBehalfByName as "modifiedonbehalfbyname"
, "c_saleslink0".ModifiedOnBehalfByYomiName as "modifiedonbehalfbyyominame" 
from
 c_saleslink as "c_saleslink0" order by
 "c_saleslink0".c_saleslinkId asc

This returns a huge amount of (useless) data. I think the join is done on the client side instead of on the DB side...

How should I improve this query? I runs in around 3 minutes and that's totally unacceptable.

Thanks.

"Solution"

Based on Daryl's answer to use QueryExpression instead of Linq to CRM, I got this which gets the exact same result.

var qe = new QueryExpression("c_product");
qe.ColumnSet = new ColumnSet("c_serialnumber");
var filter = qe.Criteria.AddFilter(LogicalOperator.Or);
filter.AddCondition("c_serialnumber", ConditionOperator.In, serialNumbers.ToArray());
var link = qe.AddLink("c_saleslink", "c_productid", "c_productsaleslinkid");
link.LinkCriteria.AddCondition("statecode", ConditionOperator.Equal, (int)CrmStateValueType.Active);
link.Columns.AddColumns("c_account");
var entities = serviceProxy.RetrieveMultiple(qe).Entities.ToList();;

var prodAndLinks = entities.Select(x => x.ToEntity<Xrm.c_product>()).Select(x => 
                   new {
                      productId = x.c_productId
                    , productSerialNumber = x.c_SerialNumber
                    , accountId = ((Microsoft.Xrm.Sdk.EntityReference)((Microsoft.Xrm.Sdk.AliasedValue)x["c_saleslink1.c_account"]).Value).Id
                    , accountName = ((Microsoft.Xrm.Sdk.EntityReference)((Microsoft.Xrm.Sdk.AliasedValue)x["c_saleslink1.c_account"]).Value).Name
                   }).ToList();

I really would have liked to find a solution using Linq, but it seems to Linq to CRM is just not there yet...

I haven't used PredicateBuilder successfully in the CRM, but you may want to check out my answer (using Dynamic Linq) and the accepted answer (using PredicateBuilder) to the question [How to get all the birthdays of today?](http://stackoverflow.com/questions/10142724/how-to-get-all-the-birthdays-of-today/10146065#10146065) for successful implementations of what you're trying to do. — Peter Majeed, Sep 21 '12 at 13:57
@Daryl: Yes, it sort of did, thanks. I would have liked a Linq to CRM solution, but apart from splitting up my query in two smaller queries, I did not succeed... `WHERE IN` isn't supported very well with Linq to CRM it seems. — dstj, Sep 25 '12 at 14:26
I know this may be a bit dated but your original query contains an `AsEnumerable` which will cause all of the `products` meeting your predicate to be returned. Eliminating that will return an `IQueryable` and execution will continue to be deferred (server side). This was likely the cause of your intermediate SQL query. — andleer, Dec 29 '15 at 17:17

Daryl · Accepted Answer · 2012-09-21T14:21:33.997

95% of the time when you're having performance issues with a complicated query in CRM, the easiest way to improve the performance is to run a straight SQL query against the database (assuming this is not CRM online of course). This may be one of the 5% of the time.

In your case, the major performance issue you're experiencing is due to the predicate builder forcing a CRM Server (not the SQL database) side join of data. If you used a Query Expression (which is what your link statement get's translated) you can specify a Condition Expression with an IN operator that would allow you to pass in your serialNumbers collection. You could also use FetchXml as well. Both of these methods would allow CRM to perform a SQL side join.

Edit:

This should get you 80% of the way with Query Expressions:

IOrganizationService service = GetService();
var qe = new QueryExpression("c_product");
var filter = qe.Criteria.AddFilter(LogicalOperator.Or);
filter.AddCondition("c_serialnumber", ConditionOperator.In, serialNumbers.ToArray());
var link = qe.AddLink("c_saleslink", "c_productid", "c_productsaleslinkid");
link.LinkCriteria.AddCondition("statecode", ConditionOperator.Equal, (int)CrmStateValueType.Active);
link.Columns.AddColumns("c_Account");
var entities = service.RetrieveMultiple(qe).Entities.ToList();

This is a much better explanation of what I originally meant. — James Wood, Sep 21 '12 at 13:06
Wow, I didn't view it has a complicated query. It's a rather simple one I find. Anyway, I don't know how to construct the FetchXml xml, nor to use the Query Expression, but I'll give it a try... — dstj, Sep 21 '12 at 14:03
@dstj When you start working with predicate builders, you definitely up the level of complication for CRM's view. The SQL itself isn't that complicated at all. — Daryl, Sep 21 '12 at 14:13

James Wood · Answer 2 · 2012-09-21T08:19:25.890

0

You will probably find you can get better control by not using Linq to Crm. You could try:

FetchXml, this is an xml syntax, similar in approach to tsql MSDN.
QueryExpression, MSDN.
You could issue a RetrieveRequest, blog.

edited Sep 21 '12 at 08:19

answered Sep 20 '12 at 20:07

James Wood

17,286
4
46
89

What evidence do you have to support the claim "You will probably find you can get better performance by not using Linq to Crm"? – Peter Majeed Sep 21 '12 at 00:58
Performance is probably the wrong word, I suppose I really mean control. – James Wood Sep 21 '12 at 08:19
I was going to call you out for plagiarism, http://stackoverflow.com/a/12413323/227436, but then I looked twice and realized this was your answer as well... :) – Daryl Sep 21 '12 at 11:34
Yeah I was tempted to just link to the same answer but I changed one of the links – James Wood Sep 21 '12 at 12:55

Optimize Linq query with PredicateBuilder with N-N join

"Solution"

2 Answers2