I'm working with a client at the moment on a system using legacy database with (wait for it) about 50 lookup tables in their database. We're using Entity Framework to access the database, so it's fairly easy to get the data out of these lookup tables.
However, I really don't want to write 50 methods to return lists for each of these objects. Thankfully, the solution is relatively simple - write a generic method to return the appropriate data.
Implementing this wasn't quite as easy as I thought, but I got there after a bit of experimentation and, I'll be honest, a fair bit of googling.
To demonstrate, let's look at an Entity Model with a subset of the AdventureWorks database.
In particular, look at the red outlined objects. These are likely to be referred to frequently throughout the application as simple lookup tables.
We want to write a single generic method that will return an IEnumerable of these objects so we can use them quite simply as lookup tables.
Here's what I ended up with:
AdventureWorksEntities adventureWorksEntities = new AdventureWorksEntities();
public IEnumerable GetLookup() where T : System.Data.Objects.DataClasses.EntityObject
{
try
{
var key = typeof(T).Name;
// 1. we need the container for the conceptual model
var container = adventureWorksEntities.MetadataWorkspace.GetEntityContainer(
adventureWorksEntities.DefaultContainerName, System.Data.Metadata.Edm.DataSpace.CSpace);
// 2. we need the name given to the element set in that conceptual model
var name = container.BaseEntitySets.Where((s) => s.ElementType.Name.Equals(key)).FirstOrDefault().Name;
// 3. finally, we can create a basic query for this set
var query = adventureWorksEntities.CreateQuery("[" + name + "]");
return query.ToList();
}
catch (System.Data.EntityException ex)
{
throw new ArgumentException("Invalid Entity Type supplied for Lookup", ex);
}
}
Now we can use the following code to return a set of all items of the appropriate type:
AdventureWorksRepository repository = new AdventureWorksRepository();
var states = repository.GetLookup();
var addressTypes = repository.GetLookup();
var territories = repository.GetLookup();
This has saved me countless hours of writing boring plumbing code.
Hopefully, some of you are already looking at this thinking, "couldn't I take this a lot further and provide a set of generic data access methods for everything?". The answer of course is yes. I haven't gone quite that far yet, but I wouldn't be surprised if it was down the track.