Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries

asp.net asp.net-mvc entity-framework linq-to-entities

Question

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;

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

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.

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.

Source Error:

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.

Stack Trace:

[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, TaskCompletionSource
1 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

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

1
3
1/5/2013 7:45:49 PM

Accepted Answer

LINQ, for most of its commands, employs deferred execution. It waits until you actually call for the data before it sends the query to that database. Here it looks like all of those queries are being deferred until later, when you try to grab something out of contactInfos.

I would try having it execute, like by throwing a .ToList() somewhere, to try and reduce the nesting that would be otherwise going on in the SQL.

EDIT: Since, per the comments, you appear to be getting the error on the first query, could you please try and having it say where id.Contains(accountsitemapping.ACCOUNTID)?

18
1/6/2013 12:55:29 AM

Popular Answer

For future reference, you can log the generated SQL using:

var db = new DbContext();
db.Database.Log = Console.Write;

This will allow you to figure out which part is getting deeply nested. From there, you may have to re-write your query to load expensive calculations in a second step. Or consider writing it as a stored procedure.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow