Entity Framework – Storing a complex entity as JSON in a single DB column

jsonDuring the development of TicketDesk 2.5, I came across an unusual case. I wanted to store a large chunk of my entity model as a simple JSON string, and put it into a single column in the database.

Here’s the setup:

I have an entity that encapsulates all of a user’s display preferences and similar settings. One of those settings is a complex set of objects that represents the user’s custom settings for list view UI pages. There can be many lists, each with separate settings. Some of the settings for a list contain collections of other objects, resulting in a hierarchy of settings that goes three levels deep

I didn’t want to represent these settings as a relational data model in the database though. Using EF’s standard persistence mapping conventions, this collection of settings ends up being spread across six  tables. The TSQL queries to access that data would be rather slow and cumbersome, and the relational model doesn’t add any value at all.

Instead, I just wanted to serialize out the entire collection of settings as a single JSON string, and store it in one column in the user settings table. At the same time though, I wanted the code behave as if this were just a natural part of my regular EF entity model.

The solution:

The solution was to use a complex type, with some fluent model binding magic, to flatten the hierarchy into a single column. The heirarchy itself is represented as a custom collection, with a bit of manual JSON serialization/deserialization built-in.

I got a pointer the right general direction from this SO post, which saved me a bunch of time when approaching this more advanced scenario.

First, let’s take a look at the root entity here:

public class UserSetting
 {
 	[Key]
    public string UserId { get; set; }

    public virtual UserTicketListSettingsCollection ListSettings { get; set; }
}

This is the only entity which will map to its own table in the DB. The ListSettings collection is the property I want persisted as JSON in a single column.

Here is the custom collection that will be stored:

public class UserTicketListSettingsCollection: Collection<UserTicketListSetting>
{
    public void Add(ICollection<UserTicketListSetting> settings)
    {
        foreach (var listSetting in settings)
        {
            this.Add(listSetting);
        }
    }

    [JsonIgnore]
    public string Serialized
    {
        get { return Newtonsoft.Json.JsonConvert.SerializeObject(this); }
        set
        {
            if (string.IsNullOrEmpty(value))
            {
                return;
            }

            var jData = Newtonsoft.Json.JsonConvert.DeserializeObject<List<UserTicketListSetting>>(value);
            this.Items.Clear();
            this.Add(jData);
            
        }
    }
}

This is a collection type, and is inheriting generic Collection<T>. In this case, T is the UserTicketListSetting type — which is a standard POCO wrapping up all of the settings for all of the various list views in one place.

Some of the properties inside UserTicketListSetting contain collections of other POCOs. The specific details of what’s going inside those classes doesn’t matter to this discussion, just understand that it results in a hierarchy of related objects. None of the properties in that hierarchy are marked up with EF attributes or anything.

The only magic here is that we have a Serialized property, which manually handles converting from/to JSON. This is the only property that we want persisted to the database.

To make that persistence happen, we will make UserTicketListSettingsCollection an EF complex type, though not by using the [ComplexType] attribute. Instead, we’ll manually register this complex type via the fluent model builder API.

In the DB Context this looks like this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
	modelBuilder.ComplexType<UserTicketListSettingsCollection>()
        .Property(p => p.Serialized)
        .HasColumnName("ListSettingsJson");
}

This just tells EF that UserTicketListSettingsCollection is a complex type, and the only property we care about is the Serialized property. If there were other properties in UserTicketListSettingsCollection, you would need to exclude them with something like:

modelBuilder.ComplexType<UserTicketListSettingsCollection>().Ignore(p => p.PropertyToIgnore);

And that’s all I needed to get EF to store this entire hierarchy as a single JSON column.

Using this model in code is just like using any other EF entity. I can query it with LINQ expressions, and SaveChanges on the DbContext updates the JSON data in DB just like any other entity. Even the generation of code-based migrations works as expected.

It took a LOT of experimentation and digging to figure out how to make this work, but the implementation is rather simple once you know how to approach the problem.

This also reflects the amazing power and flexibility of Entity Framework. EF can be extended to fit very advanced scenarios even when the designers didn’t anticipate them directly.

You can see the full implementation of this in TicketDesk 2.5. Currently, TD 2.5 is in alpha, so look to the develop branch in source control on CodePlex. You will find this example, as well as several variations in the TicketDesk.Domain assembly.

TicketDesk 2.5 – Coming soon!

TicketDesk-2.5While I’ve been working on TicketDesk 3, the code for TicketDesk 2 hasn’t been getting any younger. Since TD3 is still a ways out from a production release, I’ve decided to release a major overhaul of TicketDesk 2 in the meantime. The new TicketDesk 2.5 release will bring the technology stack of TD2 up to the latest version of Microsoft’s Web Platform. Several of the changes will be derived from code developed for TD3.

I am targeting the end of October for a beta version, with a final release in mid to late November (subject to change, as always).

Here are the major changes I have planned so far:

Remove AD Security:

This release will not support direct integration with Active Directory. This was a popular feature in TicketDesk 1 and 2, but it has also been a major problem area as well. Instead, TD 2.5 will only support AD indirectly, via federation (ADFS for example) or through integration with an external identity server (like Azure AD).

Modernized Local Security:

TicketDesk 2.1x still uses the ancient SqlMembership providers that shipped with .Net 2.0 back in 2005. Authorization and identity have come a very long way since then, so TD 2.5 will be upgraded to the newest Aspnet.Identity framework version. It will also provide on-screen management tools to help administrators migrate existing user accounts from their legacy TD 2.1x databases.

UI Changes:

TD2.1x was built on Asp.net MVC 2, with the original Asp.net view engine. This isn’t very well supported by recent versions of Visual Studio, and most developers have long since abandoned the old engine in favor of Razor. I don’t plan many major changes to the general UI’s behavior or appearance, but by re-implementing on Razor I can bring the project into compatibility with Visual Studio 2013 and current Asp.net coding standards.

There will be some minor changes to the UI. I will be removing some of the crusty old JQuery components, and updating the styles to take advantage of newer CSS features that weren’t widely supported when TD2 was first built.

Entity Framework Code-First:

TicketDesk 2.5 will move from Entity Framework 4 database-first models to Entity Framework 6 with a Code-First model. EF Migrations will provide ongoing schema management from here out, including the ability to migrate legacy TicketDesk 2.1x databases. Along with this change, TD 2.5 will also include on-screen database management utilities for migrating legacy databases, seeding demo/test data, and so on.

This refactoring will also bring TD 2.5 in line with the technologies backing TD 3, which will greatly simplify future upgrades.

Eliminate MEF:

The managed extensibility framework has continued to evolve, but it still isn’t a very good choice for Dependency Injection concerns in a web application. Instead, TD 2.5 will use Simple Injector for IoC. Some of the simplifications in the back-end should also reduce the reliance on dependency injection techniques quite a lot.

Improved Email Notifications:

Several improvements to Email Notifications are planned. Most of these are intended to give administrators greater control over how and when TicketDesk sends email notifications. This will include better on-screen testing and diagnostic tools for troubleshooting email related issues.

Multiple Projects:

TicketDesk 2.5 will support multiple projects, which let you handle tickets for different operations, projects, or products in isolation. You will be able to move tickets from one project to another, and can turn off multiple project support  entirely if you don’t need the functionality. I do not know yet if I’ll support user permissions on a per-project basis in this version, but TD3 will certainly provide that functionality.

Watch/Follow Tickets:

Users will be able to watch or follow tickets without having to be the ticket’s owner or assigned user. This will allow these users to receive notifications of changes as the ticket progresses.

Azure Deployments:

TicketDesk 2.5 will be deployable as an Azure WebSite. Currently, there are several issues that make deploying to cloud or farm environments tricky. The biggest are that the Lucene search indexes are stored on the file system, and the old SqlMembership providers are not compatible with the features provided by Azure SQL. These issues are not insurmountable for experienced .Net developers, but deployment to web farms or cloud providers is not currently an out-of-box capability of the system.

To make TicketDesk play well with cloud environments, a pluggable storage provider will be used for any features needing access to non-database storage. When deployed to single instance environments, TD 2.5 will use the file system, but you will be able to reconfigure it for Azure Blob storage when deploying to the cloud. Attachment storage will be moved out of the SQL database to the new storage provider as well.

The only hold-up for Azure SQL is the membership system, but the newer Aspnet.Identity framework fully supports EF migrations and is compatible with any EF data provider –including Azure SQL.

Early pre-alpha code is already committed to the CodePlex repository, and will be updated regularly as work continues. Right now, there isn’t much to see. I’m still working on the basic plumbing for database and identity management, so there are no TD specific user facing features yet. As soon as the back-end is shored up, I’ll start porting in TD specific UI features.

A demo version of the site will be available soon, hosted on Azure. I just have to workout a few minor details related to resetting the database and providing sample seed data, then I can open the demo to the public.

TicketDesk 2.1.1 – Official Release

TicketDesk 2.1 LogoTicketDesk 2.1.1 has been officially released at codeplex, and the source code has been merged and pushed to the public mercurial repository.

This is a platform refresh of the TicketDesk 2 project. The source code now supports development with Visual Studio 2012, and the application has been updated to target the .Net Framwork 4.5 and Asp.net MVC 4.

The databases have not been modified, but have been verified for compatibility with SQL 2012, including localdb.

There are no new user-facing features in this release.

TicketDesk 3 Dev Diary – Getting TicketDesk 2 back in shape

TicketDesk 2 LogoWhen developing a new version of an existing product, it really helps if you can run the old version and use it as a reference.

TicketDesk 2 works well, but it was designed for MVC 2 running on .Net 4, which would be fine if I still maintained a local development environment compatible with the old code. But I’ve ditched Visual Studio 2010 and 2008 entirely.

So, the first order of business is migrating TicketDesk 2 to the newer asp.net webstack.  I’d also like to update the source on codeplex so others can use it with current dev tools too.

I will not be upgrading the application to use MVC 4 or .Net 4.5 specific technologies or techniques; I’m not switching it to the razor view engine or upgrading to EF 5. The goal is to just get it running on current platforms with as few changes as possible.

The first step was finding the asp.net MVC 2 to MVC 3 project converter. Once I tracked that down, and did a bit of tweaking to get it to work, the conversion ran without a problem. This allowed the project to open  in VS 2012 at least, though it wouldn’t build or run.

Next I moved the web project up to MVC 4, and re-targeted both projects to compile against .Net 4.5. Re-targeting was just a matter of switching the setting in the project properties dialog. Then I just pulled down the asp.net MVC 4 NuGet packages, along with the three dependent packages. TicketDesk uses the old ASPX view engine, and doesn’t use WebApi or anything fancy, so I didn’t need most of the other NuGet packages that you’d see in most MVC 4 applications.

TicketDesk 2 used MEF 1. This was probably the riskiest decision I made when building it originally. MEF 1 was intended for Silverlight development, and I had a LOT of trouble getting it to work correctly for asp.Net MVC (I wrote about those issues at length here and here). Had my only need been dependency injection, I would have switched to Ninject. In the end though, I did get MEF to work for TicketDesk 2. But to be honest, the experience was not very inspiring. Since then, I’ve stuck with Ninject, and been the happier for it.

Now we have MEF 2, but it is nearly impossible to find coherent guidance on how to use it with web apps. There are tons of articles, blog posts, and discussions all over the net, but they all use different techniques, none of the examples actually work beyond demo land (most don’t work there either), and most of the info is based on obsolete pre-release versions. The MSDN docs are of particularly low quality, and frustratingly out of date too.

I’m sure MEF 2 is amazing, but if MS doesn’t do something serious about cleaning up the documentation and providing some coherent guidance appropriate for real world applications, then I doubt it’ll get much use among the asp.net developer community.

I spent several hours working with the best example of MEF 2 with MVC that I could find. In the end though, I still couldn’t get it to play nice with TicketDesk 2. But I got lucky and was able to get what I had built originally to work with very few changes. I’m sure the old pattern misses out of some nice MEF 2 specific improvements, but the goal here is to get TicketDesk 2 working again not optimize it for the new platforms.

I’ll probably take a deeper look at MEF 2 for TicketDesk 3 before I decide if I’ll switch to Ninject, or give the new MEF another shot.

I no longer have SQL Express installed locally. I am using SQL 2012 localdb, and have a full instance of SQL 2012 developer edition. So, I switched the TD 2 connection strings to localdb, and have had no problems with it. I’ll put them back to SQL express before I merge the code back into the default branch, and there are no databases changes for the new build.

This got me to up a working version of TicketDesk 2 that can run on MVC 4 using the .Net 4.5 framework. Mission accomplished!

Once I had it working though, I went ahead and upgraded the jQuery stuff. I just couldn’t resist. This required a tiny tweak to the corners plug-in to fix a compatibility problem with the newer jQuery releases; but overall it seems to work great.

At this point, I have what appears to be a fully functional update of TicketDesk 2. All that remains it to put it through some live testing, and update the documentation. Then I will merge the new code in source control, and push it up to codeplex along with a new downloadable release.

Best of all, I now have a working local copy I can use as a reference while I build TicketDesk 3.

TicketDesk 1.2.3 Released on CodePlex

I’ve formally packaged a new version of TicketDesk at Codeplex.

TicketDesk 1.2.3 is a minor update. The most significant change is that the HTML editor has been replaced with the markitUp! editor using the markdown syntax.

This should hopefully work around some problems many users were reporting with adding comments in IE8 as well as some display problems that could occur when a user cut/paste content from a word processor or other sources with embedded rich formatting.

TicketDesk 2.0 MVC – alpha demo now available

As I’ve mentioned before, I’m working on the next major version of TicketDesk 2.0 on the  ASP.NET  MVC framework. The project is still a little early in development, but is starting to resemble a real application now.

I’ve put demo site up to give the public a preview…

The demo of TicketDesk 2.0 MVC alpha is now online. I’ll be updating it from time to time as I reach different milestones. When the project gets closer to a beta state I’ll likely check it into source control over at the TicketDesk codeplex project, but for now I’m working offline on it.

Implemented so far:

  • TicketCenter with the default list views
  • TicketEditor displays ticket information

    • The visual formatting is very rough
    • Attachments cannot be downloaded (this is on purpose… my demo is not an FTP server for the public’s warez  :P)
  • New Ticket feature should be fully functional 
  • TicketEditor activity panel should support most activities

    • “edit ticket” and “add attachments” remain incomplete.   
  • Account management is borrowed mostly from the “sample” MVC app, but has been customized for ticketdesk.

Stuff that isn’t done:

  • Notifications and RSS are not implemented
  • Visual Styling and formatting is very “stock MVC sample”  for now

    • I like the general layout, but the text formatting needs lots of work

  • Ticket Search is absent
  • None of the admin tools are complete

Go poke at the demo and see what you think.

I welcome any comments, observations, or questions you might have, but don’t go reporting bugs yet…this is an alpha demo so I already know it doesn’t work very well yet 🙂

Enjoy…

TicketDesk 2.0 and the ASP.NET MVC Framework

Now that the ASP.NET MVC Framework is out, I’ve decided to tackle learning the new platform the same way I usually do… by writing a real application for the new platform.

TicketDesk 1.0 was originally just a playground application to help me get up to speed during the last round of new-tech releases from Microsoft… so it seemed natural to explore the MVC Framework with a re-write of the same application. TicketDesk is just small enough to be workable by a lone part-time programmer, and it is just big enough to provide a decent proving ground for the new technologies.

So let’s discuss MVC and how it relates to TicketDesk 2.0…

One of the ironies of my life is that I’ve been primarily an ASP.NET developer ever since it was first released and I’ve also been working with MVC and MVC-like development patterns nearly that entire time too.

MVC patterns just makes sense for web apps seeing as the nature of HTTP itself matches that pattern so cleanly. In other environments, MVC has been a formally accepted pattern for years and years.

But ASP.NET Webforms was initially designed to make programming for the web feel more like windows programming with an event driven programming model. Microsoft excels at event driven programming techniques, and the resulting webforms framework was a fantastic adaptation of the pattern into web development space. Webforms allows you to mostly ignore all that messy HTTP stuff and code pages just like you would in a persistent windows environment.

But like most abstractions, webforms tends to break-down when you try to do stuff at the edges. So it wasn’t uncommon for platform developers to find problems that just didn’t map well to the abstractions provided by webforms. So many of us ended up spending amazing amounts of time hacking into the gap between the webforms model and the raw HTTP pipeline itself.

If you look at the architectures behind most of the larger and more successful ASP.NET application platforms (sharepoint, the 1.x starter kits, IBuySpy, DotNetNuke, CommunityServer, etc.) you will usually find elaborate examples these kinds of hacks. All of them are just variations on a theme… use MVC-like patterns to gain some control over the HTTP request/response pipeline.

With the rise of modern AJAX techniques and technologies, the need for a new approach has become very apparent. Ajax mucks around with the request pipeline in ways that the webforms framework does not tolerate elegantly. If you’ve tried to do any significant Ajax stuff in webforms, you’ve probably noticed how quickly things get messy.

Fortunately Microsoft recognized this and decided to formally embrace the MVC pattern. The result is the ASP.NET MVC Framework which was delivered a few months ago.  

Which brings me back to TicketDesk….

I originally built TicketDesk 1.x  as a way to experiment with .NET technologies that were new at the time (Ajax, EF, and LINQ to SQL). So I thought it would be fitting to do the same thing again now to get my hands dirty with the Microsoft MVC Framework.

I didn’t port the existing TicketDesk 1.x code though. Instead, I’ve started with a clean solution and am re-implementing the same set of features as TicketDesk 1.x using all fresh code written for the MVC framework.

I suspected all-along that TicketDesk would probably map very well to the MVC Framework, and I’m no stranger to the MVC design pattern itself. I had also hoped that the MVC design pattern might eliminate many of the obstacles I had encountered especially with the Ajax parts of TicketDesk 1.x.

The experiment is about 3 months old now, and has been very challenging. The MVC Framework itself has a lot of room for improvement, but is a solid foundation on which to start. Some of the most obvious drawbacks are the slim Visual Studio IDE support, sparse documentation, and poor examples of how to do ASP.NET MVC “the right way”.

The biggest challenge for me has been the steep learning curve. I’ve been writing web apps for over 12 years, most of that working with ASP.NET, but the ASP.NET MVC framework really requires an entirely different way of thinking. I’m also just now learning my way around JQuery too which has further slowed me down.

Currently Microsoft is providing only basic Ajax functionality within the MVC framework, but they have encouraged the use of JQuery. JQuery gives you a rich and very successful source for all those fancy UI components that Microsoft doesn’t provide on the MVC framework. While the ASP.NET MVC Framework doesn’t help you much with JQuery, it also doesn’t interfere any.  Future versions of the framework promise to further embrace JQuery head-on. I’ve not been impressed with Microsoft’s own ability to deliver decent Ajax libraries so far, but JQuery has a very large 3rd party community developing high quality code… and most of it is some kind of open source to boot.

While I’ve found that writing against the MVC Framework takes significantly longer and requires much more effort, the quality and usability of the resulting application is many orders of magnitude better.

So I’ve formally decided to re-write the official TicketDesk application on the ASP.NET MVC Framework.

The initial 2.0 release will not contain very much new functionality compared to 1.x, but I hope to provide a significantly better user experience and a much more compartmentalized code-base.

Currently TicketDesk 2.0 is targeting the ASP.NET MVC Framework 1.0 on the .NET 3.5 stack. I did experiment with the RTM release of the Entity Framework this time, but I still find that EF is just not ready… so I’ll be sticking with LINQ to SQL for a while longer.  I’m confident that I can switch back to EF should the next version resolve my remaining concerns. It is likely that the next version of the MVC Framework will be released before I am done with TicketDesk 2.0, so it will likely shift to target that version before the final release.

Here are some early goals for the TicketDesk 2.0 project:
  • Implement 100% of the functionality from TicketDesk 1.x
      
  • Upgrade Tools for 1.x to 2.x migration
      
  • Improve Application Settings and Administration (more and better online admin tools)
      
  • Improve formatting for RSS and Email notifications
      
  • Enable full functionality for browsers without JavaScript
      
  • Enable smoother Ajax UI features for browsers that do support JavaScript
      
  • Use a Markup editor instead of a WYSIWYG HTML editor (too many problems with raw HTML data entry). I’m currently working with MarkItUp! using Markdown syntax.
      
  • Unit testing for controllers and business/entity logic (using VS Test Project)
      
  • A cleaner separation between the web application and model/business/entity logic
      
  • Fully W3C compliant XHTML 1.0 Strict Output
I have no real time-frame for a 2.0 delivery as this is still a part-time project for me. Currently I have implemented most of the functionality for the TicketCenter, new ticket creation, and have just started work on the Ticket Viewer/Editor.

I have not marked out a potential 1.3 upgrade of the older code-base either, but my primary focus will be on the 2.0 MVC version.