Using Decimal as Primary key in Entity Framework 4

Entity Framework 4 does some automatic things which is very useful in many scenarios. For example, if we declare a “ID” property for a class, it automatically assumes that property as a primary key for the mapped table in the database. And It also sets the “identity” property of the column to true.

 

Figure: Simple model with “integer” type Primary key

 

This works very good in most of the time. Unfortunately, sometimes we may need to use “decimal” data type rather than “integer”, specially for large table.

 

If we want to see the same behavior for decimal that we have seen for integer before (for example, set primary key and identity property), we need to do some extra configurations:

1. Explicitly add the “DatabaseGenerated” attribute to set the “identity” value of the column in database.

[DatabaseGenerated(System.ComponentModel.DataAnnotations.DatabaseGeneratedOption.Identity)]

2. Specify the precision for the decimal data type. This is because by default it assumes there are two numbers after the decimal for decimal data type. We need to set it 0.

modelBuilder.Entity<User>().Property(x => x.ID).HasPrecision(16, 0);

 

That’s it. Lets see the full view after updating the classes-

Figure: User class after adding the attribute

 

Figure: Set precision for ID

Now add users in the database through the application. “ID” will be automatically increment by 1 like the integer before.

 

Happy programming!

Left Outer Join Using LINQ

I was in a kind of puzzled while I started searching how to do left outer join with LINQ. Finally, I found the solution and proper explanation of left outer join using LINQ. I am going to explain that here to help those who is still searching and also for my future reference :-)

We know “Join” combines two or more tables in a database. And by definition, left outer join returns all the values from the left table; doesn’t matter whether they match or not with the right table (returns NULL, incase don’t match).

We need to use LINQ’s “DefaultIfEmpty()” function to accomplish the task which returns the default value if the sequence is empty. It also takes default item as the parameter. However- let’s see how can we do a left outer join between Customer and Order table using LINQ:

list = from c in customers
                   join o in orders on c.ID equals o.CID into t_c

                   from r in t_c.DefaultIfEmpty()
                   select new { Name=c.Name, Item=(r!=null)?r.Item:"nothing" };

We have two steps in the above query:

1. Produce a collection by doing inner join between orders and customers table.

2. Use “DefaultIfEmpty” to invoke each element of first collection.

For your reference, below is the Customer and Order classes:

   class Customer {
        public int ID { get; set; }
        public string Name { get; set; }
    }

    class Order {
        public int ID { get; set; }
        public int CID { get; set; }
        public string Item { get; set; }
    }

Please check in MSDN for more details explanation-

http://msdn.microsoft.com/en-us/library/bb397895.aspx

Happy Programming!

Customizing ASP.Net MVC Template

Asp.net scaffold templates for controller and view are really useful but often we need to change the default template to fit with particular project. It's easy to do. Follow the steps below to customize the ASP.Net MVC3 built-in T4 template:

1. Go ahead and find the default template from the following location- 
[Visual Studio Installation Directory]\Common7\IDE\ItemTemplates\CSharp\Web\MVC 3 
2. Copy the "CodeTemplates" folder. 
3. Paste the "CodeTemplates" folder to the project's root. 
4. Remove the templates(extension '*.tt') that you do not need to customize from the project's "CodeTemplates" folder. 
5. Select the templates(press and hold ctrl and click on the template) and go to properties(right click and select properties). 
6. Clear "Custom Tool" entry. By doing this, it will not run the template automatically every time you build the project. 
7. Now customize the template as you want and build the project. 
8. Right click on View folder and select add view. From the add view popup dialog box, select the model class and you will find the customized template in the "scaffold template" drop down list. 

 Happy Programming!