Entity Framework Core and calling a stored procedure

I thought this would be easy, there is a method called ExecuteSqlCommand which takes the stored proc name and an array of parameters. I wanted to pass in two parameters and get one back, no problem, I’ve done this with ADO.Net and Entity Framework 6.

But holy s**t, two hours later and it was nowhere near working and I was very annoyed. ExecuteSqlCommand takes SqlParameters and the constructor for the output parameter is way more confusing than it needs to be. The input SqlParameter takes a name, a type and a value but the output SqlParameter needs eight more constructor params!!

Fortunately, EF Core still supports old style commands –

            DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();

DbCommand still has the ExecuteNonQueryAsync method. Save yourself a major headache and use the cmd.ExecuteNonQueryAsync().

Here is a simple example passing in two parameters and getting one output back.

private async Task ExecuteStoredProc()
{
	DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();

	cmd.CommandText = "dbo.sp_DoSomething";
	cmd.CommandType = CommandType.StoredProcedure;

	cmd.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar) { Value = "Steve" });
	cmd.Parameters.Add(new SqlParameter("@lastName", SqlDbType.VarChar) { Value = "Smith" });

	cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.BigInt) { Direction = ParameterDirection.Output });

	if (cmd.Connection.State != ConnectionState.Open)
	{
		cmd.Connection.Open();
	}

	await cmd.ExecuteNonQueryAsync();

	long id = (long)cmd.Parameters["@Id"].Value;
}

Getting Web API Exception Details from a HttpResponseMessage

The Problem

It’s hard to get the details of an exception from a Web Api response when calling Web Api from a C# program. (Skip to the solution if you don’t care about the background), it even handle inner exceptions!

Some background

If you are working on a Web Api project and testing with a web browser you get a wonderful error page when an exception occurs. It gives you the message, exception message, exception type and the stack trace. Pretty much all you need to get started figuring out what has gone wrong.
Exception in browser

Same thing with fiddler, get a 500 back and you’ll even be treated to a Json version of the above.

Exception in fiddler

What about calling the action method from inside a c# program? Should be easy, you just create a client, setup the query, let it rip and examine the response for a success status and then read the content to get the returned values. Great, works fine.

What if the server threw an exception like the ones shown above, I thought it would be a simple thing to call response.Exception or the like and get all the details. But easy it is not.
I rooted around in the response for a while but found nothing that was simple to use.

The Solution

Instead I have added an extension method to HttpResponseMessage to parse the details of the exception from the Json in the response.Content.

using System.Net.Http;
using System.Threading.Tasks;
using Newtonsoft.Json;

namespace SimpleWebApiClient
{
    public static class HttpResponseMessageExtension
    {
        public static async Task<ExceptionResponse> ExceptionResponse(this HttpResponseMessage httpResponseMessage)
        {
            string responseContent = await httpResponseMessage.Content.ReadAsStringAsync();
            ExceptionResponse exceptionResponse = JsonConvert.DeserializeObject<ExceptionResponse>(responseContent);
            return exceptionResponse;
        }
    }

    public class ExceptionResponse
    {
        public string Message { get; set; }
        public string ExceptionMessage { get; set; }
        public string ExceptionType { get; set; }
        public string StackTrace { get; set; }
        public ExceptionResponse InnerException { get; set; }
    }
}

Usage is simple.

    //snip
    HttpResponseMessage response = await httpClient.GetAsync(query).ConfigureAwait(false);
    
    if (response.IsSuccessStatusCode)
        // return the value
    
    // But if an error occurred read the details           
    ExceptionResponse exceptionResponse = response.ExceptionResponse();

    //snip

 

Cached nuget packages causing you problems?

Short version
If you are having problems with nuget packages and think it might be related to local caching delete packages from these directories –

%LocalAppData%\NuGet\Cache
and
%userprofile%\.nuget\packages\

Long version
I was making nuget package, lets call it Foo, for my own use, not one that I was going to distribute publicly, but I was putting in on MyGet.

I had a few classes in it and after making the nuget package, I uploaded it to MyGet. I then switched to the Business solution and referenced this newly created nuget package.

After doing a little bit of work, I noticed that I had incorrectly made a class internal rather than public in the Foo nuget package.

No problem, back to the Foo solution where I was making the library, change the class to public, rebuild, and remake the nuget package. I didn’t want to increment the version number, so I left it at 1.0.0. On MyGet I deleted the old package and then uploaded the new one.

Here’s what I tried and didn’t work –

  • In the Business solution referencing the Foo nuget package, I uninstalled Foo and then reinstalled it.
  • But Visual Studio still reported that the class was inaccessible!
  • Uninstall and reinstall again, same problem!!
  • Delete the Foo directory from the packages directory beside the solution file, remove the Foo line from the packages.config. Reinstall Foo. Same problem!!!
  • Recomplie Foo, delete Foo from MyGet, reupload Foo to MyGet. Reinstall Foo. Still inaccessible!!!!
  • WTF!!!!!

And this is what works –

  • Download the Foo nuget package directly from MyGet and reference it directly. Success! There must be a local cache that Visual Studio is using!!!!!!

Yes there are, I found two –

%LocalAppData%\NuGet\Cache
and
%userprofile%\.nuget\packages\

Delete the offending nuget package; back in Visual Studio I can now successfully install the nuget package.

In general avoid using the same package version number when uploading a new package to MyGet.

Web API 2 and ninject, how to make them work together

Full source code to download.

I’ve been using ninject for a few years, but every time I use it with Web Api I hit some problem and they usually stem from not including the right nuget packages, not changing the DependencyResolver or (once) forgetting how to make a binding!

For my future self and your reference, here is how it is done.

1. Nuget packages

Add Ninject.Web.WebApi using nuget to your Web Api project.

That will install two other package dependencies:
Ninject
Ninject.Web.Common

To make everything work you need to add one more nuget package
Ninject.Web.Common.WebHost

This will pull down the WebActivatorEx package and add a new class called NinjectWebCommon to your App_Start directory.

2. Edit NinjectWebCommon.cs

NinjectWebCommon.cs is missing a key feature if you want to use ninject to construct the controllers, and I presume that is why you are using ninject inside a Web Api project.

In the CreateKernel() method add the second line shown below. Now ninject will be used to resolve controller dependencies.

RegisterServices(kernel);
GlobalConfiguration.Configuration.DependencyResolver = new NinjectDependencyResolver(kernel);
return kernel;

You will need to add a couple of using statements too –

using System.Web.Http;
using Ninject.Web.WebApi;

3. Register some services

To actually register some services we move to the RegisterServices(..) method do some binding.

private static void RegisterServices(IKernel kernel)
{
    kernel.Bind<ICaclulator>().To<Caclulator>();
}

4. Use it all

And here is the usage in the controller.

    public class ValuesController : ApiController
    {
        private readonly ICaclulator _caclulator;
        public ValuesController(ICaclulator calculator)
        {
            _caclulator = calculator;
        }

        public int Get(int num1, int num2)
        {
            return _caclulator.Add(num1, num2);
        }
    }

Full source code to download.

Entity Framework, checking the connection string of your context

Sometimes when using Entity Framework I want to verify that I’m connected to the database I think I’m connected to.

Here’s how to check in Entity Framework 5, Entity Framework 6 and Entity Framework Core 1 (EF 7)

//Entity Framework 5
myContext.Database.Connection.ConnectionString
//Entity Framework 6
myContext.Database.Connection.ConnectionString
//Entity Framework Core 1
myContext.Database.GetDbConnection().ConnectionString

Customizing a specific string inside a class using AutoFixture

Full source code.

I’ve been using AutoFixture for a while with my unit tests. It creates objects with prefilled data, saving me the hassle of manually constructing them.

Basic usage

If you want a string from AutoFixture do something like –

string myString = fixture.Create();

//"c2eefff9-9cc2-4358-aee1-2d27b0476e41"

If you want to prefix the string do this –

string myPrefixedString = fixture.Create("Prefix");

//"Prefix191dd4bc-f3ed-4d19-ac36-f3c84c958155"

If you want something that looks like an email address –

string emailAddress = fixture.Create().Address;

//"9ed7e16b-f6df-42d6-8812-d7ea6580f300@example.org"

Where it starts to get a bit tricky is if you have a class like this –

public class Account
{
	public Guid AccountId { get; set; }
	public string Firstname { get; set; }
	public string Lastname { get; set; }
	public IEnumerable<string> EmailAddresses { get; set; }
}

Because I am using a string to represent an email address AutoFixture will give me a string, not an email address.

Here are two solutions.

Solution 1

fixture.Customize<Account>(c => c.With(a => a.EmailAddresses, fixture.CreateMany<MailAddress>().Select(ma => ma.Address.ToString())));

This produces emails that look like –

"f45a37ae-6d2c-42a5-92ac-832e6ea2d028@example.net"
"72625222-e7af-4c29-96ed-4219efc1a859@example.net"
"54a39694-fd7a-458c-8739-0667ec9fa2d7@example.net"

Solution 2


If I want to generate email address that look a little more like real world address I have to create a SpecimenBuilder.

    public class EmailAddressesStringSpecimenBuilder : ISpecimenBuilder
    {
        public object Create(object request, ISpecimenContext context)
        {
            var propertyInfo = request as PropertyInfo;

            if (propertyInfo != null)
            {
                if (propertyInfo.Name == "EmailAddresses" && propertyInfo.PropertyType == typeof (IEnumerable<string>))
                {
					// of course you can customize how the mail addresses are created, you can even use a fixture to create strings 🙂 
                    IEnumerable<string> emailAddresses = new List<string>() {"user1@acme.com", "user2@unknown.com", "user3@nothing.com"};
                    return emailAddresses;
                }
            }
            return new NoSpecimen();
        }
    }

How to use the customizations

            Fixture fixture = new Fixture();

			fixture.Customizations.Add(new EmailAddressesStringSpecimenBuilder());
            // use one of the two customizations here, but not both
			// fixture.Customize<Account>(c => c.With(a => a.EmailAddresses, fixture.CreateMany<MailAddress>().Select(ma => ma.Address.ToString())));

            var account1 = fixture.Create<Account>();
            var account2 = fixture.Create<Account>();

Full source code.

Why you should use IDictionary, IList, etc

Summary
When returning objects from a method try to use IList, IDictionary, etc instead of List and Dictionary. This is especially important when the method is inside a class library which you distribute.

Details
I had to edit a method that was returning a Dictionary. Inside of which some complex work was being done to populate the dictionary.
The changes needed were going to be much easier if I could use and return a ConcurrentDictionary. See here for what I was doing.

But because the method was returning a simple Dictionary it was not as straight forward as I hoped. More work needed to be done and I had the choice of:

  1. Doing the processing in a more difficult way, not involving a ConcurrentDictionary.
  2. Moving the entries from the new ConcurrentDictionary to a simple Dictionary just before calling return.
  3. Changing the return type of the method (and the associated interface).

I went with option three, this happened to be the easiest, but it was also the best. If the code had initially been written to return IDictionary I would only have had to concern myself with required logic changes inside the method.

Here is a quick, but contrived, example of the flexibility gained by using IDictionary.

The class has a single public method which returns an IDictionary. Depending on the boolean value of getConcurrentDictionary it does its work using either as a simple Dictionary or a ConcurrentDictionary, but the caller does not need be aware of this. This gives great flexibility, the method GetDataDictionary can change its internal implementation and even the type of dictionary it returns without any negative impact on the caller.

The caller also has the flexibility to cast the IDictionary to a ConcurrentDictionary as needed.

    class GetDataWithInterfaceCollections : IGetDataWithInterfaceCollections
    {
        public IDictionary<int, int> GetDataDictionary(int start, int end, bool getConcurrentDictionary)
        {
            IDictionary<int, int> dictionaryToReturn;

            if (getConcurrentDictionary)
            {
                dictionaryToReturn = GetConcurrentDictionary(start, end);
            }
            else
            {
                dictionaryToReturn = GetDictionary(start, end);
            }

            return dictionaryToReturn;
        }

        private IDictionary<int, int> GetConcurrentDictionary(int start, int end)
        {
            //ConcurrentDictionary offers a lot of features not available in Dictionary
            ConcurrentDictionary<int, int> dictionary = new ConcurrentDictionary<int, int>();
            for (int loop = start; loop <= end; loop++)
            {
                dictionary.AddOrUpdate(loop, loop*10, (i, i1) => loop * 10 ); // AddOrUpdate is specific to a ConcurrentDictionary
            }

            return dictionary;
        }

        private IDictionary<int, int> GetDictionary(int start, int end)
        {
            IDictionary<int, int> dictionary = new Dictionary<int, int>();
            for (int loop = start; loop <= end; loop++)
            {
                dictionary.Add(loop, loop * 10);
            }

            return dictionary;
        }
    }

Here is how to call the method shown above.

            IDictionary<int, int> concurrentDictionary = _getDataWithInterfaceCollections.GetDataDictionary(1, 10, true);

            IDictionary<int, int> simpleDictionary = _getDataWithInterfaceCollections.GetDataDictionary(11, 20, false);

            ConcurrentDictionary<int, int> explicitConcurrentDictionary = _getDataWithInterfaceCollections.GetDataDictionary(21, 30, true) as ConcurrentDictionary<int, int>; 

Using the Watch window in Visual Studio we can see more info.

Dictionary Types

Filtering a Dictionary by value with a List as the value

Filtering out entries in a dictionary is not too difficult when the key and value are simple.
For example if you had –

IDictionary oneToFourDictionary = new Dictionary {{ "one", 1 }, { "two", 2 }, { "three", 3 }, { "four", 4 } };

You could easily filter out all values greater than 2.

IDictionary onlyGreaterThanTwoDictionary = oneToFourDictionary.Where(pair => pair.Value > 2).ToDictionary(pair => pair.Key, pair => pair.Value);

But it gets a little more complex when you have a dictionary where the value is an IList any you want to filter out certain entries in the list. But fortunately this is not too difficult either when using the ConcurrentDictionary.

In the example below I am using a string as the key and list of ints as the value. Of course the same principle applies if your key and value are different.

I’m using a List as my filter, i.e. only numbers in this list are considered valid. Entries in the dictionary should be removed if the values are not in this list.

using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Linq;

namespace ConcurrentDictionaryWithList
{
    class Program
    {
        static void Main(string[] args)
        {
            Program p = new Program();

            IDictionary<string, IList<int>> unfilteredDictionary = p.PopulateUnfilteredDictionary();

            IList<int> filter = new List<int> {5, 1, 2};

            IDictionary<string, IList<int>> filteredDictionary =  p.Filter(filter, unfilteredDictionary);
        }

        private IDictionary<string, IList<int>> Filter(IList<int> filter, IDictionary<string, IList<int>> unfilteredDictionary )
        {
            ConcurrentDictionary<string, IList<int>> filteredResults = new ConcurrentDictionary<string, IList<int>>();

            foreach (KeyValuePair<string, IList<int>> unfilteredEntry in unfilteredDictionary)
            {
                foreach (int number in unfilteredEntry.Value)
                {
                    if (filter.Contains(number))
                    {
                        filteredResults.AddOrUpdate(unfilteredEntry.Key, new List<int> { number }, (key, value) => { value.Add(number);
                                                                                                                     return value;
                                                                                                                    });
                    }
                }
            }
            return filteredResults;
        }

        private IDictionary<string, IList<int>> PopulateUnfilteredDictionary()
        {
            IDictionary<string, IList<int>> unfilteredDictionary = new Dictionary<string, IList<int>>();
            unfilteredDictionary.Add("key1", new List<int> { 1, 2, 3, 4, 5 });
            unfilteredDictionary.Add("key2", new List<int> { 1, 7, 8, 9, 10 });
            unfilteredDictionary.Add("key3", new List<int> { 5, 10, 15 });
            unfilteredDictionary.Add("key4", new List<int> { 200, 300, 400 });

            return unfilteredDictionary;
        }


        // Showing the simple filter just for completeness. 
        private void SimpleDictionaryFilter()
        {
            IDictionary<string, int> oneToFourDictionary = new Dictionary<string, int> {{ "one", 1 }, { "two", 2 }, { "three", 3 }, { "four", 4 } };

            IDictionary<string, int> onlyGreaterThanTwoDictionary = oneToFourDictionary.Where(pair => pair.Value > 2)
                .ToDictionary(pair => pair.Key, pair => pair.Value);

        }
    }
}

Parameterized SQL WHERE IN clause c#

If you are using some legacy SQL in C# you’ll occasionally need to write a WHERE IN.
Yon don’t want to end up writing something like the below, either directly or through some sort of loop.

string cmdText = "SELECT * FROM Members WHERE MemberId IN (100, 200, 300, 400);
SqlCommand cmd = new SqlCommand(cmdText);

Instead you should parameterize the sql. This involves two steps.

  1. Add parameters to the sql string.
  2. Add the parameters to SqlCommand.

Add a class to your project like SqlWhereInParamBuilder, I chose to use a static method and an extension method, but you can do it in any number of other ways.

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

namespace SqlWhereInParameters
{
    public static class SqlWhereInParamBuilder
    {
        public static string BuildWhereInClause<T>(string partialClause, string paramPrefix, IEnumerable<T> parameters)
        {
            string[] parameterNames = parameters.Select(
                (paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString())
                .ToArray();

            string inClause = string.Join(",", parameterNames);
            string whereInClause = string.Format(partialClause.Trim(), inClause);
            
            return whereInClause;
        }

        public static void AddParamsToCommand<T>(this SqlCommand cmd, string paramPrefix, IEnumerable<T> parameters)
        {
            string[] parameterValues = parameters.Select((paramText) => paramText.ToString()).ToArray();

            string[] parameterNames = parameterValues.Select(
                (paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString()
                ).ToArray();

            for (int i = 0; i < parameterNames.Length; i++)
            {
                cmd.Parameters.AddWithValue(parameterNames[i], parameterValues[i]);
            }
        }
    }
}

This is the usage

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

namespace SqlWhereInParameters
{
    class Program
    {
        static void Main(string[] args)
        {
            Program p = new Program();
            p.RunCommandBulderForFullSelectExample();
        }

        private void RunCommandBulderForFullSelectExample()
        {
            List<long> memberIds = new List<long> { 100, 200, 300, 400 };
            string parameterPrefix = "MemberId";
            string cmdText = "SELECT * FROM Member WHERE Name IN ({0})";

            cmdText = SqlWhereInParamBuilder.BuildWhereInClause(cmdText, parameterPrefix, memberIds);

            SqlCommand cmd = new SqlCommand(cmdText);
            cmd.AddParamsToCommand(parameterPrefix, memberIds);
        }
    }
}

How to fix ‘No database providers are configured’ when scaffolding a controller in ASP.NET 5

If got this error when trying to scaffold a new controller (MVC and Web Api) for an ASP.NET 5 web app using Visual Studio 2015.

There was an error running the selected code generator:
'No database providers are configured. Configure a database provider by overriding OnConfiguring in your DbContext class or in the AddDbContext method when setting up services.'

Scaffolding

MemeberContext was not part of the web application project, instead it was in a referenced class library. This was causing the problem.

To resolve it go to the Startup.cs file.

Inside the ConfigureServices(..) method add something like –

            services.AddEntityFramework()
            .AddSqlServer()
            .AddDbContext<MemberContext>(options =>
                options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]));

Of course you need to make sure your Data:DefaultConnection:ConnectionString is pointing to the right place.