none
How to use split and contain clause?

    Question

  • Dear all, 

    I am trying to call the query( api/test/name=stop,tap,app...(24 names values)), but I get a following error -- EntityCommandExeceptionException error as an output response:

    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

    var data = db.database_bd.AsQueryable();
     
                    if (query.startDate != null)
                    {
                        data = data.Where(c => c.UploadDate >= query.startDate);
                    }
     
                    // If any other filters are specified, return records which match any of them:
                    var filteredData = new List<IQueryable<database_bd>>();
     
                    if (!string.IsNullOrEmpty(query.name))
                    {
                        var ids = query.name.Split(',');
                        foreach (string i in ids)
                        {
                            filteredData.Add(data.Where(c => c.Name != null && c.Name.Contains(i)));
                        }
                    }
    
                    if (filteredData.Count != 0)
                    {
                        data = filteredData.Aggregate(Queryable.Union);
                    }

    I assuming its the contain clause causing the issue.  

    Any advice or help, in how to improve the contain clause, would be greatly appreciated. 

    Thanks in advance.

    Friday, April 25, 2014 9:38 AM

Answers

  • Hello,

    >>I assuming its the contain clause causing the issue.

    Being not sure whether it is the contain clause causing this issue. You can call the .ToList() method to check whether it is as:

    data.Where(c => c.Name != null && c.Name.Contains(i)).ToList();

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

    Usually, this message shows that it's the LINQ-generated SQL query that gets over the limit.

    Have a try with this to use contains:

    if (!string.IsNullOrEmpty(query.name))
    
                    {
    
                        var ids = query.name.Split(',');
    
                        data.Where(c => ids.Contains(c.Name));
    
                    }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by missy786 Thursday, May 01, 2014 2:30 PM
    Monday, April 28, 2014 3:10 AM