Here’s the 15th post in our series of guest posts by Microsoft Most Valued Professionals (MVPs). (Click the “MVPs” tag in the right column to see the rest.) Since the early 1990s, Microsoft has recognized technology champions around the world with the MVP Award. MVPs freely share their knowledge, real-world experience, and impartial and objective feedback to help people enhance the way they use technology. Of the millions of individuals who participate in technology communities, around 4,000 are recognized as Microsoft MVPs. You can read more original MVP-authored content on the Microsoft MVP Award Program Blog.
This post is by Richard Minerich, an F# MVP.
Richard here! In an era where the line between program and data is becoming ever more blurred one of the biggest challenges for many programmer teams has become bridging the data-application divide. So deep is this gap that some companies have resorted to keeping programmers and DBAs in separate groups to try to protect themselves, but this comes at a cost of significant decreases in efficiency. As data increasingly becomes the focus of our day-to-day work, it’s important that we step back and rethink how we approach these problems.
What if your compiler could reach into your database and understand the schema? Would it streamline your processes if compilation would fail if something in the database it depended on was removed or changed? How much time would you save if you had IntelliSense coming right from the structure, relationships, and field types in your database? It may seem too good to be true, but you can have all of these things right now and much more with F# 3.0’s new Type Providers feature.
Let’s pretend for a moment that we’ve inherited a badly designed customer database and we’re writing an application that has a particular feature requirement the database obviously won’t support well.
Namely, we’re going to need to support government as a different type of customer. Thankfully, we have our business customer listing app which was previously written with F# type providers to use as a model.
Now, at first glance this may look like a standard LINQ query, but it’s actually a living and breathing .NET representation of the database schema with full IntelliSense support.
Moving back to the problem we’re trying to solve, it looks like we’re going to need to change the schema to support government customers. We could simply try and squeeze yet another value into the already badly defined IsBusiness field, but in the interest of fighting technical debt we’re going to refactor the database. While we’re at it, we might as well make it a nice clean schema with a foreign key into a table that defines the different customer types.
After performing a somewhat painful migration, we return to our application in Visual Studio 2012 and immediately see something’s changed:
The IsBusiness field on the Customer record is no longer there, and the compiler knows it. If we try to build, compilation will fail with "error FS0039: The field, constructor or member 'IsBusiness' is not defined.”
No need to worry, though; this is an easy fix because we put the effort in and made a foreign key constraint. The F# SQL Type Provider will use this constraint to perform automatic joins across the two tables. It even shows up in the IntelliSense:
After a rather large refactoring of our database, just a small change to our application is needed to support our new government customers. We can simply change our old predicate to a direct comparison with the text field representation in the CustTypes table. There’s no need to worry about the join—it’s completely automatic.
Now this is certainly an impressive user story, but it’s really just the tip of the Type Provider iceberg. First consider that F# Type Providers work with many different types of databases and schema. If your favorite isn’t supported, someone in the F# community might have already written one and you can make them yourself easily enough.
Even more impressive, Type Providers aren’t limited to just pulling information from databases—they’re a general-purpose mechanism for generating types at compile time (or IntelliSense time). Talented programmers in the F# community have already begun probing the limits of what Type Providers can do. My favorite of these efforts is the R Type Provider, which provides a direct interface layer with the R Statistical Programming Language.
When we open our compilers to types from the outside world, we enable all kinds of improvements to existing best practices through exploratory programming, immediate feedback, and simplified interoperability layers. With the release of Type Providers in Visual Studio 2012 just weeks ago we’ve already seen some users reach far beyond even these broad domains. With so much happening so fast, I for one can’t wait to see what the coming months will bring.
Great post, thanks!
I have a question which would be great if someone could answer:
Is this type provider (or the DBML File type provider) married/hardcoded to MS SQL Server or can it be used with say Oracle? If it's only for MS SQL Server, is it possible to adapt it to access Oracle databases? It's not clear to me whether MS in their documentation uses "SQL Database" or "Relational database" as a generic term or a synonym for "MS SQL Server". Oracle is soon (?) to release a fully managed provider for accessing Oracle databases, for some (or many?) enterprise developers it could be a killer argument for using F# if this type provider (or an adapted one) could be used to access Oracle databases.
I tried asking this question yesterday, but it looks like it never got through, so let me try again.
Has Microsoft ever released the source (or even binary) for the provider that Don Syme demo'ed for the Freebase "database"?
And, of course, if so, where can we find it?
Jarle: There's a SQL type provider in the box, and as I understand it that should work with anything supported by C# Linq. Worse case, you can always just try it out. Even if you don't have Visual Studio 2010, you can install F# in the free web version of Visual Studio.
Larry Smith, Don Syme just wrote to say that he's working on this. Stay tuned.
Larry: Great news! The freebase type provider sample was just recently released.
F# TypeProviders Don't Work with AdventureWorks