Enums and Lookup Tables with EF Code First

With EntityFramework’s support for enums, there is no longer any need to include lookup tables in the model. But I do want to have them in the database for integrity, even with code first.

I’ve been thinking for some time about to handle enums with code first. The idea behind code first is to be able to write the code as close as possible to how object oriented code is normally written. For enums that means that the enum definition itself is the constraint that is used to ensure that only valid values are used in the code.

For databases it would be possible to use a column constraint, but the normal way is to use a lookup table where the valid values are present. Any column in the database mapped against the enum type is then a foreign key to the lookup table to ensure integrity of the data.

What I would prefer is a solution where the lookup table is present in the database, but not mapped against any entity in the code.

On Null or Why Empty Strings are not Same as Null

Null is an important, but sometimes hard concept. What’s the difference between an empty string and a null string? One of my first Stack Overflow questions was (NOT) NULL for NVARCHAR columns.

Some people claim that using null is always wrong or is some kind of voodoo.

This time Mike is wrong. There is a place for null, including Nullable<bool> and Nullable<int>.

Posted in C#, SQL on 2013-01-07 | Tagged

Avoid broken Views in SQL Server with Schemabinding

SQL Server views can get really weird when an underlying table is updated. I’ll show some examples and also the one must do for views to avoid problems.

The broken views problem is something I discovered when working on a production system. When I first observed it I was shocked. When I dug deeper into it I was even more shocked at the pure evilness this behaviour could cause. But let’s take it from the start with a simple table of colours and a view on top of it to start with.

CREATE TABLE [dbo].[Colours] (
    [Id]     INT           IDENTITY (1, 1) NOT NULL,
    [Colour] NVARCHAR (10) NOT NULL,
    CONSTRAINT [PK_Colours] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE VIEW [dbo].[BadView]
	AS SELECT * FROM Colours

Populate the table with some data and test the view.

SELECT * FROM BadView
Id          Colour
----------- ----------
1           Red
2           Green
3           Blue
4           White
5           Black

Now, let’s break havoc to the view. I’ll recreate the table with another column RGB which is inserted right after the Id column. The Colour column is renamed to ColourName. Before running the next test, I’ve inserted the appropriate RGB values in the new column.

SELECT * FROM BadView
Id          Colour
----------- ------
1           FF0000
2           00FF00
3           0000FF
4           FFFFFF
5           000000

This is what I consider weird behaviour. The output of the view still contains two columns with the names Id and Colour. That would be good – if it contained the right data. It doesn’t. The data is taken from the second column of the underlying table. If you’re not scared enough by this I have some more, even worse things to show…

Dissecting MVC Scaffolded Code for EF Updates

MVC and Entity Framework scaffolding is powerful, but I prefer to know what actually happens before accepting generated code. The code generated to update an entity when an edit has been done looked interesting, so I decided to have a closer look at it.

When an edit is done to an entity, the data is posted as a simple collection of form elements from the web browser. Remember that when working with MVC, we’re closer to the metal than with Web Forms and we are fully exposed to the stateless nature of the web. The gigantic hidden ViewState form field is gone, leaving us in a clean, but stateless world. When the updated data is posted to the server, the server has no more info about it than available in the request. The server doesn’t know where it came from and has to find out itself where, how and what part of it to update.

Tracking Changes

Tracking changes for objects moving across tiers, being disconnected from their original data source is always tricky. I’m still looking for the silver bullet to this problem and one step in the search is to investigate what other have done. In this post, I’ll take a closer look on how the Scaffolded code of MVC handles updates to EF Entities.

Idempotent DB Update Scripts

An idempotent function gives the same result even if it is applied several times. That is exactly how a database update script should behave. It shouldn’t matter if it is run on or multiple times. The result should be the same.

A database update script should be made to first check the state of the database and then apply the changes needed. If the script is done this way, several operations can be combined into one script that works on several databases despite the databases being at different (possibly unknown) state to start with.

For the database schema itself I usually use Visual Studio 2010 database projects that handles updates automatically (in VS2012 the functionality has been changed significantly). Even with the schema updates handled automatically, there are always things that need manual handling. One common case is lookup tables that need initialization.

Software Development is a Job – Coding is a Passion

I'm Anders Abel, a systems architect and developer working for Kentor in Stockholm, Sweden.

profile for Anders Abel at Stack Overflow, Q&A for professional and enthusiast programmers

The complete code for all posts is available on GitHub.

Popular Posts

Archives

Series