使用CRM 2011动态实体选择记录的百分比 [英] Select a percentage of records using CRM 2011 Dynamic Entity
问题描述
我正在开发一项服务，该服务通过动态实体（如Microsoft.Xrm.Sdk.Entity，即后期绑定方法）来使用CRM 2011数据。我故意不使用Xrm.cs方法（早期绑定）来保持解决方案的通用性。
I'm developing service which consumes CRM 2011 data via dynamic entities (as in, Microsoft.Xrm.Sdk.Entity, the late-binding method). I'm deliberately not using Xrm.cs method (early binding) in an attempt to keep my solution generic.
此外，我想避免直接连接到CRM数据库（例如EDMX），因为这将使我的解决方案无法用于托管CRM（例如，没有直接的数据库访问权限）。
Also, I want to avoid connecting to a CRM database directly (e.g. EDMX) as this would stop my solution being usable for a hosted CRM (e.g. with no direct DB access).
我有以下（简化）要求，我我真的在选择标准上苦苦挣扎：
I have the following (simplified) requirement, I'm really struggling with the selection criteria:
需要选择记录的随机7％ （并且
A random 7% of records needs to be selected (and updated).
在SQL中，选择标准相对容易-我知道如何选择随机百分比的记录。像这样的东西：
In SQL, the selection criteria would be relatively easy - I know how to select a random percentage of records. Something like:
SELECT TOP 7 PERCENT * FROM
(
SELECT TOP 1000 NEWID() AS Foo, [someColumns]
FROM [someTable]
)
AS Bar ORDER BY Bar.Foo ASC
这很好用。我收集的LINQ等效项是这样的：
This works perfectly. I gather the LINQ equivalent is something like:
from e in someEntities
orderby Guid.NewGuid()
select e;
虽然有一个问题，但我不知道在CRM 2011动态实体中使用LINQ的方法-相反，他们坚持使用某些限制性QueryExpression类/语法或fetchXML，如此页面（MSDN）。
There's a problem though, I don't know of a way to use LINQ with CRM 2011 dynamic entities - instead they insist on using either some restrictive QueryExpression classes/syntax, or fetchXML, as seen on this page (MSDN).
我已经确定了满足此要求的以下选项：
I've identified the following options for fulfilling this requirement:
-
使用动态实体，将整个记录集返回到列表中，然后只需通过索引选择随机选择即可。但是，这涉及通过互联网数据服务返回多达10,000条记录，这可能是缓慢/不安全的等等。
Using dynamic entities, return the whole record set into a List, then simply choose a random selection by index. This however involves returning up to 10,000 records over an internet data service, which may be slow/insecure/etc.
使用fetchXML语句。不幸的是，我不知道fetchXML，所以不知道是否可以执行COUNT，TOP，PERCENT或NEWID（）之类的事情。
Use a fetchXML statement. Unfortunately I don't know fetchXML, so I don't know if it's possible to do things like COUNT, TOP, PERCENT or NEWID().
使用Xrm.cs和LINQ，或使用存储过程或SQL视图。所有这些选项都意味着将解决方案绑定到直接数据库连接和/或早期绑定上，这是不希望的。
Use Xrm.cs and LINQ, or use a Stored Procedure, or a SQL view. All of these options mean tying the solution down to either direct database connectivity and/or early binding, which is not desirable.
客户。
任何建议将不胜感激！ fetchXML可以执行此查询吗？有更好的方法吗？
Any advise would be greatly appreciated! Can fetchXML perform this query? Is there a better way to do this?
推荐答案
FetchXML不支持此功能，因此您只能使用1或3。没错，3仅适用于本地版本，因为您无法使用CRM Online产品直接连接到SQL。但是，除非您完全确定客户将转移到CRM Online，否则我将选择这种方式。如果必须使用1，则可以至少将返回的列限制为仅记录的GUID，以减小有效负载大小。然后，当您选择随机记录时，只要需要就可以获取它们的其他列（当然，由于个性（取决于要处理的随机记录的数量，最终结果可能会变慢）。
FetchXML does not support this, so you are down to either 1 or 3. And you are right, 3 would only work in the On Premise version, as you can't connect directly to SQL with the CRM Online product. However, that's the one I would go with unless you are absolutely sure the customer will be moving to CRM Online. If you must go with 1, you can at least limit the returned columns to only be the GUID of the record to decrease the payload size. Then when you select your random records, just go get their additional columns if needed (of course this could end up being slower due to "chattiness" depending on how many random records you are dealing with).
这篇关于使用CRM 2011动态实体选择记录的百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
