LINQ to Dynamics CRM Query filtering records local

Question

I have written a Linq to CRM query using CRM 2011 RC (v5) LINQ-to-CRM provider. I have a locally declared List<T> which I want to join to a CRM entity and I want the query to be executed on the CRM Server. An example might help:

MyObject myObject = new MyObject();
List<myAccount> myAccountsList = new List<myAccount>();

myAccountsList.Add(new myAccount() {AccountNumber = "123"};
myAccountsList.Add(new myAccount() {AccountNumber = "456"};

myObject.ListOfAccounts = myAccountsList;

var accountsQuery = from ax in myObject.ListOfAccounts
                    join a in orgContext.CreateQuery<customAccountEntity>() on ax.AccountNumber equals a.account_number
                    select a;

foreach(var item in accountsQuery)
{
    Console.WriteLine("Id of record retrieved: " + a.Id.ToString());
}

The code above compiles and executes, however, the filtering of the records is being performed locally after retrieving the entire CRM entity recordset. Obviously when the CRM entity contains thousands of rows the query will perform poorly or even timeout.

I have read about IQueryable and IEnumerable and tried converting the List using the AsQueryable() extension method, which had no effect. I need my above Linq query to run SQL like this:

SELECT a.*
FROM customAccountEntity AS a
WHERE a.account_number IN ('123', '456');

Or using a temporary table if wanted to join on multiple fields. How can I accomplish this?

Answer 1

Instead of playing with predicates you could also simply use the join expression for filtering.

var myLinqToCrmQuery =  from ax in myObject.ListOfAccounts
                            join cx in orgContext.CreateQuery<customAccountEntity> on ax.AccountNumber equals cx.account_number                      
                            select cx;

Cheers, Lukasz

Answer 2

Edit: Try that one:

MyObject myObject = new MyObject();
List<myAccount> myAccountsList = new List<myAccount>();

myAccountsList.Add(new myAccount() {AccountNumber = "123"};
myAccountsList.Add(new myAccount() {AccountNumber = "456"};

myObject.ListOfAccounts = myAccountsList;

var accountNumbers = myObject.ListOfAccounts.Select(a => a.AccountNumber);

var accountsQuery = orgContext.CreateQuery<customAccountEntity>()
                              .Where(a => accountNumbers.Contains(a.account_number));

foreach(var item in accountsQuery)
{
    Console.WriteLine("Id of record retrieved: " + a.Id.ToString());
}

Edit: if you query provider don't support Contains, build a Where condition with multiple OR, you can use predicate builder to make that easy

Answer 3

After a lot of head banging and research I have resolved the issue by using Predicate Builder and LINQKit. I need to create an Or based predicate using the keys in my local List<T> then pass the predicate to the Where LINQ extension method. Importantly, I need to call the AsExpandable extension method exposed by LINQKit. So my code would look like this:

var predicate = PredicateBuilder.False<customAccountEntity>();
// Loop through the local List creating an Or based predicate
foreach (var item in myAccountsList)
{
    string temp = item.AccountNumber;
    predicate = predicate.Or (x => x.customCrmEntityAttribute == temp);
}
// The variable predicate is of type Expression<Func<customAccountEntity, bool>>
var myLinqToCrmQuery =  from ax in myObject.ListOfAccounts
                        from cx in orgContext.CreateQuery<customAccountEntity>().AsExpandable().Where(predicate)
                        where ax.AccountNumber == cx.account_number
                        select cx;

foreach (resultItem in myLinqToCrmQuery)
{
    Console.WriteLine("Account Id: " + resultItem.Id);
}

The above code will run a SQL Statement on the CRM Server like this:

SELECT a.*
FROM customAccountEntity AS a
WHERE a.account_number = '123' OR a.account_number = '456'

This means I can create a dynamic where clause at runtime and know that my query will run the filtering logic on the CRM SQL Server. Hope this helps somebody else.