Monday, May 21, 2012

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-

Happy Programming!

Thursday, May 10, 2012

Customizing ASP.Net MVC Template 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- 
  2. [Visual Studio Installation Directory]\Common7\IDE\ItemTemplates\CSharp\Web\MVC 3 
  3. Copy the "CodeTemplates" folder. 
  4. Paste the "CodeTemplates" folder to the project's root. 
  5. Remove the templates(extension '*.tt') that you do not need to customize from the project's "CodeTemplates" folder. 
  6. Select the templates(press and hold ctrl and click on the template) and go to properties(right click and select properties). 
  7. Clear "Custom Tool" entry. By doing this, it will not run the template automatically every time you build the project. 
  8. Now customize the template as you want and build the project. 
  9. 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!