Entity Framework updating (Code First) existing entity using foreign key Id(s) mistakes

Recently we have come across with a scenario like below.

We had an entity (EF bounded), and we wanted to update the entity’s foreign key references from an existing entity. For example, say..

A Person has a Brazilian Passport, and that person need to update the Passport to an American Passport.

The domain/entity model looks like this

 public class Person
 {
   public int Id { get; set; }
   public string Name { get; set; }

   public virtual Passport Passport {get; set; }
   public int PassportId { get; set; }
 }

public class Passport
{
  public int Id { get; set; }
  public string Number { get; set; }
  public string Nationality { get; set; }
}

Notice that Person has 2 properties relates to Passport foreign key references. One is a <a href=”http://blog.staticvoid.co.nz/2012/7/17/entity_framework-navigation_property_basics_with_code_first&#8221; target=”_blank”>Navigational Property</a> and the other is the foreign key id reference. Notice that it uses the {EntityName}Id as the property name. It need to be in this convention for EF to generate the foreign key. If anything other than that EF would create an additional key.

Once you run the <a href=”http://msdn.microsoft.com/en-au/data/jj591621.aspx&#8221; target=”_blank”>EF migration</a> you would see the tables generated as below..

ef_table

Now in order to update the Person’s (only person) passport to an American passport, the code would be something like this..

 using (var dbContext = new PersonContext())
 {
   Console.WriteLine("Update person with passport");
   var person = dbContext.Persons.First();
   var americanPassport = dbContext.Passports.
            Single(x => x.Nationality == "American");

   person.PassportId = americanPassport.Id; //Notice this line

   dbContext.SaveChanges();
 }

I personally don’t like this approach for couple of reasons.

a. It feels “hacky” that you have set the id explicity to update the FK referece.
b. See the entity model, Passport. There are two properties roughly to achieve the same thing. If you use the Navigational Property you can access the Id right? So why we need another Id property?
c. Suddenly your domain entity become database ‘aware’ of FKs etc.

My preferred approach is to leave entity as it is, and just use the Navigational property. Unless if you really have to no the id property for some other reason, which I can’t think of.

 public class Person
 {
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual Passport Passport {get; set; }
}

That’s it! EF would generate the Foreign Key for Passport Entity as usual.

Now you can just update the entity using the Navigational Property (not the FK property id)

 using (var dbContext = new PersonContext())
 {
    Console.WriteLine("Update person with passport");

    var person = dbContext.Persons.First();
    var americanPassport = dbContext.Passports.
        Single(x => x.Nationality == "American");
    person.Passport = americanPassport;

    dbContext.SaveChanges();
 }

This is lot cleaner than setting up ID reference. To perform the update like this you need to make sure your, both entities (Person and Passport), are in unchanged State.

Unchanged: the entity is being tracked by the context and exists in the database, and its property values have not changed from the values in the database

Entity Framework Code First Automatic Migration & Existing Tables

We have been smooth sailing with EF6 Code First migration, but recently had few issues to dealt with migrating existing db tables.

Basically we have the below requirements.

  1. All new tables we create use Code First migration.  We want to avoid migration any existing db tables. Also we still want the EF mappings to configure for the existing tables, and have those entities in DbContext so we can work with those tables.
  2. We want to use the automatic migration. – this seems to be working well within the team environment.
  3. We want the migration to work only in our Dev Env, i.e not in QA, UAT, or Prod Envs.

Below are couple of  related SO questions..

http://stackoverflow.com/questions/19964679/ef-5-code-migration-errors-there-is-already-an-object-named-in-the-datab

http://stackoverflow.com/questions/15303542/entity-framework-automatic-migrations-existing-database

The problem is that the EF migration does not appear to be seamlessly working with existing table structure.

When we run the automatic migration for the very first time, we want the existing table structure to be unchanged. But we also want the EF to be aware of the existing tables so we can perform operations on those tables.  It seems like we can only have one way or the other but not both.

Old and new Entities

(I have simplified the code so we can focus on the problem)

public class NewTable : BaseEntity
{
    public string Title { get; set; }
}

public class OldTable
{
     public int Id { get; set; }
     public string Name { get; set; }
}

BaseEntity contains some common properties such as ID, DateModified etc

Below is how the Mappings look like

    public class NewTableMap : EntityMap<NewTable>
    {
        public NewTableMap()
        {
            Property(x => x.Title).HasMaxLength(40);
        }
    }

    public class OldTableMap : EntityTypeConfiguration<OldTable>
    {
        public OldTableMap()
        {
            HasKey(t => t.Id);
            ToTable("OldTable");
            Property(x => x.Id).HasColumnName("fldId");
        }
    }

Note that I have explicitly specified the OldTable entity has been mapped to “OldTable” And the Id has the db column name “fldId”

DBContext has the standard operations including the model binding.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     modelBuilder.Configurations.Add(new OldTableMap());
     modelBuilder.Configurations.Add(new NewTableMap());

     base.OnModelCreating(modelBuilder);
}

The SQL Database would just have the old table

Since we starting from the scratch let’s enable the migration.

PM> Enable-Migrations -EnableAutomaticMigrations

The above command would create a new Migration Configuration with AutomaticMigration set to True.

We have not created any tables yet! So let’s run the migration command.

PM> Update-Database

We get the below error..
There is already an object named ‘OldTable’ in the database.

As you know there is our existing table in the db.
The script that EF creates had a CreateTable(“dbo.OldTable”), the migration throws the above error.

How can we tell the EF migration to ignore my OldTable??? Honestly don’t know the answer for this. This why the below work around may help you.

First create a specific migration.

PM> Add-Migration FirstMigration

This will create a migration script within the Migration folder. The script has 2 create table functions. One for the NewTable, one for the OldTable.


public override void Up()
{
     CreateTable(
                "dbo.NewTable",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        Title = c.String(maxLength: 40),
                        IsActive = c.Boolean(nullable: false),
                        DateModified = c.DateTime(nullable: false,
                                    precision: 7, storeType: "datetime2"),
                        DateCreated = c.DateTime(nullable: false,
                                    precision: 7, storeType: "datetime2"),
                        UserId = c.Guid(nullable: false),
                        MarkAsDeleted = c.Boolean(nullable: false),
                    })
                .PrimaryKey(t => t.Id);

  CreateTable(
                "dbo.OldTable",
                c => new
                    {
                        fldId = c.Int(nullable: false, identity: true),
                        Name = c.String(),
                    })
                .PrimaryKey(t => t.fldId);

}

If we were to run this migration (as above) we would get exactly the same error. So let’s modify the above migration script, for example by removing the CreateTable(“dbo.NewTable”,()… DropTable(“dbo.OldTable”); We leave the scripts for creation of new Tables as it is.

Now if you run the

PM> Update-Database
Specify the ‘-Verbose’ flag to view the SQL statements being applied to the target database.
Applying explicit migrations: [201407120749125_FirstMigration].
Applying explicit migration: 201407120749125_FirstMigration.
Running Seed method.

Now if you check the database, the NewTable has been created and the existing table has not been changed.

Handling practical problems

It is very likely that someone else in your team has a different model changes to your model changes. If that’s the case, you also want to make sure that you don’t commit your initial migration script. You can treat it as your private migration script. This also assumes that you have non-shared database for development.

You team would also have to follow the same process. You can add this migration file to the exclusion list of the source control so the file won’t get committed to the repository.

Now you can make changes to the model as you would normally do and running the automatic migration would apply those changes accordingly.
It will never attempt to create your existing table as the automatic migration is now based on the modified FirstMigration. Subsequent migrations would use the last successful migration.

Just be cautious that in your next automatic migration…
Changing an entity which already mapped to an existing table would also make changes to the existing table.
If you use Ignore() method i.e ( Ignore(x => x.Name)) on the existing entity, it will also exclude that property from the existing table.
If you Ignore() method on the entire existing table during model binding i.e modelBuilder.Ignore(); it will just remove your exiting table. If this happens, simply remove the .Ignore and run the migration.

 

Debugging, Visualizing and Querying data using LINQ

One of the challenging things that I have confronted in my day-to-day work is writing complex LINQ queries. I’m sure there are lot of developers out there who have experienced the same when writing complex LINQ queries. This is a simple article to assist for those developers to debug, visualize and query using LINQ to SQL.

Those who new to LINQ to SQL, it is a database development model in the context of an Object Oriented Programming Model. LINQ is a broad topic. If you need to know more about LINQ, there lots of great articles in the web including some excellent sources from MSDN. .NET Framework 4.0 has also made significant enhancements to its LINQ model. This includes improvements to areas such as performances, query stabality, usability, LINQ to SQL class designer and much more. You can find some of those improvement in here.

LINQ to SQL translates the queries you write into parameterized SQL queries (in text form) and send them to the SQL server for processing. Sometimes debugging LINQ queries can be problematic. If you want to know more about debugging LINQ queries please refer to this article.

There are other ways to output LINQ to SQL queries so you can see the entire query that get executed. For example you can output to a debug window, file, or to a memory. Some examples are described in the below articles.

LINQ to SQL log to debug window.

View LINQ to SQL..

VS2010 has a Historical Debugger  which can also be used to view and debug LINQ queries. More info is here.

 

Debug Visualisers

LINQ to SQL Debug Visualiser enables you to view and execute the LINQ queries on the fly. It was a part of beta releases of VS2008, but for some reason MS has dropped it from the RTM release. I cannot see this is integrated into VS2010 either. It is a separate download. You can download it from here.

Take a look at this article on LINQ to SQL Debug Visualizer, which explains how it is integrated and used within Visual Studio. VS LINQ to SQL Debug Visualizer has its own limitations to it. For example it does not diplay the complete query. It is hard to edit and execute while debugging. This article discribe some of limitations.

There is another similar tool that you can download from VS Gallary. This tool should work with any database as well as MS SQL. You can find more information here.

 

LINQPad 

I think this is an excellent tool to write and test your LINQ queries. It is a great tool to help you build any type of queries with LINQ. Standard edition is free and I recon every developer should have it. You save lot of time by having these tools which allows you to be more productive when writng queries

LINQ

You can also connect to a SQL Server DB (Express or Compact Edition) and with a connection to your LINQ Data component (if you have built one) you can perform queries right against your data with LINQ – that’s excellent!  The standard edition is free to download and it supports .NET 4.0 as well. Standard edition does not have the Autocompletion feature.  For more information on Autocompletion please click here.

There is also great webcast that explains the new features of LINQPad with respect to the Entity framework.

More importantly it is a great tool to learn and improve your skills on LINQ to SQL. You can download this tool from here.

Keeping your C# code clean with Regionerate

I came across with this really nice VS add-in called Regionerate, which allows you to group various member types into regions. I’m a fan of keeping code tidy and this add-in really helps me to organise the code.
It works on Visual Studio 2010 and only works with C#.
You can download the Add-In from here.
Once you install this tool, you are ready to use the Regionerate.
You can also change the settings of Regionerate, go to Tools and click Regionerate Settings.
To use the Regionerate, simply use the Ctrl +R. You should see a window with few options as below. Alternatively just right click on Text Editor and select ‘Regionerate this’ from the context menu.

image
image a

As per the above options, you can create regions based on the member types (I.e public, private, and internal), regionalise by the member name, regionalise by the member type and the member name, order the members without and regions, and remove existing regions etc..

Below is a code sample on selecting the ‘Primary Code Layout’.

I strongly encourage you to have a look at this Add-In as it is very useful.

Getting started with Enterprise Library Performance Counters

This article is based on Enterprise Library 4.1 – October 2008.

One of the hurdles of using Enterprise Library is that there are no much help on how to do certain things from the scratch. The Enterprise Library Quick Start guide and the Labs are helpful but I find there is no much depth in some areas such as Performance counters. Recently I had to utilize some performance counters within our application. It wasn’t that straightforward and I had to do lot of trial and error exercises to get it working. This is an attempt to give you some insight into how to utilise performance counters using Enterprise Library 4.1.

I’m going to explain you how to create performance counters using Entib configuration. There are other ways – For example using Performance Counter handlers using attributes which I’m not going to explain here. Also I do not cover detail information on using performance counters and best practices. This is just to get you started with Entlib 4.1 and performance counters

First thing is first – You must install the Microsoft practices Enterprise Library 4.1 from the location here.
http://www.microsoft.com/downloads/details.aspx?FamilyId=1643758B-2986-47F7-B529-3E41584B6CE5&displaylang=en

Creating performance counters using Entlib configuration.

1. To create counters you need to have an administrative account. You need to open Visual Studio (VS) as an administrator. Then create a new Console application for example let’s say it SamplePerformanceCounters.
Now add below reference to your project.
Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
Microsoft.Practices.EnterpriseLibrary.PolicyInjection;
Microsoft.Practices.EnterpriseLibrary.PolicyInjection.CallHandlers.Installers;

2. Create a new class called MyCounters.cs.
Your class should derive from MarshalByRefObject. This class sits under System namespace and you need to add the using statement using System.Reflection;

public class MyCounters : MarshalByRefObject {}

MarshalByRefObject object will allow you to do policy injection on the MyCounters class. Alternatively you may also create an interface i.e IMyCounters. This will allow us to create a proxy object that can be used with Entlib policy injection.
public void SampleMethod(int i) { }

4. Our next task is to install the counters which we want to execute on SampleMethod. You can create performance counters as below.

public static void  InstallCounters()
{
    PerformanceCountersInstaller installer = new PerformanceCountersInstaller(new SystemConfigurationSource());
    IDictionary state = new System.Collections.Hashtable();
    installer.Context = new InstallContext();
    installer.Install(state);
    installer.Commit(state);
    Console.WriteLine("Performance counters have been successfully installed. Press enter to continue");
    Console.ReadLine();
}

These counters needed to be installed first before you calling the SampleMethod. Therefore within your Main method call the InstallCounters() method.
Note : You can also remove the installed counters by….

private static void RemoveCounters()
{
    PerformanceCountersInstaller installer  = new PerformanceCountersInstaller(new SystemConfigurationSource());
    installer.Context = new InstallContext();
    installer.Uninstall(null);
    Console.WriteLine("Performance counters have been successfully removed. Press enter to continue.");
    Console.ReadLine();
}

These counters needed to be installed first before you calling the SampleMethod. Therefore within your Main method call the InstallCounters() method.

Note : You can also remove the installed counters by….

private static void RemoveCounters()
{
    PerformanceCountersInstaller installer  = new PerformanceCountersInstaller(new SystemConfigurationSource());
    installer.Context = new InstallContext();
    installer.Uninstall(null);
    Console.WriteLine("Performance counters have been successfully removed. Press enter to continue.");
    Console.ReadLine();
}

5. Within the Main method you need to create the policy injection for MyCounters object.
MyCounters myCounter = PolicyInjection.Create();

6. Now you can call the SampleMethod as below. You can create a for loop and pass the integer variable to the sample method so every time when it executes, you can see the integer in the console window.
myCounter.SampleMethod(i)

7. All together this is how it looks like

static void Main(string[] args)
{
    InstallCounters();
    //RemoveCounters();    

    MyCounters myCounter = PolicyInjection.Create();

    for (int i = 0; i < 100000000; i++)
    {
            Thread.Sleep(500);
            service.SampleMethod(i);
    }
    Console.ReadLine();
}

Note that we use Thread.Sleep so we can delay the method execution, which gives us clear indications on the performance being monitored.
We have not finished yet. Below is the most important section.

8. Now we need to define the counters that we need to instrument. To do that you need to create an App.config file. You can either add entries to app.config manually, or you could use the Enterprise library configuration tool which comes with the installation. My personal opinion is to use the tool as it is much easier to configure the counters.

image1

9. You also need to create a policy injection application block and a policy with a matching rule. In our case I created the SampleCounters with the Method Signature matching rule.

image2

10. The final step is to configure the counters.
image3

That’s it, and run your application.
image4

To view the performance counters in action, you need to open the Windows Performance Monitor tool. You can find it in Windows Administrative tools.

As mentioned above Press enter to continue. While the integers are printing in the console window, click on Add Counters and select Demo Counter category from the list. Add Instances as required. Click OK. Now you should be able to see counters in action in the Windows Performance Monitor.

image5

I hope this helps.

Tips for converting VB.NET to C#

As soon as anyone sees this title, most of you would think, ‘Ah..that’s easy, use a tool and it will do the job for you’. Recently I have been given a small task to convert some VB.NET code to C#. I’m not good at writing VB.NET so this was a challenge.

As any other C# developer, I decided to use a conversion tool. I copied and paste the VB.NET code I needed to translate into C#.NET. Another approach would be to use the Refactor. If you can include this function in an assembly you can easily view the C# equivalent of the code using Refactor. I got it translated using a conversion tool. However I saw the translated code was bit confusing. Which also bring me to the next topic that you should not blindly translate your VB.NET code to C#.

Take a look at this example.

VB.NET


Public Function GetAppGUID(ByVal sectionId As String) As String 

Dim hexString As String = Nothing 
Dim i As Integer 
Dim guidlen As Integer 

guidlen = 16 

If sectionId.Length < guidlen Then 
sectionId = sectionId & New String(" ".Chars(0), guidlen - sectionId.Length) 
End If 

For i = 1 To guidlen 
hexString = hexString & Hex(Asc(Mid(sectionId, i, 1))) 
Next 

GetAppGUID = hexString 

End Function

C#


public string GetAppGUID(string sectionId) 
{
          
	    
	string hexString = null; 
	int i = 0; 
	int guidlen = 0; 
	    
	guidlen = 16; 
	    
	if (sectionId.Length < guidlen) { 
	        sectionId = sectionId + new string(" ".Chars(0), guidlen - sectionId.Length); 
	} 
	    
	for (i = 1; i <= guidlen; i++) { 
	     hexString = hexString + Conversion.Hex(Strings.Asc(Strings.Mid(sectionId, i, 1))); 
	} 
	    
	        
	return hexString; 
}

As you can see the converted function require Microsoft.VisualBasic.dll for Conversion and Asc functions. In some cases yes you would use this dll but most of the time you don’t have to. You can easily write this function in C# using same line of code but not using Microsoft.VisualBasic.dll

private string GetAppGUID(string sectionId) 
    { 
        string hexString = null; 
        int i = 0; 
        int guidLength = 0; 
 
        guidLength = 16; 
 
        if (sectionId.Length < guidLength) 
        { 
            sectionId = sectionId + new string(" "[0], guidLength - sectionId.Length); 
        } 
 
        foreach (char c in sectionId) 
        { 
            int tmp = c; 
            hexString += String.Format("{0:x2}", (uint)System.Convert.ToUInt32(tmp.ToString())) 
        } 
 
        return hexString; 
    }  

So this is my opinion.

a. If you ever have to convert VB.NET to C#, you may use a conversion tool or Reflector but make sure you are not blindly converting the code.
b. If the converted code require Microsoft.VisualBasic.dll, then try to avoid this dll. Refactor your code to use the equivalent C# syntax. To include a completely different dll in your C# project for just a small method/syntax change is not really worth it.
c. Don’t take your task as “hell this is not the language I’m familiar with”. See this opportunity is an excellent way to learn another language. It is quite common in the industry that you get to do these types of tasks often. Which also prove you are an all-rounded developer,