如果没有结果出来，Dynamics CRM OData对扩展属性的查询筛选仅适用吗? [英] Dynamics CRM OData query filtering on expanded attributes only works if no results come out?

问题描述

我需要提取符合以下要求的Price List Item条记录:

I have a requirement to fetch Price List Item records which adhere to the following requirements:

1. 按特定的价目表过滤
2. 按特定货币过滤
3. 按包含给定字符串的相关Product的Name进行过滤

1. Filter by a specific PriceList
2. Filter by a specific currency
3. Filter by the Name of the related Product containing a given string

我得到的前两点都没问题，但是感觉好像无法很好地应对过滤.我从对Product实体的直接"查询开始:

I got the first two points working no problem, but it feels like expanding doesn't cope well with filtering. I started from a "straight" query on Product entity:

.../ProductSet?$filter=substringof('sometext', Name)

Equivalent SQL (targeting the corresponding CRM filtered views for clarity):
SELECT * FROM FilteredProduct WHERE ProductNumber LIKE '%sometext%'

以上查询有效，我可以对其进行调整，没有任何问题.但是，如果我尝试继续使用ProductPriceLevel(因此扩展了与Product(即product_price_levels)的关系)，我最终将得到以下结果:

The above query works, I can tweak it and have no issues. However, if I attempt to move on to ProductPriceLevel (thus expanding the relationship with Product, which is product_price_levels) I end up with this:

.../ProductPriceLevelSet?$expand=product_price_levels&$filter=substringof('sometext', product_price_levels/Name)

Equivalent SQL (again, targeting the relevant filtered views):
SELECT * FROM FilteredProductPriceLevel PPL JOIN FilteredProduct P 
    ON PPL.ProductId = P.ProductId WHERE P.ProductNumber LIKE '%sometext%'

我看到两种不同的结果:

Which has two different outcomes I see:

• 如果$filter没有匹配项，则可以正常工作并返回空结果集
• 如果$filter与某项匹配，我会报错

• If the $filter has no matches, it works fine and returns an empty result set
• If the $filter matches something, I get an error

代码:-2147220970

code: -2147220970

消息:加入"操作的结果选择器必须返回两个属性的匿名类型.

message: The result selector of the 'Join' operation must return an anonymous type of two properties.

当您遇到一次在多个实体上同时使用.Where()时，遇到LINQ-to-CRM的限制时，发生的AFAIK似乎无关紧要！

AFAIK that's what happens when you hit a limitation of LINQ-to-CRM regarding using .Where() on multiple entities at once... doesn't seem relevant!

我的查询出了什么问题?

What's wrong with my query ?

注意:我正在使用的CRM 2013是On-Premise，没有任何更新汇总/服务包.

NOTE: The CRM 2013 I'm using is On-Premise, without any update rollup / service pack.

还请注意:可以预期，等效的SQL可以完美运行

ALSO NOTE: The equivalent SQL, as can be expected, works perfectly

推荐答案

我不认为CRM OData支持在连接的实体上添加过滤器.尝试反转您实际开始使用的实体，并添加引用实体的路径:

I don't think CRM OData supports adding a filter on a joined entity. Try reversing the entity you're actually starting with, and add a path to the referencing entity:

ProductSet()?$filter=substringof('sometext',ProductNumber)&$expand=product_price_levels&$select=product_price_levels/*

P.S.如果您有linqPad，这是我用来生成此查询的查询:

P.S. If you have linqPad, this is the query I used to generate this:

from p in ProductSet
where p.ProductNumber.Contains("sometext")
select new { p.product_price_levels }

这篇关于如果没有结果出来，Dynamics CRM OData对扩展属性的查询筛选仅适用吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！