SQL: Nested query does not have corresponding key

Question

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

However, when trying to_ret.select(--something--).ToList()

I get the following error:

The subquery does not have a corresponding key

LINQ Code:

  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 code:

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

Code that works instead of the 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)); 

However, the code that works is not efficient enough since it readily downloads all ids for comparison

Answer 1

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

You need to include all the combined functions in the [dbo] function. [search] table value (as a view). Then just call [dbo]. [Search] from EF and filter it.

My mentioned has joined the full-text table function.

Please note that full text and filtering in one query can take a lot of time, because this is not an easy task for the query optimizer. The query optimizer chooses to execute the first full text on the entire table (s), and then filter or return.