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

Question

I have action in my controller which calls the following method :

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

    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)
                    select userdept;

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

    return  contactsinfos;
}

But when I run the application and I navigate to the action method the folowing error will be raised on the view level:-

System.Data.EntityCommandExecutionException was unhandled by user code
  HResult=-2146232004
  Message=An error occurred while executing the command definition. See the inner exception for details.
  Source=System.Data.Entity
  StackTrace:
       at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
       at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
       at System.Data.Entity.Internal.Linq.InternalQuery`1.GetEnumerator()
       at System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator()
       at System.Linq.Enumerable.Count[TSource](IEnumerable`1 source)
       at ASP._Page_Views_Home_CustomersDetails_cshtml.Execute() in c:\Users\Administrator\Desktop\new app DEMO2\MvcApplication4 - LATEST -\MvcApplication4\Views\Home\CustomersDetails.cshtml:line 6
       at System.Web.WebPages.WebPageBase.ExecutePageHierarchy()
       at System.Web.Mvc.WebViewPage.ExecutePageHierarchy()
       at System.Web.WebPages.StartPage.RunPage()
       at System.Web.WebPages.StartPage.ExecutePageHierarchy()
       at System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext, TextWriter writer, WebPageRenderingBase startPage)
       at System.Web.Mvc.RazorView.RenderView(ViewContext viewContext, TextWriter writer, Object instance)
       at System.Web.Mvc.BuildManagerCompiledView.Render(ViewContext viewContext, TextWriter writer)
       at System.Web.Mvc.ViewResultBase.ExecuteResult(ControllerContext context)
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionResult(ControllerContext controllerContext, ActionResult actionResult)
       at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass1c.<InvokeActionResultWithFilters>b__19()
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilter(IResultFilter filter, ResultExecutingContext preContext, Func`1 continuation)
  InnerException: System.Data.SqlClient.SqlException
       HResult=-2146232060
       Message=Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
       Source=.Net SqlClient Data Provider
       ErrorCode=-2146232060
       Class=15
       LineNumber=105
       Number=191
       Procedure=""
       StackTrace:
            at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
            at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
            at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
            at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
            at System.Data.SqlClient.SqlDataReader.get_MetaData()
            at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
            at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
            at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
            at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       InnerException:

So what is causing this error??

UPDATED:- enter image description here

Corey Adler · Accepted Answer · 2013-01-06T00:55:29.653

17

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)?

edited Jan 06 '13 at 00:55

answered Jan 04 '13 at 19:19

Corey Adler

15,897
18
66
80

1

but i have note that if i query less data then the queires will work without errors, but if the number of records which are returned from the database are large then the exception will raise. – Jan 04 '13 at 19:22
1

All the more reason, then, to break up the queries. If you want your application to properly scale, you're going to need to break them up. – Corey Adler Jan 04 '13 at 19:30
what you mean by break them up, and can not break them more than the way i am doing ? – Jan 04 '13 at 19:36
As in don't let deferred execution do that to you. Use .ToList after the first or second query to prevent this from happening. – Corey Adler Jan 04 '13 at 19:44
i insert .tolist() in the fitst query but now the error will be raised on my update code with the .tolist() instead of being raised on the view ? ,,, any advice ?? thnaks – Jan 04 '13 at 20:14
Is it still being raised when you do that? – Corey Adler Jan 04 '13 at 20:22
yes, and on the code contaning the .tolist ,, previously the error occured on the view level – Jan 04 '13 at 20:24
1

Right. It's giving the error there because that's where the query is actually being run (instead of before being on the view because of deferred execution). See my edit for another possible solution. – Corey Adler Jan 06 '13 at 00:54
As a related issue, joining to a list adds a UNION ALL SELECT for each item in the list. If the number of items in the list is > than the number of nested levels then you get the same error. – Noel Feb 04 '14 at 22:05
Use Contains and do not use Any. A solution that does not seem right but it works. – arame3333 Nov 06 '15 at 09:14

score 6 · Answer 2 · answered Sep 22 '15 at 14:19

6

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.

answered Sep 22 '15 at 14:19

John Zabroski

2,212
2
28
54

2

`dataContext.Log = Console.Out` If using a [DataContext](http://stackoverflow.com/a/18238103/1175496) instead – Nate Anderson Oct 16 '15 at 22:36

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

2 Answers2

Linked

Related