The Foreign Key on table with columns could not be created because the principal key columns could not be determined

The Foreign Key on table with columns could not be created because the principal key columns could not be determined. Use the AddForeignKey fluent API to fully specify the Foreign Key.

This error takes place when the code first model is trying to create a foreign key to a table that is not included in the model. Since Entity Framework (EF) can’t find the model that contains the primary key association, it raises the error. 

To enable a foreign key to a non-existent model in you project, just use the fluent API to add or remove the constraint. This enables us to basically run SQL queries on the target database. To illustrate this problem, let’s take a look at the following model:

    [Table("App")]

    public partial class App

    {      

        [Key]

        public int Id { get; set; }      

        [Required]

        [StringLength(150)]

        public string Name { get; set; }

        /// <summary>

        /// add this field as a FK to the dbo.Roles table

        /// which does not exist in my model

        /// </summary>       

        public string RoleId { get; set; }       

    }

We want to create a foreign key on the RoleId field, but we do not have the dbo.Roles table model defined in the project. The migration code attempts to do that by just calling the fluent API  ForeignKey  as shown below:

CreateTable(

                "dbo.App",

                c => new

                    {

                        Id = c.Int(nullable: false, identity: true),

                        Name = c.String(nullable: false, maxLength: 150),

                        RoleId = c.String(),

                    })

                .PrimaryKey(t => t.Id)

                .ForeignKey("dbo.MyRoles", t => t.RoleId).Index(t => t.RoleId);

The highlighted line generates the error because the mapping between RoleId to the primary key on the dbo.Roles table is not defined or is unknown by EF.  To make it explicit, we can change the code as the error suggests to using the AddForeignKey API and removing the fluent method ForeignKey as follows:

CreateTable(

                "dbo. App",

                c => new

                    {

                        Id = c.Int(nullable: false, identity: true),

                        Name = c.String(nullable: false, maxLength: 150),

                        RoleId = c.String(),

                    })

                .PrimaryKey(t => t.Id).Index(t => t.RoleId);;

AddForeignKey("dbo.App", "RoleId", "dbo.Roles","Id");

We should notice how on this method we are explicitly indicating that we want to map the RoleId column to the Primay key column of Id on the Roles table.

We can now build the code and run the database update preview to see what SQL script would be executed by using this command:

Update-Database  -script

Note: Run this on the Package Manager Console

Thanks for reading.

Database Project build fails Could not load file or assembly Microsoft.SqlServer.TransactSql, Version=12.0

This error can take place when Visual Studio 2013 is updated to Update 4 and extended Transact-SQL verification is enabled on the database project.

Error message:

System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.TransactSql, Version=12.0.0.0

To fix this issue, uncheck the Enable extended Transact-SQL verification option from the project properties.  If you wonder why this happened, the feature was deprecated and has been removed from the latest SQL Server tooling update which is what takes place when using VS2013 Update 4.

Database Project Properties

Microsoft Reference VS2013 Update 4:

 http://support.microsoft.com/kb/2994375

ActiveDirectory Membership Provider a referral was returned from the server

The “A referral was returned from the server” error usually means that there is a conflict between the IP address and the domain controller (DC) defined on the connection string.

To illustrate the problem, we define two IP addresses hosted on different domains:

IP Address	DC Name	Notes
172.1.1.10	ozkary.com	Production domain
172.1.30.50	ozkaryDev.com	Development domain

If we defined a LDAP connection string with this format:

LDAP://172.1.1.10:389/OU=USERS,DC=OZKARYDEV,DC=COM

This will generate the error because the IP is actually on the OZKARY DC not the OZKARYDEV DC. To correct the problem, we would need to use either one of these valid connections:

Connection String	Comments
LDAP://172.1.1.10:389/OU=USERS,DC=OZKARY,DC=COM	Production setting
LDAP://172.1.30.50:389/OU=USERS,DC=OZKARYDEV,DC=COM	Development Setting

That should address the problem.

Self-Describing ENUM Types on MVC application

For project download see link at end or article.

We are familiar with the use of enumerated type fields to provide category information.  For developers, the enum type may provide enough information to understand its meaning. However, it would be ideal if the enum type could be self-descriptive and provide a detail message of what it really means which can be achieved with the use of extension methods.

Let’s start by looking at this enum type declaration:

public enum EnumProjectType:int

{

Web =0,

Windows=1,

Console=2,

Mobile=3,

Cloud=4

}

We know the categories that are available, but let’s say that we would like to also show more information about each of one those entries. Web category is very general, so we would like to query it and ask more about what it contains. Let’s work on an extension method to see how we can extend this type to be more descriptive.

public static class EnumProjectTypeExtension

{

public static string Describe(this EnumProjectType type)

{

string content = type.ToString();       //default to the type

try

{

content = ResProjectType.ResourceManager.GetString(content, ResProjectType.Culture);

Trace.TraceInformation(String.Format("Message status {0} content {1}", type, content));

}

catch (Exception ex)

{

Trace.TraceError(String.Format("Message status {0} error {1}", type, ex.Message));

}

return content;

}

}

This extension method uses the enumerated type instance to call the Describe static method which provides more information about the category. Here we associate each category to a particular content, and we would also like to be flexible and allow for the content to change. For that, we can use a resource file and associate each category as a string resource with a more descriptive message. Look at the resource table below:

 

Category Description

We can now use the ResourceManager helper methods to look for that resource and load the value.

To show how that would work, we can quickly build a list with each enum category and corresponding description as follows:

//list of the categories

foreach (EnumProjectType type in EnumProjectType.GetValues(typeof(EnumProjectType)))

{

var item = new ProjectTypeItem()

{

Id = (int)type,

Description = type.Describe(),   //load the description

Tag = type.ToString()

};

list.Add(item);

}

The code just iterates the categories and calls the Describe extension method. We can then use this list and show the content on the web page as shown below:

Demo Application

We can now see that the enum type can tell us more about itself with the help of the extension method.   Since we are also using resource files, we can use localization of the resources to support other languages.

CodeProject

Well that is some descriptive enum type.

Demo Project Reference:

Available at:  GitHub  (see Dev Branch for latest changes)

Project Reference: og.samples.library.EnumTypes

Run And Select this option.

Run Demo