August 2010 - Posts

Dynamic LINQ Part 2 (Evolution)

A while ago, Scott Gu blogged about a Dynamic LINQ library that was part of the C# samples in VS2008. This library let developers put together late bound LINQ queries using a special syntax. This really comes in handy when you want to build a LINQ query based on dynamic input. Consider the following example:

public ActionResult List(string sort) {
    var northwind = new NorthwindEntities();
    IQueryable<Product> products = northwind.Products;
    switch (sort) {
        case "ProductName":
            products = products.OrderBy(p => p.ProductName);
            break;
        case "UnitsInStock":
            products = products.OrderBy(p => p.UnitsInStock);
            break;
        default:
            break;
    }

    return View(products.ToList());
}

In the above example, we’re trying to sort a list of products based on the sort parameter before we render the view. Writing the above code is painful, especially if you have a large number of sortable fields. Of course this isn’t the only way to write this code, in fact, if you’re a LINQ expert you can build an expression tree at runtime that represents the sort expression, but that solution is far from trivial to implement.


Last time we showed how we can (ab)use C# 4 dynamic feature to enable LINQ queries against dynamic types. Today I’ll show you how we can expand on that concept, and turn our previous solution into a general purpose library that allows us to execute late bound queries against existing LINQ providers.

The library itself is pretty small, it consists of 2 classes:

  • DynamicExpressionBuilder – The dynamic object that we used last time to build the actual expression tree
  • DynamicQueryable – a static class with a bunch of extension methods that are parallel to the actual Queryable static class

DynamicExpressionBuilder uses the same trick as last time (dynamic dispatch to build expressions), but this time we’re building expressions that would be recognized by most LINQ providers.

public override bool TryGetMember(GetMemberBinder binder, out object result) {
    result = new DynamicExpressionBuilder(Expression.Property(Expression, binder.Name));
    return true;
}

public override bool TryBinaryOperation(BinaryOperationBinder binder, object arg, out object result) {
    result = MakeBinaryBuilder(Expression, binder.Operation, arg);
    return true;
}

public override bool TryInvokeMember(InvokeMemberBinder binder, object[] args, out object result) {           
    MethodInfo methodInfo = null;           
    if (args.Any(a => a == null)) {
        methodInfo = Expression.Type.GetMethod(binder.Name);
    }
    else {
        var types = args.Select(arg => arg.GetType()).ToArray();
        methodInfo = Expression.Type.GetMethod(binder.Name, types);
    }           

    if (methodInfo != null) {
        var expression = Expression.Call(Expression, methodInfo, args.Select(Expression.Constant));
        result = new DynamicExpressionBuilder(expression);
        return true;
    }
    return base.TryInvokeMember(binder, args, out result);
}
The actual implementation overrides more methods, but for brevity we’re only going to look at 3 of them. Each method captures the intent of the code 
and turns it into the appropriate
expression tree.
public static class DynamicQueryable {            
    public static IQueryable Where(this IQueryable source, Func<dynamic, dynamic> predicate) {
        return Where(source, GetExpression(source.ElementType, predicate));
    }

    private static LambdaExpression GetExpression(Type elementType, Func<dynamic, dynamic> expressionBuilder) {
        ParameterExpression parameterExpression = Expression.Parameter(elementType, expressionBuilder.Method.GetParameters()[0].Name);
        DynamicExpressionBuilder dynamicExpression = expressionBuilder(new DynamicExpressionBuilder(parameterExpression));
        Expression body = dynamicExpression.Expression;
        return Expression.Lambda(body, parameterExpression);
    }

    private static IQueryable Where(IQueryable source, LambdaExpression expression) {
        return source.Provider.CreateQuery(Expression.Call(typeof(Queryable),
                                                            "Where",
                                                            new[] { source.ElementType },
                                                            source.Expression,
                                                            Expression.Quote(expression)));
    }
    …etc
}
Normally when you’re writing LINQ queries, the implementations of Where, Select, SelectMany and other extension methods come from either the Enumerable or Queryable 
class.The DynamicQueryable class uses this pattern to provide overloads of the same extension methods on the non generic IQueryable.
This lets us do a lot of cool things with existing LINQ providers. Let’s look at some examples:
using System;
using System.Linq;

class Program {
    static void Main(string[] args) {
        NorthwindEntities northwind = new NorthwindEntities();
        var query = northwind.Products.Where(p => p.UnitPrice < 10).OrderBy(p => p.ProductName);

        foreach (Product p in query) {
            Console.WriteLine(p.ProductName);
        }
    }
}
Above we have a regular LINQ to Entities query against Northwind. Notice the usings are System, and System.Linq, those make sure that you’re using the Queryable 
implementations of Where and OrderBy.
We can write this same code in with Dynamic LINQ:
using System;
using DynamicLINQ;

class Program {
    static void Main(string[] args) {
        NorthwindEntities northwind = new NorthwindEntities();
        var query = northwind.Products.Where(p => p.UnitPrice < 10).OrderBy(p => p.ProductName);

        foreach (Product p in query) {
            Console.WriteLine(p.ProductName);
        }
    }
}
We replaced the System.Linq using with a DynamicLINQ using, which makes use of our extensions methods. If we wanted to sort by a dynamic variable you could do it 
using the indexer syntax supported by DynamicQueryBuilder:
using System;
using DynamicLINQ;

class Program {
    static void Main(string[] args) {
        NorthwindEntities northwind = new NorthwindEntities();

        var query = northwind.Products.Where(p => p.UnitPrice < 10).OrderBy(p => p["ProductName"]);

        foreach (Product p in query) {
            Console.WriteLine(p.ProductName);
        }
    }
}
That’s all pretty cool, but there are more scenarios that can’t be done today using regular LINQ which Dynamic LINQ makes easy. 
One of the biggest annoyances for me using LINQ today, is the fact that custom functions can’t be used inside of query expressions. This makes total sense once 
you understand how LINQ expressions work, but if it were possible, you could make big queries a lot be more maintainable.
Take the following query using LINQ to Entities:
using System;
using System.Linq;

class Program {
    static void Main(string[] args) {
        NorthwindEntities northwind = new NorthwindEntities();

        var query = northwind.Products.Where(p => IsCheap(p));

        foreach (Product p in query) {
            Console.WriteLine(p.ProductName);
        }
    }

    public static bool IsCheap(Product product) {
        return !product.Discontinued && product.UnitsInStock < 10;
    }
}
Anyone who has done any programming with LINQ to Entities knows that this won’t work. When we try to run this code, we get an error saying that this expression 
cannot be converted to SQL, or more precisely:

Unhandled Exception: System.NotSupportedException: LINQ to Entities does not recognize the method 'Boolean IsCheap(Sample.Product)' method, and this method cannot be translated into a store expression

The reason this doesn’t work is because the compiler puts the IsCheap method call in the expression tree and EF tries to convert it into SQL:

Convert(value(System.Data.Objects.ObjectSet`1[Sample.Product])).MergeAs(AppendOnly).Where(p => IsCheap(p))

What we really wanted is for the compiler to replace IsCheap with the expression in the body of IsCheap, i.e. replace IsCheap with !product.Discontinued && product.UnitsInStock < 10.

This same expression would work today with DynamicLINQ:

using System;
using DynamicLINQ;

class Program {
    static void Main(string[] args) {
        NorthwindEntities northwind = new NorthwindEntities();

        var query = northwind.Products.Where(p => IsCheap(p));

        foreach (Product p in query) {
            Console.WriteLine(p.ProductName);
        }
    }

    public static dynamic IsCheap(dynamic product) {
        return !product.Discontinued && product.UnitsInStock < 10;
    }
}

We tweaked the method slightly, changing the argument and return type to be dynamic. When we run this code it works and we see a list of cheap products.

The difference here is that we’re actually executing code to build the expression tree, so instead of putting the method call into the resultant tree we actually invoke the method and the expression tree ends up looking like this:

Convert(value(System.Data.Objects.ObjectSet`1[Sample.Product])).MergeAs(AppendOnly).Where(p => (Not(p.Discontinued) And (Convert(p.UnitsInStock) < 10)))

There are also some scenarios that don’t work in the current implementation. For example, casting and some projections don’t work well with existing LINQ providers. I’ve put the code on a bitbucket repository.

http://bitbucket.org/dfowler/dynamiclinq

Enjoy!

Posted by davidfowl with 10 comment(s)
Filed under: , ,

Dynamic LINQ (A little more dynamic)

It seems I’ve become (in)famous in the last two days. With all the buzz around Microsoft.Data, I figure I’d post on something else that is somewhat related.

There were a bunch of comments on my previous posts that said we should do something to this effect:

dynamic db = Database.Open("name");
var q = from p in db.Products
        where p.UnitsInStock < 20
        select p;
Which looks like LINQ but it would be over a dynamic object. Of course you don’t get the strong typing benefits of LINQ since there are no classes, but at least the 
syntax is nice and the user is protected from SQL injection.

Disclaimer

This blog post is a technical discussion about the limitations that exist today around LINQ and dynamic and interesting ways to get around it. Nothing more. Continue reading.

Unfortunately when we try to compile this the compiler screams:

Query expressions over source type 'dynamic' or with a join sequence of type 'dynamic' are not allowed

Maybe it’s better if we just use a real ORM now, or the other Dynamic LINQ library, but now I’m curious and want to figure out if this is even possible. Let’s try it another way:

dynamic db = Database.Open("name");
var q = db.Products.Where(p => p.UnitsInStock < 20);

The compiler complains again, but this time with a different error:

Cannot use a lambda expression as an argument to a dynamically dispatched operation without first casting it to a delegate or expression tree type

So we’re being blocked in two different ways. Can we work around this somehow?

Since we can’t query over a dynamic object we’ll give the Database and the Products property static types:

class Database {
    private string _databaseName;
    public Database(string databaseName) {
        _databaseName = databaseName;
    }

    public static Database Open(string name) {
        return new Database(name);
    }

    public Query Products {
        get {
            return new Query();
        }
    }
}

class Query {

}

Now we can do this:

var db = Database.Open("name");
var q = from p in db.Products
        where p.UnitsInStock < 20
        select p;

Changing dynamic to var lets type inference take over so that db is typed as a Database instead of dynamic. Now we try to compile again:

Could not find an implementation of the query pattern for source type 'Query'.

This is a problem we can solve. The compiler translates code written using the query syntax:

var q = from p in db.Products
        where p.UnitsInStock < 20
        select p;

Into this:

var q = db.Products.Where(p => p.UnitsInStock < 20);

Bart de Smet has a great explanation on how this works. What this means is that we can implement the Select, Where, SelectMany or any other methods on our dynamic object that will make the compiler happy.

Given that, we can implement a dummy Where method to try to get this code to compile:

class Query { 
    public dynamic Where(Expression<Func<dynamic, dynamic>> predicate) {
        return null;
    }
}
Turns out that trying to compile this yields yet another error:
An expression tree may not contain a dynamic operation
Three different compiler errors, all to do with LINQ and dynamic. Looks like the compiler team went out of their way to block this. Let’s see if removing the 
expression works:
class Query {
    public dynamic Where(Func<dynamic, dynamic> predicate) {
        return null;
    }
}

Finally we’ve gotten the code to compile, now what? Well if this was LINQ to SQL/EF/SharePoint/etc., the query would be translated into an expression tree and executed on the server when we enumerated the results. I’m more interested in the first part of that, i.e. can we build an expression tree using dynamic LINQ.

Normally when you declare a lambda it can act like an expression tree or a delegate:

Expression<Func<int, int, int>> expr = (a, b) => a + b;
Func<int, int, int> add = (a, b) => a + b;
Console.WriteLine(expr);
Console.WriteLine(add);
The above code prints out (a, b) => (a + b) and System.Func`3[System.Int32,System.Int32,System.Int32], as expected. The compiler does all the hard work of figuring 
out how to build an expression tree from the code the user writes, but we don’t have this luxury since we’re in the dynamic world.

Enter Dynamic Expression

NOTE: If you aren’t familiar with the new dynamic in C#/VB feature, read up on it here.

We introduce a new class called DynamicExpression that we will use to make the compiler do our bidding:

class DynamicExpression : DynamicObject {
   
}
Before we dig into the implementation of this class, we have to figure out what kind of expressions we are going to build. How can we represent p.UnitsInStock in an
expression tree. Normally that would be a PropertyExpression that represents the UnitsInStock property on the Product class generated by either LINQ to SQL or

LINQ to Entities. We can’t do this since we don’t have any strong types.
For simplicity, we’re going to assume that all of the properties are integers, and that property access will be represented by a call to a Property method defined 
on DynamicExpression i.e. p.UnitsInStock will become a call to Property(“UnitsInStock”).
Now that we have that our of the way, we can get to the implemention. Lets start with TryGetMember:
class DynamicExpression : DynamicObject {
    public DynamicExpression(Expression expression) {
        GeneratedExpression = expression;
    }

    public Expression GeneratedExpression { get; private set; }

    public override bool TryGetMember(GetMemberBinder binder, out object result) {
        MethodInfo propertyMethod = typeof(DynamicExpression).GetMethod("Property", BindingFlags.NonPublic | BindingFlags.Static);
        var expression = Expression.Call(propertyMethod, Expression.Constant(binder.Name));
        result = new DynamicExpression(expression);
        return true;
    }

    private static int Property(string propertyName) {
        return 0;
    }
}
We’re storing the generated expression in a property so we can grab it after doing a bunch of manipulation. The Property method doesn’t actually have to do anything since 
its sole purpose is to represent property access.
Next we move on to simple binary expressions:
public override bool TryBinaryOperation(BinaryOperationBinder binder, object arg, out object result) {
    DynamicExpression dynamicExpression = arg as DynamicExpression;
    Expression rhs = null;
    if (dynamicExpression != null) {
        rhs = dynamicExpression.GeneratedExpression;
    }
    else {
        rhs = Expression.Constant(arg);
    }
    var expression = Expression.MakeBinary(binder.Operation, GeneratedExpression, rhs);
    result = new DynamicExpression(expression);
    return true;
}
First we are checking to see if the right hand side of the expression is itself a DynamicExpression and unwrapping the underlying expression if that is the case. Otherwise it
takes the right hand side to be a constant.
Some people may have already put the big picture together, if you haven’t as yet don’t worry, it isn’t exactly obvious how this works. We are trying to create an expression
tree using dynamic dispatch, that is, using the execution of the dynamic operations to build an expression tree.

Putting the pieces together

Remember how the compiler complained earlier when we tried to use dynamic in an expression tree? That’s why our overload of Where doesn’t take a Expression<Func<dynamic, dynamic>> but instead it takes a Func<dynamic, dynamic>. We’re going to fill in that blank implementation of the Where method we left off earlier.

class Query {
    public Query(Expression expression) {
        GeneratedExpression = expression;
    }

    public Expression GeneratedExpression { get; private set; }

    public Query Where(Func<dynamic, dynamic> predicate) {
        DynamicExpression expr = predicate(new DynamicExpression(GeneratedExpression));
        return new Query(expr.GeneratedExpression);
    }
}
Let’s we go back to the original query:
dynamic db = Database.Open("name");
var q = from p in db.Products
        where p.UnitsInStock < 20
        select p;
The compiler is going to turn this into a call to db.Products.Where(p => p.UnitsInStock < 20). When this calls our implementation of Where, we construct a DynamicExpression 
and execute the lambda (p.UnitsInStock < 20) on it. While executing the lambda, the compiler sees a member access(p.UnitsInStock) on a dynamic object p and calls our
implementation of TryGetMember. Instead of TryGetMember returning a value for p.UnitsInStock, it returns an Expression that represents a member access and wraps
it in another DynamicExpression so that the process continues. The compiler then sees the less than operator and calls our implementation of TryBinaryOperation. We use the
same trick for this as we did for member access (return an expression instead of a value). This could be done for all of the operators but I chose to two for brevity.
Now that we’ve done this, we can print out the generated expression. Here is the full program:
using System;
using System.Dynamic;
using System.Linq.Expressions;
using System.Reflection;

class Program {
    static void Main(string[] args) {
        var db = Database.Open("name");
        var q = from p in db.Products
                where p.UnitsInStock < p.Price + 1
                select p;

        Console.WriteLine(q.GeneratedExpression);
    }
}

class Database {
    private string _databaseName;
    public Database(string databaseName) {
        _databaseName = databaseName;
    }

    public static Database Open(string name) {
        return new Database(name);
    }

    public Query Products {
        get {
            return new Query(null);
        }
    }
}

class Query {
    public Query(Expression expression) {
        GeneratedExpression = expression;           
    }

    public Expression GeneratedExpression { get; private set; }

    public Query Where(Func<dynamic, dynamic> predicate) {
        DynamicExpression expr = predicate(new DynamicExpression(GeneratedExpression));
        return new Query(expr.GeneratedExpression);
    }
}

class DynamicExpression : DynamicObject {
    public DynamicExpression(Expression expression) {
        GeneratedExpression = expression;
    }

    public Expression GeneratedExpression { get; private set; }

    public override bool TryGetMember(GetMemberBinder binder, out object result) {
        MethodInfo propertyMethod = typeof(DynamicExpression).GetMethod("Property", BindingFlags.NonPublic | BindingFlags.Static);
        var expression = Expression.Call(propertyMethod, Expression.Constant(binder.Name));
        result = new DynamicExpression(expression);
        return true;
    }

    public override bool TryBinaryOperation(BinaryOperationBinder binder, object arg, out object result) {
        DynamicExpression dynamicExpression = arg as DynamicExpression;
        Expression rhs = null;
        if (dynamicExpression != null) {
            rhs = dynamicExpression.GeneratedExpression;
        }
        else {
            rhs = Expression.Constant(arg);
        }
        var expression = Expression.MakeBinary(binder.Operation, GeneratedExpression, rhs);
        result = new DynamicExpression(expression);
        return true;
    }

    public override bool TryUnaryOperation(UnaryOperationBinder binder, out object result) {
        if (binder.Operation == ExpressionType.IsFalse || binder.Operation == ExpressionType.IsTrue) {
            result = false;
            return true;
        }
        return base.TryUnaryOperation(binder, out result);
    }

    private static int Property(string propertyName) {
        return 0;
    }
}

Caveats

So we were able to get some expression tree support while combining LINQ and dynamic, but there are things to be aware of.

If the lhs of any operation isn’t dynamic then our TryBinaryOperation will never get called:

var q = from p in db.Products
        where 1 > p.UnitsInStock
        select p;

This would fail since 1 isn’t a dynamic object, we need something to bootstrap the dynamic dispatch.  Exercise for the reader, can we get around this limitation?

Nested queries won’t work without casting:

var q = from c in db.Categories
        from p in c.Products
        where p.UnitsInStock < 20
        select p;
This doesn’t work right now because we didn’t implement SelectMany, but also because of the first compiler error we encountered (Query expressions over source 
type 'dynamic' are not allowed). The nested query (from p in c.Products) would be trying to query over a dynamic source.
Query syntax doesn’t work in VB. VB does semantic translation instead of syntactic translation:
Dim q = From p In db.Products
        Where p.UnitsInStock < 20
        Select p
When the VB compiler generates the method for p.UnitsInStock < 20, it injects a type check for a boolean result which completely breaks this approach. 
You can however still use the lambda syntax to work around this.
There are probably other quirks that come with using this approach, and it is clear that the C# team took time to block certain scenarios, so don’t expect this to 
be a 100% solution.
We’ve successfully abused the new dynamic feature to get it working with LINQ. Hopefully in some future version of C# and VB we get deeper integration with 
LINQ and dynamic.
Posted by davidfowl with 3 comment(s)
Filed under: , ,

Microsoft.Data.dll - A re-introduction

Before we look at the reasons for the existence of Microsoft.Data, we need some background on the WebMatrix initiative. Here are some excellent blog posts that describe the type of audience we are going after and the goals of the project:

http://weblogs.asp.net/scottgu/archive/2010/07/06/introducing-webmatrix.aspx

http://blogs.msdn.com/b/davidebb/archive/2010/07/07/how-webmatrix-razor-asp-net-web-pages-and-mvc-fit-together.aspx

http://geekswithblogs.net/mbcrump/archive/2010/07/06/the-forrest-gump-guide-to-the-new-webmatrix.aspx

These blog posts point out that WebMatrix targets a different audience than our traditional pro developer audience. These are people who are not yet developers (if they ever plan to be). They may never want to become a pro developer, but want to be able to put together a simple website. If you've internalized best practices and patterns and know your way around an ORM, then you will most likely never use this library. We are trying to attract a different kind of customer: people who are on other platforms and are already using SQL today in their web pages, and people that are trying to get into programming and haven’t chosen a platform as yet.

Most likely, if you're even reading this, you're not the intended audience, though we are still interested in your feedback.

Why Microsoft Data?

Scott Gu quote:

We've debated the role of ORMs for entry-level devs quite a bit (and have a dynamic ORM implemented - but which we didn't ship with today's preview).  For someone who understand objects and classes they can be great. When we brought in web developers to use the product for a few days, though, one interesting data point that came up repeatedly was that about 80% of them had never even heard the term "ORM".  We found it took awhile for them to conceptually grok it - whereas adhoc SQL came much easier to them. It is something we are going to continue to work on finding the right balance on.

I could not put this any better. We didn’t immediately decide to go with SQL, but after going through several options, we decided it was the simplest way to go.

Absolute beginners won’t have Visual Studio, and most of our existing data stack relies heavily on tooling support (designers, and code generators). We wanted to build something that felt like it could be authored from notepad.

Who Is it For?

Nikhil has a good blog post on personas http://www.nikhilk.net/Personas.aspx:

Mort, the opportunistic developer, likes to create quick-working solutions for immediate problems and focuses on productivity and learn as needed. Elvis, the pragmatic programmer, likes to create long-lasting solutions addressing the problem domain, and learn while working on the solution. Einstein, the paranoid programmer, likes to create the most efficient solution to a given problem, and typically learn in advance before working on the solution.

WebMatrix is trying to target beginners (like Mort, or pre-Mort). By beginners I mean, people that:

  • May not understand generics (or don’t care for them)
  • Do not care about TDD, DRY, SRP, (insert acronym here)
  • Do not understand lambdas or expression trees.
  • May not have any knowledge of OOP

I could go on and on, but the point I am trying to get across is that if any of this sounds absurd to you or if it offends you in any way as a developer, then you are probably not the target audience.

This post can be summarized by the following quote from Scott Gu’s blog:

If you are a professional developer who uses VS today then WebMatrix is not really aimed at you - at least not for your "day job".  You might find WebMatrix useful for quickly putting a blog on the web or doing lightweight scripting on the side.  But it isn't intended or focused on hard-core professional or enterprise development.  It is instead aimed more for people looking to learn how to program and/or who want to get a site up and running on the web without having to write much code.

Posted by davidfowl with 19 comment(s)

Introduction to Microsoft.Data.dll

UPDATE!

Before you continue reading this blog post, check out the prequel.

 

I’ve been pretty busy recently working on cool features for “ASP.NET WebPages with Razor Syntax” (what a mouth full) and other things. I’ve worked on tons of stuff that I wish I could share with you, but what I can share is something that many people haven’t blogged about - Microsoft.Data.dll.

What is Microsoft.Data

It’s an awesome new assembly/namespace that contains everything you’ll ever need to access a database. In ASP.NET WebPages we wanted people to be able to access the database without having to write too many lines of code. Any developer that has used raw ADO.NET knows this pain:

using (var connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Northwind.mdf;
Initial Catalog=|DataDirectory|\Northwind.mdf;Integrated Security=True;User Instance=True")) {
    using (var command = new SqlCommand("select * from products where UnitsInStock < 20", connection)) {
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader()) {
            while (reader.Read()) {
                Response.Write(reader["ProductName"] + " " + reader["UnitsInStock"]);
            }
        }
    }
}
Wow, that’s a lot of code compared to:
using (var db = Database.OpenFile("Northwind")) {
    foreach (var product in db.Query("select * from products where UnitsInStock < @0", 20)) {
        Response.Write(product.ProductName + " " + product.UnitsInStock);
    }
}

The user doesn’t have to learn about connection strings or how to create a command with a connection and then use a reader to get the results. Also, the above code is tied to Sql Server since we’re using specific implementations of the connection, command, and reader(SqlConnection, SqlCommand, SqlDataReader).

Compare this with code below it. We’ve reduced the amount of lines required to connect to the database, and the syntax for accessing columns is also a lot nicer, that’s because we’re taking advantage of C#’s new dynamic feature.

Why is it so much easier you ask? Well, the Database class is what you’ll be working with when accessing data. There are several methods that let you perform different kinds of queries and factory methods for connecting to the database.

Connecting to the Database

Sql Compact 4 is our main story when developing locally with web matrix, so we optimized for the “I have a database file under App_Data in my web site and I want to access it” case. The first overload we’re going to look at does exactly that and is named appropriately, Database.OpenFile.

Database.OpenFile takes either a full path or a relative path, and uses a default connection string based on the file extension in order to connect to a database. To see this in action, run the starter site template in webmatrix and add this code to the Default.cshtml:

var db = Database.OpenFile("StarterSite.sdf");
@ObjectInfo.Print(db.Connection)
The first line will create a database object with a connection pointing to the sdf file under App_Data. The second line is taking advantage of our ObjectInfo helper 
(more on this later) to show the properties of the database object.
sqlconnection

Looking at the properties you can see that the connection state is closed, pretty weird for a method called Open to return a closed connection no? The reason is we want to delay the work as long as possible(we don’t even create the connection up front) i.e. until we actually decide to do a query.

If you look at the ConnectionString property you’ll see |DataDirectory|\StarterSite.sdf, this is one of the default connection strings I mentioned earlier. We assume relative path means within the |DataDirectory| which is “App_Data” in this case.

For simple cases OpenFile works. One of the big downsides is the fact that you are essentially hardcoding that you are using a database file, which will make it harder to migrate to sql server in the future. We have a solution for this that I’ll talk about below.

For those developers that understand connection strings and need a litte more control, then Database.OpenConnectionString might be for you. This API does exactly what you think it does, create a database object with a connection that uses the connection string specified.

var db = Database.OpenConnectionString(@"Data Source=.\SQLEXPRESS;
                                         AttachDbFilename=|DataDirectory|\Northwind.mdf;
                                         Integrated Security=True;User Instance=True");
This is nice for more control but, connection strings are things that normally are stored in a web.config so it can be changed without recompiling the application.
Last but not least is the most magical (and controversial) solution. We went back and forth about this one but put it in the beta in order to get some feedback. 
Database.Open allows the user to specify a named connection, which can be one of 3 things (this is where the magic comes in):
  1. A database file under the data directory (App_Data) that matches the name plus one of our supported extensions (.sdf or .mdf)
  2. A connection registered with and Database.RegisterConnectionString, Database.RegisterFile APIs
  3. A connection string from web.config
We had lots of debate (still ongoing) internally about what the fall back order should be and if there should even be a fallback order. Today, we look for a 
connection in all the mentioned places and throw an exception if it is ambiguous (i.e. more than one) to prevent confusion. Database.Open is what we recommend since
it allows
the user to change what connection a name maps to, making a simple migration from Sql Compact 4 to Sql Server possible without any code change.
With this in mind here are some examples on how you can use Database.Open:
File based
// (Assume you have Northwind.mdf in the database)

Database.Open("Northwind")

Register API

// _start.cshtml

Database.RegisterConnectionString("MyDatabaseServer", "Data Source=192.168.1.20;Initial Catalog=MyDb")


// SomeFile.cshtml
Database.Open("MyDatabaseServer")

Configuration

// web.config
<
configuration>
  <connectionStrings>
    <add name="Northwind2"
        connectionString="Data Source=.\SQLEXPRESS;Integrated Security = true;Initial Catalog=Northwind"
        providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

Database.Open("Northwind2")

I mentioned that the code in the very first example was tied to SqlServer. We internally use ADO.NET’s data providers to construct connections, so Microsoft.Data will mostly work with any database has a registered ADO.NET provider.

Caveats

Since we optimized for what we thought would be the most common scenario we set up some default values and behaviors that may be hard to debug.
One of these is the fact that the default ADO.NET provider is Sql Compact 4. To work around these limitations we added a providerName paramter to methods like OpenConnectionString and RegisterConnectionString. We also recommend that you specify the providerName attribute when defining connection strings in web.config. Following those patterns will mitigate most of the issues.

We also provide a global override so you can change what the default provider is. For example, if you wanted to make the default provider Sql Server then you can do it by adding the following piece of configuration to your website:

<configuration
  <appSettings>
    <add key="systemData:defaultProvider" value="System.Data.SqlClient" />
  </appSettings>
</configuration>
Today I covered connecting to the database, next time I’ll concentrate on querying. Stay tuned…
Posted by davidfowl with 74 comment(s)
More Posts