The End of SQL?

Series: Predictions

I’ve been working with Entity Framework for about 9 months now and I don’t think I can go back to a data access layer that relies on SQL strings and stored procedures. The benefits of using an ORM (Object Relational Mapper) over using traditional SQL and generic data access objects are overwhelming.

  • strongly typed data entities, no looping through arrays with hard-coded column names to retrieve data
  • compile errors on critical database changes
  • intellisense, code hints and compile warnings
  • less code to write in general, especially when casting and joining tables
  • more semantic code, easier to read and comprehend
  • automatic data class (model) updates from database

I just gave a demo to my coworkers and there was a mixed reaction. Some DBA’s looked a bit nervous. “You can build apps without SQL? But SQL is what I write! Will this make me irrelevant?” Fear not, gentle DBA. We still need reports, nightly scripts, backup procedures, data migration, triggers, optimization and many other things that require intimate knowledge of database architecture. But we don’t need 3 stored procedures to update every table. I think this will actually free DBA’s from the more mundane tasks so they can focus on architecture and other concerns.

ORMs are not perfect and they come with their own unique challenges, but they are nonetheless a giant step forward.

The Object Database

Another not-so-new technology that’s been gaining traction is the Object Database. Many experts are saying, why bother mapping objects to relational data tables when you can just save the object directly into the database? It makes sense and it is quite possible that object databases are easier to use than ORM’s with a relational database. They’re apparently quite efficient at serializing and querying data.

The main anchor keeping this technology from taking off is the need to support legacy systems, and DBA’s who are unfamiliar with or uncertain of object databases. With ORMs, a programmer can evolve their code independently of the database. Entity Framework just released support for POCO classes, which is a fancy word for regular classes you write yourself. Writing the classes yourself allows some extra freedom, but loses some of the automation that makes the framework cool and easy. One reason this feature was included was to allow developers to ease the ORM into a system that already has classes built, then to slowly replace their old SQL with LINQ.

Either way, ORMs provide a visual editing tool to represent data relations, and a tool will always exist for this purpose. With Entity Framework, you can actually generate the database from the models you design, so it’s quite likely that there will soon be a “switch” behind the scenes to simply generate an object database instead, and there will be little to no effort for the programmer. Switching to ORM makes a system more prepared for an upgrade to an object database. More and more reporting and analysis tools will likely begin to plug in to the ORM directly instead of the database to employ the same benefits.

The Bottom Line

The bottom line is the same. Object databases and ORMs require no SQL writing. I don’t think SQL will simply vanish or that the skill will lose value right away. But it could gradually go the same direction as Regular Expressions. The need to write SQL will become less frequent, and the tools to aid in the process will continue to improve. Of course an understanding of the SQL language will aid any developer working with an ORM or any other visual tool in much the same way that an understanding of HTML markup aids the use of visual web page design software.

So, no, I don’t see SQL simply going away any time soon. It’s far too entrenched and it will be decades before all legacy systems begin to migrate away. But don’t be surprised if you begin to see job descriptions begin to pop-up requiring knowledge of Entity Framework, NHibernate or other ORM’s.

Posted in asp.net, Entity Framework, predictions, sql | Leave a comment

Thumbtack?

Mindstorm just posted on thumbtack.com. What is thumbtack.com? From what we can tell it’s similar to CraigsList however it is a services only directory.

From their site:

Why can you go online right now and buy any product you want but you can’t do the same for tutors, handymen, dog walkers, or other local services? Thumbtack is changing that.

Thumbtack isn’t like typical local search directories that simply return business listings with ratings and reviews, leaving you no better off than the paper Yellow Pages.

Instead, Thumbtack gives you the ability to vet, contact and book service professionals the moment you find them.

At Thumbtack, we understand that hiring service professionals requires a high degree of trust. This is why we go out of our way to make sure that our community is held to the highest standards and that you have all the tools you need to feel comfortable hiring someone online.

Thumbtack helps you get the job done at the time and place you want, with someone you know you can trust.

One thing we did like when researching thumbtack is that they preform background checks and have user reviews. However like with any other free service a user should proceed with caution and common sense.

If you are interested in seeing our posting it can be found at:
Web Design, Presentation Design, Branding & Print

We have actually had a lot of success (and a lot of junk) with CraigsList so hopefully we will also be successful with thumbtack. Only time will tell if anything is actually going to come of our free ad, if thumbtack does prove to be fruitful we’ll be sure to let you know.

Posted in uncategorized | Leave a comment

The ObjectContext instance has been disposed and can no longer be used for operations that require a connection.

If you are using Entity Framework, this is one of the most common and often misleading errors you will see. There are several possible explanations for this error that may lie in the code or in the database. Here are a few reasons for this error and how to resolve them.

Code Issues

reason #1:

You are attempting to access data and the database connection is closed.

ObjectContext is the base class you inherit from when you generate entities from entity framework. It handles several tasks, most significantly opening and closing the database connection, writing and executing queries.

If you instantiate your ObjectContext in a using block, the connection is closed at the end of that block.

solution:

First off, be aware that retrieving data is a two step process of writing and executing SQL, even though it’s hidden away and often written in one line. Any code that attempts to access the database must be executed within the using block.

If retrieving an IEnumberable with a query, wrap in a .ToList() to force data loading.

For navigation properties of an entity, use Include(), or call entities.Load(). You must check the .IsLoaded property of any navigation property to see if it’s there. You can’t check if the property is null or it may attempt to access the database.

Also note that even within an entity context, you must call .Load() before calling .Clear() to delete navigation properties from the database. Failing to do so won’t cause an exception but it will fail to delete the data. Also be aware that calling .Load() on a detached entity will cause the next error…

 

reason #2:

Your entity is not Attached.

This is just a variation of the previous issue. If you already have an entity before instantiating your context, they are not associated just because you are in a using block. Therefore, if you attempt to access navigation properties of that entity, you will get this error because the system assumes the entity came from a context which is now closed.

solution:

Call ObjectContext.Attach(entity) before attempting to access the entity navigation properties. Note: calling Attach can cause other errors which I will address in a separate post.

 

 

Database Issues

reason #3:

Another reason is missing navigation entities (a.k.a orphan data). This is the most likely cause if the bug only occurs when requesting specific data, or if you step through your code and the bug occurs within a loop, but not on the first iteration.

solution:

Navigation properties are based on foreign keys. If the foreign key points to a row that does not exist, you have an orphan child row. This can occur if the entity is deleted or updated but the foreign key is set to do nothing. Check your data and try setting the FK action to cascade or set to null.

If the navigation property is nullable, you much check if it is null before accessing any property. This sounds like common sense but it is very easy to forget.

Also, check the table mappings of your parent entity. If your parent entity has any mapping conditions (ex. ‘When ActiveFlag = true ), any rows that don’t match the condition will be excluded from the data sets gathered by entity framework. They will be treated as if they were deleted from the database. This will essentially make your child rows orphaned data, but without the safety net of the foreign key violation. Be very cautions with exclusionary table mappings on parent rows.

reason #4

edmx file is out of sync with the database. This issue can occur any time you change a table or column name in the database and forget to update your model. It is even more likely when multiple developers are working off a shared database.

solution:

Open your edmx file. Right-click anywhere in the white space and select ‘Update Model From Database’. Select ‘validate’ from the same menu. This will find many issues but some will pass through compile and only get caught at run time. If you have made extensive changes to an entity, it’s often easier and safer to simply delete it from the edmx and add it again. This will reset the names of all your properties but it will ensure they match the database names, which is usually the best route for clarity.

 

 

Posted in asp.net, Entity Framework, mvc, sql | Tagged | Leave a comment