I have a web application, made using c#/mvc/ef/unitofworkPattern. I have database with two tables, PERSON and CASENOTE. A PERSON may have multiple CASENOTES, but a CASENOTE may only have one PERSON. The key in PERSON is PasNo. This is a foreign key in CASENOTE, but it's not defined as a foreign key in DDL for reasons.
So, I have a search page, where someone can input data into a CASENOTE number textbox, and get back all people linked to any casenotes it may find. So they could put in just a partial casenote, and it would get back all casenotes containing that partial string, and then find all of the PEOPLE that have those casenotes using the PasNo in each casenote.
So here is my code:
public IEnumerable<PERSON> GetSearchResults(SearchViewModel viewModel)
{
var people = unitOfWork.PersonRepository.GetAll();
if (viewModel.Casenote != null)
{
var casenotes = unitOfWork.CasenoteRepository.GetAll()
.Where(x => x.CASENOTE1.Trim()
.ToLower()
.Contains(viewModel.Casenote.Trim().ToLower()))
.Take(1000)
.ToList();
if (casenotes.Count > 0)
{
foreach (var casenote in casenotes)
{
var pasint = casenote.PAS_INT_NO;
people = people.Where(w => w.PAS_INT_NO == pasint);
//it gives me the stackoverflow.exception at execution of the above line
}
}
}
return people.Take(1000).ToList().OrderBy(x => x.SURNAME).ThenBy(x => x.FORENAMES);
}
Can anybody help me and tell me what I'm doing wrong? I can provide more info if needed.
EDIT! I forgot to mention, when I put in a single letter into the CASENOTE textbox, I get the stackoverflow exception, but when I type more than 1 letter, I don't and the search works.
If you need to select from people using or condition over w.PAS_INT_NO == pasint, this would work:
public IEnumerable<PERSON> GetSearchResults(SearchViewModel viewModel)
{
var people = unitOfWork.PersonRepository.GetAll();
if (viewModel.Casenote != null)
{
var casenoteNos = unitOfWork.CasenoteRepository.GetAll()
.Where(x => x.CASENOTE1.Trim()
.ToLower()
.Contains(viewModel.Casenote.Trim().ToLower()))
.Select(casenote => casenote.PAS_INT_NO)
.Take(1000)
.ToList();
if (casenoteNos.Any())
{
people = people.Where(w => casenoteNos.Contains(w.PAS_INT_NO));
}
}
return people.Take(1000).ToList().OrderBy(x => x.SURNAME).ThenBy(x => x.FORENAMES);
}
Description
The culprit in the original code was:
people = people.Where(w => w.PAS_INT_NO == pasint)
because IEnumerable produced by Where is not materialized, here we creating up to 1000 chained Where, so eventually people becomes people.Where(...).Where(...).Where...1000 times.
If check the sources of Where extension here, first Where produces WhereEnumerableIterator object then its Where and consequent Wheres generate nested lambda calls here by calling CombinePredicates, which is:
static Func<TSource, bool> CombinePredicates<TSource>(Func<TSource, bool> predicate1, Func<TSource, bool> predicate2) {
return x => predicate1(x) && predicate2(x);
}
Which being called 1000 times becomes:
(x=> ...1000 times... (x => predicate1(x) && predicate2(x)) && predicate2(x)) ...1000 times... ) && predicate2(x)
Then 1000 nested calls produced a stack overflow exception.
Conclusion: don't chain a ton of Where, it was not designed for that. Anyway chained Where (a ton of AND filters) here is not what was intended, but IN / Contains is needed instead.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments