Linq query - exception error

Question

0.00/5 (No votes)

See more:

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:

C#

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:

C#

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.

Posted 29-May-14 1:25am

miss786

Updated 29-May-14 3:19am

v2

Add a Solution

Comments

Andreas Gieriet 29-May-14 8:49am

What exactly is your input and what your output?
- You have a query object - what is this?
You seem to create dynamically a LINQ query from some outside data - this is also known under the term "dynamic LINQ": You construct a query on-the-fly. Is it what you want? If so, use one of the available Dynamic LINQ implementations or construct your own by constructing an Expression tree to pass to the Linq-To-SQL provider.
Cheers
Andi
PS: I see in your records that you ask many questions on LINQ and Database in the last few weeks. How about working through some tutorial on LINQ to at least half way understand the machinery?

miss786 29-May-14 11:43am

Thank you for your response and feedback. I have updated my original post above in regards to the input and query object class. I have tried adding in [Enumerable.Range(1, 50);], to contain clause but I cannot seem to get that to work.
Please advice, if possible. Thank you.

2 solutions

Solution 1

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[^]

Posted 29-May-14 3:54am

Ranjeet Patel

Comments

miss786 29-May-14 10:15am

Thank you for response. My code above, is currently passing the data value as ToList. If anyone ,could provide some guide to how I can remove the limit to search contain clause. Many thanks.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andreas Gieriet · Accepted Answer · 2014-05-29T06:37:00

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:

C#

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

(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:

C#

...
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
...

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