May, 2011

  • Wriju's BLOG

    Cost of My Windows Azure Application : Pricing Calculator

    • 0 Comments

    Often question comes to our mind “how to determine the cost of Azure application”. Now the pricing calculator is more interactive and easy to use. Sliders would help you determine the required numbers,

    image

    http://www.microsoft.com/windowsazure/pricing-calculator/

    Namoskar!!!

  • Wriju's BLOG

    Code First EF 4.1 : Table per Hierarchy

    • 0 Comments

    Concept of Table Per Hierarchy (TPH) is to implement polymorphism and to de-normalize the relational database via inheritance. The entire hierarchy is mapped to a single database table holding all the properties of all the classes. The database then would create a column called “[Discriminator]” to hold the specific type. In Code First this is default behavior and we do not have to do anything extra.

    TPH is the simplest and possibly the fastest approach. Code First just amazingly supports it.

    If we consider the below diagram of Animal, Mammal and Reptile. Here Animal is the abstract base class holding common properties.

    image

     This finally would get converted into a table as below (notice the additional [Discrminator] column which is NOT NULL)

    image

    To have this we need the below code

    public abstract class Animal
    {
        public int AnimalId { get; set; }
        public string Name { get; set; }
    }
    
    public class Reptile : Animal
    {
        public double Length { get; set; }
    }
    
    public class Mammal : Animal
    {
        public double Weight { get; set; }
        public bool IsMarried { get; set; }
    }
    
    public class AnimalContext : DbContext
    {
        public DbSet<Animal> Animals { get; set; }
    }
    

    To add data

    To add data to Reptile

    using (var ctx = new AnimalContext())
    {
        var Rep = new Reptile() { Name = "Snake", Length = 10.0 };
        ctx.Animals.Add(Rep);
        ctx.SaveChanges();

    To add data to Mammal

    using (var ctx = new AnimalContext())
    {
        var Mam = new Mammal() { IsMarried = true, Name = "Adam", Weight = 100.0 };
        ctx.Animals.Add(Mam);
        ctx.SaveChanges();
    

    After adding data would be saved as below

    image

    Querying data

    We can query data in various ways,

    var q = from a in ctx.Animals.OfType<Mammal>()
            select a;
    foreach (var k in q)
    {
        Console.WriteLine(k.Name);
    }
    

    We can also use Entity SQL with “OFTYPE”.

    Now the the column [Discriminator] can be altered through FluentAPI

    image

    This would create a column in the database as “AnimalType” and for each entry of Mammal add “M” and Reptile add “R”.

    image

    TPH anyways violets the third normal form and has serious data integrity issue and for a long term perspective this is not manageable. But still the simplest approach.

    Namoskar!!!

  • Wriju's BLOG

    Using ADO.NET EF 4.1 Code First with existing Database

    • 0 Comments

    Most of the time questions comes to our mind about EF 4.1 Code First whether or not can we use it with already available database? Simple answer is YES.

    I have very few personal view on it. I have seen in most of my application development scenario, database gets designed and created before and then evolves as application and requirements. So I expect to use Code First with already created database.

    This is how we can,

    image

    Step 1: We will go for the database-first approach and create our EDMX.

    image

    Step 2: Right click on the edmx and then choose “Add Code Generation Item…

    image

    Step 3: Select “ADO.NET DbContext Generator”. Then delete the .edmx file from your project.

    After that you need few small but important changes,

    Change 1 : Add the connection string (if using SQL Server)

    image

    Change 2 : The tables you are using in your code (check all the DbSet<T> in your context) and add their corresponding onModelCreating code

    image

    Then your code should run without any trouble,

    image

    Namoskar!!!

  • Wriju's BLOG

    Code First EF 4.1 : Querying Many to Many Relationship

    • 0 Comments

    In continuation to my previous post on how to create one to many, let’s see how can we query it.

    We will use the same code base and query it

    Option 1

    When we want to query it normally like below

    var emps = ctx.Emps
                .Where(e => e.EmpId == 1)
                .SelectMany(e => e.Projects, 
                        (em, proj) => new 
                        {
                            em.EmpName, 
                            proj.ProjectName
                        });
    

    The generated object graph would look like

    image

    Option 2

    Whereas if we want to write our query as below

    var em = from e in ctx.Emps.Include(p => p.Projects)
                where e.EmpId == 1
                select e;
                

    This case object graph is more complicated

    image

    Generated SQL

    Interestingly both the cases the generated SQL is same

    SELECT 
    [Project1].[EmpId] AS [EmpId], 
    [Project1].[EmpName] AS [EmpName], 
    [Project1].[C1] AS [C1], 
    [Project1].[ProjectId] AS [ProjectId], 
    [Project1].[ProjectName] AS [ProjectName]
    FROM ( SELECT 
    	[Extent1].[EmpId] AS [EmpId], 
    	[Extent1].[EmpName] AS [EmpName], 
    	[Join1].[ProjectId] AS [ProjectId], 
    	[Join1].[ProjectName] AS [ProjectName], 
    	CASE WHEN ([Join1].[Project_ProjectId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    	FROM  [dbo].[Emps] AS [Extent1]
    	LEFT OUTER JOIN  (SELECT [Extent2].[Project_ProjectId] AS [Project_ProjectId], [Extent2].[Emp_EmpId] AS [Emp_EmpId], [Extent3].[ProjectId] AS [ProjectId], [Extent3].[ProjectName] AS [ProjectName]
    		FROM  [dbo].[ProjectEmps] AS [Extent2]
    		INNER JOIN [dbo].[Projects] AS [Extent3] ON [Extent3].[ProjectId] = [Extent2].[Project_ProjectId] ) AS [Join1] ON [Extent1].[EmpId] = [Join1].[Emp_EmpId]
    	WHERE 1 = [Extent1].[EmpId]
    )  AS [Project1]
    ORDER BY [Project1].[EmpId] ASC, [Project1].[C1] ASC

    Namoskar!!!

  • Wriju's BLOG

    Code First EF 4.1 : Building Many to Many Relationship

    • 8 Comments

    Since we do not have any designer question might arise how can we create Many to Many relationship in Code First 4.1. Here it is

    public class Emp
    {
        public Emp()
        {
            Projects = new HashSet<Project>();
        }
    
        public int EmpId { get; set; }
        public string EmpName { get; set; }
        public ICollection<Project> Projects { get; set; }
    }
    
    public class Project
    {
        public Project()
        {
            Emps = new HashSet<Emp>();
        }
    
        public int ProjectId { get; set; }
        public string ProjectName { get; set; }
        public ICollection<Emp> Emps { get; set; }
    }
    
    public class EmpContext : DbContext
    {
        public DbSet<Emp> Emps { get; set; }
        public DbSet<Project> Projects { get; set; }
    }
    
    class Program
    {
        static void Main(string[] args)
        {
            var p1 = new Project() { ProjectName = "Fun Boat" };
            var p2 = new Project() { ProjectName = "Jumbo Jet" };
            var p3 = new Project() { ProjectName = "Free Zoo" };
    
            var e1 = new Emp() { EmpName = "Wriju" };
            var e2 = new Emp() { EmpName = "Wrishika" };
            var e3 = new Emp() { EmpName = "Saswati" };
    
            p1.Emps.Add(e1);
            p1.Emps.Add(e2);
    
            p2.Emps.Add(e2);
            p2.Emps.Add(e3);
    
            p3.Emps.Add(e3);
            p3.Emps.Add(e1);
    
            using (var ctx = new EmpContext())
            {
                ctx.Projects.Add(p1);
                ctx.Projects.Add(p2);
                ctx.Projects.Add(p3);
                    
                ctx.SaveChanges();
            }
        }
    }

    Generated database would look like

    image

    Namoskar!!!

  • Wriju's BLOG

    CodeFirst EF 4.1 : Changing Database Table and Column name

    • 1 Comments

    At times we need to control the Table and Column name of our generated database or have different Entity/Property name of code than actual database in EF 4.1 Code First. There are two ways we can do it.

    Using Annotations

    using System.ComponentModel.DataAnnotations;
    
        //Changing database Table name to Employee
        [Table("Employee")]
        public class Emp
        {
            //Changing database column name to EmpId
            [Column("EmpId")]
            public int Id { get; set; }
            public string Name { get; set; }
        }

    Using FluentAPI

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //Changing Database Column name to EmployeeId
        modelBuilder.Entity<Emp>()
            .Property(p => p.Id)
            .HasColumnName("EmployeeId");
    
        //Changing Database table name to EmployeeData
        modelBuilder.Entity<Emp>()
            .ToTable("EmployeeData");
    }
    Note: If you have both Annotation and FluentAPI available, the FluentAPI will win.

    Namoskar!!!

  • Wriju's BLOG

    Code First EF 4.1 : Missing “ADO.NET DbContext Generator” VS Template

    • 6 Comments

    I have seen people coming back to me asking questions about the template visible in Visual Studio 2010 as “ADO.NET DbContext Generator”. This template allows you to create the POCO classes and the context for CodeFirst from already existing edmx file.

    The template in Visual Studio looks like

    image

    But missing. But why?

    At times if you have used NuGet package manager to install the EF 4.1 then the ADO.NET DbContext Generator would not come.

    This template gets installed only when you install the complete EF 4.1 from MSDN here

    Namoskar!!!

  • Wriju's BLOG

    Code First 4.1 : Using Stored Procedure to Insert Data

    • 4 Comments

    Code First in Entity Framework does not support Stored Procedure by default. As there is no designer we cannot even map our stored procs to the entity. There are a many scenario we have seen where we are bound to use stored procedure for any database modifications (insert/update/delete). Here is how we can use stored procedure.

    image

    This would finally call the Stored Procedure.

    The stored proc used here is very simple

    ALTER PROCEDURE [dbo].[stp_InsertEmp]
    (
    	@Name as VARCHAR(50)
    ) AS
     
    	INSERT INTO Emps(Name) VALUES(@Name)

    Namoskar!!!

  • Wriju's BLOG

    LINQ to XML : Handling blank or no Element and Attribute

    • 6 Comments

    It quite so happen that you are working with XML where you are expecting a specific element in every set. But somehow that is missing in some of the sets.

    Now at runtime you would get an error..

    The XML file which I am targeting

    <?xml version="1.0" encoding="utf-8"?>
    <Employees>
      <Employee Location="Earth">
        <Name>Wriju</Name>
        <Email>a@a.com</Email>
      </Employee>
      <Employee Location="Moon">
        <Name>Tupur</Name>
        <Email>a@b.com</Email>
      </Employee>
      <Employee>
        <Name>Wrishika</Name>
      </Employee>  
    </Employees>

    Notice above the third element has missing Location attribute and Email element. So this would throw me a runtime error if I try to execute as below.

    var xml = XElement.Load(@"D:\Temp\Employee.xml");
    
    var q = from e in xml.Descendants("Employee")
            select new 
            {
                Name = e.Element("Name").Value,
                Location = e.Attribute("Location").Value,
                Email = e.Element("Email").Value
            };
    
    foreach (var k in q)
    {
        Console.WriteLine("Name : {0}, Email : {1}, Location : {2}", 
            k.Name, k.Email, k.Location);
    }

    Error would come for the 3rd element where both Location attribute and Email element are missing.

    Unhandled Exception: System.NullReferenceException: Object reference not set to an instance of an object

    Now what I need is simple. By using C# 3.0 extension method I can create two additional methods in a public static class as below

    //This method is to handle if element is missing
    public static string ElementValueNull(this XElement element)
    {
        if (element != null)
            return element.Value;
    
        return "";
    }
    
    //This method is to handle if attribute is missing
    public static string AttributeValueNull(this XElement element, string attributeName)
    {
        if (element == null)
            return "";
        else
        {
            XAttribute attr = element.Attribute(attributeName);
            return attr == null ? "" : attr.Value;
        }
    }
    

    Now my code would look like and would not throw any runtime error

    var xml = XElement.Load(@"D:\Temp\Employee.xml");
    
    var q = from e in xml.Descendants("Employee")
            select new 
            {
                Name = e.Element("Name").ElementValueNull(),
                Location = e.AttributeValueNull("Location"),
                Email = e.Element("Email").ElementValueNull()
            };
    
    foreach (var k in q)
    {
        Console.WriteLine("Name : {0}, Email : {1}, Location : {2}", 
            k.Name, k.Email, k.Location);
    }

    Namoskar!!!

Page 1 of 1 (9 items)