Configuring Entity Framework 6.0.X for Services That Use Windows Azure SQL Database

Since Entity Framework 6 has achieved General Availability I have been waiting for version 6.0.2 (available on NuGet). This version of Entity Framework packs a ton of new features, including async queries and built-in retries to deal with the inevitable transient faults. On Windows Azure, there are to be expected. Having the retry logic built-in is music to any seasoned Windows Azure developer who has had to implement this logic using the Transient Fault Handling Application Block.

Don’t get me wrong, the Transient Fault Handling Application Block is still very useful to implement your own error handling strategies. A good example can be found in my post about defining an HTTP Transient Error Detection Strategy for REST calls.

Since Entity Framework has changed a lot since version 5.0, it may come as a surprise that the transient fault retry logic isn’t activated by default. If you want to take advantage of this feature, you will need to activate it. The following piece of code, will show you what you need to know about configuring Entity Framework 6.0.X for services that use Windows Azure SQL Database.

My Reading List for 2014

Every year I hunt for new books to read. 2013 was a wild year where I didn’t really have a plan in place and my reading was all over the place. Wanting to get organized in 2014 is all about not missing out.

Last year I read some great books about SQL Server, Windows Azure and methodology, this year I want to focus on catching up some more on those key books that greatly affected our industry. Are there any books that shouldn’t be on my list? What’s missing from the list?

• Domain-Driven Design: Tackling Complexity in the Heart of Software
• Refactoring: Improving the Design of Existing Code
• Executable Specifications with Scrum: A Practical Guide to Agile Requirements Discovery
• A CQRS Journey
• Exploring CQRS and Event Sourcing (Microsoft patterns & practices)
• Data Access for Highly-Scalable Solutions: Using SQL, NoSQL, and Polyglot Persistence (Microsoft patterns & practices)
• Dependency Injection with Unity (Microsoft patterns & practices)
• Developing Big Data Solutions in the Cloud
• How to Stop Sucking and Be Awesome Instead
• Extreme Programming Explained: Embrace Change
• Planning Extreme Programming
• Extreme Programming in Practice
• Agile Testing: A Practical Guide for Testers and Agile Teams
• Continuous Delivery: Reliable Software Releases through Build, Test, and Deployment Automation
• Agile Estimating and Planning
• Managing Agile Projects
• Service Design Patterns: Fundamental Design Solutions for SOAP/WSDL and RESTful Web Services
• Patterns of Enterprise Application Architecture
• Growing Object-Oriented Software, Guided by Tests
• Analysis Patterns: Reusable Object Models
• User Stories Applied: For Agile Software Development
• Developer’s Guide to Microsoft Enterprise Library, 2nd Edition (Microsoft patterns & practices)
• TypeScript for C# Programmers
• Slack: Getting Past Burnout, Busywork, and the Myth of Total Efficiency

I probably won’t get to read everything on my list, but with your help I can try to pick the best ones to read this year and continue working down the list next year.

2013 was a slow year for reading, because my focus was mostly geared towards sharing what I had learnt about Windows Azure through my blog. This year is going to be different, I will try to balance my efforts between writing and catching up on the past.

Friends Don’t Let Friends use Lazy Loading on Windows Azure!

Now don’t get me wrong, I’m a HUGE fan of Entity Framework. What I don’t get though, is why Lazy Loading is on by default.

As one of my friends likes to quote “with great power comes great responsibility” and he’s absolutely right! Lazy loading is very powerful and should be used selectively. I’m not saying that you should never use it, but you should really think long and hard about how you use it.

By forgetting that Lazy Loading is on by default, it’s quite easy to create N+1 situations where what could have been a simple read from the database can turn into a nightmare!

Book – Building Interactive Queries with LINQPad

A few months ago Sébastien Finot was approached to write a book about working with LINQ using LINQPad and I had the honor of being one of the technical reviewers.

If you need to interact with databases, XML, in-memory collections, or remote services, LINQ can make your life simpler. The best way to discover LINQ is with the help of LINQPad, a free IDE whose first goal is to make sure that writing and interacting with your LINQ query is fun and easy. More generally, LINQPad is a C#/VB/F# scratchpad that instantly executes any expression, statement block, or program with rich output formatting and a wealth of features.

With Building Interactive Queries with LINQPad, you will quickly learn everything you need to know to start using LINQ. To accelerate your learning curve, you will discover how to use LINQPad and its features to test your queries interactively and experiment with all the options offered by LINQ.

In all probability, you already know C#, but have you had a chance to try out LINQ? Building Interactive Queries with LINQPad will introduce you to everything LINQ can offer and will let you interact with every example in LINQPad, LINQ’s best companion.

You will learn how to build and experiment with interactive queries with this practical guide illustrated with short and detailed code samples. You will also get acquainted with other cool applications of LINQpad such as testing, code snippet generation, and so on, along with a broad approach to LINQ (to object, to SQL, to XML, and so on).

My Recommendations For Entity Framework Literature

In 2010 I bought a Kindle and it changed my life! Since then I’ve been catching up on books I should have read years ago. Back then, reading technical books meant carrying bulky/ heavy printed books in my bag. The day I got my first kindle, is the day I started reading again!

Since then, I read quite a few books! Below are some of the books that helped me love and master Entity Framework!

Before you go through this list of books, I have to admit that I have a huge preference for Entity Framework Code First (aka. Magic Unicorn) because I find it easy to work with. It is easier to maintain, to evolve and it allows you to use true Plain Old CLR Objects (POCOs). If you have any suggestions for good Entity Framework, please share them through this post’s comments.

Query Level Caching For Entity Framework And Windows Azure SQL Database Including Transient Fault Handling

Caching with Entity Framework has always been a challenge. Out of the box Entity Framework doesn’t have second level caching support. Even though, many open source solutions like the Scalable Object Persistence (SOP) Framework exist, I decided to implement a query level cache that uses the Transient Fault Handling Application Block to execute retry policies to transparently handle transient faults.

Keep in mind that transient faults are normal and that its not a question of if they will occur, it’s really a question of when they will occur. SQL Database instances are continuously being shifted around to prevent the service’s performance from degrading.

The Database fluent API I created executes reusable queries. Reusable queries greatly simplify the application’s design by encapsulating query logic in named concepts. They are like Stored Procedures in SQL Server, where team members can discover functionality by reading file names from the query folders. More details about reusable queries can be found in the reference section at the bottom of this post.

The code from this Post is part of the Brisebois.WindowsAzure NuGet Package

To install Brisebois.WindowsAzure, run the following command in the Package Manager Console

PM> Install-Package Brisebois.WindowsAzure

Get more details about the Nuget Package.

Why am I Getting so Many Deadlocks From Windows Azure SQL Database?

The short answer is that you’re probably not using transactions.

I came across this issue a few time and decided to create the ReliableModel. When I modify the state of my database, it automatically wraps my code with a TransactionScope. By default, TransactionScopes are set to timeout after 30 minutes. This configuration is set in the machine.config and it cannot be modified on Windows Azure Roles. Therefor, if your statements need more than 30 minutes to execute, I strongly recommend using Stored Procedures. Be sure to use Transactions in your Stored Procedures.

Ingesting Massive Amounts of Relational Data With Windows Azure SQL Database (70+ Million records/day)

In a previous post I discussed using Table Valued Parameters and Stored Procedures to insert large amounts of data into Windows Azure SQL Database with reasonable  throttling by the SQL Database. Not long after that post, I rapidly came to the conclusion that it was a great solution for reasonable amounts of data. Then I wrote about using SqlBulkCopy to overcome the limitations imposed by the stored procedure approach and provided the ReliableBulkWriter.

This article will show how to organize a  database to enable the insertion of large amounts of  relational data. To illustrate the changes that were required I will be using the following model.

 

Fixing PlanAffectingConvertWarning: ConvertIssue= SeekPlan, Expression= CONVERT_IMPLICIT(nvarchar(50),[a].[Name],0)=[b].[Name] With Entity Framework On Windows Azure SQL Database

When observing Query Plans through the Windows Azure Management Portal for SQL Database you may come across warnings. Some warning are about missing Indexes and some are about performance issues related to the Query itself.

This specific warning occurs when you are comparing two strings that are not of the same Type. By this I mean one is NVARCHAR and the second is VARCHAR.

Addressing these warnings will have a great impact on query performance with large datasets. It may not be noticeable on tables that have a small amount of records. Once you start querying over a few million records, performance gains will be noticeable by the end users.

Fixing these issues can go a long way when it comes to end user satisfaction! Remember, slow applications are applications to which people find alternatives!

Unidirectional Many to Many Relationship With Entity Framework Code First on Windows Azure SQL Database

There are times when entities don’t need navigation properties on both sides of the relationship. This is an example you can use to map to an existing database or generate a brand new database on Windows Azure SQL Database.

The ERD shows a Many to Many relationship between the FlightLog and the Airport entities. The requirements for the current model has no need for Airport to have a Navigation Property of FlightLogs.

Using Entity Frame Work Code First, the following statement can be used in the EntityTypeConfiguration to configure the relationship.

HasMany(r => r.Airports)
    .WithMany() // No navigation property here
    .Map(m =>
        {
            m.MapLeftKey("FlightLogId");
            m.MapRightKey("AirportId");
            m.ToTable("BridgeTableForAirportsAndFlightlogs");
        });

FlightLog has many Airports, the relationship is held in an intermediate table called BridgeTableForAirportsAndFlightlogs. By not specifying a property for the inverse Navigation Property, a unidirectional Many to Many relationship is created.