Marquee de Sells: Chris's insight outlet via OData 2.0 Marquee de Sells: Chris's insight outlet via ATOM 1.0 csells on twitter

You've reached the internet home of Chris Sells, who has a long history as a contributing member of the Windows developer community. He enjoys long walks on the beach and various computer technologies.

EF Concurrency Mode Fixed + MVC

Imagine a very simple EDM to describes web advertisements:


Now imagine that I’d like to build a web application to manage instances of the Ad type. If multiple people are editing ads at once, especially the same set of ads, I’m likely to run into concurrency errors. By default, EF lets the last change win.

For example, if Chris and Bill are both editing Ad.Id == 1, if Chris pushes his changes to the database first, EF will not notice that the ad has been updated underneath Bill will he saves his changes and Chris’s changes will be lost. What we really would like to happen is that, when Bill attempts to save his changes, that we check if the data has changed since we cached it so that Bill gets an error and is able to merge his changes in with Chris’s.

This style of multi-user concurrency management is called “optimistic concurrency” because it assumes few people will be changing the same data at the same time. It’s the most efficient means of concurrency management when that condition is true. Another type of concurrency management is named “pessimistic concurrency,” and is generally implemented using locks on the database, which tends to slow things down.

By default, EF provides no concurrency support; if two people push changes to the same row in the database, whoever’s change goes in last wins. This results in data loss, which in the world of data is a big, fat, no-no.

The way that EF lets you decide how a row is changed is via the Concurrency Mode property on every one of the entity’s properties in the designer. By default, the Concurrency Mode is set to “None”, which results in SQL like the following when an update is needed:

update [dbo].[Ads]
set [Title] = @0, [ImagePath] = @1, [Link] = @2, [ExpirationDate] = @3
where ([Id] = @4)

The Id column is used to select whether to perform an update, so any changes made to the underlying columns for that row are not detected and are therefore lost. The way to tell EF which columns to check is with the Concurrency Mode property set from None (the default) to Fixed on an entity’s property. For example, if you set Concurrency Model to Fixed for each of the read-write properties for our sample Ad entity, the update would look like the following:

update [dbo].[Ads]
set [Title] = @0, [ImagePath] = @1, [Link] = @2, [ExpirationDate] = @3
where ((((([Id] = @4) and ([Title] = @5)) and [ImagePath] is null)
and ([Link] = @6)) and ([ExpirationDate] = @7))

This is handy, but it also requires that we keep around an entity in memory in both its original state and its updated state for the length that the user is editing it. For desktop applications, that’s not an issue, but for stateless web pages, like MVC-based web pages, it is.

It’s for this reason that the EF team itself recommends using a special read-only column just describing the “version” of the row. Ideally, whenever any of the data in a row changes, the version is updated so that when an update happens, we can check that special column, e.g.

update [dbo].[Ads]
set [Title] = @0, [ImagePath] = @1, [Link] = @2, [ExpirationDate] = @3
where (([Id] = @4) and ([TimeStamp] = @5))

Here, the TimeStamp column is our “version” column. We can add such a column in our SQL Server database using the “timestamp” type, as shown in SQL Server Management Studio here:


The semantics of the timestamp type are just what we want: every time a row is updated, the timestamp column is updated. To see this new column in the Entity Data Model, you’ll have to right-click on the designer surface and choose Update Model from Database, which results in the TimeStamp being added to our model:


The TimeStamp field will come through as type Binary, since EF4 doesn’t have direct support for it, and with a StoreGeneratedPattern of Computed (which is exactly right). To enable EF to use the new column to perform optimistic concurrency, we need only change the Concurrency Mode to Fixed.

Now, here’s a simple Edit method on our MVC controller:

// GET: /Ad/Edit/5
public ActionResult Edit(int id) {
  return View(db.Ads.Single(ad => ad.Id == id));

This kicks off the view, but with one key missing ingredient – the view doesn’t have the TimeStamp field in it; because it’s mapped in EF as binary data, the MVC form generator wouldn’t provide a field for it. To make sure we pass the version of the data along with the data itself, we have to add a field to our HTML form and, because we don’t want the user to see it, let alone edit it, we need to make it hidden:

<% using (Html.BeginForm()) {%>
<%: Html.HiddenFor(model => model.TimeStamp) %>
<% } %>

The Html.HiddenFor is an MVC helper that produces HTML that looks like so:

<input id="TimeStamp" name="TimeStamp" type="hidden" value="AAAAAAAAB9E=" />

Now, when we press the Save button, the SQL we saw earlier is invoked to use the ad’s unique ID as well as the version (our timestamp column). If there’s a concurrency problem, i.e. somebody else has updated the underlying row in the database since we cached our values on the HTML form, we get an exception:


The message is saying that no rows were updated, which happens when the timestamp of the underlying row no longer matches. To provide a more helpful message, you’ll want to catch the specific error yourself:

// POST: /Ad/Edit/
public ActionResult Edit(Ad ad) {
  try {
    if (!ModelState.IsValid) { return View(); }
    // Attach the ad to the context and let the context know it's updated
    db.ObjectStateManager.ChangeObjectState(ad, EntityState.Modified);
    return RedirectToAction("Index");
  catch (OptimisticConcurrencyException ex) {
    ModelState.AddModelError("", "Oops! Looks like somebody beat you to it!");
    return View(ad);

Here we’re catching the OptimisticConcurrencyException and setting our own message before sending the user back to their data for them to grab what they want and try again.

Where Are We?

EF works great with MVC, but in the case of optimistic concurrency, you’ve got to work around the stateless model of the web a little to get it working just the way you like.


Using SQL Server Profiler with EF

I’m a big fan of the SQL Server profiler for figuring out what the Entity Framework (EF) is really doing on the wire. If you’re unfamiliar with how to use the profiler, the easiest thing to do once you’ve got it started is File | New Trace. It will ask to which database you’d like to connection and then pop-up the Trace Properties window. If you click on the Events Selection tab, you can filter the events you see. For tracing EF, it doesn’t matter what type of events we see, but it does matter from whom they come. To see EF calls (or any ADO.NET calls) against your database, the easiest thing to do is to press the Column Filters button and set the ApplicationName to be like “.NET SqlClient Data Provider”:


When you press the Run button, you’ll see a rolling list of calls made to that instance of SQL Server from EF. Now when you run an EF program, you’ll see exactly what SQL that EF is generating for SQL Server:


This is a handy technique to see whether EF batches SQL statements when you call SaveChanges (EF4 doesn’t batch) or how many round-trips lazy loading will cost you (lots – prefer the Include method).


Be Careful with Data Services Authentication + Batch Mode

I was doing something quite innocent the other day: I was trying to provide authentication on top of the .NET 4.0 WCF Data Services (DS) on a per method basis, e.g. let folks read all they want but stop them from writing unless they’re an authorized user. In the absence of an authorized user, I threw a DataServicesException with a 401 and the right header set to stop execution of my server-side method and communicate to the client that it should ask for a login.

In addition, on the DS client, also written in .NET 4.0, I was attempting to use batch mode to reduce the number of round trips between the client and the server.

Once I’d cleared away the other bugs in my program, it was these three things in combination that caused the trouble.

The Problem: DataServicesException + HTTP 401 + SaveChanges(Batch)

Reproducing the problem starts by turning off forms authentication in the web.config of a plain vanilla ASP.NET MVC 2 project in Visual Studio 2010, as we’re going to be building our own Basic authentication:


Next, bring in the Categories table from Northwind into a ADO.NET Entity Data Model:


The model itself doesn’t matter – we just need something to allow read-write. Now, to expose the model, add a WCF Data Service called “NorthwindService” and expose the NorthwindEntities we get from the EDMX:

public class NorthwindService : DataService<NorthwindEntities> {

  public static void InitializeService(DataServiceConfiguration config) {
    config.SetEntitySetAccessRule("Categories", EntitySetRights.All);
    config.DataServiceBehavior.MaxProtocolVersion =

Notice that we’re allowing complete read/write access to categories on our service, but what we really want is to let everyone read and only allow authenticated users to write. We can do that with a change interceptor:

public void OnChangeCategory(Category category, UpdateOperations operation) {
  // Authenticate
  string[] userpw = GetCurrentUserPassword();
  if (userpw == null ||
    !userpw[0].Equals("admin", StringComparison.CurrentCultureIgnoreCase) ||
    !userpw[1].Equals("pw")) {

AddHeader("WWW-Authenticate", "Basic realm=\"Northwind\""); throw new DataServiceException(401, "Unauthorized");
} } // Use HTTP Basic authentication string[] GetCurrentUserPassword() { string authorization = HttpContext.Current.Request.Headers["Authorization"]; if (string.IsNullOrEmpty(authorization)) { return null; } if (!authorization.StartsWith("Basic")) { return null; } byte[] base64 = Convert.FromBase64String(authorization.Substring(6)); string[] userpw = Encoding.ASCII.GetString(base64).Split(':'); if (userpw.Length != 2) { return null; } return userpw; }

The change interceptor checks whether the client program provided a standard HTTP Basic authentication header and, if so, pulls out the admin user name/password pair. If it isn’t found, we set the “WWW-Authenticate” header and throw a DataServicesException, which will turn into an HTTP error response, letting the client know “I need some credentials, please.”

The code itself is very simplistic and if you want better code, I recommend Alex James’s most excellent blog series on Data Services and Authentication. However, it’s good enough to return a 401 Authorized HTTP error back to the client. If it’s the browser, it’ll prompt the user like so:


The browser isn’t a very interesting program, however, which is why I added a service reference for my new service to my plain vanilla console application and wrote this little program:

class Program {
  static void Main(string[] args) {
    var service =
new NorthwindEntities(new Uri(@"http://localhost:14738/NorthwindService.svc"));

service.Credentials = new NetworkCredential("admin", "pw");
var category = new Category() { CategoryName = "My Category" }; service.AddToCategories(category); //service.SaveChanges(); // works service.SaveChanges(SaveChangesOptions.Batch); // #fail

Console.WriteLine(category.CategoryID); } }

Here we’re setting up the credentials for when the service asks, adding a new Category and calling SaveChanges. And this is where the trouble started. Actually, this is where the trouble ended after three days of banging my head and 4 hours with the WCF Data Services team (thanks Alex, Pablo and Phani!). Anyway, we’ve got three things interacting here:

  1. The batch mode SaveChanges on the DS client which bundles your changes into a send OData round-trip for efficiency. You should use this when you can.
  2. The DataServicesException which bundles extra information about your server-side troubles into the payload of the response so that a knowledgeable client, like the .NET DS client, can pull it out for you. You should use this when you can.
  3. The HTTP authentication scheme which doesn’t fail when it doesn’t get the authentication it needs, but rather asks for the client to provide it. You should use this when you can.

Unfortunately, as of .NET 4.0 SP0, you can’t use all of these together.

What happens is that non-batch mode works just fine when our server sends back a 401 asking for login credentials, pulling the credentials out of the server reference’s Credentials property. And so does batch mode.

However, where batch mode falls down is with the extra payload data that the DataServicesExpection packs into the HTTP error resposne, which confuses it enough so that the exception isn’t handled as a request for credentials, but rather reflected back up to the client code. It’s the interaction between all three of these that causes the problem, which means that until there’s a fix in your version of .NET, you need a work-around. Luckily, you’ve got three to choose from.

Work-Around #1: Don’t Use DataServiceException

If you like, you can turn off the extra information your service endpoint is providing with the DataServiceException and just set the HTTP status, e.g.

HttpContext.Current.Response.AddHeader("WWW-Authenticate", "Basic realm=\"Northwind\"");
//throw new DataServiceException(401, "Unauthorized");
HttpContext.Current.Response.StatusCode = 401;
HttpContext.Current.Response.StatusDescription = "Unauthorized";

This fix only doesn’t work with Cassini, but Cassini doesn’t work well in the face of HTTP authentication anyway, so moving to IIS7 should be one of the first things you do when facing an authentication problem.

Personally, I don’t like this work-around as it puts the onus on the service to fix a client problem and it throws away all kinds of useful information the service can provide when you’re trying to test it.

Work-Around #2: Don’t Use Batch-Mode

If you use “SaveChanges(SaveChangesOptions.None)” or “SaveChanges()” (None is the default), then you won’t be running into the batch-mode problem. I don’t like this answer, however, since batch-mode can significantly reduce network round-trips and therefore not using it decreases performance.

Work-Around #3: Pre-Populate the Authentication Header

Instead of doing the “call an endpoint,” “oops I need credentials,” “here you go” dance, if you know you’re going to need credentials (which I argue is most often the case when you’re writing OData clients), why not provide the credentials when you make the call?

var service =
new NorthwindEntities(new Uri(@http://localhost/BatchModeBug/NorthwindService.svc));

service.SendingRequest += delegate(object sender, SendingRequestEventArgs e) { var userpw = "admin" + ":" + "pw"; var base64 = Convert.ToBase64String(Encoding.ASCII.GetBytes(userpw)); e.Request.Headers.Add("Authorization", "Basic " + base64); };

Notice that we’re watching for the SendingRequest event on the client-side so that we can pre-populate the HTTP Authentication header so the service endpoint doesn’t have to even ask. Not only does this work around the problem but it reduces round-trips, which is a good idea even if/when batch-mode is fixed to respond properly to HTTP 401 errors.

1 comment

Enabling the Tip Calculator in Your Brain

I can’t imagine anyone reading this blog needs to read this, but I can’t help myself.

When I was just a wee lad, probably the most valuable thing I learned was how to perform mathematical estimation, the importance of which and several techniques you can get by reading Jon Bentley’s The Back of the Envelope (this essay along with several others, are collected in his most excellent books Programming Pearls and More Programming Pearls, both of which are still relevant a decade later). Not only is estimation generally quicker than running a calculator, but even when you do run a calculator, it helps you figure out when you did it wrong, the latter of which has saved my bacon time and again.

For example, as much as I love the Windows Phone 7 marketplace and it’s quality and quantity of applications, the ones that puzzle me are the “tip calculator” apps (several!). I don’t understand why it’s worth the trouble of pulling out your phone and punching buttons when you can know the tip instantly.

For example, let’s assume the dinner bill is $37.42. If the service was bad, that’s a 10% tip (you have to tip them something ‘cuz the IRS assumes you will and taxes them accordingly – bastards). So, with a 10% tip, take the bill and move it right one decimal point: $3.74. Now, round up or down depending on how bad the service was, e.g. $3.50 or $4. Quick and easy.

Assuming the service was great, that’s a 20% tip, so double the bill and move it right one decimal point, making the math easier for yourself, e.g. $37.42 is close to $35, doubling is $70, so a $7 tip. Boom: 20% tip.

If you want to get fancy and provide a 15% tip for good but not great, then average the two numbers: ($4 + $7)/2 = $5.50. Zim zam zoom.

Honestly, as great as the apps are on your phone, tablet or BlueTooth headset (seriously), think about using the apps in your head first. Now only are they quicker and cheaper, but using them staves off dementia (which is a good thing!).

Oh, and if the tip is added as a mandatory minimum, then the additional tip is easy: $0.00. I don’t deal well with authority.


Windows Phone 7: Beating Expectations

Years ago, when I was on my T-Mobile Dash, I would purchase a new phone every quarter or so, just to see if something better had come along. Always, within a week or so, I returned it and went happily back to my T-Mobile Dash. Then came the iPhone, which I instantly fell in love with. I didn’t think I’d ever give it up. Then came the Samsung Focus, one of the first Windows Phone 7 phones and I haven’t turned my iPhone 4 back on since. It’s not all I’d hoped for, but it’s damn close!

Meeting Expectations

Let’s check my list and see how WP7 did:

Stuff I Forgot To Ask For

I believe that the universe gives you what you ask for and in this case, even if I didn’t get everything, there was even some stuff I forgot to request:

Beating Expectations

Seriously, ever day is something new and cool on this phone. I continue to get blown away by features I never thought I’d want that have really changed how I use my phone:

Where Are We?

According to my math, I got a little more half what I asked for, but true love can’t be measured in percentages. Of the features that I’m missing, only camera quality, copy-paste and Kindle are things I actually miss from my iPhone 4, and two of those are supposed to be fixed in software RSN.

On the other hand, my Samsung Focus has giving me more than a dozen things I never thought to ask fore and really use. The full calendar support, contact linking, voice dialing (with great Bluetooth support), voice searching, the auto-correct on the keyboard, the location and phone number recognition and OneNote sync’ing make this phone a delight to use every day.


If you want something from eBay, don’t bid on it!

I’m fond of quoting my father to my sons. I have a terrible memory for these kinds of things in general, but what he says sticks with me:

I’ve learned a ton of things from my father and continue to do so, so when I wanted to win something on eBay as a Christmas present for my girlfriend/fiancé’ (what’s it called when you’re engaged to be engaged?), I knew he had the experience, so I tapped it. And here’s what he told me:

If you really want something on eBay, don’t bid on it; that only gives your competition information on how to outbid you.

Instead, set yourself some free time when the auction is going to happen and start up two browser window at the following pages:

  1. The page where the count-down timer is shown.
  2. The page where you have already entered your top bid and are poised at the Confirm Bid button.

The idea is that people’s “top bid” changes over time as the auction goes on. I know this happens to me:

“Oh, this is only worth $20 to me. Well, maybe $25. OK, $40, but that’s all. Dammit I gotta have it! Where’s the button to enter the Social Security number of my first born!?”

So, instead of putting in your top bid and walking away, which lets other folks probe your top bid with their top bid and deciding later that their top bid goes toppier, wait ‘til the last minute to put in your bid. I believe the practice is called “sniping” and there are even apps that do it, although so far, I’ve found IE and a cool hand sufficient.

Of course, the most important question is this:

“Dad, at what time in the countdown do I press the Confirm Bid button?”

“Oh, well, I do it at 4 seconds, but my computers are slow.”

What can I say; the man’s a pro.


Fluent-Style Programming in JavaScript

I’ve been playing around with JavaScript a great deal lately and trying to find my way. I last programmed JS seriously about 10 years ago and it’s amazing to me how much the world has changed since then. For example, the fifth edition of ECMAScript (ES5) has recently been approved for standardization and it’s already widely implemented in modern browsers, including my favorite browser, IE9.

Fluent LINQ

However, I’m a big C# fan, especially the fluent API style of LINQ methods like Where, Select, OrderBy, etc. As an example, assume the following C# class:

class Person {
  public Person() { Children = new List<Person>(); }
  public string Name { get; set; }
  public DateTime Birthday { get; set; }
  public int Age { get { return (int)((DateTime.Now - Birthday).Days / 365.25); } }
  public ICollection<Person> Children { get; private set; }
  public override string ToString() { return string.Format("{0} ({1})", Name, Age); }
Creating a set of them is a matter of using the C# member initialization syntax:
var chris = new Person() {
  Name = "Chris",
  Birthday = new DateTime(1969, 6, 2),
  Children = {
    new Person() {
      Name = "John",
      Birthday = new DateTime(1994, 5, 5),
    new Person() {
      Name = "Tom",
      Birthday = new DateTime(1995, 8, 30),
Without even realizing it, I used the word “set” to describe how I think of this collection of people. That’s how the LINQ methods encourage me to think and I like it – I’m working on sets of things, whether I’m filtering them, projecting them, ordering them, aggregating them in some way, etc. For example, here’s how I would print all of the items in my set:
var people = new Person[] { chris }.Union(chris.Children);
Console.WriteLine("People: " +
  people.Aggregate("", (s, p) => s + (s.Length == 0 ? "" : ", ") + p.ToString()));
Console.WriteLine("Teens: " +
  people.Where(p => p.Age > 12 && p.Age < 20).
    Aggregate("", (s, p) => s + (s.Length == 0 ? "" : ", ") + p.ToString()));
Here I’m using the Union method to combine all three elements into a new set, the Where method to filter one set to produce another and the Aggregate method to produce a single string. The output should be unsurprising:
People: Chris (41), John (16), Tom (15)
Teens: John (16), Tom (15)

Fluent JavaScript

LINQ is lousy with such set-oriented functions, e.g. Select, OrderBy, Take, Skip, First, Single, etc. and I use them a ton in my code (and my scripts). As it turns out, JS has these methods, too, both provided by popular JS toolkits like jQuery and in the language itself. Specifically, ES5 has a number of lovely methods for fluent programming. For example, given the same “class” in JS:
// Person constructor
function Person(args) {
  if ( { =; }
  if (args.birthday) { this.birthday = args.birthday; }
  if (args.children) { this.children = args.children; }

// Person properties and methods
Person.prototype = Object.create(null, {
  name: { value: "", writable: true },
  birthday: { value: new Date(), writable: true },
  age: { get: function () { return Math.floor((new Date() - this.birthday) / 31557600000); } },
  children: { value: [], writable: true },
  toString: { value: function () { return + " (" + this.age + ")"; } }

I can do several LINQ-style things on it:

var s = "";
var tom = new Person({ name: "tom", birthday: new Date(1995, 7, 30) });
var john = new Person({ name: "john", birthday: new Date(1994, 4, 5) });
var chris = new Person({ name: "chris", birthday: new Date(1969, 5, 2), children: [tom, john] });
var people = [tom, john, chris];

// select
s += "<h1>people</h1>" + (p) { return p; }).join(", ");

// where
s += "<h1>teenagers</h1>" + people.filter(function (p) { return p.age > 12 && p.age < 20 }).join(", ");

// any
s += "<h1>any person over the hill?</h1>" + people.some(function (p) { return p.age > 40; });

// aggregate
s += "<h1>totalAge</h1>" + people.reduce(function (totalAge, p) { return totalAge += p.age; }, 0);

// take
s += "<h1>take 2</h1>" + people.slice(0, 2).join(", ");

// skip
s += "<h1>skip 2</h1>" + people.slice(2).join(", ");

// sort
s += "<h1>sorted by name</h1>" + people.slice(0).sort(
  function (lhs, rhs) { return; }).join(", ");

// dump
document.getElementById("output").innerHTML = s;

Notice that several things are similar between JS and C# LINQ-style:

The output looks as you’d expect:


We’re not all there, however. For example, the semantics of the LINQ First method are to stop looking once a match is found. Those semantics are not available in the JS filter method, which checks every element, or the JS some method, which stops once the first matching element is found, but returns a Boolean, not the matching element. Likewise, the semantics for Union and Single are also not available as well as several others that I haven’t tracked down. In fact, there are several JS toolkits available on the internet to provide the entire set of LINQ methods for JS programmers, but I don’t want to duplicate my C# environment, just the set-like thinking that I consider language-agnostic.

So, in the spirit of JS, I added methods to the build in types, like the Array type where all of the set-based intrinsics are available, to add the missing functionality:

Object.defineProperty(Array.prototype, "union", { value: function (rhs) {
  var rg = this.slice(0);
  rhs.forEach(function (v) { rg.unshift(v); })
  return rg;

Object.defineProperty(Array.prototype, "first", { value: function (callback) {
  for (var i = 0, length = this.length; i < length; ++i) {
    var value = this[i];
    if (callback(value)) { return value; }
  return null;

Object.defineProperty(Array.prototype, "single", { value: function (callback) {
  var result = null;
  this.forEach(function (v) {
    if (callback(v)) {
      if (result != null) { throw "more than one result"; }
      result = v;
  return result;

These aren’t perfectly inline with all of the semantics of the built-in methods, but they give you a flavor of how you can extend the prototype, which ends up feeling like adding extension methods in C#.

The reason to add methods to the Array prototype is that it makes it easier to continue to chain calls together in the fluent style that started all this experimentation, e.g.

// union
s += "<h1>chris's family</h1>" +
[chris].union(chris.children).map(function (p) { return p; }).join(", ");

Where Are We?

If you’re a JS programmer, it may be that you appreciate using it like a scripting language and so none of this “set-based” nonsense is important to you. That’s OK. JS is for everyone.

If you’re a C# programmer, you might dismiss JS as a “toy” language and turn your nose up at it. This would be a mistake. JS has a combination of ease-of-use for the non-programmer-programmer and raw power for the programmer-programmer that makes it worth taking seriously. Plus, with it’s popularity on the web, it’s hard to ignore.

If you’re a functional programmer, you look at all this set-based programming and say, “Duh. What took you so long?”

Me, I’m just happy I can program the way I like to in my new home on the web. : )


Using LINQPad to Run My Life: Budgeting

I use LINQPad all the time for a bunch of stuff, but most recently and most relevant, I’ve been using it for a personal chore that isn’t developer-related: I’ve been using it to do budgeting.

What is LINQPad?

LINQPad is an interactive execution environment for LINQ queries, statements or programs. The typical usage model is that you point LINQPad at a SQL database or an OData endpoint via a dialog box and then start writing queries against the tables/collections exposed by that connection, e.g.


Here, you can see that I’ve added a connection on the left to the Northwind database, typed a query into the text box (I paid the $25 for the auto-completion module), executed the query and got the table of results below. If I want to operator over multiple results, including dumping them for inspection, I can do so by switch from C# Expression to C# Statements:


Notice the use of “Dump” to see results along the way. If I want to, I can switch to C# Program, which gives me a main and allows me to create my own types and methods, all of which can be executed dynamically.

To save queries, notice the “My Queries” tab in the lower left. I use this for things I run periodically, like the ads on my web site that are going to expire, some data cleanup I want to get back to and, the subject of today: budgeting.

Budgeting with and LINQPad

For the uninitiated, is a free online personal financial management site. At its core, it uses financial account, loan and asset information that lets it log into various financial sites and grab my data for me, e.g.,,, etc. It uses this to let me categorize transactions so that it can do budgeting for me. However, it doesn’t give me the control I want, so I write programs against this unified transaction information. Essentially, I re-categorize each transaction to my own set using a map I maintain in an Excel file, then compare the amount I spend each month against what my budget amount is, maintained in another sheet in that same Excel file. Because doesn’t provide a programmatic API (OData would be a godsend!), I pull down my transaction history as a CSV file that the web site provides for me, which I then translate to an Excel file.

Once I have these three Excel sheets, the translation history, the category map and the category budget amounts, I bring these pieces of data into my LINQPad script:

void Main() {
    var mintExcel = ExcelProvider.Create(@"D:\data\finances\2010-08-25 mint transactions.xlsx");
    var minDate = new DateTime(2010, 8, 1);
    var txs = mintExcel.GetSheet<Tx>().Where(t=>t.Date>=minDate);
    var debits = txs.Where(tx => tx.Type == "debit");

    var classExcel = ExcelProvider.Create(@"d:\data\finances\2010-08-03 mint category map.xlsx");
    var map = classExcel.GetSheet<CategoryClass>().ToList();
    var classBudget = classExcel.GetSheet<ClassBudget>().ToList();
    var unclassified = new ClassBudget() { Class = "UNCLASSIFIED" };
    var classifiedDebits = debits.
      Select(d => new { d.Date, d.Description, Amount = d.Amount, d.Category, Class = GetClass(map, d) }).
      Where(d => d.Class != null);
    // TODO: break this down by month
    // TODO: sum this by ytd
    var classifiedTotals =
        from d in classifiedDebits
        group d by d.Class into g
        let b = classBudget.FirstOrDefault(b=>b.Class == g.Key) ?? unclassified
        let total = g.Sum(d=>d.Amount)
        select new {
            Class = b.Class,
            BudgetAmount = b.Amount,
            ActualAmount = total,
            AmountLeft = b.Amount - total,
            TxCount = g.Count(),
            Transactions = from tx in g.OrderBy(tx=>tx.Date)
                select new { Date = tx.Date.ToString("M/d/yy"), tx.Description, tx.Category, tx.Amount }

static string GetClass(List<CategoryClass> map, Tx tx) {
  CategoryClass cc = map.FirstOrDefault(m => m.Category == tx.Category);
  if( cc != null ) { return cc.Class; }
  return null;

[ExcelSheet(Name = "transactions(1)")]
public class Tx {
    [ExcelColumn()] public DateTime Date { get; set; }
    [ExcelColumn()] public string Description { get; set; }
    [ExcelColumn()] public decimal Amount { get; set; }
    [ExcelColumn(Name = "Transaction Type")] public string Type { get; set; }
    [ExcelColumn()] public string Category { get; set; }
    [ExcelColumn(Name = "Account Name")] public string AccountName { get; set; }

[ExcelSheet(Name = "Sheet1")]
public class CategoryClass {
    [ExcelColumn()] public string Category { get; set; }
    [ExcelColumn(Name="Classification")] public string Class { get; set; }

[ExcelSheet(Name = "Sheet2")]
public class ClassBudget {
    [ExcelColumn(Name="Classification")] public string Class { get; set; }
    [ExcelColumn()] public decimal Amount { get; set; }
    public int Transactions { get; set; }

There are some interesting things to notice about this script:

LINQPad Output

By default, the output from my budgeting program looks like this (w/ my personal financial details blacked out):


Some things to notice:


Bringing in Excel

To bring my Excel data into LINQPad, which supports LINQ to SQL, EF and OData natively but not Excel, I have to right-click on the design surface, choose query properties and tell it about where the source code and namespace is that defines the Excel LINQ Query Provider:




The thing that makes this app really work for me is the REPL nature. It’s very immediate and I can see where my money is going with very little ceremony. It’s really the intelligence of the Dump command that keeps me from moving this app to WPF. Dump gives me the view I need to understand where my money goes and it gives me the programming surface to slice/dice the data the way I want to. I have no control out of the box in WPF that’s even close to as useful.

However, Even though I could extend LINQPad myself, there is no integrated support for Excel or CSV files. Further, for some stupid reason, I have to load the files into a running instance of Excel for them to load in LINQPad, which pisses me off because the error messages are ridiculous. Also, there is no intrinsic support for multiple data sources; instead I need to build that myself.

Further, I had one case where I couldn’t figure out an error (it was that I forgot to load the data into Excel) and had to have a real debugger, which LINQPad didn’t have. The good news was that I was able to copy/paste my code into a console application and debug it, but the bad news was that I really missed the Dump command when I was running inside Visual Studio.

Where Are We?

I really love LINQPad. In fact, I find myself wanting the same functionality for other uses, e.g. SQL (real SQL), JavaScript and as a shell. It’s the interactive data access that makes it for me – munge some data, look at it, repeat. It doesn’t quite do everything I want, though – where’s the full-featured, all-data, Swiss army knife for data?


Management vs. Motivation

“If you want to build a ship, don’t drum up people to gather wood, divide the work, and give them orders. Instead, teach them to yearn for the vast and endless sea."

Antoine De Saint-Exupery, author of "The Little Prince"


LINQ Has Changed Me

In the old days, the post-colonial, pre-LINQ days of yore, I’d have written a one-way MD5 encryption like so:

static string GetMD5String(string s) {
  MD5 md5 = new MD5CryptoServiceProvider();
  byte[] hash = md5.ComputeHash(Encoding.ASCII.GetBytes(s));
  StringBuilder sb = new StringBuilder();
  foreach( byte b in hash ) sb.AppendFormat("{0:x2}", b);
  return sb.ToString();

This implementation is fine and has served me well for 10 years (I pulled it out of the first .NET project I ever really did). However, after using LINQ for so long, it’s hard not to see every problem as an operation over sets:

static string GetMD5String(string s) { 
  return (new MD5CryptoServiceProvider()). 
    Aggregate(new StringBuilder(), (working, b) => working.AppendFormat("{0:x2}", b)). 

I can’t say that the LINQ version is any better, but it felt better. However, you’ll notice that I’m not using any of the LINQ keywords, e.g. “select”, “where”, etc. I find that I don’t really use them that much. It’s too jarring to mix them, e.g. “(from f in foos select f).Take(3)”, since not everything has a LINQ keyword equivalent. I tend to do “LINQ-less LINQ” more often then not.

P.S. I assume someone will be able to tell me how I can do it better. : )

P.P.S. I’m using the Insert Code for Windows Live Writer add-in. I love WLW!


A Function That Forces

Far Side - Midvale School for the GiftedAt Microsoft, there’s this passive-aggressive cultural thing called a “forcing function,” which, to put it crudely, is an engineering way for us to control the behavior of others. The idea is that you set up something to happen, like a meeting or an event, that will “force” a person or group to do something that you want them to do.

For example, if someone won’t answer your email, you can set up a meeting on their calendar. Since Microsoft is a meeting-oriented culture (even though we all hate them), a ‘softie will be very reticent to decline your meeting request. So, they have a choice – they can attend your meeting so that they can answer your question in person or they can answer your email and get that time back in their lives. This kind of forcing function can take larger forms as well. I can’t say that our execs make the decision like this (since they don’t talk to me : ), but it is the case that signing up a large number of Microsoft employees to host and speak at important industry events does have the effect of making us get together to ensure that our technologies and our descriptions of those technologies holds together (well, holds together better than they would otherwise : ).

Unfortunately, this way of thinking has become so much a part of me that I’ve started to use it on my family (which they very much do not like). Worse, I use it on myself.

For example, I have been holding back on half a dozen or more blog posts until I have the software set up on my newly minted web site to handle blog posts in a modern way, namely via Windows Live Writer. In other words, I was using the pressure inherent in the build up of blogging topics to motivate me to build the support I wanted into to have a secure blogging endpoint for WLW. Before I moved all my content into a database, I could just pull up FrontPage/Expression Web and type into static HTML. Now that everything is data-driven, however, the content for my posts are just rows in a database. As much as I love SQL Server Management Studio, it doesn’t yet have HTML editing support that I consider adequate. Further, getting images into my database was very definitely a programming task not handled by existing tools that I was familiar with.

So, this is the first post using my new WLW support and I’m damn proud of it. It was work that I did with Kent Sharkey, a close friend of mine that most resembles Eeyore in temperament and facial expressions, and that just made it all the more fun!

Anyway, I’m happy with the results of my forcing function and I’ll post the code and all the details ASAP, but I just wanted to apologize for my relative silence on this blog and that things should get better RSN. XXOO.

P.S. I’m loving Windows Live Writer 11!


Data at PDC 2010

There are lots of great data talks at PDC 2010, all of which are available for online viewing:



Time to check the donuts

One day when I was supposed to be writing, I needed something to do (as often happens). In this particular case, I built a little tray icon app using the new (at the time) tray icon support in Windows Forms (this was a while ago : ). The data I was checking was my gmail account and whenever there was new mail, I'd pop up a notification. All very simple, so to be funny, instead of saying "You've got mail,"� my program said "I's time to check the donuts."

Over time, I came to rely on this app but lamented the lack of features, like seeing who the email was from or marking an email as read w/o logging in, etc. Over time, I came to wish I had something like Gmail Notifier. I's free and while it doesn't contain an '80s commercial reference, it has way more features than I ever built into mine. Oh, and the noise it makes when you get an email is priceless. Recommended.

1 comment

The Downside Of Working At Home

I've been working at home off and (mostly) on for 16 years...

From Recommended!


Why can't it all just be messages?

My mobile device is driving me crazy.

I have an iPhone 4.0. Normally when it's driving me crazy, it's standard stuff like the battery life sucks or that the iOS 4.0.1 update didn't fix the proximity detection or stop emails I send via Exchange from just disappearing into the ether.

This time, it's something else and I can't blame the iPhone; I think all modern smart phones have the same problem. The problem is that I constantly have to switch between apps to see my messages. Here are screenshots for 5 of the messaging clients I use reguarly:

Voicemail Exchange Email SMS/MMS Facebook Twitter

This list doesn't include real-time messages like IM, or notifications like Twitter or RSS. I'm just talking about plain ol' async messaging. We used to think of it as "email," but really voicemail, email, SMS, MMS, Facebook messages and Twitter Direct Messages are all the same -- they are meant to queue up until you get to them.

Now, some folks would argue that SMS/MMS aren't meant to be queued; they're meant to be seen and handled immediately. Personally, I find it annoying that there is a pop-up for every single text or media messages I get on my phone and there seems to be no way to turn that off. On the other hand, if I want that to happen for other types of messages, e.g. voicemail, I can find no way to turn it on even if I want to. Why are text messages special, especially since most mobile clients let you get over the 160 character limit and will just send them one after the other for you anyway?

iOS 4 takes a step in the right direction with the "universal" inbox:

iOS4 "universal" inbox

Here I've got a great UI for getting to all my email messages at once, but why can't it handle all my messages instead?

super-universal inbox

Not only would this put all my messages in one place at one time, but it would unify up the UI and preferences across the various messaging sources. Do you want your text messages to quietly queue up like email? Done. Do you want your voicemail to pop to the front like an SMS? Done. Do you want the same swipe-to-delete gestures on your voicemail as you have with your email? Done!

Maybe someone with some experience on the new Windows Phone 7 can tell me that there is a "messaging" hub that manages all this for me. Since they're already doing things like bringing facebook pictures into the "pictures" hub (or whatever they call it), that doesn't seem completely out of the realm of possibility. If that's the case, I'll say again what I've been saying for a while -- I can't wait for my Windows Phone 7!


David Ramel Asks About Interviewing at Microsoft

David Ramel from is writing an article that includes the Microsoft interviewing process and he send me some questions:

[David] How would you succinctly sum up the Microsoft interview process as compared to those of other tech companies?

[Chris] MS does some things similarly to other high-tech companies I've worked with, e.g. having each interviewer focus on an aspect or aspects, e.g. team skills, people skills, technical skills, etc., expecting a candidate to ask questions, communicating between interviewers to push more on one area or another, etc. The riddle questions are a uniqueness at Microsoft (at least they were when I last interviewed), but theyire pretty rare these days. Coding on the whiteboard also seems pretty unique to Microsoft (myself, I prefer the keyboard : ).

[David] How has the Microsoft interview process changed over time? (Microsoft seems to have shaken up the tech interview process some years ago with those brain-teasing puzzle� questions, but now seem to be much more technically-oriented and job-specific. Just wondering about your thoughts on this observation.)

[Chris] While I have had them, puzzle questions were rare even when I was interviewed 7 years ago. Since then, I haven't run into many people that use them. However, when they are used, an interviewer is often looking for how a candidate works through an issue as much as the solution that they come up with. In an ever changing world, being able to learn and adapt quickly is a huge part of how successfully you can be in the tech industry at all and at Microsoft specifically. I prefer technical design questions for these kinds of results, however, and it seems that most 'softies agree.

[David] What would you say was the biggest factor in your being offered a job at Microsoft?

[Chris] I had a reputation outside of MS before I interviewed, but that almost didn't matter. If I hadn't done well during the interview, I would not have been offered the job. When in doubt, a team generally prefers to turn away a good candidate rather than to risk taking on a bad one, so if there's anything wrong, team fit, technical ability, role fit, etc., a candidate won't get an offer.

[David] What's the single most important piece of advice you can offer to those preparing for a Microsoft job interview?

[Chris] You asked for just one, but I'm going to give you two anyway. : )

  1. If you need more information to answer a question, ask for it. Thatis how the real-world works and many questions are intentionally vague to simulate just this kind of interaction.
  2. Try to answer non-technical questions based on your personal experience, e.g. instead of saying "here's how I would deal with that situation,"� say "I had a similar situation in my past and hereis how I dealt with it."� This is a style of interviewing known as behavioral� and even if your interviewer doesn't phrase his questions in that way, e.g. "give me an example of how you dealt with a situation like blah,"� it's helpful and impressive if you can use your own history to pull out a positive result.

[David] Could you please share any other observations you have on the Microsoft interview process that may not be covered in your site or the Jobsblog?

[Chris] I run a little section of my web site dedicated to the MS interviewing process and the thing I will tell you is this: don't prepare. Be yourself. If you're not a fit for MS, no amount of preparation in the days before an interview will help and if you are a fit, that will come through in the interview. Also, make sure you ask questions. Working at Microsoft isn't just a job, it's a way of life, so make sure you're sure you want the team and the job for which you're interviewing.

[David] Does MS provide training for interviewers? If so, what do they stress most?

[Chris] I'm sure MS does provide training for interviewing, but Iive never been to it. At Intel, I learned the behavioral interviewing technique, which Iive used in every interview since, both as an interviewer and as a job candidate.

[David] Do you have standard questions, or do you tailor them to the situation? If the latter, is it usually tailored for team fit, to a specific open position, particular skills, etc.?

[Chris] I have once standard technique question and a few standard behavioral interview questions. The technical question is to ask them what their favorite technology is and/or what they consider themselves to be an expert� in and then drill in on their understanding. If they can answer my questions deeply, this shows passion about technology and the ability to learn something well, both of which are crucial for success at MS.

My behavioral interviewing questions are things like "Tell me about a time when youive been in conflict with a peer. How did you resolve it? What was the result? What did you learn?"� and "Tell me about a time when you had much too much work to do in the time you were given. How do you resolve that issue? What was the result? What did you learn?"� The core idea of behavioral interviewing is that past behavior indicates future behavior, so instead of asking people things like "How would you deal with such-and-such?�" you ask them "How did you dealt with such-and-such in the past?"� This forces them to find a matching scenario and you get to see if they way they dealt with the issue in real life matches what you want from a team mate in that job.

[David] How would you describe the kinds of coding questions you ask? A couple of real examples would be perfect!

[Chris] I don't often ask coding questions, but when I have, I let them use a keyboard. I hate coding on the board myself as it's not representative of how people actually code, so I don't find it to be a good indicator of what people will actually do. I guess I even use behavioral techniques for technical questions, now that I think about it. : )


Spurious MachineToApplication Error With VS2010 Deployment

Often when I'm building my MVC 2 application using Visual Studio 2010, I get the following error:

It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. This error can be caused by a virtual directory not being configured as an application in IIS.

On the internet, this error seems to be related to having a nested web.config in your application. I do have such a thing, but it's just the one that came out of the MVC 2 project item template and I haven't touched it.

In my case, this error in my case doesn't seem to have anything to do with a nested web.config. This error only started to happen when I began using the web site deployment features in VS2010 which by itself, rocks (see Scott Hanselman's "Web Deployment Made Awesome: If You're Using XCopy, You're Doing It Wrong" for details).

 If it happens to you and it doesn't seem to make any sense, you can try to fix it with a Build Clean command. If you're using to previous versions of Visual Studio, you'll be surprised, like I was, not to find a Clean option in sparse the Build menu. Instead, you can only get to it by right-clicking on your project in the Solution Explorer and choosing Clean.

Doing that, however, seems to make the error go away. I don't think that's a problem with my app; I think that's a problem with VS2010.


a whole new

The new implementation has been a while in the making. In fact, I've had the final art in my hands since August of 2005. I've tried several times to sit down and rebuild my 15-year-old completely from scratch using the latest tools. This time, I had a book contract ("Programming Data," Addison-Wesley, 2010) and I needed some real-world experience with Entity Framework 4.0 and OData, so I fired up Visual Studio 2010 a coupla months ago and went to town.

The Data Modeling

The first thing I did was design my data model. I started very small with just Post and Comment. That was enough to get most of my content in. And that lead to my first principle (we all need principles):

thou shalt have no .html files served from the file system.

On my old site, I had a mix of static and dynamic content which lead to all kinds of trouble. This time, the HTML at least was going to be all dynamic. So, once I had my model defined, I had to import all of my static data into my live system. For that, I needed a tool to parse the static HTML and pull out structured data. Luckily, Phil Haack came to my rescue here.

Before he was a Microsoft employee in charge of MVC, Phil was well-known author of the SubText open source CMS project. A few years ago, in one of my aborted attempts to get my site into a reasonable state (it has evolved from a single static text file into a mish-mash of static and dynamic content over 15 years), I asked Phil to help me get my site moved over to SubText. To help me out, he built the tool that parsed my static HTML, transforming the data into the SubText database format. For this, all I had to do was transform the data from his format into mine, but before I could do that, I had to hook my schema up to a real-live datastore. I didn't want to have to take old web site down at all; I wanted to have both sites up and running at the same time. This lead to principle #2:

thou shalt keep both web sites running with the existing live set of data.

And, in fact, that's what happened. For many weeks while I was building my new web site, I was dumping static data into the live database. However, since my old web site sorting things by date, there was only one place to even see this old data being put in (the /news/archive.aspx page). Otherwise, it was all imperceptible.

To make this happen, I had to map my new data model onto my existing data. I could do this in one of two ways:

  1. I could create the new schema on my ISP-hosted SQL Server 2008 database ( rocks, btw -- highly recommended!) and move the data over.
  2. I could use my existing schema and just map it on the client-side using the wonder and beauty that was EF4.

Since I was trying to get real-world experience with our data stack, I tried to use the tools and processes that a real-world developer has and they often don't get to change the database without a real need, especially on a running system. So, I went with option #2.

And I'm so glad I did. It worked really, really well to change names and select fields I cared about or didn't care about all from the client-side without ever touching the database. Sometimes I had to make database changes and when that happened, I has careful and deliberate, making the case to my inner DB administrator, but mostly I just didn't have to.

And when I needed whole new tables of data, that lead to another principle:

build out all new tables in my development environment first.

This way, I could make sure they worked in my new environment and could refactor to my heart's content before disturbing my (inner) DB admin with request after request to change a live, running database. I used a very simple repository pattern in my MVC2 web site to hide the fact that I was actually accessing two databases, so when I switched everything to a single database, none of my view or controller code had to change. Beautiful!

Data Wants To Be Clean

And even though I was careful to keep my schema the same on the backend and map it as I wanted in my new web site via EF, not all of my old data worked in my new world. For example, I was building a web site on my local box, so anything with a hard-coded link to had to be changed. Also, I was using a set of <a name="tag" ? elements to reference specific posts in my static HTML that just didn't scale to my dynamic ID-based permalinks, so data had to be "cleaned."

To do this cleaning, I used a combination of LINQPad, SSMS and EF-based C# code to perform data cleaning tasks. This yielded two tools that I'm still using:

  1. BlogEdit: An unimagintively named general-purpose post and comment creation and editing tool. I built the first version of this long before WPF, so kept hacking on it in WinForms (whose data binding sucks compared to WPF, btw) as I needed it to have new features. Eventually I gave this tool WYSIWIG HTML editing by shelling out to Expression Web, but I need real AtomPub support on the site so I can move to Windows Live Writer for that functionality in the future.
  2. BulkChangeDatabaseTable: This was an app that I'd use to run my questions to find "dirty" data, perform regular expression replaces with and then -- and this is the best part -- show the changes in WinDiff so I could make sure I was happy with the changes before commiting them to the database. This extra eyeballing saved me from wrecking a bunch of data.

During this data cleaning, I applied one simple rule that I adopted early and always regretted when I ignored:

thou shalt throw away no data.

Even if the data didn't seem to have any use in the new world, I kept it. And it's a good thing I did, because I always, always needed it.

For example, when I ran Phil's tool to parse my static web pages, he pulled out the <a name="tag" /> tags that went with all of my static posts. I wasn't going to use them to build permalinks, why did I need them?

I'll tell you why: because I've got 2600 posts in my blog from 15 years of doing this, I cross-link to my own content all the live-long day and a bunch of those cross-links are to, you guessed it, to what used to be static data. So, I have to turn links embedded in my content of the form "/writing/#footag" into links of the form "/posts/details/452". But how do I look up the mapping between "footag" and "452"? That's right -- I actually went to my (inner) DB admin and begged him for a new column on my live database called "EntryName" where I tucked the <a name="tag" /> data as I imported the data from Phil's tool, even though I didn't know why I might need it. It was a good principle.

Forwarding Old Links

And how did I even figure out I had all those broken links? Well, I asked my good friend and web expert Kent Sharkey how to make sure my site was at least internally consist before I shipped it and he recommended Xenu Link Sleuth for the job. This lead to another principle:

thou shalt ship the new site with no broken internal links.

Which was followed closely by another principle:

thou shalt not stress over broken links to external content.

Just because I'm completely anal about making sure every link I ever pass out to the world stays valid for all eternity doesn't mean that the rest of the world is similiarly anal. That's a shame, but there's nothing I can do if little sites like decide to move things without a forwarding address. I can, however, make sure that all of my links worked internally and I used Xenu to do that. I started out with several hundred broken links and before I shipped the new site, I had zero.

Not all of that was changing old content, however. In fact, most of it wasn't. Because I wanted existing external links out in the world to find the same content in the new place, I had to make sure the old links still worked. That's not to say I was a slave to the old URL format, however. I didn't want to expose .aspx extensions. I wanted to do things the new, cool, MVC way, i.e. instead of /news/showTopic.aspx?ixTopic=452 (my old format), I wanted /posts/details/452. So, this lead to a new principle:

thou shalt built the new web site the way you want and make the old URLs work externally.

I was using MVC and I wanted to do it right. That meant laying out the "URL space" the way it made sense in the new world (and it's much nicer in general, imo). However, instead of changing my content to use this new URL schema, I used it as a representative sample of how links to my content in the real-world might be coming into my site, which gave me initial data about what URLs I needed to forward. Ongoing, I'll dig through 404 logs to find the rest and make those URLs work appropriately.

I used a few means of forwarding the old URLs:

  1. Mapping sub-folders to categories: In the old site, I physically had the files in folders that matched the sub-folders, e.g. /fun mapped to /fun/default.aspx. In the new world, /fun meant /posts/details/?category=fun. This sub-folder thing only works for the set of well-defined categories on the site (all of which are entries in the database, of course), but if you want to do sub-string search across categories on my site you can, e.g. /posts/details/?category=foo.
  2. Kept sub-folder URLs, e.g. /tinysells and /writing: I still liked these URLs, so I kept them and built controllers to handle them.
  3. Using the IIS URL Rewriter: This was the big gun. Jon Galloway, who was invaluable in this work, turned me onto it and I'm glad he did. The URL Rewriter is a small, simple add-in to IIS7 that lets you describe patterns and rules for forwarding when those patterns are matched. I have something like a dozen patterns that do the work to forward 100s of URLs that are in my own content and might be out in the world. And it works so, so well. Highly recommended.

So, with a combination of data cleaning to make my content work across both the old site and the new site under development, making some of my old URLs work because of conventions I adopted that I wanted to keep and URL rewriting, I had a simple, feature-complete, 100% data-driven re-implementation of

What's New?

Of course, I couldn't just reimplement the site without doing something new:

The Room for Improvement

As always, there's a long list of things I wish I had time to do:

Luckily, with the infrastructure I've got in place now, laying in these features over time will be easy, which was the whole point of doing this work in the first place.

Where are we?

All of this brings me back to one more principle. I call it Principle Zero:

thou shalt make everything data-driven.

I'm living the data-driven application dream here, people. When designing my data model and writing my code, I imagined that was but one instance of a class of web applications and I kept all of the content, down to my name and email address, in the database. If I found myself putting data into the code, I figured out where it belonged in the database instead.

This lead to all kinds of real-world uses of the database features of Visual Studio, including EF, OData, Database projects, SQL execution, live table-based data editing, etc. I lived the data-driven dream and it yielded a web site that's much faster and runs on much less code:

Do the math and that's 100+% of the content and functionality for 10% of the code. I knew I wanted to do it to gain experience with our end-to-end data stack story. I had no idea I would love it so much.


Entity Framework 4.0 POCO Classes and Data Services

If you've flipped on the POCO (Plain Ol' CLR Objects) code generation T4 templates for Entity Framework to enable testing or just 'cuz you like the code better, you might find that you lack the ability to expose that same model via Data Services as OData (Open Data). If you surf to the feed, you'll likely see something like this:

The XML page cannot be displayed

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

The following tags were not closed: feed. Error processing resource 'http://localhost:10749/MyODataEndpoint.svc/Posts'


There are two problems. The first problem is that we're not reporting the problem very well. You can't see what's happening in IE8 with a simple View Source, as apparently IE won't show malformed XML. Instead, you have to use Fiddler or some other tool (I'm a big tcpTrace fan) to see the actual error in the HTTP response:


<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<feed ...>
<title type="text">Posts</title>
<link rel="self" title="Posts" href="Posts" />
<m:message xml:lang="en-US">Internal Server Error. The type
is not a complex type or an entity type.</m:message>


It's in the creation of the OData feed that the error happens, so instead of clearing the response and just returning the error, we dump it into the middle of the output, making it very difficult to find. In this case, what we're telling you is that you've mistakenly left dynamic proxy creation on, which doesn't work with EF4 POCO objects and Data Services in .NET 4.0. To fix this, you need to override the CreateDataSource method in your DataService<T> derived class:

public class MyODataEndpoint : DataService<FooEntities> {
  public static void InitializeService(DataServiceConfiguration config) {

  protected override sellsbrothersEntities CreateDataSource() {
    var dataSource = new FooEntities();
    dataSource.ContextOptions.ProxyCreationEnabled = false;
    return dataSource;


This solution came from Shyam Pather, a Dev Manager on the EF team. He says that once you turn off proxy generation, you give up lazy loading and "immediate" change tracking. Instead, you'll get "snapshot" change tracking, which means the context won't be informed when the properites are changed, but the context still detects changes when you call DetectChanges() or SaveChanges(). For the internals of a Data Service, none of this matters, but any code you write in query interceptors, change interceptors, or service operations will have to be aware of this.

This limitations are only true when used from the OData endpoint, of course. The rest of your app will get proxy creation by default unless you turn it off.


Working Hard: WhirlyBall

What my team does on an average Wednesday afternoon:

It was surprisingly fun.


We're taking OData on the Road!

We have a series of free, day-long events we're doing around the world to show off the beauty and wonder that is the Open Data Protocol. In the morning we'll be showing you OData and in the afternoon we'll help you get your OData services up and running. Come one, come all!

Your speakers are going to include Doug Purdy, so book now. Spots are going to go fast!


SQL Server Modeling CTP (November 2009 Release 3) for Visual Studio 2010 RTM Now Available

Here's what Kraig has to say about the November 2010 SQL Server Model CTP that matches the RTM of Visual Studio 2010:

A update of the SQL Server Modeling CTP (November 2009) that's compatible with the official (RTM) release of Visual Studio 2010 is now available on the Microsoft Download Center.  This release is strictly an updated version of the original November 2009 CTP release to support the final release of Visual Studio 2010 and .NET Framework 4.

We highly recommend you uninstall and install in the following order.

  1. Uninstall any existing SQL Server Modeling CTP from Add and Remove Programs
  2. Uninstall Visual Studio 2010 and .NET Framework 4 Beta 2 or RC from Add and Remove Programs
  3. Install Visual Studio 2010 and .NET Framework 4
  4. Install the SQL Server Modeling November 2009 CTP Release 3.

If you are unable to uninstall SQL Server Modeling CTP from Add and Remove Programs for any reason, you can remove each component using the following command lines.  You need to run all three in order to completely remove SQL Server Modeling CTP so you can install the new CTP:

M Tools: Msiexec /x {B7EE8AF2-3DCC-4AFE-8BD2-5A6CE9E85B3A}

Quadrant: Msiexec /x {61F3728B-1A7D-4dd8-88A5-001CBB9D2CFA}

Domains: Msiexec /x {11DA75C8-10AB-4288-A1BB-B3C2593524A7}

Note: These steps will not remove the SQL Server Modeling CTP entry in Add and Remove Programs but you will be able to install the new CTP.

Thank you and enjoy Visual Studio 2010!

Kraig Brockschmidt
Program Manager, Data Developer Center


The performance implications of IEnumerable vs. IQueryable

It all started innocently enough. I was implementing a "Older Posts/Newer Posts" feature for my new web site and was writing code like this:

IEnumerable<Post> FilterByCategory(IEnumerable<Post> posts, string category) {
  if( !string.IsNullOrEmpty(category) ) {
return posts.Where(p => p.Category.Contains(category));
  var posts = FilterByCategory(db.Posts, category);
  int count = posts.Count();

The "db" was an EF object context object, but it could just as easily been a LINQ to SQL context. Once I ran this code, it failed at run-time with a null reference exception on Category. "That's strange," I thought. "Some of my categories are null, but I expect the 'like' operation in SQL to which Contains maps to skip the null values." That should've been my first clue.

Clue #2 was when I added the null check into my Where expression and found that their were far fewer results than I expected. Some experimentation revealed that the case of the category string mattered. "Hm. That's really strange," I thought. "By default, the 'like' operation doesn't care about case." Second clue unnoticed.

My 3rd and final clue was that even though my site was only showing a fraction of the values I knew where in the database, it had slowed to a crawl. By now, those of you experienced with LINQ to Entities/SQL are hollering from the audience: "Don't go into the woods alone! IEnumerable kills all the benefits of IQueryable!"

See, what I'd done was unwittingly switched from LINQ to Entities, which takes my C# expressions and translates them into SQL, and was now running LINQ to Objects, which executes my expressions directly.

"But that can't be," I thought, getting hot under the collar (I was wearing a dress shirt that day -- the girlfriend likes me to look dapper!). "To move from LINQ to Entities/SQL to LINQ to Objects, I thought I had to be explicit and use a method like ToList() or ToArray()." Au contraire mon fraire (the girlfriend also really likes France).

Here's what I expected to be happening. If I have an expression like "db.Posts" and I execute that expression by doing a foreach, I expect the SQL produced by LINQ to Entities/SQL to look like this:

select * from Posts

If I add a Where clause, I expect the SQL to be modified:

select * from Posts where Category like '%whatever%'

Further, if I do a Count on the whole thing, e.g.

db.Posts.Where(p => p.Contains(category)).Count()

I expect that to turn into the following SQL:

select count(*) from Posts where Category like '%whatever%'

And that's all true if I keep things to just "var" but I wasn't -- I was being clever and building functions to build up my queries. And because I couldn't use "var" as a function parameter, I had to pick a type. I picked the wrong one: IEnumerable.

The problem with IEnumerable is that it doesn't have enough information to support the building up of queries. Let's take a look at the extension method of Count over an IEnumerable:

public static int Count<TSource>(this IEnumerable<TSource> source) {
int num = 0;
  using (IEnumerator<TSource> enumerator = source.GetEnumerator()) {
    while (enumerator.MoveNext()) { num++; }
  return num;

See? It's not composing the source IEnumerable over which it's operating -- it's executing the enumerator and counting the results. Further, since our example IEnumerator was a Where statement, which was in turn a accessing the list of Posts from the database, the effect was filtering in the Where over objects constituted from the following SQL:

select * from Posts

How did I see that? Well, I tried hooking up the supremely useful SQL Profiler to my ISP's database that was holding the data, but I didn't have permission. Luckily, the SQL tab in LinqPad will show me what SQL is being executed and it showed me just that (or rather, the slightly more verbose and more correct SQL that LINQ to Entities generates in these circumstances).

Now, I had a problem. I didn't want to pass around IEnumerable, because clearly that's slowing things down. A lot. On the other hand, I don't want to use ObjectSet<Post> because it doesn't compose, i.e. Where doesn't return that. What is the right interface to use to compose separate expressions into a single SQL statement? As you've probably guessed by now from the title of this post, the answer is: IQueryable.

Unlike IEnumerable, IQueryable exposes the underlying expression so that it can be composed by the caller. In fact, if you look at the IQueryable implementation of the Count extension method, you'll see something very different:

public static int Count<TSource>(this IQueryable<TSource> source) {
  return source.Provider.Execute<int>(
((MethodInfo) MethodBase.GetCurrentMethod()).
new Type[] { typeof(TSource) }),
new Expression[] { source.Expression }));

This code isn't exactly intuitive, but what's happening is that we're forming an expression which is composed of whatever expression is exposed by the IQueryable we're operating over and the Count method, which we're then implementing. To get this code path to execute for our example, we simply have to replace the use of IEnumerable with IQueryable:

IQueryable<Post> FilterByCategory(IQueryable<Post> posts, string category) {
  if( !string.IsNullOrEmpty(category) ) {
    return posts.Where(p => p.Category.Contains(category));
  var posts = FilterByCategory(db.Posts, category);
  int count = posts.Count();

Notice that none of the actual code changes. However, this new code runs much faster and with the case- and null-insensitivity built into the 'like' operator in SQL instead of semantics of the Contains method in LINQ to Objects.

The way it works is that we stack one IQueryable implementation onto another, in our case Count works on the Where which works on the ObjectSet returned from the Posts property on the object context (ObjectSet itself is an IQueryable). Because each outer IQueryable is reaching into the expression exposed by the inner IQueryable, it's only the outermost one -- Count in our example -- that causes the execution (foreach would also do it, as would ToList() or ToArray()).

Using IEnumerable, I was pulling back the ~3000 posts from my blog, then filtering them on the client-side and then doing a count of that.With IQueryable, I execute the complete query on the server-side:

select count(*) from Posts where Category like '%whatever%'

And, as our felon friend Ms. Stewart would say: "that's a good thing."


College info for my sophomore

I went to a college planning sessions at my sons' high school not because I'm hung up on getting my Sophomore into a top school, but because I thought I'd get a jump on things. I learned I was actually behind.

For one, I learned that the high school has an online system that will do some amazing things:

That means that my son can answer questions about personality and interests and draw a straight line through to what he needs to do to get into a school so he can learn to do the jobs he'll like and be good at. Holy cow. We didn't have anything like that when I was a kid.

Further, the online system has two complete SAT and ACT tests in it, so, along with the PSAT that he's already taking, he can do a practice ACT, figure out which test he's best at (my 34 ACT score was way better than my 1240 SATs) and just take that test, since most schools these days take both SAT or ACT results.

This is all freely provided by the high school and, in fact, they have counseling sessions with the students at each grade level for them to get the most from this system.

It's no wonder that 93% of students from this high school go on to 4 or 2-year college degree programs.

That was the good part.

The scary part is that my eldest, half way through his Sophomore year, is essentially half-way through his high school career. Colleges only see their grades through the end of Junior year, since most college applications are due in the middle of January of their Senior year at the latest. I have to sit down with my son and have the conversation about how "even if you get a 4.0 from now on, the best grades you can have are..."

Is it just me or is the world moving faster with each passing day?


Updated the CsvFileTester for Jet 4.0

I was playing around building a tool to let me edit a database table in Excel, so I updated my CvsFileTester project to work in a modern world, including the 32-bit only Jet 4.0 driver you've probably go lying around on your HD.



2586 older posts       25 newer posts

Powered By ASP.NET

Hosted by SecureWebs