July, 2010

  • Wriju's BLOG

    ADO.NET Entity Framework 4.0: Simple N-Tier with Self-Tracking Entities and WCF

    • 2 Comments

    ADO.NET Entity Framework 4.0 allows us to easily create N-Tier Application with the help of Self-Tracking Entities. This means now we do not have to worry about merging and checking values in more disconnected scenario. Here is how we can do it in very simple example to start with.

    Create Model Layer

    ·         Create a blank solution in Visual Studio 2010 with the name NTier.

    ·         Create a new Class Library application. Give the name TestDBModel.

    ·         Right click to the TestDBModel project and choose Add New Item. Add ADO.NET Entity Data Model and name it “TestDBModel.edmx”.

    ·         Point to the database and table (here it is Emp)

    ·        The Emp table is simple and has

     

    CREATE TABLE [dbo].[Emp](

         [EmpId] [int] IDENTITY(1,1) NOT NULL,

         [EmpName] [varchar](50) NULL,

     CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED

    (

         [EmpId] ASC

    ) 

    ·        Then select the TestDBModel.edmx from the designer and go to the property window and select the property “Code Generation Strategy” and change it to None. The reason behind doing it is to get WCF enabled entity classes which can keep the track of changes made across the layers without having an open connection (context).

    ·         Then right click on the TestDBModel.edmx designer and select “Add Code Generation Item”.

    ·         Choose “ADO.NET Self-Tracking Entity Generator” and name it Model1.tt.

    ·        If you explore it you will find the context and Emp class created by that template already. You do not have to write/change anything there.

     

    Create Service Layer

    ·        Now create a WCF Class Library project with a name WcfServiceLibrary1.

    ·     Add a class file ITestDB.cs with the following code.

    namespace WcfServiceLibrary1
    {
        [ServiceContract]
        public interface ITestDB
        {
            [OperationContract]
            Emp[] GetEmp();

            [OperationContract]
            Emp[] UpdateEmployee(Emp[] emps);        
        }
    }

    ·     Now add one more class file TestDBService.cs with the following code,

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Runtime.Serialization;
    using System.ServiceModel;
    using System.Text;
    using TestDBModel;

    namespace WcfServiceLibrary1
    {
        public class TestDBService : ITestDB
        {
            public Emp[] GetEmp()
            {
                TestDBEntities ctx = new TestDBEntities();
                return ctx.Emps.ToArray();
            }

            public Emp[] UpdateEmployee(Emp[] emps)
            {
                throw new NotImplementedException();
            }
            
        }
    }

    ·         You also need to bring the EF connection string from the model project to this project’s App.config file.

     

    Create UI Layer

    ·         Create a Windows Forms Application with a name WindowsFormsApplication1. Add Service reference to your WCF Class Library.

    ·        Then add Grid View named dataGridView1 and Button named button1.

    ·        Add the following code,

    private void button1_Click(object sender, EventArgs e)
    {
        var svc = new ServiceReference1.TestDBClient();

        dataGridView1.DataSource = svc.GetEmp();
    }

    I also have added the code here (check below of this post if you have clicked the post title to read it)

    Namoskar!!!

  • Wriju's BLOG

    ADO.NET Entity Framework 4.0: Making it fast through Compiled Query

    • 1 Comments

    If we are using similar query frequently, we can increase it’s performance by Compiling it through CompiledQuery. It’s always recommended to use CompiledQuery if you happen to see the query is getting executed many times.

    Let’s take an example, in Northwind database if you are getting Customer based on City. You may follow the below approach. Also a very important point to observe is to see how it runs faster in subsequent calls even though the parameter values differ.

    Compiled Query
    1. private static readonly Func<NorthwindEntities, string, IQueryable<Customer>> myCompiledQuery =
    2.     CompiledQuery.Compile<NorthwindEntities, string, IQueryable<Customer>>(
    3.     (ctx, city) => from c in ctx.Customers
    4.                     where c.City == city
    5.                     select c);

    Now, instead of writing raw LINQ use this Compiled Query

    Calling it
    1. private static void CallCompiledQuery(string myCity)
    2. {
    3.     using (NorthwindEntities ctx = new NorthwindEntities())
    4.     {
    5.         var q = myCompiledQuery(ctx, myCity);
    6.  
    7.         foreach (var k in q)
    8.         {
    9.             Console.WriteLine(k.CompanyName);
    10.         }
    11.     }
    12. }

    We also wanted to capture the time.

    1. static void CallIt(string sCity)
    2. {
    3.     Stopwatch sw = new Stopwatch();
    4.     sw.Start();
    5.  
    6.     CallCompiledQuery(sCity);
    7.  
    8.     Console.WriteLine("+++++++++++++++++++++");
    9.     Console.WriteLine("Elapsed Time in Milliseconds : {0}", sw.ElapsedMilliseconds);
    10.     sw.Stop();
    11. }

    Just call twice to capture the time,

    1. static void Main(string[] args)
    2. {
    3.     Console.WriteLine();
    4.     Console.WriteLine("++++++++++++++++");
    5.     Console.WriteLine("Call No. 1");
    6.     Console.WriteLine("++++++++++++++++");
    7.     CallIt("London");
    8.     Thread.Sleep(2000);
    9.     Console.WriteLine();
    10.  
    11.     //
    12.     Console.WriteLine();
    13.     Console.WriteLine("++++++++++++++++");
    14.     Console.WriteLine("Call No. 2");
    15.     Console.WriteLine("++++++++++++++++");
    16.     CallIt("México D.F.");
    17.     Thread.Sleep(2000);
    18.     Console.WriteLine();
    19. }

    In my machine the first call took 322 milliseconds and the second one took 4 milliseconds. This may differ time to time and machine to machine. But this is faster indeed.

    Namoskar!!!

  • Wriju's BLOG

    EF4 : Use MergeOption.NoTracking for better query performance

    • 0 Comments

    If you are just trying to fetch the data and has no intention to update it. Then you should be using the MergeOption.NoTracking to ensure that the ObjectStateManager does not store the required information for update and delete. Hence your query would be more quick in nature.

    using (NorthwindEntities ctx = new NorthwindEntities())
    {
        ctx.Customers.MergeOption = MergeOption.NoTracking;

    So this simple line would add a lot of value to your performance and clearly visible in second call onwards.

    Namoskar!!!

  • Wriju's BLOG

    EF4: What’s in it there in Foreign Key

    • 0 Comments

    There are a lot of buzz around the Foreign Key support in ADO.NET Entity Framework v4. Question often comes to our mind that what is so special here? While discussing with my fellow Consultant Vijay, he gave me a nice example. Let me share here,

    Let’s assume that you have two tables Dept and EmpDept. Connected with DeptId which is Primary in Dept table and Foreign key in EmpDept table. When we simply use SQL query we can easily change the DeptId of an employee by sending one single Update statement like

    UPDATE EmpDept SET DeptId = 2 WHERE EmpId = 1

    This simple thing was totally missing in the previous version of EF (in Visual Studio 2008). We had to pull out the whole new Dept to update an existing EmpDept.

    If we have the database table like,

    image

    From it if we have built the model like, (as it was in Visual Studio 2008)

    image

    Notice, here you do not have the DeptId as static property in the EmpDept entity. Thus the pain. So to simply shift one employee from one dept to another you had to write something like,

    When no Property for FK
    1. using (var ctx = new TestDBEntities())
    2. {
    3.     //Get the Employee to Update
    4.     EmpDept q = (from e in ctx.EmpDepts where e.EmpId == 2 select e).First();
    5.  
    6.     //Get the Dept to update to
    7.     Dept dd = (from d in ctx.Depts where d.DeptId == 3 select d).First();
    8.  
    9.     //Finally Update
    10.     q.Dept = dd;
    11.  
    12.     ctx.SaveChanges();
    13. }

    Which would execute 3 queries in the database.

    T-SQL
    1. --Query 1 : Get Emp to Update
    2. SELECT TOP (1) [Extent1].[EmpId] AS [EmpId], [Extent1].[EmpName] AS [EmpName], [Extent1].[DeptId] AS [DeptId]
    3. FROM [dbo].[EmpDept] AS [Extent1] WHERE 2 = [Extent1].[EmpId]
    4.  
    5. --Query 2 : Get the Dept
    6. SELECT TOP (1) [Extent1].[DeptId] AS [DeptId], [Extent1].[DeptName] AS [DeptName]
    7. FROM [dbo].[Dept] AS [Extent1] WHERE 3 = [Extent1].[DeptId]
    8.  
    9. --Query 3 : Finally Update it
    10. exec sp_executesql N'update [dbo].[EmpDept] set [DeptId] = @0 where (([EmpId] = @1) and ([DeptId] = @2))
    11. ',N'@0 int,@1 int,@2 int',@0=3,@1=2,@2=2

    Now, in EF4 you get the foreign key as static column. So things becomes much more simpler both from the front end code perspective and backend execution. The new model looks like as below if we select the “Include foreign key columns in the model

    image

    as,

    image

    Now notice here the new static property in EmpDept as DeptId. If you want to achieve the same goal here, things would become much more simpler,

    When FK is static property
    1. using (var ctx = new TestDBEntities())
    2. {
    3.     EmpDept q = (from e in ctx.EmpDepts where e.EmpId == 2 select e).First();
    4.  
    5.     q.DeptId = 2;
    6.  
    7.     ctx.SaveChanges();
    8. }

    And also the generated SQL would be only 2,

    T-SQL
    1. --Query 1 : Get Emp to Update
    2. SELECT TOP (1) [Extent1].[EmpId] AS [EmpId], [Extent1].[EmpName] AS [EmpName], [Extent1].[DeptId] AS [DeptId]
    3. FROM [dbo].[EmpDept] AS [Extent1] WHERE 2 = [Extent1].[EmpId]
    4.  
    5. --Query 2 : Finally Update it
    6. exec sp_executesql N'update [dbo].[EmpDept] set [DeptId] = @0 where (([EmpId] = @1) and ([DeptId] = @2))
    7. ',N'@0 int,@1 int,@2 int',@0=3,@1=2,@2=2

    I personally still feel that being ORM we are paying the additional database roundtrips here. And if you want to reduce it again to simple one either you use Stored Procedure and create Function or execute RAW T-SQL using ExecuteStoreQuery method of Context.

    Namoskar!!!

  • Wriju's BLOG

    Every Developer needs to be Social

    • 0 Comments

    This is exactly Scott Hanselman communicated on which we live our everyday life. How were those days when there were only books and different CDs of MSDN. Then Google and Bing came into the picture. Being a developer how we drink resource and make them useful for others.

    image

    http://channel9.msdn.com/posts/Glucose/Hanselminutes-on-9-Social-Networking-for-Developers-Part-1-Every-Developer-Needs-a-Blog/

    Every developer should watch it !!!

    Namoskar!!!

  • Wriju's BLOG

    SQL Server 2008 R2 Management Studio designer was not allowing me to add primary key to a column of an empty table

    • 0 Comments

    What the heck!!! Then finally Amar did help me to get it done. Simple “option”. Life has too many “options”.

    Tools > Options

    image

    You need to just uncheck the option “Prevent saving changes that require table re-creation”

    Namoskar!!!

  • Wriju's BLOG

    EF4 : Complex Type

    • 0 Comments

    Is to simplify your life which might seem too complex :). Let’s suppose you have a table like,

    image

    Now you might want to club all address related field and make one single representation to it. Once you have created the model in your Visual Studio 2010, you may just need to select all the properties and choose the option to create the Complex Type.

    image

    Once you have done it, the Model Browser and Model would look like,

    image

    Your query to fetch the required data then would look like,

    using (var ctx = new TestDBEntities())
    {
        var q = from c in ctx.Addresses
                select c;

        foreach (var k in q)
        {
            Console.WriteLine(k.AddLines.Street);
            Console.WriteLine(k.AddLines.Country);
        }
    }

    Namoskar!!!

  • Wriju's BLOG

    EF4 : How to enable Model without code

    • 0 Comments

    When we need to work with an Entity Framework model and use POCO. We need to get rid of the designer generated code. But how? There are few ways by which we can achieve it.

    Option 1

    I personally like this approach. You need to open the Model designer and click F4. The change the “Code Generation Strategy” to “None”.  Benefit of this approach is that this is drop down option and you can get the designer generated code back anytime later.

    image

    Option 2

    Under Model’s file property (as in pic) change the “Custom Tool” property “” from “EntityModelCodeGenerator”. If you remove it, then of required later you have to type the whole text to this property value.

    image

    Namoskar!!!

  • Wriju's BLOG

    Visual Studio 2010 Tip : Set multiple project as startup

    • 0 Comments

    This is not new in Visual Studio 2010. However this could be helpful while creating WCF based application. You may like to test your application from Visual Studio 2010 by hitting F5. By default, we can make only one project as Startup Project

    Select the solution and right click and select

    image

    image

    Namoskar!!!

Page 1 of 1 (9 items)