Working with Foreign Keys when using EntityFramework Code First Migrations

One issue I’ve come across lately while working with Entity Framework Migrations has to do with foreign key relationships. If you’ve ever done any reconfiguration of your schema, you know you probably need to update your migration files to get all the data loaded correctly. Let’s take a simple example:

Let’s say you have an Order model for all of your orders defined as such:

public class Order
{
    public int ID { get; set; }
    public string OrderNumber { get; set; }
    public DateTime? ShipDate { get; set; }
}

This is obviously a very contrived example and a real order would have a lot of other information, but it works for this example.

Now let’s say you add a new ordering customer and you want to distinguish orders by Customer (probably a good thing to do!). Here is the Customer object:

public class Customer
{
    public int ID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Order> Orders { get; set; }

    public Customer()
    {
        Orders = new List<Order>();
    }
}

Now we need to modify our order by adding a CustomerID column. The result looks as you’d expect:

public class Order
{
    public int ID { get; set; }
    public string OrderNumber { get; set; }
    public DateTime? ShipDate { get; set; }
    public int CustomerID { get; set; }
    public virtual Customer Customer { get; set; }
}

You’ll need to properly set up your mapping where ever you have that defined. There are multiple ways to do this, but the approach I prefer is to have mapping files defined separate from my POCOs (Plain old C# objects, the classes defined above) and then add them in the OnModelCreating function of my Context class like this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{    
    modelBuilder.Configurations.Add(new OrderMap());
    modelBuilder.Configurations.Add(new CompanyMap());
    //other mapping classes
}

Inside the constructor of my OrderMap class, this line of code will add the relationship between Customer and Order:

this.HasRequired(t => t.Customer)
      .WithMany(t => t.Orders)
      .HasForeignKey(d => d.CustomerID);

Now, with all that setup, if you add a migration, Entity Framework will scaffold the changes required to make all this happen.

You’ll probably want to modify a couple of things, though.

For starters, you’ll want to setup your customers and set all of your existing orders to use the Customer ID associated with the orders that already exist in the system. Also, you have to do this before adding the Foreign Key between Order and Customer because after you add the new column (it is non-nullable), all CustomerID fields will be “0” in your Orders table.

Your migration might look something like this (EF will also add some indexes, I am showing this for the sake of brevity):

public partial class AddCustomerToModels : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.Orders", "CustomerID", c => c.Int(nullable:false));
        CreateTable(
		"dbo.Customers",
		c => new
                {
                    ID = c.Int(nullable: false),
		    Name = c.String(nullable: false, maxLength: 50)
		})
                .PrimaryKey(t => t.ID);
        
	AddForeignKey("dbo.Orders", "CustomerID", "dbo.Customers", "ID");
    }

    public override void Down()
    {
        //code to reverse the migration goes here
    }
}

You’ll need to add code to update those customers. I usually write a line of Sql like the following, and place it after the create table but before the AddForeignKey call:

Sql(@"INSERT INTO Customers (ID, Name) VALUES (1, 'Acme');
INSERT INTO Customers (ID, Name)
VALUES (2, 'Evil Corp')

UPDATE Orders SET CustomerID = 1");

This will then get everything in your database ready to add that foreign key constraint. Of course, if you don’t like hardcoding company information into your migrations (not a great practice, really), you can do this after the fact, but sometimes you already have all the data in your database and just need to move it around due to a schema change. This is, again, a bit of a contrived example.

Now, to the tricky part I really want to highlight: you have to be really consistent in the way you add foreign keys.

For example, these two lines are slightly different – the first one uses the schema name in both the dependent and principal tables, while the second only does in for the dependent table:

AddForeignKey("dbo.Orders", "CustomerID", "dbo.Customers", "ID");

AddForeignKey("dbo.Orders", "CustomerID", "Customers", "ID");

The foreign key names they generate are as follows:

[FK_dbo.Orders_dbo.Customers_CustomerID]

[FK_dbo.Orders_Customers_CustomerID]

If you later try to drop a foreign key and don’t use the same exact format as you did when setting it up, you will encounter errors – usually something like:

The object ‘FK_dbo.Orders_dbo.Customers_CustomerID’ is dependent on column ‘CustomerID’.
ALTER TABLE DROP COLUMN CustomerID failed because one or more objects access this column.

So the moral here is to be very consistent with your foreign key naming scheme. If you’ve got an old database that you’ve added code first to after the fact, you’ll probably have a lot of relationships that don’t use the schema name in the key name, so you’ll run into this frequently if you’re modifying your schema.

How to bind multiple SSL Certificates to port 443 for different domains/subdomains on the same IP address on an IIS Server

The title is a bit of a mouthful, but I’ve recently encountered a situation where I had multiple SSL certificates I wanted bound to two different domains being hosted on the same server with the same IP address.

Using IIS’s Internet Information Services Manager UI application in IIS 7 (not sure if this applies to newer versions), you can assign a binding for an SSL certificate to port 443, but you can only enter an IP address and not the host-header information:

In order to accomplish this, you have to use command line tools. Below is a great resource I found that helped me solve this problem. I’ll pull out the most relevant command:

https://blogs.iis.net/thomad/ssl-certificates-on-sites-with-host-headers

appcmd set site /site.name:”MySubDomainSite” /+bindings.[protocol=’https’,bindingInformation=’*:443:mysubdomain.mysite.com’]

In this example, “MySubDomainSite” is the site you have defined in IIS for the subdomain (or domain) where you are trying to assign the second certificate.

Killing all connections to a SQL Server Database

One issue I’ve run across frequently during development is restoring a database to a newer state. Often, when I want perform the restore, there are active connections to my development database, so restoring will fail.

Of course, StackOverflow had the answer to this, but I’ve searched for the solution to this problem enough times where it made sense for me to finally write it down.

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

User AlexK posted this excellent solution:

For MS SQL Server 2012 and above

USE [master];

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('MyDB')

EXEC(@kill);

For MS SQL Server 2000, 2005, 2008

USE master;

DECLARE @kill varchar(8000); SET @kill = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'  
FROM master..sysprocesses  
WHERE dbid = db_id('MyDB')

EXEC(@kill); 

 

Fix: Visual Studio doesn’t remember last open documents

After installing Visual Studio 2017 a few months back, I noticed that some projects were loading strangely, while others loaded just fine. The two main issues I experienced were:

  • Documents I had open on my previous run of VS wouldn’t load upon running the Visual Studio 2017 application
  • Windows I had arranged in my multi-monitor layout were not loading where I expected them

A quick Stack Overflow search led me to the answer regarding the first: the .suo file had become corrupt and needed to be deleted. Once I knew that, the trick was finding the .suo file:

  1. From the directory containing your solution file (.sln), open the folder named “.vs”.
  2. In the “.vs” folder, open the folder that has a name matching your solution name.
  3. Inside the solution folder, there may be multiple folders, one for each version of Visual Studio
    1. v14 is for Visual Studio 2015
    2. v15 is for Visual Studio 2017

These folders will contain your .suo file, which is hidden by default in Windows, so you need to enable “Show hidden files, folders, and drives” in your Folder options in order to see it. For instructions on that (Win 7,8, or 10), see the following article: https://www.howtogeek.com/howto/windows-vista/show-hidden-files-and-folders-in-windows-vista/

After you’ve found the .suo, go ahead and delete it (make sure Visual studio is not running with the solution open). A new one will be created for you when you open the solution file the next time.

I still haven’t found a solution to my second issue (I will definitely write about it if I find one).

What the SUO (Solution User Options) file controls

After solving my problem, I decided to take a look at the responsibilities of the .suo file. Microsoft’s documentation (VS 2015 version – 2017 isn’t available at the time of this writing) isn’t very forthcoming in detailing what exactly the SUO is doing. Based on digging around on the web, it seems that the following are its responsibilities (among others):

  • Remembers last open files
  • Remembers breakpoints
  • Remembers expanded nodes in solution explorer
  • Remembers startup project
  • Remembers last open tool windows and their positions
  • Remembers watch window contents

The file is encoded and not human-readable, so it’s not something you can simply hack around with like you can a solution (.sln) or project (.xxproj) file. It should not be added to version control.

How to Fix credential validation issue on Azure WebJob renewal of Let’s Encrypt Certificate

A while back, I posted about setting up SSL encryption for free with Azure and Let’s Encrypt: Let’s Encrypt + Azure = Win!

This has been working smoothly for me since I set it up, but I noticed that errors started popping up in the log recently. Here is part of the stack trace:

Microsoft.Azure.WebJobs.Host.FunctionInvocationException: Microsoft.Azure.WebJobs.Host.FunctionInvocationException: Exception while executing function: Functions.RenewCertificate —> Microsoft.IdentityModel.Clients.ActiveDirectory.AdalServiceException: AADSTS70002: Error validating credentials. AADSTS50012: Invalid client secret is provided. Trace ID: 958b11ab-839d-4a8d-97e6-fad1c3df0300 Correlation ID: e3f7c035-8978-4aa2-b01a-5c8fc74661ac Timestamp: 2017-05-31 14:14:26Z —> System.Net.WebException: The remote server returned an error: (401) Unauthorized. at System.Net.HttpWebRequest.GetResponse() at Microsoft.IdentityModel.Clients.ActiveDirectory.HttpWebRequestWrapper.

It turns out that the API Key I had setup for my application registration had expired. I had to create a new key with no expiration and then update my Web Applications’ settings with the new Client secret. The exact steps I took are listed below:

  1. Login to Azure
  2. Navigate to “App Registrations”
  3. Choose the Registration you need to update
  4. Click the “settings” icon (or “All Settings” button)
  5. Choose “Keys” under API Access
  6. Type a description into the new row, choose “Never” under the duration drop down and then hit “Save” above.
  7. Once saved, copy the value (it won’t be visible again if you don’t copy it now)
  8. (Optional) delete your old key
  9. Navigate to the Azure App Service that has the web job that registers your SSL certificate
  10. Choose “Application Settings” from the menu
  11. Scrolling down to where you have a setting titled something like “letsencrypt:ClientSecret” (assuming you did the setup as in the article linked at the top) and paste the value you copied into the second text box
  12. Click “Save” above

Once you’re done, the web job should work the next time it runs. For another explanation with some pictures of the process, check out this blog post here: Let’s Encrypt on Azure Web Apps – Key Expiration Issue.

EntityFramework – Grouping by Date Ranges

If you’ve ever created an outstanding balance report or other report that deals with aggregating data into date ranges, you’ll know that it isn’t immediately obvious how to structure your query, whether using SQL or LINQ (at least, it wasn’t to me).

My initial thought was to run multiple queries (one for each time range) and munge the results together. However, an elegant solution is to use SQL’s CASE expression to group date ranges together.

Let’s say you wanted a report that summed the amount of unpaid invoices in 20 day groupings (0-19 days past due, 20-39 past due, 40+). You could write something like this:

SELECT DaysSinceDueRange, SUM(Amount)
FROM (SELECT CASE WHEN GETDATE() - DueDate < 20 THEN 0
		  WHEN GETDATE() - DueDate BETWEEN 20 AND 39 THEN 20
		  WHEN GETDATE() - DueDate > 39 THEN 40
             END AS DaysSinceDueRange,
             Amount
       FROM Invoices
       WHERE Unpaid=1) inv
GROUP BY DaysSinceDueRange

This is really elegant, but then the question becomes how to do this with an ORM like EntityFramework. There are a couple of tricks required here:

  1. To do the date comparisons, EntityFramework requires the usage of System.Data.Entity.DbFunctions.DiffDays method (in EF 6 – it used to be in System.Data.Objects.EntityFunctions). If you try to do something like (DateTime.Now – invoice.DueDate).TotalDays, you’ll get an exception “DbArithmeticExpression arguments must have a numeric common type” because the subtraction operator is not defined for Dates in SqlServer.
  2. To do CASE / WHEN / THEN / END in EntityFramework, you have to make use of a lot of ternary operators. It can be kind of ugly, but if you write your code well enough, it should be fairly readable (or at least as readable as the SQL expression).

Here is an example of the SQL above translated into LINQ:

Context.Set<Invoice>()
       .Where(inv => inv.Unpaid)
       .Select(inv => new
       {
           DaysSinceDueRange = DbFunctions.DiffDays(inv.DueDate, DateTime.Today) < 20 ? 0 :
                               DbFunctions.DiffDays(inv.DueDate, DateTime.Today) >= 20 && 
                                   DbFunctions.DiffDays(inv.DueDate, DateTime.Today) < 40 ? 20 : 
                               40,
           Amount = inv.Amount
       }).GroupBy(inv => inv.DaysSinceDueRange)
       .Select(g => new
       {
           DaysSinceDueRange = g.Key.DaysSinceDueRange,
           Amount = g.Sum(inv => inv.Amount)
       });

Of course, you can get more complicated in a hurry, but I think this is a pretty elegant way to handle grouping data by date ranges.