过滤：哪里......在{...} [英] Filter: Where ... In { ... }

问题描述

嗨！

我想为动态创建的查询添加额外的过滤器。

My EntityModel：  MASTER< ------->详情（一个主人有很多细节，一个细节属于一个主人）

现在我可能有这样的事情：

ObjectQuery< Master> query = context.Master.Include（" Details"）。其中（m => m.master_property == 1）;

对于此查询，我现在想要添加一个额外的过滤器：

 query.Where（" it.master_id IN {Select master_id FROM ModelNamespace.CustomDataBaseFunction（@ userid）}"） ; 
 query.Parameters.Add（new ObjectParameter（" userid"，userid））; 
 query.Execute（MergeOption.AppendOnly）; 

但我无法获得额外的 Where 工作。 CustomDataBaseFunction存在于SSDL中，返回类型为
Collection（uniqueidentifier）。但是当我运行应用程序时，会抛出异常，该异常表示查询在 Where 子句中的
选择附近无效。

CustomDataBaseFunction看起来像这样并返回一个具有一行（master_id）的表，其类型为uniqueidentifier：

 ALTER FUNCTION [dbo]。[AllowedMastersForRead]（@ user uniqueidentifier）
 RETURNS TABLE 
 AS 
 RETURN 
 
（
 SELECT DISTINCT master_id 
 FROM masters 
 ... some joins ... 
 WHERE users.user_id = @user 
）

当我自己编写查询并在SQL Server Management Studio中执行它时，它的工作非常完美。

有没有办法让它工作？

感谢您的帮助！

解决方案

{}用于创建多重集，然后包含的元素是表达式或文字。

Hi!

I want to add an additional filter to a dynamic created query.

My EntityModel:  MASTER <-------> DETAILS (one Master has many Details, one Detail belongs to one Master)

Now I could have something like this:
ObjectQuery<Master> query = context.Master.Include("Details").Where(m => m.master_property == 1);
To this query I want now add an additional filter:

query.Where("it.master_id IN {Select master_id FROM ModelNamespace.CustomDataBaseFunction(@userid)}");
query.Parameters.Add(new ObjectParameter("userid", userid));
query.Execute(MergeOption.AppendOnly);

But I can't get the additional Where working. The CustomDataBaseFunction exists in the SSDL with Returntype Collection(uniqueidentifier) . But when I run the app a exception is thrown which says that the query is not valid near the Select in the Where clause.

The CustomDataBaseFunction looks like this and returns a Table with one row (master_id) which is of type uniqueidentifier:

ALTER FUNCTION [dbo].[AllowedMastersForRead] (@user uniqueidentifier)
RETURNS TABLE
AS
RETURN 

(
  SELECT DISTINCT master_id
	FROM masters
    ...some joins...
	WHERE users.user_id = @user 
)

When I write the query by myself and execute it in SQL Server Management Studio it works perfect.

Is there a way to get this working?
Thanks for your help!

解决方案

{ } are used to create multiset, then the containing elements are expressions or literals.

这篇关于过滤：哪里......在{...}的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！