Using Generics for Lookup Tables in Entity Framework
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.
An Adventure Works Entity Model
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<T> GetLookup<T>() 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<T>("[" + 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<StateProvince>(); var addressTypes = repository.GetLookup<AddressType>(); var territories = repository.GetLookup<SalesTerritory>();
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.
Follow me on Twitter
Do you have sample code for your createquery method?
Are you returning all fields for any table? I guess you would have to since the lookup tables are different.
Let me know if you have the rest of the code for clarity. Thanks
I see it is from the objectcontext. DO you know how to do this using the DBContext?
Yes, you got it – the CreateQuery method is a method on the ObjectContext.
As for using the DBContext, I haven’t specifically played with that class in EF 4.1… but…
Based on the MSDN docs, DBContext does expose the ObjectContext it wraps. You should presumably be able to access it with the .ObjectContext property.
In the GetLookup method, you could start by retrieving the ObjectContext (adventureWorksEntities.ObjectContext) and working with that.
Let me know how it goes!
I created an ObjectContext and was able to get the code to compile. But it says that my domain object called State, just like your StateProvince cannot be used as type parameter T in the generic type or method. Any ideas?
I got it working. I did not need the following line–>. where T : System.Data.Objects.DataClasses.EntityObject
There is probably an equivalent object that you should use to restrict the type instead of EntityObject.
The danger with leaving this out is someone could call the method with any type. Of course they’d get an exception, but it’s best to avoid letting them do it in the first place. An error at compile time is much better than an error at runtime.
Damian thanks for the code, very helpful. Any idea on how to include a sort in there? Thanks
Hi Eric,
Good question. You could sort the records when they come back of course, but if you wanted to do it inside the method, there are probably a few options.
I haven’t tested these, but:
1) You could overload the GetLookup method to accept an expression parameter you can use for sorting inside the method.
e.g. GetLookup (Expression> sortSelector) where T : System.Data.Objects.DataClasses.EntityObject {)query).OrderBy (sortSelector).ToList();
public IEnumerable
…
return ((IQueryable
}
Then just call it with:(x => x.StateId);
var states = repository.GetLookup
Of course you’d need an overload for each data type you’d want to be able to sort on – the one above is for an int.
Or instead of an IEnumerable which would allow you to sort it at the calling end before resolving it.
2) You could return an IQueryable
e.g.
return query.AsQueryable();
instead of
return query.ToList();