‘Microsoft.SqlServer.Types’ version 10 or higher could not be found

Tested on:
.Net Framework 4.5.1
Entity Framework 6.2.0

You get this error when using System.Data.Spatial features in .Net (for example: DbGeography Class or DbGeometry Class):

Spatial types and functions are not available for this provider because the assembly ‘Microsoft.SqlServer.Types’ version 10 or higher could not be found.

Possible solutions – you may need to implement all of them.
1. Install latest Microsoft.SqlServer.Types nuget package – this will create new SqlServerTypes folder in your project.
2. Add following code in Application_Start() method, located in Global.asax.cs file – change the SqlServerTypes version to the same version as the one you installed.

SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~/bin"));
SqlProviderServices.SqlServerTypesAssemblyName =
    "Microsoft.SqlServer.Types, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

References:
Stackoverflow

Unit Test Entity Framework

Tested with:
.Net Framework 4.5
Entity Framework 6.2.0
Moq 4.13.1
MSTest 1.3.2

Github repository here.

This is a demo of how to write unit tests for Entity Framework.
This demo is based on Microsoft article.
It consists of testing EF query and non-query scenarios on both async and non-async calls.

The entity model looks like this.

2019-12-23 14_20_09-Infinity - Microsoft Visual Studio

The data access class look like this.

public class InfinityDataAccess
{
    private readonly InfinityEntities dbContext;
    public InfinityDataAccess(InfinityEntities dbContext)
    {
        this.dbContext = dbContext;
    }

    public IEnumerable<Misc> GetMisc()
    {
        using (this.dbContext)
        {
            return this.dbContext.Misc
                .Select(o => o)
                .ToList();
        }
    }

    public async Task<IEnumerable<Misc>> GetMiscAsync()
    {
        using (this.dbContext)
        {
            return await this.dbContext.Misc
                .Select(o => o)
                .ToListAsync();
        }
    }

    public bool InsertMisc(string data, string description)
    {
        try
        {
            using (this.dbContext)
            {
                var newModel = new Misc
                {
                    Data = data,
                    Description = description
                };

                this.dbContext.Misc.Add(newModel);
                this.dbContext.SaveChanges();
                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public async Task<bool> InsertMiscAsync(string data, string description)
    {
        try
        {
            using (this.dbContext)
            {
                var newModel = new Misc
                {
                    Data = data,
                    Description = description
                };

                this.dbContext.Misc.Add(newModel);
                await this.dbContext.SaveChangesAsync();
                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public bool UpdateMisc(int miscId, string data, string description)
    {
        try
        {
            using (this.dbContext)
            {
                var dataModel = this.dbContext.Misc.Find(miscId);

                if (dataModel != null)
                {
                    dataModel.Data = data;
                    dataModel.Description = description;
                    this.dbContext.SaveChanges();
                }

                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public async Task<bool> UpdateMiscAsync(int miscId, string data, string description)
    {
        try
        {
            using (this.dbContext)
            {
                var dataModel = await this.dbContext.Misc.FindAsync(miscId);

                if (dataModel != null)
                {
                    dataModel.Data = data;
                    dataModel.Description = description;
                    await this.dbContext.SaveChangesAsync();
                }

                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public bool DeleteMisc(int miscId)
    {
        try
        {
            using (this.dbContext)
            {
                var dataModel = this.dbContext.Misc.Find(miscId);

                if (dataModel != null)
                {
                    this.dbContext.Misc.Remove(dataModel);
                    this.dbContext.SaveChanges();
                }

                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public async Task<bool> DeleteMiscAsync(int miscId)
    {
        try
        {
            using (this.dbContext)
            {
                var dataModel = await this.dbContext.Misc.FindAsync(miscId);

                if (dataModel != null)
                {
                    this.dbContext.Misc.Remove(dataModel);
                    await this.dbContext.SaveChangesAsync();
                }

                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }
}

For query scenario on non-async call, it is pretty straight forward.

[TestMethod]
public void TestGetMisc()
{
    // Data to be returned
    var data = new List();
    data.Add(new Misc { Data = "Data1", Description = "Description1" });
    data.Add(new Misc { Data = "Data2", Description = "Description2" });
    data.Add(new Misc { Data = "Data3", Description = "Description3" });

    // Create mock data
    var mockData = new Mock();
    mockData.As().Setup(m => m.Provider).Returns(data.AsQueryable().Provider);
    mockData.As().Setup(m => m.Expression).Returns(data.AsQueryable().Expression);
    mockData.As().Setup(m => m.ElementType).Returns(data.AsQueryable().ElementType);
    mockData.As().Setup(m => m.GetEnumerator()).Returns(data.AsQueryable().GetEnumerator());

    // Create mock EF context
    var mockContext = new Mock();
    mockContext
        .Setup(mock => mock.Misc)
        .Returns(mockData.Object);

    // Assert
    var dataAccess = new InfinityDataAccess(mockContext.Object);
    var result = dataAccess.GetMisc();
    Assert.IsNotNull(result);
    Assert.AreEqual(3, result.Count());
    Assert.AreEqual("Description1", result.FirstOrDefault(o => o.Data == "Data1").Description);
}

The test for query scenario creates mock data of queried EF entity model and setup the mock’s Provider, Expression, ElementType and Enumerator. The return object of the setup are the correspondent properties of the returned data which is a type of IQueryable (see // Create mock data in the code above).
Next, the test creates mock of EF context. The mock is setup to return mock data we created earlier (see // Create mock EF context in the code above).

For non-query scenario on non-async call, it is almost similar to query scenario.

[TestMethod]
public void TestInsertMisc()
{
    // Data to be returned
    var data = new Mock();

    // Create mock EF context
    var mockContext = new Mock();
    mockContext
        .Setup(mock => mock.Misc)
        .Returns(data.Object);

    // Assert
    var dataAccess = new InfinityDataAccess(mockContext.Object);
    var result = dataAccess.InsertMisc("UnitTestData1", "UnitTestDescription1");
    Assert.IsTrue(result);
    mockContext.Verify(m => m.Misc.Add(It.IsAny()), Times.Once);
    mockContext.Verify(m => m.SaveChanges(), Times.Once);
}

The test creates mock data of EF entity model, but it is not necessary to setup the mock’s Provider, Expression, ElementType or Enumerator.
Next, the test creates mock of EF context (see // Create mock EF context in the code above).

For async calls, there are additional helper classes we need to create.
These classes are in-memory query provider to process async requests. We use custom query provider because EF use LINQ extension methods for async calls and these methods are designed to be used only with EF. If we try to use them with our unit tests, it will throw errors.
The 3 classes we need to create are:

  1. DbAsyncQueryProvider
  2. DbAsyncEnumerable
  3. DbAsyncEnumerator

With these helper classes, we can create mock data capable of in-memory async calls. This is the generic method to create the mock data.

private Mock GenerateMockDbSet(IQueryable mockData)
    where T : class
{
    var mock = new Mock();
    mock.As()
        .Setup(x => x.GetAsyncEnumerator())
        .Returns(new TestDbAsyncEnumerator(mockData.GetEnumerator()));

    mock.As()
        .Setup(x => x.Provider)
        .Returns(new TestDbAsyncQueryProvider(mockData.Provider));

    mock.As()
        .Setup(x => x.Expression)
        .Returns(mockData.Expression);

    mock.As()
        .Setup(x => x.ElementType)
        .Returns(mockData.ElementType);

    mock.As()
        .Setup(x => x.ElementType)
        .Returns(mockData.ElementType);

    return mock;
}

So for query scenario on async call, we just call the above method to create mock data for us.

[TestMethod]
public async Task TestGetMiscAsync()
{
    // Data to be returned
    var data = new List();
    data.Add(new Misc { Data = "Data1", Description = "Description1" });
    data.Add(new Misc { Data = "Data2", Description = "Description2" });
    data.Add(new Misc { Data = "Data3", Description = "Description3" });

    // Create mock data
    var mockData = GenerateMockDbSet(data.AsQueryable());

    // Create mock EF context
    var mockContext = new Mock();
    mockContext
        .Setup(mock => mock.Misc)
        .Returns(mockData.Object);

    // Assert
    var dataAccess = new InfinityDataAccess(mockContext.Object);
    var result = await dataAccess.GetMiscAsync();
    Assert.IsNotNull(result);
    Assert.AreEqual(3, result.Count());
    Assert.AreEqual("Description1", result.FirstOrDefault(o => o.Data == "Data1").Description);
}

There are more sample tests on the github repository to demonstrate every single scenarios in both async and non-async calls.

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

Programmatically Configure EF DbConfiguration

Tested on:
Entity Framework 6.2.0

There are few ways to set EF configuration, this describes using code-based DbConfiguration.

  1. Create a class inherit from DbConfiguration.
    using System.Data.Entity;
    
    namespace InfinityDataModel
    {
        public class InfinityConfiguration : DbConfiguration
        {
            public InfinityConfiguration()
            {
                // Sample configuration
                SetDefaultConnectionFactory(new LocalDbConnectionFactory("InfinityDb"));
            }
        }
    }
    

  2. The class must be in same assembly as your the Entity Framework Data Model.

  3. In the constructor, set the configuration you wanted. For all possible configurations, see here.
  4. There is no need to do anything else. EF will load your configuration class when initialized.

References:
Microsoft

Expression Func vs. Func

Func
`Func<>` is a lambda expression. A delegate. A pointer to a method. Runtime engine can not “look” inside `Func<>`, it can only execute `Func<>`.

Example usage:

Func<int> haveFunc = () => 15; // return 15

Expression Func
`Expression<Func<>>` is an expression tree. It’s a lambda expression in form of tree data structure. It only hold metadata or information and composition of the contained lambda expression. Runtime engine can look “inside” `Expression<Func<>>` and translate it to other statement you need.

Example usage:

Expression<Func<int>> notMuchFunc = () => 25;

Why is it important ?
Misuse of this could be cause compile error or in the case of EF, a performance hit.

Compile Error Example
Linq has few methods that take `Func<>` or `Expression<Func<>>` parameter, for example `Where` extension method, as seen in signature below.

System.Linq.Enumerable
public static IEnumerable<TSource> Where<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate);

System.Linq.Queryable
public static IQueryable<TSource> Where<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate);

Depend on parameter type we pass in, compiler know which extension method to call.

In the code below, ProcessUsers3 throw compiler error. Because we pass in `Expression<Func<>>`, compiler think we are calling `Where` extension method which accept `IQueryable<>` but `this.GetUserNames()` return `IEnumerable<>`.

The workaround is to convert `IEnumerable<>` to `IQueryable<>` using `.AsQueryable()` as can be seen on ProcessUsers4.

public IEnumerable<string> GetUserNames()
{
    return new[] { "user1", "user2" };
}

public void ProcessUsers()
{
    // Return type IEnumerable<string>
    var result = this.GetUserNames()
        .Where((item) => !string.IsNullOrEmpty(item));
}

public void ProcessUsers2()
{
    // Return type IEnumerable<string>
    Func<string, bool> predicate = (item) => !string.IsNullOrEmpty(item);
    var result = this.GetUserNames()
        .Where(predicate);
}

public void ProcessUsers3()
{
    // Compile error
    // We pass in Expression<Func<>> to Where extension method, compiler think we are calling the Where extension method for IQueryable<> - this.GetUserNames() return IEnumerable<>
    Expression<Func<string, bool>> predicate = (item) => !string.IsNullOrEmpty(item);
    var result = this.GetUserNames()
        .Where(predicate);
}

public void ProcessUsers4()
{
    Expression<Func<string, bool>> predicate = (item) => !string.IsNullOrEmpty(item);

    // Return type IQueryable<string>
    var result = this.GetUserNames()
        .AsQueryable()
        .Where(predicate);
}

Performance Hit Example
In the code below, both methods are legal, compile without error and run without error.

On GetAppConfigurations1, we query EF context by passing `Func<>`, a lambda expression. Because the context can not “look” inside the lambda expression (it can only execute it), Linq would get all AppConfiguration and run `Func<>` on each record, which increase load time.

On GetAppConfigurations2, we query EF context by passing `Expression<Func<>>`, an expression tree. It allow Linq to “look” inside the expression tree, read its metadata and composition than figure out the best and most perfomant way to query AppConfiguration with filter condition we specified.

public IEnumerable<AppConfiguration> GetAppConfigurations1()
{
    Func<AppConfiguration, bool> filter = (config) => config.Id == 1;

    using (var context = new QCEntities())
    {
        var result = context.AppConfiguration.Where(filter);
        return result;
    }
}

public IEnumerable<AppConfiguration> GetAppConfigurations2()
{
    System.Linq.Expressions.Expression<Func<AppConfiguration, bool>> filter = (config) => config.Id == 1;

    using (var context = new QCEntities())
    {
        var result = context.AppConfiguration.Where(filter);
        return result;
    }
}

References
Stackoverflow

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]

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);
        }
    }
}