LINQ and Entity Framework … sigh

Our company has a ton of legacy code using VB and simple text based queries that are submitted to the database.  There are several disadvantages to this method versus new techniques like LINQ and EF.  First, syntax errors in the query itself can only be tested in run-time.  Second, syntax highlighting, automatic formatting and intellisense are not supported.

One of the improvements started earlier this year was the use of LINQ in place of string queries.  Immediately a flaw was discovered in the interface for setting up tables.  That flaw is the missing capability to synchronize changes made in tables in the corresponding database.  The developer is forced to delete the table and re-add the table in order to reflect any field changes.  This is ok, if there are no stored procedures that return a result set for that table.  Then you have to go to each stored procedure and switch to something else, replace the table, then switch the stored procedures back.  There are third-party add-ons that can synch the tables, but they seem to of the bloat-ware variety and are not very effective (some of them don’t work right).  A second “feature” of LINQ is that it creates a settings.settings file in the project to add the ability to change database settings from an interface.  This seems like a great feature until one realizes that it over writes the apps.config file when changes are saved.  The side effect is that comments in apps.config are removed.  Our company uses comments to switch between databases located on our local PC, staging server and the production server.  Powershell scripts comment and uncomment the sections for each of the connector strings to enable the correct database connector.  This goes out the window when the settings.settings control panel wipes out any commented connection strings (usually the staging server and production server settings). 

So we began looking at Entity Framework.  This was a dream.  It has a synchronize capability making it much easier to use.  I jumped in with both feet and started replacing LINQ setups with EF (they are compatible with each other, just keep the table names the same).  My plan was to create a device context of table definitions in each object directory.  This would create an organization where the tables that were used by the current object were in the same directory and any abandoned tables would not be used by some far off code not related to the object that might be refactored in the future.  Then I ran into a serious problem.  EF doesn’t recognize name spaces.  Yikes!  So I changed plans quick.  I placed the device context in a central directory under the project and decided we would just use one device context for all objects.  I further discovered that only one database can be used per device context.  While that hasn’t been an issue yet, it will be if we decide to use this for more of our larger objects (that query spanning two or more databases).  I theorized that we might be able to use nested “using” statements, but I haven’t tried it yet.  This issue, coupled with the namespace issue has made me put on the breaks until I can do further analysis.  I have also heard rumors that EF is not as efficient at queries as LINQ. 

Other issues that discovered is that the objects created for the tables that are dragged into the context may have an “s” appended to them.  This causes all kinds of confusion about the table names.  It’s also a headache to have to trace back to the device context to get the real table name to find it in the database (if you are troubleshooting code you are unfamiliar with).  I would prefer an ORM* that would create object names that matched the tables exactly, followed the standard namespace convention and had a visual interface that didn’t stomp on my app settings and was flexible enough to synchronize changes with the database (because we make database changes).  Am I asking for too much?

So now what’s my plan?  At this point, I’m going to put some time and effort into NHibernate (I have posted on that subject before).  Stay tuned for my results on this ORM.

*LINQ and EF are also known as ORMs (Object Relational Mappers).


Leave a Reply