An unhandled exception of type 'System.StackOverflowException' occurred in EntityFramework dll

c# entity-framework-6 predicatebuilder

Question

I get this exception when I try to process 270k records. It fails at 12k. Can someone explain to me what I am missing?

The database is SQL and I am using EF 6. I am using predicate builder to build my where clause. The idea being select * from table where ((a = 'v1' and b = 'v2') or (a = 'v11' and b = 'v21') or (a = 'v12' and b = 'v22') ..) I don't see anywhere that I still hold reference to my object that represents EF class. I am creating a POCO for the result I want to send back to view. Any ideas?

Also I am using CommandTimeout of 10000 and the point where it fails, when I run the query with same paramters in sql management studio, it returns 400 rows.

When I ran profiler, I noticed a few seconds before I got the error, memory usage shot up to 1GB+

Thanks

    public List<SearchResult> SearchDocuments(List<SearchCriteria> searchCriterias)
{
            List<SearchResult> results = new List<SearchResult>();
            var fieldSettings = GetData() ;// make a call to database to get this data
            using (var context = CreateContext())
            {
                var theQuery = PredicateBuilder.False<ViewInSqlDatabase>();
                int skipCount = 0;
                const int recordsToProcessInOneBatch = 100;
                while (searchCriterias.Skip(skipCount).Any())
                {
                    var searchCriteriasBatched = searchCriterias.Skip(skipCount).Take(recordsToProcessInOneBatch);
                    foreach (var searchCriteria in searchCriteriasBatched)
                    {
                        var queryBuilder = PredicateBuilder.True<ViewInSqlDatabase>();
                        // theQuery
                        if (searchCriteria.State.HasValue)
                            queryBuilder = queryBuilder.And(a => a.State == searchCriteria.State.Value);
                        if (!string.IsNullOrWhiteSpace(searchCriteria.StateFullName))
                            queryBuilder = queryBuilder.And(a => a.StateName.Equals(searchCriteria.StateFullName, StringComparison.CurrentCultureIgnoreCase));
                        if (searchCriteria.County.HasValue)
                            queryBuilder = queryBuilder.And(a => a.County == searchCriteria.County.Value);
                        if (!string.IsNullOrWhiteSpace(searchCriteria.CountyFullName))
                            queryBuilder = queryBuilder.And(a => a.CountyName.Equals(searchCriteria.CountyFullName, StringComparison.CurrentCultureIgnoreCase));
                        if (!string.IsNullOrWhiteSpace(searchCriteria.Township))
                            queryBuilder = queryBuilder.And(a => a.Township == searchCriteria.Township);
                        // and so on...for another 10 parameters
                        theQuery = theQuery.Or(queryBuilder.Expand());
                    }
                    // this is where I get error after 12k to 15k criterias have been processed
                    var searchQuery = context.ViewInSqlDatabase.AsExpandable().Where(theQuery).Distinct().ToList();
                    foreach (var query in searchQuery)
                    {
                        var newResultItem = SearchResult.Create(query, fieldSettings); // POCO object with no relation to database
                        if (!results.Contains(newResultItem))
                            results.Add(newResultItem);
                    }

                    skipCount += recordsToProcessInOneBatch;
                }
            }

            return results.Distinct().OrderBy(a => a.State).ThenBy(a => a.County).ThenBy(a => a.Township).ToList();
}
1
0
2/23/2016 5:07:30 PM

Popular Answer

Fourat is correct that you can modify your query to context.SearchResults.Where(x => ((x.a == 'v1' &&x.b == 'v2') || (x.a = 'v11' &&x.b = 'v21') || (x.a = 'v12' && x.b = 'v22')).Distinct().OrderBy(a => a.State).ThenBy(a => a.County).ThenBy(a => a.Township).ToList(); What this do with make the database do the heavy lifting for you and you

I would also suggest that you use lazy evaluation instead of forcing it into a list if you can.

0
2/23/2016 6:42:22 PM


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