I’m struggling to get my head around how best to eager load entities, and how to assign relationships when creating new entities. I’m using EF5 POCO, by the way.

I’m retreiving a large hierarchy of entities from a database, representing chemical analysis results. As a simple example I have an “AnalysisResult” entity with two generated properties that relate it to a “ChemicalElement” entity – ChemicalElement and ChemicalElementId. Standard stuff so far.

I started out by retrieving my AnalysisResults from the database and eager loading the related ChemicalElements using the .Include LINQ statement, meaning I could access a result’s chemical element simply via AnalysisResult.ChemicalElement. This was fine for results loaded from the database, but what if I wanted to create a new AnalysisResult – how would I get the ChemicalElement entity that I wanted to assign to it?

As chemical elements never change, I decided to treat them as “reference/lookup data”, so I dropped the eager loading, and I now retrieve all ChemicalElements into a separate collection that I can refer to whenever I need. The downside of dropping eager loading is that an AnalysisResult loaded from the database has a null .ChemicalElement property, so I have to use its .ChemicalElementId property to lookup the ChemicalElement entity in my “reference collection”.

Have I overcomplicated the solution? Should I keep the idea of the ChemicalElement “reference collection” but also eager load them when retrieving existing AnalysisResults? It seems wasteful to (potentially) retrieve them twice – once when I retrieve them all, and again when eager loading during the AnalysisResults retrieval.