Mapping navigation property to a different name of the database’s foreign key (Entity Framework 5)

In some rare case I had to map an entity to a column which doesn’t have the same name. What can you do if the property name of your entity is not the same as the table field? Let’s take a simple exemple of an entity “Muscle” which has many “Exercise” linked. A single exercise is related to a single required muscle.

modelBuilder
    .Entity<Exercise>()
    .HasRequired(b => b.Muscle)
    .WithMany(m => m.ExerciseRelated)
    .IsIndependent()
    .Map(p=>p.MapKey(m => m.MuscleID, "Muscle_ID")
    ;

In the example above, you can see that Exercise marks its Muscle property to be required and set the Muscle entity to have multiple Exercise. If you want to not have both side to link to each other, you can let the WithMany parameter to nothing, which would mean that only Exercise has a Muscle association, and not Muscle to Exercises.

The next keywork in the Linq statement is IsIndependent which specify to Entity Framework that the foreign key is not the one you expect to and that you will map to another name. But, this keywork doesn’t exist anymore with Entity Framework 5.

Consolidation of IsIndependent in the Code First relationship API. When configuring relationships in Feature CTP5 the IsIndependent method was used to identify that the relationship did not have a foreign key property exposed in the object model. This is now done by calling the Map method. HasForeignKey is still used for relationships where the foreign key property is exposed in the object model.

So the code above with Entity Framework 5.

modelBuilder
    .Entity<Exercise>()
    .HasRequired(b => b.Muscle)
    .WithMany(m => m.ExerciseRelated)
    .Map(p=>p.MapKey("Muscle___ID"))
    ;

So instead of using the default navigation name which should have been “Muscle_ID” (Entity name + primary key with underscore between), Entity Framework will use “Muscle_ID”.

Beware of the HasColumnName which is for property name that is not the same which is nothing related with association. The map keyword shines for association only.

modelBuilder
    .Entity<Exercise>()
    .Property(x => x.Muscle)
    .HasColumnName("MuscleX");

How to load hierarchical structure with recursive with Entity Framework 5

Everything has many way to do it, here is two ways to handle data structure that is recursive. The structure in which the solution that I will present you work is a structure that look like a tree. A main parent node with children in which can have entity or/and have itself a parent node to start a sub-tree. Also, children cannot contain parent that are already used somewhere in the tree, which could raise additional problems, like having infinite recursion.

hiearchicalStructure

In the graphic above, you can see that we have two types of container. One is green and the other one is having a white background. In fact, the green container is a node that can have either children of a specify entity which cannot contain any other structural entity or can contain an other green container which are those who contain a list of children.

So, since we do not know how the tree is structured, it’s not possible to use eager loading as we do normally by including property desired.

_context.Parent
		.Include(d => d.OtherProperty)
		.Include(d => d.Children)
		.Include(d => d.Children.Select(dd=>dd.OtherProperty)
		.Include(d => d.Children.Select(dd=>dd.Children)
		.Include(d => d.Children.Select(dd=>dd.OtherProperty........) //We cannot proceed this way because we do not know how many level
		.Include(d => d.Children.Select(dd=>dd.Children.......)//We cannot proceed this way because we do not know how many level
		.Single(p => p.ID == id);

This give us the option to load the Children, if this one has children then load it the way we just load the parent since every child become a parent. This require recursive method. The problem is that it work but every load will create a new parent. We need to map every values to the first parent to have at the end a single hierarchical tree.

Here is how we can do it with eager loading. We need to every property set back to the object that we have receive to, at the end, have a tree fully loaded.

private Parent RecursiveLoad(Guid id)
{
	var ParentFromDatabase = _context.Parent
		.Include(d => d.Children)
		.Include(d => d.Children.Select(dd => dd.OtherProperty))
		.Single(p => p.ID == id);
	
	foreach (var child in ParentFromDatabase.Children)
	{
		var childNotLoaded = child;
		var childFullyLoaded = _context.Child
			.Include(d => d.Parent)
			.Include(d => d.OtherProperty)
			.Single(d => d.ID == childNotLoaded.ID);

		child.OtherProperty = childFullyLoaded.OtherProperty;		//Require to set back the value because we want by reference to have everything in the tree
		child.Parent = RecursiveLoad(childFullyLoaded.Parent.ID);	//Require to set back the value because we want by reference to have everything in the tree
	}
	return ParentFromDatabase;
}

But, we can do better with explicit loading. One of the positive characteristic of explicit loading is that it load itself. No need to map the loaded object to the existing one, it’s already been loaded into it.

private Parent RecursiveLoad(Parent parent)
{
	var ParentFromDatabase = _context.Entry(parent).Collection(d=>d.Children);//Children are loaded, we can loop them now
	
	foreach (var child in parent.Children)
	{
		_context.Entry(child).Reference(d=>d.OtherProperty).Load();
		RecursiveLoad(child);
	}
	return ParentFromDatabase;
}

This has the advantage to remove every mapping since the object is loaded itself by entity framework. Still, this kind of loading come with a price. If we have 40 leafs in the tree, this mean that every of them will be loaded by the database which result to 40 SQL queries. One approach that can reduce the amount of request is to have the ID (int or Guid) inside the object, from there you can check if this one has a value. This will reduce the amount of call to the database at the amount of parent only (not final leaf which return 0 element). Still, the amount is huge and for large application, a custom solution with a view returning a bunch of data and parsed manually may be a good solution. Nevertheless, if you need to save the tree, you could end up with problem which you do not have when having the whole structure loaded by Entity Framework.

Installing IIS on Windows 7 Home Edition and running your Asp.Net MVC application

If you go to the administrator panel, you may not see IIS anywhere. To make sure, go to Control Panel\All Control Panel Items\Administrative Tools\

iisCongifurationPanel

If you have IIS, you will see an icon with the text “Internet Information Services (IIS) Manager”. If not, you will have to install it, which is already part of Windows 7, it’s just not enabled. To install it, go to Control Panel\All Control Panel Items\ and select Programs and Features. In the left side bar, you will see a link called “Turn Windows features on or off”. Select this open.

turningOnFeatureWindows

You have to select IIS and go into subfolder to select what you see in the following image.

IISFeatures

This will take several minutes depending of your machine. When everything is done, the control panel will now have in the administrative tools IIS.

iisOpen

So far, you have IIS, but you doesn’t have your website linked to it. If you are a developer, the best way to make it works is to link IIS to the path where your code reside. I prefer not having my code in My Document because it will cause some security problem with default configuration. In fact, the user on which IIS will run the application pool won’t have access to your file inside your My Document. Nevertheless, we can run IIS with an application pool that use your credential. First, open IIS and create a new website to which you will select the root of your web application (where the solution is). Be sure to have the website running on the good framework. I have found that it select framework 2.0 by default which should be the latest framework (4.0) if you run a MVC website. If you are using code inside your My Document, you also need to set your application pool to your account.

identityAppPool

Now, if you try to run your website, you should be able to see it. If you get this message :

IIS – this configuration section cannot be used at this path

It means that you haven’t select every options in the image above when installing IIS. The good new is that it’s not too late. You need to go back into the Windows Features and select the Application Development Features. If you get the error message : HTTP Error 500.19 – Internal Server Error – The requested page cannot be accessed because the related configuration data for the page is invalid. This mean that IIS doesn’t have access to the folder. This mean that you need to change the Identity of the application pool as instructed before.

An other point of failure may be that IIS is not running with all the new installed configuration. You can know this if you get the message :

Handler “ExtensionlessUrlHandler-Integrated-4.0” has a bad module “ManagedPipelineHandler” in its module list

To have IIS uses all new modules, open a Console as an administrator and execute the following line: c:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis.exe -i

aspnetiisreg

The last problem that may arrive is that you see your website but without Javascript and CSS loading. You can notify this not only because your website won’t display correctly but also because Javascript and CSS errors will be marked in every browser tools.

websiteCssJsErrorAuthorization

To fix this, two things must be set. First, you have the have the Static Content to be installed. This has to be checked when installing IIS. If not, you can go in Control Panel\All Control Panel Items\ and select Programs and Features. In the left side bar, you will see a link called “Turn Windows features on or off” and under Commont Http function of IIS you will see the option.

Second, you need to execute not only the application pool under your credential but also the set the authentification of the website under yours. To do it, select Basic Settings > Connect as… > Specific user.

connectas

From here, your website should work and from any location (even your My Document folder).

Getting Started With Windows Azure

This post show how to transport your Asp.Net MVC to Windows Azure. First of all, you need to create a Windows Azure account. This can be done by going into the main website of Windows Azure. You can setup a trial account for 30 days.

We won’t create a Virtual Machine which is the ultimate control over your website (you can load any Virtual Machine), but use the Web Sites type of server. The creation is straight forward. You setup a name and you are up and running.

windowsAzureWebSitesPortal

To upload your website, you have several option. One of these is to use Visual Studio with a Web Publish. Windows Azure gives an automatically generated publish file.

DownloadPublishProfileWindowsAzure

This profile can be imported inside your solution once downloaded on you development environment. Right click the web project and select publish, the first time you need to select import and select the downloaded file. Later, you will just need to select the configuration. The publish file is an Xml file.

<publishData>

<publishProfile profileName="workoutplanner - Web Deploy" publishMethod="MSDeploy" publishUrl="waws-prod-blu-003.publish.azurewebsites.windows.net:443" msdeploySite="workoutplanner" userName="$workoutplanner" userPWD="XXX" destinationAppUrl="http://workoutplanner.azurewebsites.net" SQLServerDBConnectionString="" mySQLDBConnectionString="" hostingProviderForumLink="" controlPanelLink="http://windows.azure.com"><databases/></publishProfile>

<publishProfile profileName="workoutplanner - FTP" publishMethod="FTP" publishUrl="ftp://waws-prod-blu-003.ftp.azurewebsites.windows.net/site/wwwroot" ftpPassiveMode="True" userName="workoutplanner\$workoutplanner" userPWD="XXX" destinationAppUrl="http://workoutplanner.azurewebsites.net" SQLServerDBConnectionString="" mySQLDBConnectionString="" hostingProviderForumLink="" controlPanelLink="http://windows.azure.com"><databases/></publishProfile>

</publishData>

The generated publish profile contain two profiles. One use Web Deploy and one use FTP. Azure offer these two methods with publishing.

DownloadPublishProfileWindowsAzure

The next step is to setup a database. You can create a database with the same name as yours in your computer.

DatabaseCreationWindowsAzure

If it’s your first database on Windows Azure, you will have to create a database server which will require you to create a user and password to connect to. Next, you will have to allow your IP to be able to execute SQL Query from your computer to Windows Azure. This will let you create tables, stored procedures, views, etc.

IpWindowsAzure

Next, open Microsoft SQL Manager and enter the server address specified inside your Database DashBoard.

AzureDatabaseServerAddress

SqlServerManagerWithAzure

Once you can connect, it’s time to take your database to Azure. I found easy if you doesn’t have too much data to right click your database and select Task>Generate Script and to create a Schema/Data script which will create all database tables and data.

However, you might get some error like this one :

Msg 40514, Filegroup reference and partitioning scheme’ is not supported in this version of SQL Server

This is because you might have in you create table statement “ON [PRIMARY]” keywork which is not supported.

[SQL highlight=”9-10″]
CREATE TABLE [dbo].[Exercises](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name_French] [nvarchar](max) NULL,
[Name_English] [nvarchar](max) NULL,
[Muscle_Id] [int] NULL,
CONSTRAINT [PK_dbo.Exercises] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
[/SQL]

Should become

[SQL highlight=”9-10″]
CREATE TABLE [dbo].[Exercises](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name_French] [nvarchar](max) NULL,
[Name_English] [nvarchar](max) NULL,
[Muscle_Id] [int] NULL,
CONSTRAINT [PK_dbo.Exercises] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
[/SQL]

From there, open your solution and change the connection string to setup your new database in the Release version. This need to have a connection string to the database your just created. With Windows Azure, it’s easy because it generates for you the connection string.

ConnectionStringWindowsAzureDatabase

My experience was that I was getting runtime error.

ServerErrorWindowsAzure

To get the log file, you need to have a FTP account. This can be done by creating a user/password in your website dashboard. Then, click the link that will bring you to a folder where all logs files are located.

FtpDiagnosticLogsWindowsAzure

If you do not see any logs file, you might need to enable logs. This can be done by going to “Configuration” of the Web Sites. I have enabled Web Server Logging and Detailed Error Messages.

SiteDiagnosticWindowsAzure

This give us .html file with the error. In my case, I had this message following message.

HttpError500WindowsAzure

Visual Studio 2012 Automatic Check Out Not Working with TFS

This is annoying and can come from a lot of place. How come some time you cannot automatically check out a file when editing inside Visual Studio.

First of all, the most obvious thing to do is to be sure that Visual Studio has the option to automatically checkout file when this one is edited. This can be verified by going to Tools>Options>Source Control>Environment and be sure that Visual Studio does have into the Editing setting the option “Check out automatically” selected.

checkoutautomaticvisualstudio2012

Second, your Windows account is linked with TFS and your password might been expired or changed since the last time this one has been verified by Visual Studio. To be sure, just select Team>Connect to Team Foundation Server.

connectToTFS

This will open a panel and you’ll be able to click “Connect”. The problem is that it might do nothing if you are already connected.

The last step is to verify that the solution might be offline. This is often the case if you are branching with TFS. If you open the solution in your new branch, the solution may not be “online”.

To be sure to be “online”, you need to go to File>Source Control>Go Online.

From here, if you were not connected, you will see in the output window some movement of activities.

How to register Area without having to specify them all in the global.asax.cs

When you go into an existing project you have two possibilities. The first possibility is that every area are registered into the global asax, or with Mvc4 in the routing class that the global.asax call. The second possibility is to use AreaRegistration class and have 1 class inherit this class per area.

The first solution is simple, fast and it’s suitable for a small project (rule of thumb: less than 5 areas). What you need is to add a new entry into routes.

protected void Application_Start()
{
    RegisterRoutes(RouteTable.Routes);
}

public static void RegisterRoutes(RouteCollection routes)
{
	routes.MapRootArea("{area}/{controller}/{action}/{id}", 
					 "RouteNameHere", 
					  new { 
							area="area1", 
							controller = "controller1", 
							action = "action1", 
							id = "" 
						});
}

The second solution require two steps. The first step is to tell the framework to auto register a specific type of class for routing, and the second step is to define one class per area for configuration purpose.

Step1: Inside Global.asax.cs

protected void Application_Start()
{
    AreaRegistration.RegisterAllAreas();
}

Step2: Inside every folders of every area you have create a class that inherit from AreaRegistration

public class MyArea_AreaRegistration : AreaRegistration
{
	public override string AreaName
	{
		get { return "MyArea"; }
	}

	public override void RegisterArea(AreaRegistrationContext context)
	{
		const string nameSpace = "MyNameSpaceForThisArea";
		const string areaName = "MyAreaName";
		const string defautlRouteUrl = areaName + "/{controller}/{action}/{id}";

		var defaultRouteValueDictionary = new RouteValueDictionary(new
		{
			action = "Index",
			id = UrlParameter.Optional
		});

		var dataTokensDictionary = new RouteValueDictionary(new
		{
			Namespaces = nameSpace,
			area = areaName,
			UseNamespaceFallback = false
		});

		context.Routes.Add(string.Format("{0}_Default", areaName), new Route(defautlRouteUrl, defaultRouteValueDictionary, dataTokensDictionary, new MvcRouteHandler()));
	}
}

Few things are important. First, you shouldn’t copy and paste that code in all your classes. The namespace and area will change but every other lines remain the same across all areas. Be wise and encapsulate the logic in a reusable place. Second, the new MvcRouteHandler() will be most of the time something that you will inherit from because you could set over there some specification over localization. For example, you may want to have /fr/ which if available change the current thread to the language specified in the url.

The override in the example above is more complex that it should be if you do not have a custom RouteHandler. In fact, you could use this simple 1 liner:

context.Routes.MapRoute("DefaultRouteForAreaXYZ"
                        , "AreaXYZ/{controller}/{action}/{id}"
                        , new { 
                                controller="Home"
                                , action="index"
                                , id= UrlParameter.Optional
                                }
                        );

Perhaps it’s simpler, but you won’t have the leverage of controlling every aspect of the routing, which is viable for project where you do not need to parse the routing for additional features.

SQL Cache Dependency and Sql Cache Dependency Admin

SQL Server since few years (2005) can push notifications when data has changed. The notification of this push use the Sql Cache Dependency. Before, the SQL Cacche Dependency has to continually poll the database, now, it’s really push. To make it works, SQL Server you must enable Microsoft SQL Server Service Broker.

Once everything is installed property (Sql Server), you must enable your database to works with a service broker.

ALTER DATABASE YourDatabase SET ENABLE_BROKER
GO

By doing this SQL statement, you may have error like this one : “error 9772: The Service Broker in database ‘YourDatabase’ cannot be enabled because there is already an enabled Service Broker with the same ID”. If this happen, just execute :

ALTER DATABASE YourDatabase SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE YourDatabase SET NEW_BROKER
GO

Next move is to enable notification with IIS.

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "TESTSERVER\ASPNET"

The next step is within the code. You need to setup the notification to listen the database. This can be set in the global.asax

SqlDependency.Start(ConfigurationManager.ConnectionStrings["yourConnectionString"].ConnectionString);

You can also make it stop.

SqlDependency.Stop(ConfigurationManager.ConnectionStrings["yourConnectionString"].ConnectionString);

In both case, this should be setup once for your application. Here is an example.


string tableName = "YourTable";
string connectionString = "...";
var result = HttpContext.Current.Cache[tableName] as List<T>;

if (result == null)
{
	using (var cn = new SqlConnection(connectionString))
	{
		cn.Open();
		var cmd = new SqlCommand("SELECT * FROM ...", cn);
		cmd.Notification = null;
		cmd.NotificationAutoEnlist = true;
		SqlCacheDependencyAdmin.EnableNotifications(connectionString);
		if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connectionString).Contains(tableName))
		{
			SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString, tableName);
		}                   

		var dependency = new SqlCacheDependency(cmd);
		SqlDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    // Load the list into result
                }
            }
            finally
            {
                // Always call Close when done reading.
                reader.Close();
            }

	    HttpContext.Current.Cache.Insert(tableName, result, dependency);
	}
}

As you can see, it checks if the table exist. If not, it creates a table called AspNet_SqlCacheTablesForNotification where every table name will have a row. If a value change, the entry in this table will be removed.

sqltablenotification

This is quite interesting for distributed scenario where multiple source can change the value. The SQL Server act as a gateway where reside the information if the data is cached or not.

To conclude, we have seen that we can use the SqlCacheDependencyAdmin to enable the notification and also to create the table. We have seen that we can use the SqlCacheDependency with the cache to synchronize data between the database and the caching system. When the database chance, the cache will be flushed.

If your notification is not trigged

I suggest that you search for a table called sys.transmission_queue in your database. Your notification should be stored here but was not delivered. The cause can be a lot of thing but you should try to verify if you are allowed to create the table by code which require to be able to use the EXECUTE statement.

How to handle transaction with any ADO.Net including Entity Framework

Transaction is handled by the namespace System.Transactions with TransactionScope or with SqlTransaction for DbConnection. So, they are two ways to use transaction.

The transaction scope way allows to have several transactions and also to be in multiple processes. To be able to have multiple processes/threads transactions, the Microsoft Distributed Transaction Coordinator (MSDTC) must be enabled on the machine where the code is running. Having many transactions allow you to have transaction with different sources. That mean that you can have a transaction for a SQL database and one with a complete other database. This can happen with multi-threaded application such as web application. This can be also required if both transaction is in imbrication. Both commit will be require to have the main transaction to pass. If one of the transaction fail, the rollback will be executed on all source. As stated, this require to have MSDTC running on your server/computer. By default, ADO.Net enlist all transactions with the DbConnection. But, you could handle everything manually.

Transaction defines tree levels of trust. Partially trust uses the attribute AllowPartiallyTrustedCallers removes the LinkDemand and is used with a single application domain. DistributedTransactionPermission (DTP) that use several transaction and Full trust use the attribute FullTrust which is like DTP with durable resource.

You can use transaction implicit and explicit transaction. Explicit transaction can be defined by using the class System.Transaction.TransactionScope.

using (var scope = new TransactionScope())
{
	//Code here that use ADO.Net. Can be code that use Entity Framework (because it uses ADO.Net)
	//...
	scope.Complete(); //Won't abord the transaction, everything is fine. Without this call, everything rollback when the object scope is disposed
}

The example above displays a simple use of a transaction scope. First, you can see that we are using the keyword using because TransactionScope is disposable, it inherits the interface IDisposable. This is not require but if you do not want to use the using statement, you will need to explicitly dispose the object. Also, you can notice that we call Complete() to tell that everything is fine. Not calling Complete would call a rollback. However, the documentation state that it’s not guarantee that calling the Complete() method will call a Commit of the transaction. Also, calling Complete in the nested scope has no affect on the root scope.

The last thing that you must know is if you are using the transaction from the DbConnection, you can take the transaction and set it to multiple DbCommand and all DbCommand will be than linked together.

var con = new SqlConnection(/*Connection string*/);
con.Open();
var transaction = con.BeginTransaction();
var cmd1 = con.CreateCommand()
var cmd2 = con.CreateCommand()
cmd1.Transaction = transaction;
cmd2.Transaction = transaction;
cmd1.Execute();
cmd2.Execute();
//Later
transaction.Commit() or transaction.Rollback();

Every transactions has an Isolation Level. The isolation level specify what access the transaction can reach with volatile data. The isolation is set when opening the transaction with SqlConnection.BeginTransaction method.

var transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);

Here is the official list of value. The default one is Read Commited which block the client to write

memberIsolationLevel

If you verify the DbCommand, you will see that by default this one has also a transaction. Instead of having “complete” you can explicitly call Commit() or RollBack().

var con = new SqlConnection(/*Connection string*/);
con.Open();
var transaction = con.BeginTransaction();
//...
transaction.Commit(); OR  transaction.Rollback();