LINQ到Dynamics CRM中查询筛选记录在本地 [英] LINQ to Dynamics CRM Query filtering records locally

问题描述

我已经写了使用CRM 2011 RC（V5）LINQ到CRM提供商的LINQ to CRM查询。我有一个局部声明表< T>我想加入到CRM的实体，我想成为CRM服务器上执行的查询。一个例子可以帮助：

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());
}

以上编译和执行的代码，然而，记录的滤波正在被检索整个CRM实体记录后，本地执行的。很明显，当CRM实体包含数千行的查询会很差，甚至暂停执行。

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.

我看了一下IQueryable的和IEnumerable并试图转换使用AsQueryable已（）扩展方法，该方法没有效果的列表。我需要上面的LINQ查询我运行的SQL是这样的：

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?

推荐答案

很多头撞和研究后，我已使用的谓词构建器和的 LINQKit 。我需要在我的本地列表<使用的键的创建一个基于OR谓词; T>然后通过谓词到哪里LINQ扩展方法。重要的是，我需要调用的 AsExpandable 的扩展由LINQKit暴露的方法。所以，我的代码是这样的：

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);
}

上面的代码将运行CRM服务器这样一个SQL语句：

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'

这意味着我可以创建一个动态的where子句在运行时，我知道自己的查询将运行CRM SQL Server上的筛选逻辑。希望这可以帮助别人。

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.

这篇关于LINQ到Dynamics CRM中查询筛选记录在本地的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！