Setup SQL Server Connection Resiliency with Entity Framework

Tested on:
Microsoft SQL Server 13.00.5292
Entity Framework 6.2.0

When an application execute too many operations against SQL Server, a transient fault could occur.
Transient fault in SQL Server could occurred in self-hosted or cloud SQL Server. It could caused by many reasons and extremely (if not impossible) to debug.
You might encountered error messages like:

“Database * on server * is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of *.”

Other similar messages also point to transient fault errors.

Microsoft suggested solution to transient fault is to retry the executed operation, often dubbed as database connection resiliency. To set this up in Entity Framework, follow this step:

  1. Set EF DbConfiguration using code-based configuration, see this post.

  2. In the constructor, add following code

    SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy(10, TimeSpan.FromSeconds(50)));
    

  3. The first parameter of SqlAzureExecutionStrategy is the maximum number of retry and second parameter is the delay in milliseconds between retries.

Once set, EF will retry the faulted operation automatically. Consider performance when deciding how many tries and delay time to configure. The longer the try or delay time will increase operation time.

References:
Microsoft
Sql Server Backup Academy

Advertisements

Parse JSON in SQL Server

I have following rows in database.

2019-07-17 17_33_37-SQLQuery45.sql - (local)_.Infinity (ENERGY_sutet (53))_ - Microsoft SQL Server M

Here is the full json:

{
    "FirstName": "Paul",
    "LastName": "Goodman",
    "Occupations": [
        {
            "Title": "Senior Manager",
            "Company": "Purple Ocean",
            "YearOfService": 9
        },
        {
            "Title": "Manager",
            "Company": "Blue Ocean",
            "YearOfService": 4
        }
    ]
}
{
    "FirstName": "Tom",
    "LastName": "Badman",
    "Occupations": [
        {
            "Title": "Supervisor",
            "Company": "Red Sky",
            "YearOfService": 1
        },
        {
            "Title": "Janitor",
            "Company": "Yellow Sky",
            "YearOfService": 7
        }
    ]
}

Here is how I can use JSON_VALUE to parse `Data` column.

2019-07-17 17_34_04-SQLQuery45.sql - (local)_.Infinity (ENERGY_sutet (53))_ - Microsoft SQL Server M

I can also use JSON_VALUE in WHERE clause.

2019-07-17 17_34_38-SQLQuery45.sql - (local)_.Infinity (ENERGY_sutet (53))_ - Microsoft SQL Server M

Comparing Linq Join and GroupJoin

Tested on:
.Net 4.5
Entity Framework 6 – Github

This is a post comparing Linq Join and GroupJoin for querying database.

I created simple database consist of 2 tables, `Instructor` and `Class`, which has 1 to many relationship.

SQL instructor table

SQL class table

Entity Framework diagram looks like this.
EF diagram

The console app using Linq to join these 2 tables look like this.

using System.Linq;

namespace LinqJoin
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new TestEntities())
            {
                var join = db.Instructor
                    .Join(
                        db.Class,
                        i => i.InstructorId,
                        c => c.InstructorId,
                        (i, c) => new
                        {
                            Instructor = i,
                            Class = c
                        })
                    .ToList();

                var groupJoin = db.Instructor
                    .GroupJoin(
                        db.Class,
                        i => i.InstructorId,
                        c => c.InstructorId,
                        (i, c) => new
                        {
                            Instructor = i,
                            Class = c
                        })
                    .ToList();

                var flattenGroupJoin = db.Instructor
                    .GroupJoin(
                        db.Class,
                        i => i.InstructorId,
                        c => c.InstructorId,
                        (i, c) => new
                        {
                            Instructor = i,
                            Class = c
                        })
                    .SelectMany(r => r.Class, (r, c) => new
                    {
                        Instructor = r.Instructor,
                        Class = c
                    })
                    .ToList();
            }
        }
    }
}

Following are the result.

** Join **

Visual Studio Debug
VS join result

SQL Profiler

SELECT 
    [Extent1].[InstructorId] AS [InstructorId], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[LastName] AS [LastName], 
    [Extent2].[ClassId] AS [ClassId], 
    [Extent2].[Name] AS [Name], 
    [Extent2].[ClassNumber] AS [ClassNumber], 
    [Extent2].[InstructorId] AS [InstructorId1]
    FROM  [dbo].[Instructor] AS [Extent1]
    INNER JOIN [dbo].[Class] AS [Extent2] ON [Extent1].[InstructorId] = [Extent2].[InstructorId]

** Group Join **

`GroupJoin` result in grouped data by specified entity. In this case I grouped by `Instructor`.

Visual Studio Debug
VS groupjoin result

SQL Profiler

SELECT 
    [Project1].[InstructorId] AS [InstructorId], 
    [Project1].[FirstName] AS [FirstName], 
    [Project1].[LastName] AS [LastName], 
    [Project1].[C1] AS [C1], 
    [Project1].[ClassId] AS [ClassId], 
    [Project1].[Name] AS [Name], 
    [Project1].[ClassNumber] AS [ClassNumber], 
    [Project1].[InstructorId1] AS [InstructorId1]
    FROM ( SELECT 
        [Extent1].[InstructorId] AS [InstructorId], 
        [Extent1].[FirstName] AS [FirstName], 
        [Extent1].[LastName] AS [LastName], 
        [Extent2].[ClassId] AS [ClassId], 
        [Extent2].[Name] AS [Name], 
        [Extent2].[ClassNumber] AS [ClassNumber], 
        [Extent2].[InstructorId] AS [InstructorId1], 
        CASE WHEN ([Extent2].[ClassId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[Instructor] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Class] AS [Extent2] ON [Extent1].[InstructorId] = [Extent2].[InstructorId]
    )  AS [Project1]
    ORDER BY [Project1].[InstructorId] ASC, [Project1].[C1] ASC

** Flatten Group Join **

Visual Studio Debug
VS flatten groupjoin result

SQL Profiler

SELECT 
    [Extent1].[InstructorId] AS [InstructorId], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[LastName] AS [LastName], 
    [Extent2].[ClassId] AS [ClassId], 
    [Extent2].[Name] AS [Name], 
    [Extent2].[ClassNumber] AS [ClassNumber], 
    [Extent2].[InstructorId] AS [InstructorId1]
    FROM  [dbo].[Instructor] AS [Extent1]
    INNER JOIN [dbo].[Class] AS [Extent2] ON [Extent1].[InstructorId] = [Extent2].[InstructorId]

Adding and Reference a Database Name in Data-Tier Application SQL Server

This apply to SQL Server Database Project.

Continuing from this post.

When adding data-tier application to SQL Server project, you can specify how to reference a database.

1. Right click on `References` under your project, select `Add Database Reference…`.
1

2. A dialog box will show up. Select your file data-tier application file with `Browse…` button.

3. Once data-tier application is selected, you can specify how you would like to reference a database. The default is show on screen show below.
3

The `Example usage` show how you can reference a database. For example, in your View, default way to reference a database is:

CREATE VIEW dbo.AccountView
	AS

SELECT *
FROM [$(test)].dbo.Account

4. (Optional) To change default way to reference a database, remove `Database variable` or set to anything you like. If `Database variable` is removed, you can reference a database the “normal” way.
2

CREATE VIEW dbo.AccountView
	AS

SELECT *
FROM test.dbo.Account

Entity Framework, .Net and SQL Server Table Valued Parameter

This is step by step setup of using SQL Server TVP (Table Valued Parameter) in .Net application with EF (Entity Framework). In this example, I use SQL Server 2016 (SP2-CU3), .Net 4.5.1 and EF 6.20.

1. Create a table to store data.

CREATE TABLE [dbo].[Something] (
    [Id]            INT	IDENTITY(1,1)   NOT NULL,
    [Name]          VARCHAR(150)        NOT NULL,
    [Status]        CHAR(1)             NOT NULL,
    CONSTRAINT [PK_Something_Id] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO

2. Create `User Defined Table Type` in SQL Server. For simplicity, in this example the type’s columns are same as table I created on step 1. In real-world, the type’s columns could be significantly different than table where we store the data, it might even used for join with other tables.

CREATE TYPE [dbo].[udt_Something] AS TABLE (
	[Name]		VARCHAR(150)	NOT NULL,
	[Status]	CHAR(1)		NOT NULL,
);
GO

3. Create stored procedure to take parameter (of a `User Defined Table` type we created earlier) and perform necessary task to persist our data.

CREATE PROCEDURE [dbo].[sp_SaveSomething]
	@udt_Something [dbo].[udt_Something] READONLY
AS
BEGIN
    INSERT INTO [dbo].[Something]
        SELECT *
        FROM @udt_Something
END
GO

4. Create extension method to convert `IEnumerable<T>` object to a `DataTable` object. In order to use SQL TVP, we have to pass our parameter as a `DataTable`. This method will help convert our data to `DataTable` type.
Helper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace QC
{
    public static class Helper
    {
        public static DataTable ToDataTable<T>(this IEnumerable<T> enumerable, IEnumerable<string> orderedColumnNames)
        {
            var dataTable = new DataTable();

            // Get all properties of the object
            PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            PropertyInfo[] readableProperties = properties.Where(w => w.CanRead).ToArray();

            // Get column
            var columnNames = (orderedColumnNames ?? readableProperties.Select(s => s.Name)).ToArray();

            // Add columns to data table
            foreach (string name in columnNames)
            {
                dataTable.Columns.Add(name, readableProperties.Single(s => s.Name.Equals(name)).PropertyType);
            }

            // Add rows to data table from object
            foreach (T obj in enumerable)
            {
                dataTable.Rows.Add(columnNames.Select(s => readableProperties.Single(s2 => s2.Name.Equals(s)).GetValue(obj)).ToArray());
            }

            return dataTable;
        }
    }
}

5. For the purpose of this example, let’s say we want to save a collection of objects. This is our object definition.
Something.cs

namespace QC
{
    public class Something
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Status { get; set; }
    }
}

6. Using EF, called stored procedure we created and pass in `SqlParameter`, which is a collection of objects that we converted to `DataTable`. Don’t forget to specify parameter type as `User Defined Table Type`.
DataAccess.cs

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace QC
{
    public class DataAccess
    {
        public void Save(IEnumerable<Something> data)
        {
            // Columns for ordering, the order of the columns must be the same as user defined table type
            var orderedCols = new[] { "Name", "Status" };

            // SQL parameter to pass to stored procedure
            var param = new SqlParameter("@udt_Something", SqlDbType.Structured);
            param.Value = data.ToDataTable(orderedCols);
            param.TypeName = "dbo.udt_Something";

            try
            {
                // QCDB is our EF entities
                using (var db = new QCDB())
                {
                    // Call stored procedure and pass in table valued parameter
                    db.Database.ExecuteSqlCommand("EXEC dbo.sp_SaveSomething @udt_Something", param);
                }
            }
            catch
            {
                throw;
            }
        }
    }
}

7. Example of usage.
OpsController.cs

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace QC
{
    public class OpsController : ApiController
    {
        public void SaveSomething()
        {
            var data = new List<Something>();
            data.Add(new Something { Id = 1, Name = "Chap", Status = "A" });
            data.Add(new Something { Id = 2, Name = "Stick", Status = "D" });

            var dataAccess = new DataAccess();
            dataAccess.Save(data);
        }
    }
}

Extract (Create) SQL Data-Tier Application

SQL Data-Tier allow database administrator to package SQL Server objects into portable artifact.
There are 2 types:
1. DACPAC – contain database schema only.
2. BACPAC – contain database schema and data.

You can create Data-Tier in SSMS (SQL Server Management Studio) or Visual Studio Data Tools. I’d like Visual Studio Data Tools as it give me more options to configure.

To extract SQL Data-Tier from a database in Visual Studio.
1. Open SQL Server Object Explorer.
2018-12-10 14_06_30-

2. Right click on database you want to extract (assuming you have configured database connection).
3. Select “Extract Data-Tier Application…”
2018-12-10 14_04_49-MDEDatabase - Microsoft Visual Studio (Administrator)

4. Dialog displayed.
2018-12-10 14_07_26-Extract Data-tier Application

Here you can configure different options. Some to consider:

  • Include user login mappings – whether to include objects under Security (users, roles, etc).
  • Verify extration – whether Visual Studio verify reference in every objects, for example: if you have view that join table from another database, VS will verify the connection to the other database.

To deploy Data-Tier to database engine (in VS):
1. Right click on “Databases”
2. Select “Publish Data-Tier Application…”
2018-12-10 14_17_53-MDEDatabase - Microsoft Visual Studio (Administrator)

What is SQL Server “Included Columns” ?

SQL Server organized indexes in a table as B-tree structure, which look like this.

B-tree-structure

A table that has `clustered index` store actual data rows at leaf level.
A table that has `non-clustered index`, only store (at leaf level) the value from the indexed column and a `row locator` point to actual data rows. It does not store actual data rows.

Specifying Included Columns in `non-clustered index` tell SQL Server you want to store actual data rows at leaf level.

For example:

CREATE NONCLUSTERED INDEX IX_Employee_Department
ON Employee(EmployeeFirstName)
INCLUDE (EmployeeAddress, EmployeeDOB)
GO

This example will create `non-clustered index` and its leaf level will store value from indexed column (EmployeeFirstName) and data rows from EmployeeAddress and EmployeeDOB columns.