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