A Certain Point of View Part 1 - EF Code-First

Wednesday, March 26, 2014 @ 09:09

By: Corey Adler, Staff Developer

SQL Views can be an invaluable tool in any application’s arsenal. The ability to have a complex query, one that hits multiple tables, and have it return data speedily to whatever needs it can be a real performance saver. Yet it seems with the rise of ORMs (Object Relational Mappers) that developers are having a difficult time in fully implementing these helpful time savers. The 2 most recent projects that I have been a part of have had me solve this issue with 2 of the more popular ORMs—Microsoft’s Entity Framework and the open-source NHibernate. In this first of two posts I will show how to create and maintain a SQL View while using Entity Framework 5 in Code-First style. The second post will discuss doing the same thing in Fluent NHibernate—which follows a very similar method to doing it in Entity Framework.

The first thing to understand about trying to create a SQL View from a Code-First class is…that you can’t. It’s not possible, nor would it be. SQL Views rely on specifying tables and query logic in order to populate it with the relevant data. There’s no way, as of EF 6, for doing that solely within a C# class. Any attempt to do so will cause the code class to be created as a regular SQL Table, and not a View.

So, then, how do you do it? Simple: By creating the SQL View first, as below. Note the usage of the ID field—it’s important!

Once you’ve constructed the View, then it’s time to construct the code class. Take care when doing so, though, that the field names in the code class match the View fields precisely. EF here is both case and spelling sensitive.


 

Notice the ID field that I have here, and that it matches up with the one from the View. An ID field of some sort needs to be on your View. If it’s not then EF will throw an exception because it thinks it’s a table that has no primary key. Even though you’re mapping to a View, it doesn’t matter. It will still blow up.

So, now it’s time to automatically migrate over the database, and run the program!

Ok…So why can’t I get any objects?

It turns out that EF’s auto migration tool won’t automatically do the mapping to the View. It needs to be done manually; otherwise it will automatically create it as a Table. So, in order to map it as a View, be sure that the table does not exist (delete it if it does!), and run a manual migration from the Package Manager Console. You should get a class that looks like this (if it doesn’t, be sure to edit it to look like it):

               

Now, finally, after the manual migration, starting the program and making the query:

We now have objects populating from the View. If you ever need to make any adjustments to the View, be sure to do so in the same order as creating the View. That is to say: Adjust the SQL View, adjust the C# class, and run a manual migration. Granted that it’s a painful process, and one that’s certainly not very intuitive, but that’s how the cookie crumbles.

In my next post I will discuss creating a SQL View using Fluent NHibernate, and how eerily similar it is to creating one in Entity Framework. Until then, I wish you good coding.