Both Phil Haack and Jon Gruber linked to this post today.

And with good reason. Its exquisite, sublime (in the OED sense of the word not the IDE), faciniating and insightful in ways that only reading it will adequately communicate.

Paul Ford’s Magnum Opus – What is Code?

It should be required reading for anyone involved in anything to do with Software.

PS – Took me 107 minutes (and two cups of tea) to read all 38000 words

esterday, I was doing so some work with EF and ran into something unusual – my navigational properties weren’t being populated.

I rechecked my model and the DB schema and eerything was normal.

So it turns out that my entity object had just been inserted earlier in the procedure. This makes sense becuase EF usually does the navigational property population when you pull an entity directly from the DB.

So – either I find a solution or re-write my method to pull down the recently inserted entity again after insertion.

It turns out there is a single line of code that will fix everything:

Entities.Entry(item).Reference(c => c.NavgationProperty).Load();

This forces EF to load the property using the defined FK – and then using that navigational property works as expected. You’ll have to do this for each property that needs to be populated.

Hope someone finds this useful.

ike any good dev, I have side projects. And like any good busy dev, I don’t get enough time to finish them properly.

So this time of the year is always productive as i try and get stuff done before returning to work.

Today I needed to translate a whole bunch of resource strings into a bunch of other languages. So I thought the Bing Translate Api would be a good fit. So off I went to Azure Marketplace to add the service to my Azure subscription.

And right away I started running into problems – its nowhere to be found.

To cut a long story short – it seems to have been superceded by the Microsoft Translator API.

Now. Signing up and adding it to my existing subscription is simplicity itself.

And when you get to the dashboard for the service, you get something that looks like this:

The proxy class is nice and compact and excellent and saves the hassle of adding a full blown service reference to your VS2013 project.

Then they give you this small code snippit to use the proxy class:

var client = new TranslatorContainer(new Uri("https://api.datamarket.azure.com/Data.ashx/Bing/MicrosoftTranslator/v1/"));
client.Credentials = new NetworkCredential("accountKey", "Insert Account Key");
var marketData = client.Translate(
"hello",
"nl",
null
).Execute();

(obviously you need to swap “Insert Account Key” with your actual account key)

One thing to note here is that this is using V1 of the API – and its just for OData (tho this doesn’t preclude you from calling it from javascript). This API is fairly basic. There is a more powerful V2 API floating about that requires a bit more elaborate authentication.

(The difference between the two versions caught me out for a while.)

Now, when I went to use the proxy class right out of the box, I got an entity mismatch error:

"There is a type mismatch between the client and the service. Type 'Microsoft.Translation' is not an entity type, but the type in the response payload represents an entity type. Please ensure that types defined on the client match the data model of the service, or update the service reference on the client."

Now if you open Fiddler and allow HTTP decryption, you’ll see that the request actually suceeds. So the error is not in the call, or the authentication (as I initially thought), but in parsing the response.

And if we look at the error text, we see that it spells out eaxtly what the issue is – ‘Microsoft.Translation’ is not an entity type

Of course, being the doofus that I occasonally am, I missed that completely at first.

Once I noticed that, it hit me – I simply need to tell the DataServiceContext that Microsoft.Translation is an entity type.

And how do you do that?

Enter the [DataServiceEntity] attribute.

So. Open up the proxy class you downloaded from the Azure dashboard page and decorate the Translation, Language and DetectedLanguage classes with [DataServiceEntity] and you’re good to go.

Your code should look like this:

[DataServiceEntity]
public partial class Translation {
    
    private String _Text;
    
    public String Text {
        get {
            return this._Text;
        }
        set {
            this._Text = value;
        }
    }
}
[DataServiceEntity]
public partial class Language {
    
    private String _Code;
    
    public String Code {
        get {
            return this._Code;
        }
        set {
            this._Code = value;
        }
    }
}

[DataServiceEntity]
public partial class DetectedLanguage {
    
    private String _Code;
    
    public String Code {
        get {
            return this._Code;
        }
        set {
            this._Code = value;
        }
    }
}

And if anyone knows anyone at Microsoft, hopefully the proxy class file can be updated.

Entity Framework is great. I’ve used it since its’ first public beta. And with each release, its gets better and better.

Code First is awesome. Being able to generate a database from pure code was great. Then they added a hybird version of this – you could generate Code First from an Existing Database. I remember screaming because I could have used that very feature the week before it got released – would have saved me a weeks work. 🙂

I recently wrote an program to bulk insert test data into a blank version of a production database.

The program used EF and a whole bunch of logic to check and verify data integrity before inserting data into the database.

To start with I used AddRange(). The AddRange method does exactly what it says on the tin – it’ll insert all the data passed to it into the database.

However – before inserting anything, EF has an eleborate set of checks to go through to verify that the inserted data against the model it has of the database. Chief among these checks is that of the primary keys. If a primary key is an integer and a Computed column, EF will not insert it, rather leaving it up to the database to assign a primary key.

But what if you want to force EF to insert your Primary Key? Generally speaking, we want this if we are importing multiple tables and wish to preserve the Foreign Key relationships between them. Which was exactly what I wanted.

So, how do we do this?

Step 1

In your Code First model (or your .edmx file for Database First) the primary keys should not have any attibutes, or they should be set to [DatabaseGenerated(DatabaseGeneratedOption.Identity)].

All the primary keys that you want to Force to be inserted should be changed to [DatabaseGenerated(DatabaseGeneratedOption.None)] This ensures that EF will not try and change the values and will simply insert them with the rest of the data.

If you go ahead and try and insert the data now you’ll get an error says that Identity Insert needs to be enabled.

Step 2 A

So. We need to execute a SQL statement against the database. Basically the SQL loos like this: SET IDENTITY_INSERT [dbo].[TableName] ON

So we’ll try this code:

context.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT [dbo].[TableName] ON");
context.TableName.AddRange(items);

context.SaveChanges();

context.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT [dbo].[TableName] OFF");

Now, if you try and run that, you’ll get the exact same Identity Insert error.

The code looks legit! Why is this happening?

Basically – Identity Insert is scope limited. It is only enabled for the scope of a single transaction. Mainly to prevent you forgetting to turn it back off after you’re finished and causing choas.

So the second call to Identity insert is superfluous. But I include it for readabilities sake.

Step2 B

So we need a way of executing the EF AddRange() and SaveChanges() methods in the same transaction scope as our Identity Insert ON statement.

Fortunately there is a way. Enter the TransactionScope class.

<!-- wp:code -->
<pre class="wp-block-code"><code>using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
      {
      using (var conn = new System.Data.SqlClient.SqlConnection(_connectionstring))
            {
                 conn.Open();
                 using (Context context = new Context(conn, false))
                        {
                            context.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT &#091;dbo].&#091;TableName] ON");
                            context.TableName.AddRange(items);

                            context.SaveChanges();

                            context.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT &#091;dbo].&#091;TableName] OFF");
                            scope.Complete();
                          }
                 }
	}</code></pre>
<!-- /wp:code -->

So what are we doing here?

We first create a TransactionScope object in a using block. The using block ensures that the TransactionScope Object will be destroyed once the flow of control leaves the using block.

In the body of the using block we create another using block and create a new SqlConnection Object and pass it our connection string. The SQLConnection will automatically be enrolled in the Transaction.

We create our third using block and we create a new version of our EF Database Context and pass it our open SqlConnection object for it to use internally. Note that we also pass false as a second parameter to the context constructor. This tells EF that it does not own the SqlConnection object and ensures that it plays nicely with the TransactionScope.

Except for the last line of the using block, the rest of the code is the same as our earlier attempt. The last line calls scope.Complete().

This closes the scope and commits the transaction. We then exit all three using blocks.

At this point all the objects we’ve created have been destroyed. We do this because we want to clean up objects we won’t be using and free up resources. And also because using two EF Contexts can be dangerous. Although its not in the code snippit above, its likely that you will have one EF Context to do all the operations on the database that don’t require this special treatment along with the second Context that we create to force the Primary keys to be inserted.

So by destoying the second one, we avoid any confusion between them. A quick look at Stackoverflow shows that this is more of an issue than you might want to believe.

The result of this code is that the data would have been sucessfully added to the table with its primary keys intact.

Success!!

(written mainly for my future self to rediscover this at an acute moment of crisis)