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.

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

Read Article →

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

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

Read Article →

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.

The “A referral was returned from the server” error usually means that there is a conflict between the IP address and the domain controller...

Read Article →

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

For project download see link at end or article. We are familiar with the use of enumerated type fields to provide category information.  F...

Read Article →

Use Sql Server named instance as local to avoid changing the connection strings

When working on a team project, we often come across the issue that the database connection string in the configuration file uses the default instance name, but our development database is actually using a named instance (see below)

Default or Named Instance

Default Instance	Named Instance
Localhost	localhost\sqlexpress

Some developers just create a default instance and continue on their work. Others would just change the configuration file or have a local copy of the configuration file to match their environment and move on.

There is another approach that we can use which would use less resources (in the case of another instance), and it is a lot more convenient than having to manage multiple configuration files. We can use named pipes configuration to change the pipe name from the instance name to the default instance.

Named Pipes Configuration

When connecting to the default instance, SQL Server uses the default pipe name of "\.\pipe\sql\query". A named instance uses a different pipe name as listed below:

Pipe Name (Default)	Pipe Name (Instance)
\\.\pipe\sql\query	\\.\pipe\MSSQL$SQLEXPRESS\sql\query

We can use SQL Server Configuration Manager to first enable the named pipes setting and update the pipe name. This can be done as follows:

Select SQL Server Network Configuration (see pic below for details)

• Click protocols for (Instance Name)
• Double click on Named Pipes
•             Set Enabled to Yes
•             Update the pipe name
•             Apply the changes.
•             Restart the SQL Server service - SQL Server (SQLEXPRESS)

After making those changes, we can try to connect to our database using SQL Server Management Studio. We can try to connect to the database with both (local) and (local)\express for server name, and both connections should be successful.

Thanks for reading.

When working on a team project, we often come across the issue that the database connection string in the configuration file uses the defau...

Read Article →

ASP.NET MVC 5 Passwords must have at least one non letter or digit

If you are trying to register a new user, and you are getting the error below:

The new ASP.NET MVC 5 web templates are using the Identity framework to do the validation and password policy. This message indicates that the password policy requires a special character. In the event that our password policy does not need such requirements, we can disable this constrain.

To disable this constrain, we can follow these steps:

• Open the identity_config.cs files under the App_start folder
• Search for the string manager.PasswordValidator
• We should see this code snippet:

// Configure validation logic for passwords

manager.PasswordValidator = new PasswordValidator

{

RequiredLength = 6,

RequireNonLetterOrDigit = true,

RequireDigit = true,

RequireLowercase = true,

RequireUppercase = true,

};

• Set the RequireNonLetterOrDigit = false
• Save and compile the application

The password requirements should now be a bit more flexible, and the error should no longer be displayed.

I hope that helps.

If you are trying to register a new user, and you are getting the error below: The new ASP.NET MVC 5 web templates are using the Id...

Read Article →

This project references NuGet package(s) that are missing on this computer

This project references NuGet package(s) that are missing on this computer. Enable NuGet Package Restore to download them.  

When getting this error, Visual Studio indicates that a NuGet package is missing from your project references.  If you take a look at the project->references node, you will probably see that there are some missing libraries that have been added to the project via a NuGet package installation, and your system does not have the package.  These files may even be the core .Net libraries like System, System.Xml etc.

To address this problem, we can just follow these steps:

• Select Solution Explorer
• Right click on the Solution Name
• Click Enable NuGet Package Restore

This tells Visual Studio that whenever it finds a missing package, it needs to download it. After this has been enabled, we need to reload each project that has the missing references. This can be done by closing and opening the solution which will reload all the projects or just unloading and reloading each specific project (right click on the project name to see the unload project option).

Another area to make sure that the Package restore feature is enabled is to look for the .nuget folder in the solution’s directory. There should be a Nuget.targets file which manages the restore.

I hope this helps.

This project references NuGet package(s) that are missing on this computer . Enable NuGet Package Restore to download them.   When ge...

Read Article →