Trying to understand Microsoft.Data.dll

Here is my analysis of the recently “released” (embedded) Microsoft.Data.dll assembly, the namespace and the types it includes. It’s been the topic of a lot of heated debate recently, with viewpoints I’m unable to relate to and understand just from reading, so I needed to understand.

The debate is stemming from a blog post by David Fowler and his example that shows how some data-related tasks have a simpler syntax with Microsoft.Data and the ASP.NET WebPages with Razor Syntax.

What is inside the Microsoft.Data namespace?

There is very little code inside the namespace and the assembly. It’s simply some helper types that makes life's a little bit easier. It’s not a new data access framework, like Linq to SQL or Entity Framework.

It contains the following classes: ConfigurationManagerWrapper, ConfigurationConfiguration, Database, DbProviderFactoryWrapper, DynamicRecord, IConfigurationManager, IDbFileHandler, IDbProviderFactory, SqlCeDBFileHandler and SqlServerDbFileHandler. Of which only Database and DynamicRecord are public available, the others are internal.

All data access inside the Microsoft.Data types are using the common ADO.NET types, not the providers specific for any SQL platform. This means it’s not restricted to SQL Compact Edition nor SQL Server. It relies on DbConnection, DbTransaction, DataTable, etc.

Microsoft.Data on ASP.NET Web Forms

While Microsoft.Data.dll is currently not accessible in the Add References dialog, you can find it by looking on your computer, it’s located in the Global Assembly Cache (GAC). Microsoft probably don’t want us to use it outside of WebMatrix in the current release… but if you just take a copy of the assembly out of the GAC, then you can reference the assembly in any .NET project and it will load it from the GAC (you just need the file so you can add a reference).

In my project I added a database to my App_Data folder (which you normally would never do, unless you are working with a local read-only cache in a large-distributed system or working with SQL Compact Edition) and added the following code to my web form, to make it render the Name column of my Users table.

	var db = Database.OpenFile("Database1.mdf");
	var users = db.Query("SELECT Id, Name FROM Users");
	foreach (var user in users)

Take notice of the OpenFile parameter, it’s simply the filename on disk. I don’t have to care about any specific details of the connection string, nor how to figure out where the App_Data folder is.

Obviously though, if you added an entity framework (EF) model of your database, you would have very similar example to achieve the same and you don’t have to care about the connection string, at least not in theory.

	using (var db = new Database1Entities())
	var users = db.Users;
	foreach (var user in users)

The two big distinctions betweens these examples is that the first one is dynamic, I can modify the database schema whenever I want and it won’t (necessarily) break my web app, while the latter example with EF will need to refresh the entity types based on the database model.

The other distinctions is that the first example doesn’t require a connection string, while the latter generates one for you automatically, a rather cryptic looking one.

<add name="Database1Entities" connectionString="metadata=
provider connection string=&quot;
Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;
Integrated Security=True;
User Instance=True;
MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />


While all of this are peanuts for me and anyone who’s been developing on .NET for a while, I think that making things simple where possible is positive, rather than negative. It doesn’t mean we will stop using NHibernate, do proper n-tier and layered architectures just because Microsoft makes some tasks simpler for beginners. It also means some of us probably will eventually have to maintain and possibly migrate solutions built on Microsoft WebMatrix, but does that give us any right to restrict beginners the privilege of building their own solutions and feeling the immense joy of realizing their dreams?

Other’s feedback and comments

Ayende Rahien comments on his blog on the example, where he mentions the use Response.Write within the loop. Understandable this is probably not the best way to do this, but it’s understandable with the sample in question, which was already using Response.Write. There are slightly better examples available out there. He also points out that having the SQL queries directly in the view code is an open invitation for SQL injection. Using proper parameterized queries will reduce this potential security problem. Looks like David updated the sample to use parameters after the initial reactions. After the security push at Microsoft some years back, they really cleaned up their practices with examples in the MSDN documentations, I think we should expect the same level of security thinking on employee blogs as well.

Ayende quotes David, which (David) made the assumption that Microsoft.Data is tied to Sql Server in any way, which my investigations has shown is not correct.

David tried to respond to some of the feedback on embedding SQL directly in the view, with hacking around to get the new dynamic keyword to work properly with LINQ. To me, this defeats the whole purpose of simplicity with Microsoft WebMatrix, Razor and Microsoft.Data.

KristoferA comments on the post and suggests generating Linq to SQL datacontext using a page-level directive, which would essentially give the developer entity objects to work with (and query against). This again defeats the purpose of simplicity, and now you can no longer change the database scheme without “recompiling” your web-app.

The namespace naming is another sour point for some, and I can agree that there is little point is “abusing” the Microsoft.Data namespace for such a trivial little helper, perhaps Microsoft.WebMatrix.Data or Microsoft.Data.Connection?

Who is this for?

Microsoft WebMatrix (and ASP.NET WebPages) is not a tool built for “professional” programmers, additionally is it not a fully generic framework for building everything. It’s a domain specific language that is optimized for building simple web applications without to much resources.

It is not meant for enterprise applications that handles millions of transactions. Will it be used for that? Yes, it probably will! We’ve seen plenty of classic examples of websites that starts with simple web-frameworks and find themselves in big trouble when their services become popular. Services like Twitter and Facebook was not built to scale to their current levels, yet they started as simple concepts and services and has grown to become important services which affects global policies and real-life social interactions.

It's Not Rocket Science, But It's Our Work:

And obviously, it’s for those of us who still miss the old days with ASP Classic, here is a pretty good (and funny) read, 8 Reasons to Stick with ASP 3.0 in 2006 (and 2007).

Final thoughts

It’s very clear that Microsoft WebMatrix (and related technologies) are primarily is focused towards beginners and it’s a great tool to build simple websites. I wouldn’t advice anyone to use this if you already know ASP.NET MVC and want to build complex web solutions, ASP.NET Web Forms, MVC or other more general purpose frameworks would probably be more fit.

Additionally I think it’s important to remember that WebMatrix is primarily focused on SQL Compact Edition for data access, the built in editor doesn’t allow you to modify SQL Server database. So the question (and response to some of the comments) is how many layers do you want to wrap your data access logic for a SQLCE database?

Been a while since Microsoft did a push towards simplifying development for beginners, when we went from VB6 to VB.NET, everything was more complex and the entry level for VB.NET is on-par with C#. With the release of .NET Framework 4, the complexity and total amount of features is mind blowing. I for sure welcome tools, languages and frameworks that simplifies how we develop software.

Simplicity is hard and it's something we should strive towards in all that we do.


Boks: Organize Your Stuff

drop_box256x256 During the winter I’ve been working on a new project called Boks. Boks is a rich interactive application that helps you organize all your stuff. Stuff such as books, movies, games and more.

Boks runs entirely in the cloud, that means all your stuff is stored online and there is never any need for downloads or installations. Just hit the website with your browser to access and manage your items. One feature I’m particularly proud about is how Boks will be accessible to anonymous (unregistered) users, so anyone can test it and understand how it works, before filling out out a very simple registration.

While you manage everything using an Silverlight 4 app, your content will be available as a webpage as well. This makes it easy for you to check what content you have, from any device, even from your mobile.

If you like to read a lot of books, listen to music or watch movies, you’ve probably already experience buying an item twice. Boks will help you avoid duplicate purchases and it will help you plan your wish-list for what you want to buy.

Boks is currently under development and beta testers will gain access in the first quarter of 2010. If you want to sign up, please send an short e-mail to and you will be notified when you can register.

Developing Boks has had the focus in using the latest technologies and techniques that Microsoft advocates. Windows Azure, SQL Azure, Silverlight 4, Entity Framework, Blend, Visual Studio 2010 and a lot is used in the production of Boks.

Boks is an interesting product and service, so I’ve written a presentation about it, which I’m hoping to do at the MIX10 conference.

Please vote for my session submission to MIX10 before January 15th. (make sure you submit your ballot after you’ve added the Boks session to it)

Learn about the challenges of building services in the Cloud. How to build solutions that can scale globally and massively. See how you can allow potential users, to interact with your service before registration, which can dramatically help increase user adoption.

Here is some early screenshots and a video, that demonstrates Boks in action on how it will work. Please beware that these screenshots are only from the protoype and the design will change dramatically in the coming weeks. Please visit our website for more information,






Entity Framework with extended properties

Here is an example that show how you can extend your Entity Framework entities with properties, which you might return from a stored procedure, a database view or inline SQL. The use case if when you want and need to return more data from your database than what is in your 1:1 mapped entity models. All my entity models have a 1 to 1 relationship between the physical and the logical models. It’s a requirement in Entity Framework that all mappings have a defined definition, so it’s not easy to just extend the model with extra properties that you might use in a few cases.

What we’re trying to achieve here is something that traditionally has been very easy with ADO.NET datasets, where you can populate the entity with data from extra columns even though the original up-front-design didn’t contain those columns.

This example shows how to extend the entity model and use it with WCF RIA Services without using the built-in feature for entity inheritance and complex types in Entity Framework. Initially I tried to use these features, but was unable to make it work properly in combination with WCF RIA Services. Current version of WCF RIA Services for Silverlight 4 and .NET 4 does not support Complex Types, only Entity Objects. The simplest way to use Stored Procedures in Entity Framework, if you don’t need WCF RIA Services support, is to import the function into the model and auto-generate a Complex Type based upon the return structure from your procedure.

This example is for read-only operations, for CUD-operations you can use the normal pattern.

Entity Model

Here is my simple model which contains Catalogs which holds Items. A catalog can only hold certain types of items, hence the ItemType table connected to the Catalog table. In our example we need to extend the Catalog entity with three more properties: ItemCount, ItemTypeName and Icon. As you can see, my table structure has no relationships. If you have relations between the entities, you will require to handle some compilation errors as you try to expose the custom type through the WCF RIA Services. I don’t have a solution for that in this post.


Entity Inheritance

As I mentioned earlier, this is not the built-in entity inheritance in Entity Framework. This has been explained by Muhammad Mosa here: Inheritance and Associations with Entity Framework Part 1 and Zeeshan Hirani here: Single Table Inheritance in Entity Framework.

First thing you need to do is create a new class that inherits from your generated entity model. I called my custom type for CatalogView and it extend the Catalog type with three simple properties.

public class CatalogView : Catalog
    public string Icon { get; set; }

    public int ItemCount { get; set; }

    public string ItemTypeName { get; set; }

The next step is to make the Catalog entity type aware of this custom type, this is a requirement for WCF. Open the *.Designer.cs for your Entity Model, find the correct entity type and add the following attribute to the class.

public partial class Catalog : EntityObject

This is required for WCF to be able to properly parse your custom entity. Remember that this change will be lost the next time you update your model. Without this property, you should see a similar compilation error: The derived entity type ‘CatalogView’ must be declared in a KnownTypeAttribute on the root entity ‘Catalog’.

Data Access Layer

Now we need to write some data access code which will query our database and return a list of CatalogView objects. For simplicity I’m using inline SQL in this example, yet the code would be very similar if you move the SQL code into a stored procedure.

public class BoksDataAccess
    public List<CatalogView> GetCatalogs(Guid userId)
        List<CatalogView> catalogs = new List<CatalogView>();

        string connectionString = Properties.Settings.Default.BoksConnectionString;

        using (var conn = new SqlConnection(connectionString))
            using (var cmd = new SqlCommand())
                cmd.Connection = conn;
                cmd.Parameters.Add("UserId", System.Data.SqlDbType.UniqueIdentifier).Value = userId;
                cmd.CommandText = @"SELECT
                                    it.Name AS ‘ItemTypeName’,
                                    (SELECT COUNT(i.Id) from Item i where i.CatalogId = c.Id) AS ‘ItemCount’
                                FROM Catalog c
                                LEFT JOIN ItemType it
                                ON c.ItemTypeId = it.Id
                                WHERE c.UserId = @UserId";

                var reader = cmd.ExecuteReader();

                while (reader.Read())
                    var catalog = new CatalogView();

                    catalog.Id = (Guid)reader["Id"];
                    catalog.Name = (string)reader["Name"];
                    catalog.Created = (DateTime)reader["Created"];

                    if (reader["Modified"] != DBNull.Value)
                        catalog.Modified = (DateTime)reader["Modified"];

                    catalog.UserId = (Guid)reader["UserId"];
                    catalog.ItemTypeId = (int)reader["ItemTypeId"];
                    catalog.Count = (int)reader["ItemCount"];
                    catalog.ItemTypeName = (string)reader["ItemTypeName"];
                    catalog.Icon = (string)reader["Icon"];



        return catalogs;

Domain Service

With our new custom data access layer in place, we can extend our domain service for the Catalog with an method that calls this operation. Here is my example:

public IEnumerable<CatalogView> GetCatalogsWithSummary()
    return new BoksDataAccess().GetCatalogs(new Guid("0DD43518-8AA2-4FF7-8A0B-6DECCB6C4412"));

Within your Silverlight application, you can now call this method to return the entities with the extended properties. Here is a simple example for a click event on a button within my app:

private void button1_Click(object sender, RoutedEventArgs e)
    Web.Services.CatalogContext service = new Web.Services.CatalogContext();
    service.Load(service.GetCatalogsWithSummaryQuery(), CatalogViewCallback, null);

private void CatalogViewCallback(LoadOperation<Models.Data.CatalogView> loadOperation)
    foreach (var catalogView in loadOperation.Entities)
        MessageBox.Show(catalogView.Name + " " + catalogView.ItemCount);


The procedure explained in this post should be considered a work-around for using extended properties with Entity Framework in relation with WCF RIA Services. There might be other ways of achieving the same, this is a solution I came up with after many hours of scanning through the examples and samples available on the web.

One positive feature of this implementation, as oppose to writing a new custom complex type that maps to your custom SQL query, is that you get inheritance of the entities on the WCF RIA Service client. You don’t get two different types with duplicate properties, but real inheritance. Here is the declaration of the CatalogView generated code on the client:

public sealed partial class CatalogView : Catalog