Linq查询 - 异常错误 [英] Linq query - exception error

问题描述

我试图用50或更多值调用tag参数（api / test / tag = 9008,9008,9008 ... [50]），但是，它继续抛出一个 - > SQL语句的某些部分嵌套得太深。重写查询或将其分解为较小的查询 - >异常错误，在以下代码行上：

var data = Data.ToList（）;



查询允许我搜索最多45个标签值，除此之外，它会抛出上述异常错误。关于如何改进以下查询，请咨询一下：

I trying to call tag parameter with 50 or more values (api/test/tag=9008,9008,9008...[50]), however, it keep throwing a --> Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries --> exception error, on the following line of code:
var data = Data.ToList();

The query allows me to search tag values upto 45, anything more than that, it throws the above exception error. Could some please advice, on how I can improve the following query below:

IQueryable<data_qy> Data = null;

               if (!string.IsNullOrEmpty(query.tag))
               {
                   var ids = query.tag.Split(',');

                   var dataMatchingTags = db.data_qy.Where(c => ids.Any(id => c.TAG.Contains(id)));

                   if (Data == null)
                       Data = dataMatchingTags;
                   else
                       Data = Data.Union(dataMatchingTags);
               }

               if (!string.IsNullOrEmpty(query.name))
               {
                   var ids = query.name.Split(',');

                   var dataMatchingTags = db.data_qy.Where(c => ids.Any(id => c.Name.Contains(id)));

                   if (Data == null)
                       Data = dataMatchingTags;
                   else
                       Data = Data.Union(dataMatchingTags);
               }

               if (Data == null) // If no tags or name is being queried, apply filters to the whole set of products
                   Data = db.data_qy;

               if (query.endDate != null)
               {
                   Data = Data.Where(c => c.UploadDate <= query.endDate);
               }

               if (query.startDate != null)
               {
                   Data = Data.Where(c => c.UploadDate >= query.startDate);
               }

               Data = Data.OrderByDescending(c => c.UploadDate);

               var data = Data.ToList();

               if (!data.Any())
               {
                   var message = string.Format("No data found");
                   return Request.CreateErrorResponse(HttpStatusCode.NotFound, message);
               }

               return Request.CreateResponse(HttpStatusCode.OK, data);
           }

查询类：

Query class:

public class Query
{
    public string name { get; set; }
    public string tag { get; set; }
    public Nullable<DateTime> startDate { get; set; }
    public Nullable<DateTime> endDate { get; set; }

}

输入数据：

Input data:

35729VAD9,65538DAA3,86358EME3,57643LBZ8,31381UYJ0,2027829AAC3,2086359AW74,2000764MFD4,2093364LAM0,93363CAC3,02660UAB7,04964KAC5,62475FAD4,38378B3K3,59020UKZ7,66859GAE7,126671UU8,61745M6V0,90270YAN9,126670VZ8,2061754JAL,20396789LM9,2068620CAB6,2074977YAA7,2094983PAC2,20225470G23,20576455AC3,2074041NAE5,2067091QAE1,2038377YXT2,2005524WAL9,2050183WAB9,2009253DAG1,20606935AJ3,20059512AU7,161571GA6,61745M3S0,108124AG3,144526AA8,92867LAB8,75115YAA7,17313KAL5,2007384YUV6,161571FL3,02149CBH3,93934FPT3,06424HBP1

输出回复：

Output Response:

{"$id":"1","Message":"An error has occurred.","ExceptionMessage":"Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.","ExceptionType":"System.Data.SqlClient.SqlException","StackTrace":"   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()\r\n   at System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)"}

请帮忙。



非常感谢。

Please help.

Many thanks.

推荐答案

id：1，消息：错误已发生。，ExceptionMessage：SQL语句的某些部分嵌套得太深。重写查询或将其分解为较小的查询。，ExceptionType：System.Data.SqlClient.SqlException，StackTrace：at System.Data.SqlClient.SqlConnection.OnError（SqlException exception，Boolean breakConnection，Action在System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning（TdsParserStateObject）的System.Data.SqlClient.SqlInternalConnection.OnError（SqlException异常，布尔breakConnection，Action`1 wrapCloseInAction）\\\\ n中的`1 wrapCloseInAction）\\\\ n System.Data.SqlClient.TdsParser.TryRun（RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream，BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject stateObj，Boolean& dataReady）\\\\ stateObj，Boolean callerHasConnectionLock，Boolean asyncClose）\\\\ n在System.Data.SqlClient.SqlClient.SqlClmand.SmandCommand.FinishExecuteRea的System.Data.SqlClient.SqlDataReader.get_MetaData（）\\\\ n中的System.Data.SqlClient.SqlDataReader.TryConsumeMetaData（）\\\\ n在System.Data.SqlClient.SqlCommand.RunExecuteReaderTds（CommandBehavior cmdBehavior，RunBehavior runBehavior，Boolean returnStream，Boolean async，Int32 timeout，Task&）中的der（SqlDataReader ds，RunBehavior runBehavior，String resetOptionsString）\\\\ n。在System.Data.SqlClient.SqlCommand.RunExecuteReader（CommandBehavior cmdBehavior，RunBehavior runBehavior，Boolean returnStream，String方法，TaskCompletionSource`1完成，Int32超时，任务和任务，布尔asyncWrite）中的task，Boolean asyncWrite）\\\在System.Data.SqlClient.SqlCommand.RunExecuteReader（CommandBehavior cmdBehavior，RunBehavior runBehavior，Boolean returnStream，String方法）中的\\ n在System.Data.SqlClient.SqlCommand.ExecuteReader中的\\\\ n（CommandBehavior behavior，String方法）\\\ at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader（CommandBehavior behavior）\ r \ n at System.Data.Common.DbCommand.ExecuteReader（CommandBehavior behavior）\\\ at System。 Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands（EntityCommand entityCommand，CommandBehavior behavior）}

id":"1","Message":"An error has occurred.","ExceptionMessage":"Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.","ExceptionType":"System.Data.SqlClient.SqlException","StackTrace":" at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()\r\n at System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)\r\n at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)"}

请帮忙。



非常感谢。

Please help.

Many thanks.

您可以尝试使用foll欠（对于 TAG 和 NAME ）

1）整合此代码 [ ^ ]进入你的项目

2）创建一个字符串来传递解析方法如下所示

3）而不是调用 compile（），将表达式传递给where子句



您的查询字符串如下所示：

You may try the following (for TAG and NAME)
1) integrate this code[^] into your project
2) create a string to pass to the parse method as shown below
3) instead of calling compile(), pass the expression to the where clause

Your query string would look like this:

string queryText = "TAG==\"35729VAD9\" ||TAG==\"...\" || ...";

（假设您的 data_qy 包含 TAG 成员对于 NAME 成员来说是明智的。



因此，创建的查询文本TAG 和 NAME ，您可以执行以下操作：

(assuming your data_qy contains a TAG member). Like wise for NAME member.

So, creating the query text for TAG and NAME, you may do the following:

...
var tags = query.tag.Split(',').Select(s=>string.Format("TAG==\"{0}\"", s));
var names = query.name.Split(',').Select(s=>string.Format("NAME==\"{0}\"", s));

string queryText = string.Join("||", tags.Concat(names));
if (queryText != string.Empty)
{
    var pred = SimpleExpression.PredicateParser<query_qy>.Parse(queryText);
    Data = Data.Where(pred);
}
// add the date-time queries here as in your example code
...

我没试过这个，但我猜它有用。

干杯

Andi

I did not try this out, but I guess it's functional.
Cheers
Andi

这可能对你有用。





http://stackoverflow.com/questions/14163390/some-part-of-your-sql-statement-is-nested-too-deeply-rewrite-the-query-or-break [ ^ ]

This might be useful for you.


http://stackoverflow.com/questions/14163390/some-part-of-your-sql-statement-is-nested-too-deeply-rewrite-the-query-or-break[^]

这篇关于Linq查询 - 异常错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！