Dynamics CRM 2011-使用外部联接筛选LINQ查询 [英] Dynamics CRM 2011 - Filtering LINQ query with outer joins

问题描述

我需要在CRM中查询没有具有某种类型的相关实体的记录.通常，我将使用左外部联接"来执行此操作，然后过滤右侧所有具有NULL的行.

I have a requirement to query for records in CRM that don't have a related entity of a certain type. Normally, I would do this with an Left Outer Join, then filter for all the rows that have NULLs in the right-hand side.

例如:

var query = from c in orgContext.CreateQuery<Contact>()
            join aj in orgContext.CreateQuery<Account>()
                on c.ContactId equals aj.PrimaryContactId.Id
            into wonk
            from a in wonk.DefaultIfEmpty()
            where a.Name == null
            select new Contact
                   {
                       FirstName = c.FirstName,
                       LastName = c.LastName,
                   };

这应该向我返回不是帐户主要联系人的所有污染.但是，此查询最终返回了所有联系人...！当您查看在SQL事件探查器中生成的SQL时，结果如下:

This should return me any Contats that are not the Primary Contact of an account. However, this query ends up returning all contacts...! When you look at the SQL that gets generated in SQL Profiler it comes out like this:

SELECT cnt.FirstName, cnt.LastName
FROM Contact as cnt
    LEFT OUTER JOIN Account AS acct
        ON cnt.ContactId = acct.PrimaryContactId AND acct.Name is NULL

所以，我得到了Left Join OK，但是过滤器位于Join子句中的上，而不是WHERE子句中，而不是应有的，如下所示:

So, I get the Left Join OK, but the filter is on the Join clause, and not in a WHERE clause.and not as it should, like this:

SELECT cnt.FirstName, cnt.LastName
FROM Contact as cnt
    LEFT OUTER JOIN Account AS acct
        ON cnt.ContactId = acct.PrimaryContactId
WHERE acct.Name is NULL

很明显，此查询的结果非常不同！有没有办法在CRM上获取查询以生成正确的SQL?

Clearly, the results from this query are very different! Is there a way to get the query on CRM to generate the correct SQL?

这是基础FetchXML请求的限制吗?

Is this a limitation of the underlying FetchXML request?

推荐答案

不幸的是，这是CRM的LINQ和FetchXML实现的局限性. SDK的此页面指出不支持外部联接:

Unfortunately, this is a limitation of CRM's LINQ and FetchXML implementations. This page from the SDK states outer joins are not supported:

http://technet.microsoft.com/en-us/library/gg328328.aspx

虽然我找不到正式文档，但是对于提及FetchXML不支持左外部联接的人来说，有很多结果，例如:

And while I can't find an official document, there are a lot of results out there for people mentioning FetchXML does not support left outer joins, for example:

http://gtcrm .wordpress.com/2011/03/24/fetch-xml-reports-for-crm-2011-online/

这篇关于Dynamics CRM 2011-使用外部联接筛选LINQ查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！