0

I have a list of some objects, which I populate from a database query result.

List<Competition> myList; // fetched from DB

If I need to access a property of one/several of the object in this list which requires to query the database again like this:

List<Person> compPersons = myList[0].Persons; // persons must be fetched in DB

then, is there a way to have but one db call which would fill Persons property for every Competition in myList?

List<Person> comp1Persons = myList[0].Persons; // Causes one big DB fetch
List<Person> comp2Persons = myList[1].Persons; // no more fetch required
List<Person> comp3Persons = myList[2].Persons; // no more fetch required

If I have 1000 competitions in my list I don't want 1000 queries when I iterate over the list and access Persons property.

EDIT: I wouldn't mind using a IEnumerable instead of List, if it allows more possibilities.

xanatos
  • 109,618
  • 12
  • 197
  • 280
Serge Profafilecebook
  • 1,165
  • 1
  • 14
  • 32
  • I suppose you use Entity Framework? Then you can use ".Include": http://stackoverflow.com/questions/24945115/entity-framework-include – Rob May 11 '15 at 07:35
  • Are you using any orm like L2Entities, L2SQL or dapper? – Tim Schmelter May 11 '15 at 07:36
  • No, I don't use any of those. – Serge Profafilecebook May 11 '15 at 07:39
  • @SergeProfafilecebook: i don't like your approach, i would prefer lazy loading only the persons that are required not all. Why do you prefer loading all persons if someone uses the `Persons` property getter? That doesn't work anyway if `myList` is a local variable since the property has no knowledge of it. – Tim Schmelter May 11 '15 at 07:43
  • This sounds even more complicated. When I access Persons property of one on the List then it's likely (or even sure) I'll have to access this property for other items in the list. – Serge Profafilecebook May 11 '15 at 07:46
  • If I have understood your question correctly then you can write a stored procedure that accepts comma separated values as parameter and fetch all the persons. – ElectricRouge May 11 '15 at 07:50
  • Why not fetch all data when program is loaded and store in a dictionary> this way everything is loaded and accessible. – deathismyfriend May 11 '15 at 07:52
  • Because there are a lot of competitions. And that would totally forbid any DB change which would not get immediately reflected on the dictionary (and vice-versa) – Serge Profafilecebook May 11 '15 at 07:59
  • @SergeProfafilecebook: why is it likely that you you need the persons from `Competition2-1000` if you access the persons of `Competition1`? – Tim Schmelter May 11 '15 at 08:01
  • @SergeProfafilecebook if you want active checking / check for people in competition every time it is clicked then you have 2 options the way you do it in your question above. Or you can use a dictionary as I said and have an event get raised whenever fb is changed to update the dictionary. Also you should change your question since you ask for a way to only query once. And you should use the @ then persons name so they get messaged that you have answered. – deathismyfriend May 11 '15 at 08:04
  • @TimSchmelter Because I will iterate over all competition object and might access their persons property. If I access it once in a loop, there's a chance I access it again on some next iterations. – Serge Profafilecebook May 11 '15 at 08:05
  • 1
    This is called the [SELECT n+1 antipattern](http://stackoverflow.com/questions/97197/what-is-the-n1-selects-issue) – xanatos May 11 '15 at 08:38

0 Answers0