Generates linq to Entity Framework Queries by examining an EF data model, thus allowing for code reduction for queries to commonly requested entities.
A code generator designed to reduce the amount of linq to Entity Framework queries needed in a parent project. Generates a linq query for standard inner joins between any entities in an EF model by querying and navigating the model. May also serve as (essentially)
an EF to SQL converter
On a recent project which used Entity Framework, we had tons of repository methods for almost every conceivable get, e.g., GetMostCommonEntityByThis, GetMostCommonEntityByThat, GetMostCommonEntityByThisList, GetMostCommonEntityByThatList, GetMostCommonEntityByTheOther,
etc. ad nauseum.
It became difficult to determine if what you needed had already been written. As we started a new project, I wondered if there was a way to cut down on the proliferation of these simple inner join queries. EF already knows how all the entities are related,
can I just use EF to build a query?
This is what I came up with. It works surprisingly well. It doesn't work for all situations (most notably if you need an outer join), but it does for very many. If it doesn't create the join you want, you may need to give it a hint (search code for
In our case, we have (essentially) these kinds of entities:
Organization -> Dataset -> Person -> PersonRelated1, PersonRelated2, etc. So I created a method that accepts OrganizationId, optional DatasetIds, optional YearIds, optional SubSystemId. The method also accepts a generic type parameter indicating which
entity type you want it to return. Thus, if I want all Person entities related to an organization filtered by a list of years I call my method:
MyService.All<Person>( organizationId, null, yearIds );
In this simple case, it would generate a linq query joining Organization, Person, and Year.
Later, it became useful for the following scenario. We needed to be able to move some data from a central database to a temporary database for some intensive computational work that would cause too much contention if all users were hitting the central database
and was also too intensive to conduct via EF.
So, this converts a method call like the one above into a TSQL (using Sql Server) statement that we can use to pull data from a simple connection circumventing EF. It does this by casting the result of DynamicFilterWrapper.FilteredEntities to ObjectQuery and
calling ToStraceString() on it.
The first thing that the dynamic filter library does is determine the relationships between the necessary entities (in EntityModelNavigator.cs). It builds lists of NavigationItem objects which know which entity they're navigating from and to as well as
the relationship and multiplicity between the entities.
Next the dynamic filter library generates the necessary linq to EF C# code by using those NavigationItem objects (in LinqGenerator.cs). The result of LinqGenerator.Generate will be the code for a static class with a single static method.
Your Wrapper / Implementation
For any method you need like the "All" method above, you would create a wrapper (see DynamicFilterWrapper.cs). That wrapper will have some specific references to entities represented by the parameters to All. It will then call into CompilerWrapper.cs
in order to compile the code the generator returned.
The performance cost of all this is surprisingly low. If you pass as your type parameter to All an entity whose relationship is many entities removed from the entities represented by one of it's regular parameters (or if the entities represented by the
regular parameters are similarly separated), EntityModelNavigator may take awhile to figure out the relationships. You would certainly have to unit test such a call.
The Sample Implementation
The sample implementation (AdventureWorksImplementation project) is not realistic, and the All method in EntityService would probably not be too terribly useful. I used the regular AdventureWorks 2012 sample database and struggled for a bit trying to come up
with some useful case, but there's nothing anywhere near as obvious as my own case. I probably should've used the AdventureWorksLT scaled down database, but it any case it's working and demonstrates how the dynamic filter is to be used.
Getting it Working
- Download the
Adventure Works Sample Database for 2012 and attach it to a server.
- Point both App.Config connections to your server.
- Run the unit tests.
- If you want to see the generated code, place a breakpoint at DynamicFilterWrapper.GenerateIQueryable on the second line. The "query" variable will be a simple string of C# code.