May 15, 2008

WARNING: LINQ to SQL SelectMany() - caveat emptor

I have some code that tries to determine if a grandparent record has any grandchildren; it looks something like this. Grandparent.Parent.SelectMany(p => p.Children).Count(); This works fine the first time it is called and I see the correct SQL output. The next time I call it I get the correct values but no SQL. It acts as if it has cached the result. I have a routine that deletes all the children and recreates them. When this happens and I execute the above line again no SQL is output but the children count comes back as 0 which is not correct. The Parent hasn't changed but there are all new children attached to that parent. The SelectMany() command seems to notice that the original children are gone and will only report 0 children. In an effort to make sure that the data was all valid I wrote the equivalent of the SelectMany() as a more verbose join command as shown below and it works all the time. This has made me very leery of the SelectMany() command in LINQ.

private bool ContextGrandParentHasChildren(Grandparent gpToCheck)
{ 
    int count = (from p in mainContext_.Parent
                 where p.Grandparent_Key == gpToCheck.Grandparent_Key //find parents
                 join c in mainContext_.Children                      //join from parents to children  
                 on p.Parent_Key equals c.Parent_Key  
                 select c).Count();   
    return count>0;
}

About Me

My photo
Tod Gentille (@todgentille) is now a Curriculum Director for Pluralsight. He's been programming professionally since well before you were born and was a software consultant for most of his career. He's also a father, husband, drummer, and windsurfer. He wants to be a guitar player but he just hasn't got the chops for it.