Microsoft Dynamics Crm Sdk-此查询可能吗？ [英] Microsoft Dynamics Crm Sdk - Is this query possible?

问题描述

我正在探索用于通过Dynamics CRM SDK检索数据的 QueryExpression机制，我认为我遇到了SDK的问题/局限性，但是我想确定这一点。

I am exploring the "QueryExpression" mechanism used to Retrieve data via the Dynamics CRM SDK, and I think I have hit a problem / limitiation with the SDK, but I would like to ascertain that for certain..

给出以下所需的SQL：

Given this desired SQL:

Select C.firstname, C.lastname 
FROM contact C 
INNER JOIN customeraddress A on C.contactid = A.parentid
WHERE 
((C.firstname = 'Max' AND C.lastname = 'Planck') OR (C.firstname = 'Albert' AND C.lastname = 'Einstein'))
OR 
A.Line1 = 'The secret moonbase'

我似乎无法将上述过滤条件（where子句）转换为等效的SDK条件/ filterexpressions等。

I cannot appear to translate the filter criteria above (the where clause) into the equivalent SDK conditions / filterexpressions etc.

如您所见，我要查询：-

As you can see, I want to query:-

1. 联系人，加入到customeraddress（这很简单，只需将链接实体添加到查询表达式）


2. ，其中联系人是Albert Einstein或Max Planck（再次是s例如，将FilterExpressions添加到QueryExpression中）


3. 或客户地址 line1等于秘密月底（这是有问题的位，我会在后面添加筛选条件链接到LinkEntity，Dynamics将 AND与主实体上的条件/筛选器结合使用。

1. contact, joined to customeraddress (thats simple, just add a link entity to the query expression),
2. where the contact is either Albert Einstein, or Max Planck (Again, that is simple, add FilterExpressions to the QueryExpression)
3. OR the customeraddress 'line1' equals 'the secret moonbase' (This is the problematic bit, as soon as I append filter criteria to the LinkEntity, Dynamics uses an "AND" conjunction with the criteria / filters on the main entity.

所以我遇到了问题已经在上面的第3点中进行了描述，这意味着我无法查询以下动态：

So the problem I have described in point 3 above, means I can't query dynamics for:

1. （阿尔伯特·爱因斯坦或马克斯·普朗克）或任何人

1. (Albert Einstein Or Max Planck) or anyone who lives at the secret moonbase.

这是目前对SDK的限制吗？

Is this is a current limtation of the SDK?

推荐答案

好，我已经找到了答案，部分原因是@mwrichardsone促使我探索了Dynamics Crm Linq查询提供程序是如何做到的，这样我便可以从那里向后工作。

Ok, I have discovered the answer to this, thanks in part to @mwrichardsone who prompted me to explore how the Dynamics Crm Linq query provider does it, I was then able to work backwards from there..

所以这是等效的Linq查询表达式，它可以工作（我正在使用CrmOrganisationServiceContext）：-

So here is the equivalent Linq query expression which works (I am using the CrmOrganisationServiceContext):-

var contactsQuery = from c in orgService.CreateQuery("contact")
                    join a in orgService.CreateQuery("customeraddress") on (Guid)c["contactid"] equals (Guid)a["parentid"]
                                where (((string)c["firstname"] == "Max" && (string)c["lastname"] == "Planck")
                                || ((string)c["firstname"] == "Albert" && (string)c["lastname"] == "Einstein"))
                                || (string)a["line1"] == "The secret moonbase"
                    select c;

http：// pogo69.wordpress.com/2012/04/05/crm-linq-provider-conversioning-expressions-to-queryexpression-andor-fetchxml/

一次我应用了该技术，我能够看到等效的QueryExpression的样子。基本上，我所缺少的一点（关键见识）是，当添加ConditionExpression时，可以将其设置为 EntityName。这意味着您可以将ConditionExpression添加到父/主实体上的过滤器组，即使该条件实际上是存在于链接实体上的属性（在本例中为customeraddrress line1）。我以为您必须将条件添加到具有该特定属性的链接实体中-这也是@Henk van Boeijen在他的回答中所做的-并且没有给出正确的结果。

Once i applied that technique I was able to see what the equivalent QueryExpression looks like.. and basically, the bit that I was missing (key insight) is that when you add a ConditionExpression you can set it's "EntityName". This means you can add a ConditionExpression to a filter group thats on the parent / main entity, even though the condition is actually for an attribute thats present on a link entity (in this case customeraddrress line1). I was assuming you had to add the condition to the linkentity that had that particular attribute - which is also what @Henk van Boeijen did in his answer - and that did not give the correct results.

因此，最终的QueryExpression如下所示（注意，地址行1的条件未添加到地址链接实体，它已添加到主实体上的过滤器组，并且设置了实体名称

So the final working QueryExpression looks like this (notice the condition for address line 1 is not added to the address link entity, its added to the filter group on the main entity, and it has an "entity name" set to the alias of the link entity)

var orgService = serviceProvider.GetOrganisationService();
        using (orgService as IDisposable)
        {

            var query = new QueryExpression("contact");
            query.ColumnSet.AddColumn("firstname");
            query.ColumnSet.AddColumn("lastname");

            // so link in customer address.
            query.AddLink("customeraddress", "contactid", "parentid", JoinOperator.Inner);
            var addressLink = query.LinkEntities[0];
            addressLink.EntityAlias = "A";
            addressLink.IncludeAllColumns();

            // conditions for max planck
            var firstName1Condition = new ConditionExpression("firstname", ConditionOperator.Equal, "Max");
            var lastname1Condition = new ConditionExpression("lastname", ConditionOperator.Equal, "Planck");

            // Groups those conditions using an "AND" conjunction.
            var maxPlankFilter = new FilterExpression(LogicalOperator.And);
            maxPlankFilter.AddCondition(firstName1Condition);
            maxPlankFilter.AddCondition(lastname1Condition);

            // conditions for albert einstein
            var firstname2Condition = new ConditionExpression("firstname", ConditionOperator.Equal, "Albert");
            var lastname2Condition = new ConditionExpression("lastname", ConditionOperator.Equal, "Einstein");

            // Groups those conditions using an "AND" conjunction.
            var albertEinsteinFilter = new FilterExpression(LogicalOperator.And);
            albertEinsteinFilter.AddCondition(firstname2Condition);
            albertEinsteinFilter.AddCondition(lastname2Condition);

            // could optionally chain the 2 filters so we get Albert's contitions chained (using AND) to max's conditions 
            //  albertEinsteinFilter.AddFilter(maxPlankFilter);

            // conditions for address line 1 moonbase
            var addressLine1Filter = new FilterExpression(LogicalOperator.And); 
            var line1Condition = new ConditionExpression("A", "line1", ConditionOperator.Equal, "The secret moonbase");
            addressLine1Filter.AddCondition(line1Condition);


            // add filters to query 
            // ensures each filter that we add to our queries criteria is chained together using an OR.
            query.Criteria.FilterOperator = LogicalOperator.Or;
            query.Criteria.AddFilter(albertEinsteinFilter);
            query.Criteria.AddFilter(maxPlankFilter);
            query.Criteria.AddFilter(addressLine1Filter);

            var results = orgService.RetrieveMultiple(query);
            int resultCount = 0;
            foreach (var r in results.Entities)
            {
                resultCount++;
                Console.WriteLine(string.Format("{0} {1} {2}", (string)r["firstname"], (string)r["lastname"], (string)((AliasedValue)r["A.line1"]).Value));
            }
            Console.WriteLine("There were " + resultCount + " results..");


        }

侧面注：参见@Henk van Boeijen's如果您想查看用于构建查询表达式的较短语法，请在下面发布。但是，如果生产力确实是您的关注点，那么我将不得不在下面@Nicknow中回应您的意见，并建议您认真考虑使用Linq查询机制执行CRM查询。

Side Note: See @Henk van Boeijen's post below if you would like to see a shorter syntax for building a query expression. If productivity is truly your concern however, I would have to echo the comment from @Nicknow below and suggest that you seriously take a look at using the Linq query mechanism for performing CRM queries.

还@Henk van Boeijen指出，我的答案基于仅在2013 SDK中出现的功能，而在以前的版本中似乎没有。我尚未亲自检查此信息，但是对于您来说，了解这些信息可能非常有用，尤其是如果您未使用最新版本的SDK。

Also @Henk van Boeijen has pointed out that my answer is based on a feature that only appears in the 2013 SDK, and doesn't appear to be in prior versions. I haven't checked this personally, but that information is probably very useful for you to know especially if you are not using the latest versions of the SDK.

这篇关于Microsoft Dynamics Crm Sdk-此查询可能吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！