c# - LINQ to Dynamics CRM Query filtering records locally -

i have written linq crm query using crm 2011 rc (v5) linq-to-crm provider. have locally declared list<t> want join crm entity , want query executed on crm server. 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 = ax in myobject.listofaccounts                     join 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 , executes, however, filtering of records being performed locally after retrieving entire crm entity recordset. when crm entity contains thousands of rows query perform poorly or timeout.

i have read iqueryable , ienumerable , tried converting list using asqueryable() extension method, had no effect. need above linq query run sql this:

select a.* customaccountentity a.account_number in ('123', '456'); 

or using temporary table if wanted join on multiple fields. how can accomplish this?

after lot of head banging , research have resolved issue using predicate builder , linqkit. need create or based predicate using keys in local list<t> pass predicate linq extension method. importantly, need call asexpandable extension method exposed linqkit. code this:

var predicate = predicatebuilder.false<customaccountentity>(); // loop through local list creating or based predicate foreach (var item in myaccountslist) {     string temp = item.accountnumber;     predicate = predicate.or (x => x.customcrmentityattribute == temp); } // variable predicate of type expression<func<customaccountentity, bool>> var mylinqtocrmquery =  ax in myobject.listofaccounts                         cx in orgcontext.createquery<customaccountentity>().asexpandable().where(predicate)                         ax.accountnumber == cx.account_number                         select cx;  foreach (resultitem in mylinqtocrmquery) {     console.writeline("account id: " + resultitem.id); } 

the above code run sql statement on crm server this:

select a.* customaccountentity a.account_number = '123' or a.account_number = '456' 

this means can create dynamic clause @ runtime , know query run filtering logic on crm sql server. hope helps else.