SQL语句的某些部分嵌套太深。重写查询或将其分解成更小的查询 [英] Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries

问题描述

我有我的asp.net MVC Web应用程序内的folloiwng方法和我使用实体框架作为数据访问层： -

I have the folloiwng method inside my asp.net mvc web application and i am using Entity framework as the data access layer:-

        public IEnumerable<AaaUserContactInfo> getcontactinfo(long[] id)
        {
var organizationsiteids = (from accountsitemapping in entities.AccountSiteMappings
where id.Any(accountid => accountsitemapping.ACCOUNTID == accountid)
select accountsitemapping.SITEID).ToList();

var usersdepts = from userdept in entities.UserDepartments
join deptdefinition in entities.DepartmentDefinitions on userdept.DEPTID equals deptdefinition.DEPTID

where organizationsiteids.Any(accountid => deptdefinition.SITEID == accountid)

var contactsinfos = from contactinfo in entities.AaaUserContactInfoes 
                                join userdept in usersdepts on  contactinfo.USER_ID equals userdept.USERID

                                 select contactinfo;

            return contactsinfos;

但如果记录的数量是巨大的，然后我将得到folloiwng错误： -

But if the number of records are huge then i will get the folloiwng error:-

SQL语句的某些部分嵌套太深。重写
  查询或将其分解成更小的查询。说明：未处理
  当前Web请求的执行过程中发生异常。
  请检查堆栈跟踪有关该错误和更多信息
  它起源于code。

Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

异常详细信息：System.Data.SqlClient.SqlException：的某些部分
  您的SQL语句被嵌套太深。重写查询，或打破它
  成更小的查询。

Exception Details: System.Data.SqlClient.SqlException: Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

源错误：

的执行过程中生成了未处理的异常
  当前Web请求。有关的起源和位置信息
  除了可以使用异常堆栈跟踪下面来识别。

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

堆栈跟踪：

[SQLEXCEPTION（0x80131904）：你的SQL语句的某些部分是嵌套
  太深。重写查询或将其分解成更小的查询。]结果
  System.Data.SqlClient.SqlConnection.OnError（SqlException异常，
  布尔breakConnection，行动 1 wrapCloseInAction）388结果
  System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning（TdsParserStateObject
  stateObj，布尔callerHasConnectionLock，布尔asyncClose）688结果
  System.Data.SqlClient.TdsParser.TryRun（RunBehavior runBehavior，
  的SqlCommand cmdHandler，SqlDataReader的数据流，
  BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject
  stateObj，布尔和放大器; dataReady）4403结果
  System.Data.SqlClient.SqlDataReader.TryConsumeMetaData（）+82结果
  System.Data.SqlClient.SqlDataReader.get_MetaData（）+135结果
  System.Data.SqlClient.SqlCommand.FinishExecuteReader（SqlDataReader的DS，
  RunBehavior runBehavior，字符串resetOptionsString）6665229结果
  System.Data.SqlClient.SqlCommand.RunExecuteReaderTds（的CommandBehavior
  cmdBehavior，RunBehavior runBehavior，布尔returnStream，布尔
  异步，的Int32超时，任务和放大器;任务，布尔asyncWrite）6667096结果
  System.Data.SqlClient.SqlCommand.RunExecuteReader（的CommandBehavior
  cmdBehavior，RunBehavior runBehavior，布尔returnStream，字符串
  方法，TaskCompletionSource 1完成的Int32超时，任务和放大器;任务，
  布尔asyncWrite）577结果
  System.Data.SqlClient.SqlCommand.RunExecuteReader（的CommandBehavior
  cmdBehavior，RunBehavior runBehavior，布尔returnStream，字符串
  法）+107结果
  System.Data.SqlClient.SqlCommand.ExecuteReader（的CommandBehavior
  行为，串法）+ 288结果
  System.Data.SqlClient.SqlCommand.ExecuteDbDataReader（的CommandBehavior
  行为）180结果
  System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands（EntityCommand
  entityCommand，行为的CommandBehavior）+689

[SqlException (0x80131904): Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +388
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +688
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4403
System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +82
System.Data.SqlClient.SqlDataReader.get_MetaData() +135
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6665229
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) +6667096
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +577
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +107
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +288
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +180
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +689

而如果返回的记录数小则code将正常工作，所以可能是什么问题？

While if the number of records returned are small then the code will work fine , so what might be the problem?

推荐答案

每回答上面的评论中提到的重复问题，请尝试以下作为第一个查询的WHERE子句，因为它是最有可能这是一个让所有的烦恼：

Per the answer to the duplicate question mentioned above in the comments, try the following as the where clause of your first query, since it's most likely that that's the one giving all of the trouble:

其中id.Contains（accountsitemapping.ACCOUNTID）

这篇关于SQL语句的某些部分嵌套太深。重写查询或将其分解成更小的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！