LINQ All() работает в одном запросе, а не в аналогичном запросе. ⇐ C#

[Anonymous]

Я успешно использовал следующий запрос LINQ для создания словаря.

Код: Выделить всё

searchResults = sharedFiles.Where(x => words
.All(y => x.Value.FileName.ToLower().Contains(y.ToLower())) && x.Value.IsFolder == false)
.OrderBy(x => x.Value.FileName)
.ToDictionary(t => t.Key, t => t.Value)

Но теперь аналогичный запрос LINQ завершается неудачно.

Код: Выделить всё

searchResults = ctx.BaseFiles.Where(x => words
.All(y => x.FileName.ToLower().Contains(y.ToLower())) && x.IsFolder == false)
.OrderBy(x => x.FileName)
.ToList();

Вот сообщение об исключении...

Код: Выделить всё

Microsoft.Data.SqlClient.SqlException
HResult=0x80131904
Message=Incorrect syntax near the keyword 'WITH'.
Incorrect syntax near the keyword 'with'.  If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Source=Core Microsoft SqlClient Data Provider
StackTrace:
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.c.b__21_0(DbContext _, Enumerator enumerator)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at InfoHub_Models.Models.DAL.Search(String searchValue) in C:\Users\31f935\source\repos\DSS-ES-InfoHub\InfoHub-Models\Models\DAL.cs:line 71
at DSS_ES_InfoHub.Controllers.HomeController.Search(String searchValue) in C:\Users\31f935\source\repos\DSS-ES-InfoHub\DSS-ES-InfoHub\Controllers\HomeController.cs:line 42
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean&  isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()

А вот вывод SQL

Код: Выделить всё

 Executing DbCommand [Parameters=[@__words_0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT [b].[Id], [b].[CreationDate], [b].[Extension], [b].[FileName], [b].[IsFolder], [b].[ParentId], [b].[ParentPath], [b].[Path], [b].[SharedLink]
FROM [BaseFiles] AS [b]
WHERE NOT EXISTS (
SELECT 1
FROM OPENJSON(@__words_0) WITH ([value] nvarchar(max) '$') AS [w]
WHERE [w].[value] IS NULL OR (CHARINDEX(LOWER([w].[value]), LOWER([b].[FileName])) 

Подробнее здесь: [url]https://stackoverflow.com/questions/79231538/linq-all-works-in-one-query-not-in-a-similar-query[/url]