SQL:嵌套查询没有适当的键 [英] SQL: Nested query does not have appropriate key

问题描述

在LINQ中，我试图将内部联接自定义函数编写为全文搜索和可查询的结果.

In LINQ, I am trying to inner join custom function written for full-text search and an Iqueryable result.

但是，当我尝试to_ret.select(--something--).ToList()

嵌套查询没有适当的键

Nested query does not have appropriate key

LINQ代码:

  var sql_query = db.search(st);
  var to_ret = from ts in sql_query
               from t in table
               where t.Id == ts.Value select t;

  to_ret = to_ret.Include(x => x.table1)
                .Include(x=> x.table2.Select(y=> y.table2Col));

  to_ret.select(-something-).toList();

SQL代码:

create function [dbo].[search]
      (@keywords nvarchar(4000))
returns table
as
  return (
  select [key] from containstable(tb,(Name,Description),@keywords)
)

代替上述LINQ代码的代码:

Code that works in place of above LINQ Code :

var ids = (from t in table join ts in db.search(st) on t.Id equals ts.Value select t.Id).ToList();

to_ret = to_ret.Where(x => ids.Contains(x.Id));

但是，有效的代码效率不高，因为它急切地加载了所有ids进行比较

However, the code that works isn't efficient enough as it eagerly loads all the ids for comparison

推荐答案

不要使用LINQ联接表，这是无效的.

Do not join the table using LINQ, it is not effective.

您需要将所有结合的函数包括在[dbo].[search]表值函数中(如视图).然后，只需从EF调用[dbo].[search]并对其进行过滤即可.

You need to include all the joined functions into [dbo].[search] table valued function (like a view). Then do just call the [dbo].[search] from EF and filter it.

我有提到在此处加入了全文表值函数.

I have mentioned joined Fulltext table valued function here.

请注意，在一个查询中将全文本和过滤器一起使用可能会花费一些时间，因为对于查询优化器而言，这并非一件容易的事.查询优化器选择先对整个表执行全文，然后再过滤或采用相反的方式.

Note that fulltext and filtering together in one query could take time, because it is not easy job for query optimizer. Query optimizer selects to perform first fulltext on entire table(s) and then filtering or the opposite way.

这篇关于SQL:嵌套查询没有适当的键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！