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.

The type ‘xxxx’ is defined in an assembly that is not referenced. System.Runtime.

If you recognize the error from the title of this post, you can jump to the solution.

The problem

I have a ASP.NET 5 solution with two projects, a web application project and a class library project.
After adding the class library I was very surprised to get this error –

The type 'IEnumerable<>' is defined in an assembly that is not referenced. You must add a reference to assembly 'System.Runtime, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.

All I was doing was querying an Entity Framework context for some simple data and returning the result as an IEnumerable.

This is not the code but is close enough.

var members = _context.Members.Where(m => m.FirstName.Contains(firstName));

To verify that I wasn’t messing up something very simple I tried the same code from the web api controller in the web application, it compiled and worked fine. I looked in the references for both projects for an indication that I had left something out of my class library but could find nothing.

I checked the project.json for both and messed around in nuget for a while but all looked fine.

The project.lock.json files

Finally, I had a look in the project.lock.json files and noticed this section in the web application file.

{
  "locked": false,
  "version": 2,
  "targets": {
    "DNX,Version=v4.5.1": {
      "EntityFramework.Core/7.0.0-rc1-final": {
        "type": "package",
        "dependencies": {
          "Ix-Async": "1.2.5",
          "Microsoft.Extensions.Caching.Abstractions": "1.0.0-rc1-final",
          "Microsoft.Extensions.Caching.Memory": "1.0.0-rc1-final",
          "Microsoft.Extensions.DependencyInjection": "1.0.0-rc1-final",
          "Microsoft.Extensions.DependencyInjection.Abstractions": "1.0.0-rc1-final",
          "Microsoft.Extensions.Logging": "1.0.0-rc1-final",
          "Microsoft.Extensions.Logging.Abstractions": "1.0.0-rc1-final",
          "Microsoft.Extensions.OptionsModel": "1.0.0-rc1-final",
          "Remotion.Linq": "2.0.1",
          "System.Collections.Immutable": "1.1.36"
        },
        "frameworkAssemblies": [
          "Microsoft.CSharp",
          "mscorlib",
          "System",
          "System.Collections",
          "System.ComponentModel.DataAnnotations",
          "System.Core",
          "System.Diagnostics.Debug",
          "System.Diagnostics.Tools",
          "System.Globalization",
          "System.Linq",
          "System.Linq.Expressions",
          "System.Linq.Queryable",
          "System.ObjectModel",
          "System.Reflection",
          "System.Reflection.Extensions",
          "System.Resources.ResourceManager",
          "System.Runtime",
          "System.Runtime.Extensions",
          "System.Threading"
        ],

 

There in theĀ project.lock.json for the web application project is "System.Runtime" in the "frameworkAssemblies" section.

But the same section in the class library’s file did NOT have a "System.Runtime".

{
  "locked": false,
  "version": 2,
  "targets": {
    ".NETFramework,Version=v4.5.1": {
      "EntityFramework.Core/7.0.0-rc1-final": {
        "type": "package",
        "dependencies": {
          "Ix-Async": "1.2.5",
          "Microsoft.Extensions.Caching.Abstractions": "1.0.0-rc1-final",
          "Microsoft.Extensions.Caching.Memory": "1.0.0-rc1-final",
          "Microsoft.Extensions.DependencyInjection": "1.0.0-rc1-final",
          "Microsoft.Extensions.DependencyInjection.Abstractions": "1.0.0-rc1-final",
          "Microsoft.Extensions.Logging": "1.0.0-rc1-final",
          "Microsoft.Extensions.Logging.Abstractions": "1.0.0-rc1-final",
          "Microsoft.Extensions.OptionsModel": "1.0.0-rc1-final",
          "Remotion.Linq": "2.0.1",
          "System.Collections.Immutable": "1.1.36"
        },
        "frameworkAssemblies": [
          "Microsoft.CSharp",
          "mscorlib",
          "System",
          "System.ComponentModel.DataAnnotations",
          "System.Core"
        ],

I added "System.Runtime" into the project.lock.json of class library project and everything compiled and worked.

But, and this is a big but, theĀ project.lock.json file is generated from the project.json file and any changes to nuget or to the porject.json will lead to my project.lock.json being overwritten.

At least I know the culprit and now I had to figure out how to get that entry Ā forĀ "System.Runtime" to be generated and put into "frameworkAssemblies".

Note also that line 5 for the two files also differs, this I think is more telling problem.

 "DNX,Version=v4.5.1": { 

vs

 ".NETFramework,Version=v4.5.1": { 

Time to compare theĀ project.json files.

The project.json files

This is project.json in the web application project

{
  "version": "1.0.0-*",
  "compilationOptions": {
    "emitEntryPoint": true
  },

    "dependencies": {
        "EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
        "Member.Business": "1.0.0-*",
        "Member.DataLayer": "1.0.0-*",
        "Member.Domain": "1.0.0-*",
        "Microsoft.AspNet.IISPlatformHandler": "1.0.0-rc1-final",
        "Microsoft.AspNet.Mvc": "6.0.0-rc1-final",
        "Microsoft.AspNet.Server.Kestrel": "1.0.0-rc1-final",
        "Microsoft.AspNet.StaticFiles": "1.0.0-rc1-final",
        "Microsoft.CSharp": "4.0.0",
        "Microsoft.Extensions.Configuration.FileProviderExtensions": "1.0.0-rc1-final",
        "Microsoft.Extensions.Configuration.Json": "1.0.0-rc1-final",
        "Microsoft.Extensions.Logging": "1.0.0-rc1-final",
        "Microsoft.Extensions.Logging.Console": "1.0.0-rc1-final",
        "Microsoft.Extensions.Logging.Debug": "1.0.0-rc1-final"
    },

  "commands": {
    "web": "Microsoft.AspNet.Server.Kestrel"
  },

  "frameworks": {
    "dnx451": { },
    "dnxcore50": { }
  },

  "exclude": [
    "wwwroot",
    "node_modules"
  ],
  "publishExclude": [
    "**.user",
    "**.vspscc"
  ]
}

And this is in the class library project.

{
  "version": "1.0.0-*",
  "description": "Member.Business Class Library",
  "authors": [ "bryan" ],
  "tags": [ "" ],
  "projectUrl": "",
  "licenseUrl": "",
    "frameworks": {
        "net451": { },
        "dotnet5.4": {
            "dependencies": {
                "Microsoft.CSharp": "4.0.1-beta-23516",
                "System.Collections": "4.0.11-beta-23516",
                "System.Linq": "4.0.1-beta-23516",
                "System.Runtime": "4.0.21-beta-23516",
                "System.Threading": "4.0.11-beta-23516"
            }
        }
    },
    "dependencies": {
        "EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
        "Member.DataLayer": "1.0.0-*",
        "Member.Domain": "1.0.0-*"
    }
}

There are some serious differences between the two, most notably around the “frameworks” and “dependencies”. I have no idea why the two project.json files are so different.

Solution A and B

I changed my project.json in the class library project to the below adding the "frameworkAssemblies" node.

{
  "version": "1.0.0-*",
  "description": "Fund.FundEntitlement.Business Class Library",
  "authors": [ "bryan" ],
  "tags": [ "" ],
  "projectUrl": "",
  "licenseUrl": "",
    "frameworks": {
        "net451": {
            "frameworkAssemblies": {
                "System.Runtime": "4.0.10.0"
            }
        },
        
        "dotnet5.4": {
            "dependencies": {
                "Microsoft.CSharp": "4.0.1-beta-23516",
                "System.Collections": "4.0.11-beta-23516",
                "System.Linq": "4.0.1-beta-23516",
                "System.Runtime": "4.0.21-beta-23516",
                "System.Threading": "4.0.11-beta-23516"
            }
        }
    },
    "dependencies": {
        "EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
        "Fund.FundEntitlement.DataLayer": "1.0.0-*",
        "Fund.FundEntitlement.Domain": "1.0.0-*"
    }
}

An alternative that also works is changing the project.json in the class library to look more like the on from the web application project.

{
    "version": "1.0.0-*",
    "description": "Fund.FundEntitlement.Business Class Library",
    "authors": [ "bryan" ],
    "tags": [ "" ],
    "projectUrl": "",
    "licenseUrl": "",
    "frameworks": {
        "dnx451": { },
        "dnxcore50": {
            "dependencies": {
                "Microsoft.CSharp": "4.0.1-beta-23516"
            }
        }
    },
    "dependencies": {
        "EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
        "Fund.FundEntitlement.DataLayer": "1.0.0-*",
        "Fund.FundEntitlement.Domain": "1.0.0-*",
        "System.Collections": "4.0.11-beta-23516",
        "System.Linq": "4.0.1-beta-23516",
        "System.Runtime": "4.0.21-beta-23516",
        "System.Threading": "4.0.11-beta-23516"
    }
}

Now everything compiles and all is good. I’m sadly sure it won’t be the last time I have screw around with project.lock.json and project.json. For more info about those files see http://davidfowl.com/diagnosing-dependency-issues-with-asp-net-5/

ASP.NET 5 Web Api Controller with multiple get methods

I was recently working on a Asp.Net 5 Web.Api application and needed a controller with multiple get methods.
I wanted to have something like GetByAdminId(int adminId) and GetByMemberId(int memberId) (yes I know people will say that you should have two controllers and maybe even two webservices, but that is the scenario I was faced with).

Of course this should not be the most difficult problem, but it was not obvious either.

Here is the solution.

using Microsoft.AspNet.Mvc;

namespace ControllerWithMultipleGetMethods.Controllers
{
    [Route("api/[controller]")] /* this is the default prefix for all routes, see line 20 for overriding it */ 
    public class ValuesController : Controller
    {
        [HttpGet] // this api/Values
        public string Get()
        {
            return string.Format("Get: simple get");
        }

        [Route("GetByAdminId")] /* this route becomes api/[controller]/GetByAdminId */
        public string GetByAdminId([FromQuery] int adminId)
        {
            return $"GetByAdminId: You passed in {adminId}";
        }

        [Route("/someotherapi/[controller]/GetByMemberId")] /* note the / at the start, you need this to override the route at the controller level */
        public string GetByMemberId([FromQuery] int memberId)
        {
            return $"GetByMemberId: You passed in {memberId}";
        }

        [HttpGet]
        [Route("IsFirstNumberBigger")] /* this route becomes api/[controller]/IsFirstNumberBigger */
        public string IsFirstNumberBigger([FromQuery] int firstNum, int secondNum)
        {
            if (firstNum > secondNum)
            {
                return $"{firstNum} is bigger than {secondNum}";
            }
            return $"{firstNum} is NOT bigger than {secondNum}";
        }
    }
}

Adding class libraries to an ASP.NET 5 web application

When adding a class library to an ASP.NET 5 application you might get an error like

“The following projects are not supported as references: – The project ClassLibrary1 has a target framework that is incompatible or has a version higher than the current project and cannot be referenced”

Project reference error

The cause is most likely that when you created the project ClassLibrary1 you selected “Class Library” as the type of project as shown here.

Class Library

Instead, you should have selected “Class Library (Package)”.
Class Library (Package)

Entity Framework with Proper Foreign Key Names

Full source code here.

One of the very nice features of Entity Framework is the navigational properties. But it is easy to set them up in a less that optimal way. If you have ever ended up with a foreign key column looking something like – TableName_TableNameId, for example Account_AccountId instead of just AccountId it’s probably because you missed a property on the related entity.

Here is the Account class with a collection of Phones and Addresses.

    public class Account
    {
        public Account()
        {
            Addressess = new List<Address>();
            Phones = new List<Phone>();
        }

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Guid AccountId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }

        public virtual ICollection<Phone> Phones { get; set; }
        public virtual ICollection<Address> Addressess { get; set; }
    }

The Phone has a navigation property to Account, but it does not have a property for the for the AccountId, this results in a foreign key named Account_AccountId in the table. This works just fine, but doesn’t look quite right.

    public class Phone
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Guid PhoneId { get; set; }
        public string Number { get; set; }

        // No AccountId defined
        // Entity Framework will add a foreign key to Phones table called Account_AccountId

        // Navigation property
        public virtual Account Account { get; set; }
    }

The better way is to add a property for the AccountId to the linked entity, resulting in a foreign key named AccountId.

    public class Address
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Guid AddressId { get; set; }
        public string Line1 { get; set; }
        public string Line2 { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string ZipCode { get; set; }
       
        // This will be the name of the foreign key in the table
        public Guid AccountId { get; set; }
        
        // Navigation property
        public virtual Account Account { get; set; }
    }

Here are the created tables, as you can see the Phones table will has the poorly named foreign key and the Address table has the properly named foreign key.

Good And Bad Foreign Keys

Full source code here.

Entity Framework Migrations with an existing database

Introduction

If you read my post on Entity Framework migrations, you might be saying, “that’s amazing, but that’s all very well for new projects, but we’ve got eighty tables in place already and that code first migration ship has sailed”.
But no, you can still make use of Entity Framework migrations, it takes a some extra work, but it is well worth the effort.

If you already have POCOs that represent your databases tables and they really match your tables, you might be able to skip this step.

Reversing the Database

If your POCOs don’t match the database you can use the database tables to generate POCOs for you.

There are plenty of tutorials on Entity Framework’s reverse engineering tool. So I won’t go into too much detail.

Create a new solution with a console application as the startup project.
Set the connection string in your app.config to point to the your reference database (this is the one that has the all the tables you are currently using).

Grab the Entity Framework Power Tools Beta 4 (https://visualstudiogallery.msdn.microsoft.com/72a60b14-1581-4b9b-89f2-846072eff19d/). Use this to reverse engineer the tables to POCOs. Review the outputted classes, mappings and context to make sure that they were generated the way you expect.

Notice that there is nothing on the POCOs to suggest where there should be indexes. Inferred primary key and foreign keys will be added in by the migration by default. But others will not.

First Migration

Go to the package manager and Enable-Migrations.
Enable-Migrations

Then add the first migration, Add-Migration InitalCreate, this is the migration that can be used to generate the tables.
Add-Migration

But, it is probably not complete, it’s going to be missing indexes as mentioned above, stored procs and more.

For example the Vehicle table looks like this
Table Layout

And has an indexes on VehicleID, TyreTypeIDa and Name, but the migration did not pick up the Name index.

Testing the migration

Change the connection string to point to new database.
And run Update-Database
Update-Database

You now have a new database based on the migration.
Open your SQL Server Management Studio, right click on the reference database and click Tasks, then Generate Scriptsā€¦
Generate-Script A

Click next and the choose the tables to script out.
6.Generate-Script B

Click next and then Advanced, scroll to the bottom of the list and turn on Script Indexes and any other feature you may need (eg, triggers).

7.Generate-Script C
Save the output to single file named ReferenceDB.sql

Follow the same steps to generate a script based on the new database.

Compare the files in a text comparison tool and you’ll see differences like this
Comparison

The index on the Name column is missing in the new database.

No problem. Go back to the migration and add it in.

Now you have two choices here, you can add a new migration to alter the existing table, or you can change the initial migration.
I suggest changing the InitalCreate migration, because you should try to get the this first migration to be as close as possible to the existing database.

Adding an index to a CreateTable call is as simple as adding one line at the end.

CreateTable( 
			"dbo.Vehicle", 
			c => new 
				{ 
					VehicleID = c.Int(nullable: false, identity: true), 
					Name = c.String(nullable: false, maxLength: 50), 
					Description = c.String(maxLength: 200), 
					MSRP = c.Double(nullable: false), 
					TyreTypeID = c.Int(nullable: false), 
				}) 
			.PrimaryKey(t => t.VehicleID) 
			.ForeignKey("dbo.TyreType", t => t.TyreTypeID, cascadeDelete: true) 
			.Index(t => t.TyreTypeID) 
                        
                         // this last line is the index I added by hand. 
			.Index(t => t.Name, name: "IX_Name");

Drop the new database the run Update-Database.

The Vehicle will now be created with the new index.
You could add the index to the POCOs, but you might want to consider how this would affect any move away from Entity Framework. See Code First Data Annotations for more.

For a full list of what you can do with migrations see DbMigration Methods.

Remember that you can run arbitrary sql with the Sql method or even from the SqlFile method. This gives you full flexibility when using Entity Framework migrations.