Nikolaos Kantzelis ASP.Net Blog

This blog will focus on ASP.NET Framework

Sponsors

About Me

Great Blogs

March 2011 - Posts

Enhancing performance in Entity Framework applications by precompiling LINQ to Entities queries

This is going to be the tenth post of a series of posts regarding ASP.Net and the Entity Framework and how we can use Entity Framework to access our datastore. You can find the first one here, the second one here , the third one here , the fourth one here, the fifth one here ,the sixth one here ,the seventh one here ,the eighth one here and the ninth one here.

I have a post regarding ASP.Net and EntityDataSource. You can read it here.I have 3 more posts on Profiling Entity Framework applications. You can have a look at them here, here and here.

In this post I will be looking into how to improve performance in EF applications. I will be looking into how to precompile LINQ to Entities queries and see the performance gains from that.

We have to know our application very well and see what queries are executed repeatedly. Those are candidates for precompilation. We will use the public sealed class CompiledQuery.

For more information click here.

I assume that you have access to a version of SQL Server.If you do not, you can download and install the free SQL Server Express edition from here. 

In this post (step 9) you will find a T-SQL script that will create the database objects of the CompanyEmployees database.Before that you execute the T-SQL script you must create in the Query window the CompanyEmployees database yourself. You can download the companiesemployeesinsertt.zip to insert data into the tables.

1) Launch Visual Studio 2010 (express edition will work fine). Create an empty ASP.Net web site from the available templates and choose a suitable name for it. Choose C# as the development language.

2) Add a new item to your website,a web form.Leave the default name. Add a button and a textbox on the the form.Leave the default names.

3) Add a new project to your solution, a class library project.Remove the class1.cs file from the project. 

4) Add a new ADO.Net Entity Data model to the class library project. Choose a suitable name for it, e.g CompanyEmployees.edmx.

5) Then the Wizard pops up. Choose "Generate from Database" option and finish the steps of the wizard by selecting only the tables (Companies,Employees) to be included in the model. Now you have your model ready with all the entities and the generated code.

6) My application is very simple. I will query the Employees entity asking for the Employee first name and last name by passing the country as a parameter.I will enter the country in the textbox and then click the button to filter the employees by that parameter. The parameter will be passed at runtime.

We will use the Compile() method of the CompiledQuery class. Inside the class declaration of the Default.aspx page, type

public partial class _Default : System.Web.UI.Page
{

private static Func<CompanyEmployeesEntitiesstringIQueryable<Employee>> 
theCompiledQuery =
CompiledQuery.Compile<CompanyEmployeesEntitiesstring
IQueryable<Employee>>((context, country) => from emp in context.Employees
                              where emp.Country == country
                              select emp);

 

As you see here I create a delegate.The Compile() method take 2 arguments. I pass as the first argument the instance of the ObjectContext object.Then I pass as a second argument a "string" because I filter on a string.

Then I have a Lambda expression which encapsulates a Linq to Entities query.The query just returns entities of type (Employee) according to the parameter it takes.

 7) Now we need to instantiate the ObjectContext object. In the Button1_Click() event handling routine type,

 


        try
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();


            using (var context = new CompanyEmployeesEntities())
            {

                string mycountry = TextBox1.Text;

                var query = theCompiledQuery(context, mycountry);

                foreach (var item in query)
                {
                Response.Write(item.EmpFirstName + " " + item.EmpLastName);
                Response.Write("<br/>");
                }

 
Response.Write("<br/>");
            Response.Write("<hr/>");
Response.Write("Time in milliseconds : " +   stopwatch.ElapsedMilliseconds);               
                stopwatch.Stop();
            }

        }
        catch (Exception ex)
        {

            Response.Write(ex.InnerException.Message);
        }

We need somehow to find the time ellapsed between consecutive requests.

Obviously the first request will not have any performance gains. Some time will be spent to compile the query.

I use the Stopwatch class.Add the following using statement in the Default.aspx.cs

 
using System.Diagnostics;

Then I just create an instance of that class and I start the time.I instantiate the context object and i get the parameter (country) from the textbox. Then I pass them to the theCompiledQuery delegate.

var query = theCompiledQuery(context, mycountry);

Then I iterate through the results and display them on the screen. Finally I find the elapsed time, and stop the watch.

8) Run your application. In my case , I enter in the textbox as a parameter-country="Norway" and I hit the button. This is what I get back

Julie Moreno
Mark Bolton


Time in milliseconds : 4452

 

9) Type another value in the textbox e.g Sweden . This is what I get.

Sonya Ryan
Luke Torres
Erin Schmidt
Nakia Zavala
Cassie Dougherty


Time in milliseconds : 24

As you can see there is a significant performance gain even though I have a different value in the paremeter.

10)  Type another value in the textbox e.g Bulgaria . This is what I get.

Frances Caldwell
Dana Heath
Ericka Rivers
Quentin Harmon
Sandra Ware
Bethany Kent


Time in milliseconds : 13

As you can see there is a significant performance gain even though I have a different value in the paremeter.

Email me if you need the source code.

Hope it helps!!!

Lazy Loading,Eager Loading,Explicit Loading in Entity Framework 4

 

This is going to be the ninth post of a series of posts regarding ASP.Net and the Entity Framework and how we can use Entity Framework to access our datastore. You can find the first one here, the second one here , the third one here , the fourth one here, the fifth one here ,the sixth one here ,the seventh one here and the eighth one here.

I have a post regarding ASP.Net and EntityDataSource. You can read it here.I have 3 more posts on Profiling Entity Framework applications. You can have a look at them here, here and here.

In this post I will be looking into Lazy Loading, Eager Loading, Explicit Loading options . It is very important to know what happens when EF generates on the fly the SQL queries that hit the database. You must understand exactly what is hitting your database.I know a lot of people who are using EF and have performance issues with their applications. Most of the time , they get surprised with the number of queries that hit the database to return the data.

I assume that you have access to a version of SQL Server.If you do not, you can download and install the free SQL Server Express edition from here. 

In this post (step 9) you will find a T-SQL script that will create the database objects of the CompanyEmployees database.Before that you execute the T-SQL script you must create in the Query window the CompanyEmployees database yourself. You can download the companiesemployeesinsertt.zip to insert data into the tables.

1) Launch Visual Studio 2010 (express edition will work fine). Create an empty ASP.Net web site from the available templates and choose a suitable name for it. Choose C# as the development language.

2) Add a new item to your website,a web form.Leave the default name.

3) Add a new project to your solution, a class library project.Remove the class1.cs file from the project. 

4) Add a new ADO.Net Entity Data model to the class library project. Choose a suitable name for it, e.g CompanyEmployees.edmx.

5) Then the Wizard pops up. Choose "Generate from Database" option and finish the steps of the wizard by selecting only the tables (Companies,Employees) to be included in the model. Now you have your model ready with all the entities and the generated code.

6) Lazy Loading is enabled by default in EF version 4. Let's write a small query and see how it behaves.We will get the company names for the first 3 companies and then ask for their employee's first name.

You must have your SQL Profiler launched and have the SQL:BatchStarting and RPC:Completed events selected in your new trace.Run the trace.Remember we do not have to do anything to enable Lazy Loading.

In the Page_Load() event handling routine of the Default.aspx page type,

using(var ctx = new CompanyEmployeesEntities())
        {
         
            var query = ctx.Companies.Take(3);

            foreach (var company in query)
            {
                Response.Write(company.CompanyName);

                Response.Write("<br/>");


                foreach (var emp in company.Employees)
                {
                    Response.Write(emp.EmpFirstName);
                    Response.Write("<br/>");
                }
            }

        }

 

7)  Run your application and you will see that we get the company names and their associated employees first names. Have a look at the SQL Profiler trace. In my case we have 4 queries (our database is being hit 4 times). I have the first query selecting the first 3 companies and then 3 queries to get the employees for that company.

  •  SELECT TOP (3)
    [c].[CompanyID] AS [CompanyID],
    [c].[CompanyName] AS [CompanyName]
    FROM [dbo].[Companies] AS [c]

  • exec sp_executesql N'SELECT
    [Extent1].[EmpID] AS [EmpID],
    [Extent1].[EmpFirstName] AS [EmpFirstName],
    [Extent1].[EmpLastName] AS [EmpLastName],
    [Extent1].[Email] AS [Email],
    [Extent1].[CompanyID] AS [CompanyID],
    [Extent1].[Street] AS [Street],
    [Extent1].[City] AS [City],
    [Extent1].[Country] AS [Country],
    [Extent1].[PostalCode] AS [PostalCode],
    [Extent1].[Phone] AS [Phone]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE [Extent1].[CompanyID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1
  • exec sp_executesql N'SELECT
    [Extent1].[EmpID] AS [EmpID],
    [Extent1].[EmpFirstName] AS [EmpFirstName],
    [Extent1].[EmpLastName] AS [EmpLastName],
    [Extent1].[Email] AS [Email],
    [Extent1].[CompanyID] AS [CompanyID],
    [Extent1].[Street] AS [Street],
    [Extent1].[City] AS [City],
    [Extent1].[Country] AS [Country],
    [Extent1].[PostalCode] AS [PostalCode],
    [Extent1].[Phone] AS [Phone]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE [Extent1].[CompanyID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2
  • exec sp_executesql N'SELECT
    [Extent1].[EmpID] AS [EmpID],
    [Extent1].[EmpFirstName] AS [EmpFirstName],
    [Extent1].[EmpLastName] AS [EmpLastName],
    [Extent1].[Email] AS [Email],
    [Extent1].[CompanyID] AS [CompanyID],
    [Extent1].[Street] AS [Street],
    [Extent1].[City] AS [City],
    [Extent1].[Country] AS [Country],
    [Extent1].[PostalCode] AS [PostalCode],
    [Extent1].[Phone] AS [Phone]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE [Extent1].[CompanyID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=3

 

8)  Let's move on to the Eager Loading option that we have . Under this line

 using(var ctx = new CompanyEmployeesEntities())
        {

 type


 ctx.ContextOptions.LazyLoadingEnabled = false;

 which means that I disable Lazy Loading.This is an option that belongs to the ObjectContext object.

9) Run again your application. You will not see the related entities (employees first names) displayed on the screen. 

In the SQL Profiler, the trace has only this query executed in my database,

  • SELECT TOP (3) [c].[CompanyID] AS [CompanyID], [c].[CompanyName] AS [CompanyName] FROM [dbo].[Companies] AS [c]

 Let's try to load the related entities using the Include() method

 

Just after the line your just typed,

 ctx.ContextOptions.LazyLoadingEnabled = false;

add this line of code

 var query = ctx.Companies.Include("Employees").Take(3);

Comment out this line of code.

var query = ctx.Companies.Take(3);

 

Clear the trace results and start your trace again.

Run your application and see the results.You will see the 3 companies and their related entities - employees first names displayed on the screen.

If you look at the trace results you will see just one query hitting the database. In my case it looks like this

SELECT
[Project1].[CompanyID] AS [CompanyID],
[Project1].[CompanyName] AS [CompanyName],
[Project1].[C1] AS [C1],
[Project1].[EmpID] AS [EmpID],
[Project1].[EmpFirstName] AS [EmpFirstName],
[Project1].[EmpLastName] AS [EmpLastName],
[Project1].[Email] AS [Email],
[Project1].[CompanyID1] AS [CompanyID1],
[Project1].[Street] AS [Street],
[Project1].[City] AS [City],
[Project1].[Country] AS [Country],
[Project1].[PostalCode] AS [PostalCode],
[Project1].[Phone] AS [Phone]
FROM ( SELECT
    [Limit1].[CompanyID] AS [CompanyID],
    [Limit1].[CompanyName] AS [CompanyName],
    [Extent2].[EmpID] AS [EmpID],
    [Extent2].[EmpFirstName] AS [EmpFirstName],
    [Extent2].[EmpLastName] AS [EmpLastName],
    [Extent2].[Email] AS [Email],
    [Extent2].[CompanyID] AS [CompanyID1],
    [Extent2].[Street] AS [Street],
    [Extent2].[City] AS [City],
    [Extent2].[Country] AS [Country],
    [Extent2].[PostalCode] AS [PostalCode],
    [Extent2].[Phone] AS [Phone],
    CASE WHEN ([Extent2].[EmpID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM   (SELECT TOP (3) [c].[CompanyID] AS [CompanyID], [c].[CompanyName] AS [CompanyName]
        FROM [dbo].[Companies] AS [c] ) AS [Limit1]
    LEFT OUTER JOIN [dbo].[Employees] AS [Extent2] ON [Limit1].[CompanyID] = [Extent2].[CompanyID]
)  AS [Project1]
ORDER BY [Project1].[CompanyID] ASC, [Project1].[C1] ASC

10) Now let's investigate the Explicit Loading option.

Comment out this line of code

var query = ctx.Companies.Include("Employees").Take(3);
   

 After these 2 lines of code type,

 Response.Write(company.CompanyName);

 Response.Write("<br/>");

 type,         
 company.Employees.Load();

Clear the trace and start the trace again in the SQL Profiler. Run your application and see the results.You will see the 3 companies and their related entities - employees first names displayed on the screen.

If you look at the SQL Profiler trace we have the same queries like in the Lazy Loading option in step 7. So we have 4 queries hitting the database.

So, I think now we are clear on what happens. Bear in mind that 4 queries sometimes can be less costly than one query.

It is nice to have options. Use them carefully depending on the specific requirements of your application.

Email me if you need the source code.

Hope it helps!!!

Data binding in an ASP.Net application with Entity Framework

This is going to be the eighth post of a series of posts regarding ASP.Net and the Entity Framework and how we can use Entity Framework to access our datastore. You can find the first one here, the second one here , the third one here , the fourth one here, the fifth one here ,the sixth one here and the seventh one here.

I have a post regarding ASP.Net and EntityDataSource. You can read it here.I have 3 more posts on Profiling Entity Framework applications. You can have a look at them here, here and here.

In this post I will be looking into databinding and Entity Framework. I will give a hands on example. We will have a dropdown list that will bind to results of a query (results implement the IQueryable interface). Then as the user makes his selection from the dropdown list the filtered results from another query (results implement the IQueryable interface) will bind to a Gridview control.

I assume that you have access to a version of SQL Server.If you do not, you can download and install the free SQL Server Express edition from here. 

In this post (step 9) you will find a T-SQL script that will create the database objects of the CompanyEmployees database.Before that you execute the T-SQL script you must create in the Query window the CompanyEmployees database yourself. You can download the companiesemployeesinsertt.zip to insert data into the tables.

1) Launch Visual Studio 2010 (express edition will work fine). Create an empty ASP.Net web site from the available templates and choose a suitable name for it. Choose C# as the development language.

2) Add a new item to your website,a web form.Leave the default name. Add a dropdownlist control on the form. Add a Gridview control on the form. Leave the default names.

3) Add a new project to your solution, a class library project.Remove the class1.cs file from the project. 

4) Add a new ADO.Net Entity Data model to the class library project. Choose a suitable name for it, e.g CompanyEmployees.edmx.

5) Then the Wizard pops up. Choose "Generate from Database" option and finish the steps of the wizard by selecting only the tables (Companies,Employees) to be included in the model. Now you have your model ready with all the entities and the generated code.

6) Now we want to populate the dropdown list control with the names of the companies.Set the AutoPostBack property of the dropdown list control to True.

AutoPostBack="True" 

In the Page_Load event handling routine of the default.aspx page type ,

try
  {
  if (!IsPostBack)
  {
  using (var ctx = new CompanyEmployeesEntities())
  {
               
  var companies = from comp in ctx.Companies
      where comp.CompanyName.StartsWith("Ad")
      orderby comp.CompanyName
      select new { comp.CompanyID, comp.CompanyName };

     DropDownList1.DataValueField = "CompanyID";
     DropDownList1.DataTextField = "CompanyName";
     DropDownList1.DataSource = companies;
     DataBind();


     }

   }
 }

        
catch (Exception ex)
 {
            
Response.Write(ex.Message);
 }

 

7) Run your application and see the dropdown list control populated with the companies names that start with "Ad".

The companies returns an anonymous type of something.Remember that the var keyword declares and initialises anonymous types.

In my case it returns (the anonymous type is)

{System.Data.Objects.ObjectQuery<<>f__AnonymousType0<int,string>>}

Now we see that this is an ObjectQuery<T> that implements the IListSource interface. Through that interface a binding list is obtained that our dropdown list control knows how to talk to.

8) In the DropDownList_SelectedIndexChanged event handling routine type,

  {
        try 
{         
using (var ctx = new CompanyEmployeesEntities())
            {
                int compID;
                if(int.TryParse(DropDownList1.SelectedValue,out compID))
                {
                var empcomp = from emp in ctx.Employees
                              where emp.CompanyID == compID
                              orderby emp.EmpFirstName
                              select new {emp.EmpFirstName,emp.EmpLastName };


                GridView1.DataSource = empcomp;
                GridView1.DataBind();


                }
            }

}
catch (Exception ex)
{

 Response.Write(ex.Message);
}
         
    }

 

This is a very easy bit of code to follow. I create again the ObjectContext object and the I get the CompanyID for the selected company name and store it on the compID variable.

Then I construct my query and get only the first and last names of the employee(s) from the Employees table where the CompanyID (foreign key) matches the value in the compID variable.

Then databinding to the Gridview is a piece of cake.

9) Run your application. Make sure there are no compile or run time errors. Select a company name from the dropdown list control and see the first and last name of that Employee(s) belonging to that company displayed on the Gridview control. Set some breakpoints on the code and step into the code.

Drop me an email if you want the source code.

Hope it helps!!!!

Identity Map Pattern and the Entity Framework

This is going to be the seventh post of a series of posts regarding ASP.Net and the Entity Framework and how we can use Entity Framework to access our datastore. You can find the first one here, the second one here and the third one here , the fourth one here, the fifth one here and the sixth one here.

I have a post regarding ASP.Net and EntityDataSource. You can read it here.I have 3 more posts on Profiling Entity Framework applications. You can have a look at them here, here and here.

In this post I will be looking into the various patterns that Entity Framework implements in order to have a O/R mapper system that functions properly.

We will show all these patterns by going through some easy to follow, hands-on examples. There will be a few posts regarding these patterns.In this one we will talk about Identity Map Pattern and how object identity is maintained.

I assume that you have access to a version of SQL Server.If you do not, you can download and install the free SQL Server Express edition from here. 

In this post (step 9) you will find a T-SQL script that will create the database objects of theCompanyEmployees database.Before that you execute the T-SQL script you must create in the Query window the CompanyEmployees database yourself. You can download the companiesemployeesinsertt.zip to insert data into the tables.

1) Launch Visual Studio 2010 (express edition will work fine). Create an empty ASP.Net web site from the available templates and choose a suitable name for it. Choose C# as the development language.

2) Add a new item to your website,a web form.Leave the default name.

3) Add a new project to your solution, a class library project.Remove the class1.cs file from the project. 

4) Add a new ADO.Net Entity Data model to the class library project. Choose a suitable name for it, e.g CompanyEmployees.edmx.

5) Then the Wizard pops up. Choose "Generate from Database" option and finish the steps of the wizard by selecting only the tables (Companies,Employees) to be included in the model. Now you have your model ready with all the entities and the generated code. We are ready to leverage this entity data model from our client application.

6) The first pattern I want to explore in EF is the Identity Map Pattern. Entity framework must enforce identity in the same way that databases enforce row identity with the use of primary keys.By that I mean that rows in the database have unique primary key values in the primary key column that distinguish them from any other row in the table.In Entity Framework we have objects in memory representing the rows retrieved from the database.So in our scenario (model) we have in-memory collection of Companies and Employees objects.We would like to have our objects in memory behaving the same way as rows. When we query the data model for the same company entity (the same row in the database) twice, we will not get back 2 distinct objects in memory.Instead we will get the same object reference.

7) In order to demonstrate that we will write some code in the Page_Load event handling routine of the default.aspx page.

So inside the routine type,

 Company comp1;
 Company comp2;
 Company comp3;
 Company comp4;

       
    using (var context = new CompanyEmployeesEntities())
    {

    comp1 = context.Companies.Where(comp => comp.CompanyID == 5).First();
    comp2 = context.Companies.Where(comp => comp.CompanyID == 5).First();
    comp3 = context.Companies.Where(comp => comp.CompanyID == 8).First();
    comp4 = context.Companies.Where(comp => comp.CompanyID == 8).First();

    Debug.Assert(Object.ReferenceEquals(comp1, comp2));
    Debug.Assert(Object.ReferenceEquals(comp3, comp4));

    }
       

8) Run your application. I query the database (twice) for the records with CompanyID=5 and CompanyID=8. I store them in 4 objects and check to see if they have the same object reference or not. If you run the code, you will not get any error(if the condition was false we will get a message box with the call stack).That means we have comp1 and comp2 objects representing the same object/row. The same applies for comp3 and comp4 objects.

More about the Debug.Assert method, you can find here. Imagine what would have happened if we had 2 or 3 or even 4 objects in the memory representing the same object. What would have happened when we tried to persist those changes in the database? Who would win?The last one? Would we have an optimistic concurrency exception?

That sounds very confusing and believe me it would be a bad situation to be in. Thank God, EF does not work like that. Entity Framework implements the Identity Map Pattern in order to work as we discussed earlier.The main points to keep in mind are:

  • There is something that is called object cache.It keeps track of all the entities retrieved from the database. In order for this to work there must be a key value in the Entity (CompanyID).So make sure you always have an Entity key defined in the model.

  • When another query will ask for an entity that has been previously retrieved it will get the same object instance.

  • Each ObjectContext object has its own object cache.


9) In order to prove this last bullet point, comment out everything you have in the Page_Load event handling routine.

 

var context = new CompanyEmployeesEntities();
var context1 = new CompanyEmployeesEntities();

        
var comp1 = context.Companies.Where(comp => comp.CompanyID == 5).First();
var comp2 = context1.Companies.Where(comp => comp.CompanyID == 5).First();

comp2.CompanyName="Another Company";
context1.SaveChanges();

Debug.Assert(comp1.CompanyName != comp2.CompanyName);

Response.Write(comp1.CompanyName);
Response.Write("<br/>");
Response.Write(comp2.CompanyName);

10) Run your application and see the results.You will not get any error message.You will see 2 different company names for the same entity (CompanyID=5). In this example I have created 2 object context objects-context,context1. As you will see the change I make in the second object context does not have any effect on the first one.

The first entity

var comp1 = context.Companies.Where(comp => comp.CompanyID == 5).First();

will not be notified by the changes made to the database from the 

var comp2 = context1.Companies.Where(comp => comp.CompanyID == 5).First();

comp2.CompanyName="Another Company";
context1.SaveChanges();
 

because it will not get the value from the database but from the Object Cache for that Object Context. Hopefully that makes sense.

We only care to have consistency inside in each ObjectContext.

The above scenario will have some problems when it comes to concurrency(many object contexts updating the same entity) but I have explained how to deal with concurrency conflicts in this post.

Email me if you need the source code.

Hope it helps!!!

 

Entity Framework and Plain Old CLR Objects in an ASP.Net application

This is going to be the sixth post of a series of posts regarding ASP.Net and the Entity Framework and how we can use Entity Framework to access our datastore. You can find the first one here, the second one here and the third one here , the fourth one here and the fifth one here.

I have a post regarding ASP.Net and EntityDataSource. You can read it here.I have 3 more posts on Profiling Entity Framework applications. You can have a look at them here, here and here.

In this post I will be looking into the issue of Pocos and how to leverage their use in Entity framework in an ASP.Net application.

I assume that you have access to a version of SQL Server.If you do not, you can download and install the free SQL Server Express edition from here. 

In this post (step 9) you will find a T-SQL script that will create the database objects of the CompanyEmployees database.Before that you execute the T-SQL script you must create in the Query window the CompanyEmployees database yourself. You can download the companiesemployeesinsertt.zip to insert data into the tables.

1) Launch Visual Studio 2010 (express edition will work fine). Create an empty ASP.Net web site from the available templates and choose a suitable name for it. Choose C# as the development language.

2) Add a new project to your solution, a class library project.Remove the class1.cs file from the project.

3) Add a new ADO.Net Entity Data model to the class library project. Choose a suitable name for it, e.g CompanyEmployees.edmx.

4) Then the Wizard pops up. Choose "Generate from Database" option and finish the steps of the wizard by selecting only the tables to be included in the model.If you have done everything correctly so far you will have the model as it is displayed in the picture below. We do not want to use the classes/code that was generated from the Entity Engine Generator. So as you will see below, the "Code Generation Strategy" is set to None. Then you will see, if you go to the code behind file, that basically is an empty file. We will step in here and add our own classes.

 

 

5) Add another class file to your class library project.Name it DataAccess.cs. Inside there we will write our own plain classes that will reflect the model we have.

Inside the DataAccess.cs file type,

public class Company
  {
      public Company()
      {

          Employees = new List<Employee>();

      }

      public int CompanyID { getset; }
      public string CompanyName { getset; }
      public virtual List<Employee> Employees { getprivate set; }
  }

    public class Employee
    {
        public Employee()
        {

        }


        public int EmpID { getset; }
        public string EmpFirstName { getset; }
        public string EmpLastName { getset; }
        public string Email { getset; }
        public int CompanyID { getset; }
        public string Street { getset; }
        public string City { getset; }
        public string Country { getset; }
        public string PostalCode { getset; }
        public string Phone { getset; }
        public Company Company { getset; }


    }



public class CompanyEmployeesEntities:ObjectContext
    {
public  CompanyEmployeesEntities():base("name=CompanyEmployeesEntities")
            {

                ContextOptions.LazyLoadingEnabled = true;

            }

        public ObjectSet<Company>Companies
            {
                get{return CreateObjectSet<Company>();}

            }

        public ObjectSet<Employee>Employees
            {
                get{return CreateObjectSet<Employee>();}

            }

    
   }

 

6) Let me explain what I do in the code above.I create the classes Company and Employee bearing in mind they must have the same name as in my model.All the properties that I define inside my classes must have the same name and must have the same type as well.Inside the Company class, I am instantiating inside the constructor a list of Employees

 public Company()
      {

          Employees = new List<Employee>();

      }

 

 All the properties from the model must be represented in the class file. You can also add more properties if you want.

 Then after that I create the ObjectContext class.I am sure you have realised by now that this is the most important class in the EF paradigm. I also set the lazy loading option to true.

ContextOptions.LazyLoadingEnabled = true;

 

 7) So now we are ready to write a LINQ to Entities query to retrieve data from our model. Let's say that we want to get all the companies that their name starts with "Tr" and have more than 2 employees.

We want to display the company name and the Employee's first and last name.

In the Page_Load event handling routine of the Default.aspx page

 CompanyEmployeesEntities ctx = new CompanyEmployeesEntities();


var companies = from comp in ctx.Companies 
where comp.CompanyName.StartsWith("Tr") &&
 comp.Employees.Count >= 2select comp;


        foreach (var comp in companies)
        {
           
            Response.Write("<h2>" + comp.CompanyName + "</h2>");
            Response.Write("<br/>");
            foreach (Employee emp in comp.Employees)
            {

               
Response.Write("<strong><em>" + emp.EmpFirstName + " " + 
emp.EmpLastName + "</em></strong>");
Response.Write("<br/>");
            
            }
           
        }

 

8) Run your application (set breakpoints if you want) and see the results displayed on the screen.

If you do not want to type the code yourself for the POCO classes, you can download through the Extension Manager in Visual Studio(Tools->Extension Manager) ( from the Online Gallery go to Templates and select Database -> ADO.NET C# POCO Entity Generator) and generate the hand made classes (we wrote above) automatically.

Email me if you need the source code.

Hope it helps!!!

How to handle concurrency in Entity Framework

This is going to be the fifth post of a series of posts regarding ASP.Net and the Entity Framework and how we can use Entity Framework to access our datastore. You can find the first one here, the second one here and the third one here. You can read the fourth one here.

I have a post regarding ASP.Net and EntityDataSource. You can read it here.I have 3 more posts on Profiling Entity Framework applications. You can have a look at them here, here and here.

In this post I will be looking into the issue of concurrency and how Entity Framework manages concurrency

I will be using Entity Framework version 4.0 that ships with Visual Studio 2010 and .Net 4.0.  

I assume that you have access to a version of SQL Server and AdventureWorkLT database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample AdventureWorkLT database, click here

1) Launch Visual Studio 2010 (express edition will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) Add a new item to your site, a web form. Leave the default name, Default.aspx

3) Add a new project to your solution, a class library project.Remove the class1.cs file from the project.

4) Add a new item to your class library project, a ADO.Net Entity Data model. Choose a suitable name for it, e.g AdventureWorkLT.edmx.

5) Then the Wizard pops up. Choose "Generate from Database". Follow the steps (5-9) in this post to complete the generation of the entity data model.

6) When we are talking about concurrency in an application where EF acts as the data access layer, our main focus is how to make sure that the integrity of the data in the database is ensured in a multi user connected environment. Databases are very good in managing concurrent users. All the objects/entities are retrieved from the datastore into the memory. The changes we make are applied in the memory and then we flush the changes to the database. But what happens if those entities have been changed from another user in the time it took us to update them in memory?Well someone will say, "Dude, you just have to use pessimistic concurrency and your problems are solved."

Well, I do not want to go down that road. Locks are applied to all related data when I use this type of concurrency management. Remember we are in a multiuser enviroment. That means I want to have data integrity on the one hand but I also want performance to be at an acceptable level. With pessimistic concurrency you limit scalability and performance.

So I will use optimistic concurrency which in plain words mean "Hm... I want to check if anyone has modified the data in the database while I did my own modifications in memory".

Out of the box , EF does not use any concurrency mechanism. It is off by default. That is exactly the opposite of LINQ to SQL where optimistic concurrency is on by default.

There is an attribute for every property of an entity that is called Concurrency Mode. if you go to the EDM designer and select the Customer entity and then e.g the NameStyle property and then hit F4 from the keyboard the Properties window will become active. There you can set the Concurrency Mode to Fixed.  Go to the EDM designer and locate the ModifiedDate property for the Customer entity.Set the Concurrency Mode to Fixed.

Have a look at the picture below to see what I mean.

 

 7) In order to test that optimistic concurrency works as expected I will write some code in the Page_Load event handling routine of the Default.aspx page.

I will create two instances of the ObjectContext class, in our case it is AdventureWorksLTEntities . Then I will try to modify the value for the same property  of the object/entity which has CustomerID=3 and see how EF handles concurrency. In the Page_Load event handling routine, type

            AdventureWorksLTEntities ctx = new AdventureWorksLTEntities();
            AdventureWorksLTEntities context = new AdventureWorksLTEntities();
            var mycustomer = ctx.Customers.Where(cust => cust.CustomerID == 3).First();
            var othercustomer = context.Customers.Where(cust => cust.CustomerID == 3).First();

            try
            {

                mycustomer.ModifiedDate = DateTime.Now.AddDays(1);
                ctx.SaveChanges();

                othercustomer.ModifiedDate = DateTime.Now.AddDays(100);
                context.SaveChanges();



            }
            catch (OptimisticConcurrencyException ex)
            {

                context.Refresh(System.Data.Objects.RefreshMode.StoreWins, othercustomer);
                othercustomer.ModifiedDate = DateTime.Now.AddDays(100);
                context.SaveChanges();
            }



In those lines

            AdventureWorksLTEntities ctx = new AdventureWorksLTEntities();
            AdventureWorksLTEntities context = new AdventureWorksLTEntities();
            var mycustomer = ctx.Customers.Where(cust => cust.CustomerID == 3).First();
            var othercustomer = context.Customers.Where(cust => cust.CustomerID == 3).First();

 

I create two instances of the AdventureWorksLTEntities and store them in 2 different objects.

Then I create 2 more variables that store the exact same entity object in them.

In those lines

try
            {

                mycustomer.ModifiedDate = DateTime.Now.AddDays(1);
                ctx.SaveChanges();

                othercustomer.ModifiedDate = DateTime.Now.AddDays(100);
                context.SaveChanges();



            }
            catch (OptimisticConcurrencyException ex)
            {

                context.Refresh(System.Data.Objects.RefreshMode.StoreWins, othercustomer);
                othercustomer.ModifiedDate = DateTime.Now.AddDays(100);
                context.SaveChanges();
            }

 

I update the value for the first customer. That will work fine. Then when you move on with the code execution and reach the second , context.SaveChanges(); ,your code will jump inside catch statement.

When this happens I know that someone else has modified ModifiedDate value for the customer object with CustomerID = 3.So I need to refresh the data from the database for that object/record and then apply the new changes and that is exactly what I do inside the catch block.

Run your application (If I were you i would place a breakpoint in the first line of the code and execute it step by step) and see the results.

You can have SQL Profiler open and see the SQL statements executed and you can query the table Customer as well to see when actually the data changes.

Drop me an email if you want the source code.

Hope it helps!!!

 

Reading data from an Entity Framework data model through a WCF Data Service

This is going to be the fourth post of a series of posts regarding ASP.Net and the Entity Framework and how we can use Entity Framework to access our datastore. You can find the first one here, the second one here and the third one here.

I have a post regarding ASP.Net and EntityDataSource. You can read it here.I have 3 more posts on Profiling Entity Framework applications. You can have a look at them here, here and here.

Microsoft with .Net 3.0 Framework, introduced WCF. WCF is Microsoft's choice to design/build Service Oriented Architecture applications. In this post I will not be hitting the database directly in a connected way. I will use a WCF data service to work with the data in a disconnected, n-tier way.

I assume that you have access to a version of SQL Server.If you do not, you can download and install the free SQL Server Express edition from here. 

In this post (step 9)you will find a T-SQL script that will create the database objects of the CompanyEmployees database.Before that you execute the T-SQL script you must create in the Query window the CompanyEmployees database yourself. You can download the companiesemployeesinsertt.zip to insert data into the tables.

1) Launch Visual Studio 2010 (express edition will work fine). Create an ASP.Net web application from the available templates and choose a suitable name for it. Choose C# as the development language.

2) Add a new ADO.Net Entity Data model to the project. Choose a suitable name for it, e.g CompanyEmployees.edmx.

3) Then the Wizard pops up. Choose "Generate from Database".Choose the database which you will base the model on , CompanyEmployees.Follow exactly the steps 5-9 that you can see in this post to finish the steps of the wizard.Now you have your entity data model.

4) Now we will add the WCF data service. Add another item to your project. From the available templates select WCF Data Service. Name it CompEmp.svc.

5) When you do that, you will see some new references added to the project. The most important is System.ServiceModel.Inside this namespace are all of the classes and methods necessary to build all our services.

Inside my CompEmp.svc.cs file, I have this code that was generated for me

namespace N_tier2
{
    public class CompEmp : DataService/* TODO: put your data source class name here */ >
    {
        // This method is called only once to initialize service-wide policies.
        public static void InitializeService(DataServiceConfiguration config)
        {
 // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
 // Examples:
// config.SetEntitySetAccessRule("MyEntityset", EntitySetRights.AllRead);
// config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);
 config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
        }
    }
}

 

We must change this line of code

public class CompEmp : DataService/* TODO: put your data source class name here */ > 

with this one

public class CompEmp : DataService<CompanyEmployeesEntities>

What we did here, is to "connect" the WCF service to our data model.

6) Now you can build and run your project. In the browser window you will see something like this

 
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<service xmlns="http://www.w3.org/2007/app" xmlns:app="http://www.w3.org/2007/app" xmlns:atom="http://www.w3.org/2005/Atom" xml:base="http://localhost:4441/CompEmp.svc/"> -<workspace> <atom:title>Default</atom:title> </workspace> </service>

So now we see that our service is up and running.

We have our WCF Service(REST service) accessing our database through the Entity data model.Now in order to see the actual data we must tell the WCF service what data we want to access.

I will change this line of code in the CompEmp.svc.cs file

// config.SetEntitySetAccessRule("MyEntityset", EntitySetRights.AllRead);

with this one

config.SetEntitySetAccessRule("*"EntitySetRights.All);

With the line above we basically say that the WCF Service can access all the entities in the model and can do all operations on them (read,create,delete,update)

Run your application again. Under the url, http://localhost:4441/CompEmp.svc/Employees (in your case it will be slightly different) you will see the Employees data.

We can get the details for a specific employee by typing http://localhost:4441/CompEmp.svc/Employees(3)

7) We will create a new web application that will consume the WCF Service. Add a new project to the solution, a web application. Name it ConsumeWCF.

Then we need to add a service reference to the project.This will play the role of the gateway to the project that contains the WCF service.

In this web application in the Solutions Explorer go to References  and then Add Service Reference. See the picture below in order to complete the steps. Basically you will find the service that exists in the same solution.

 

 

8) In the ConsumeWCF web application, in the Default.aspx page add a bulletedlist web server control.

 <asp:BulletedList ID="BulletedList1" runat="server">
    </asp:BulletedList>

In the Page_Load event handling routine of the Default.aspx page type


 try
 {
 CompanyEmployeesEntities ctx = new CompanyEmployeesEntities(new Uri
("http://localhost:4441/CompEmp.svc"));
                
 foreach (Company comp in ctx.Companies)
 {
                     
BulletedList1.Items.Add(string.Format("{0} {1}",comp.CompanyID,
comp.CompanyName)); 
 }
                
}
            
 catch (Exception ex)
 {
                
 Response.Write(ex.Message);
 }

In this simple bit of code I instantiate the class that represents the service. 

Run your application and see the data printed on the screen.

10) Let's write a more complicated query to get more data from the database through the WCF service that accesses the Entity data model.

Add a button on the About.aspx page. Add a bulletedlist web server control on the same page.

In the Button1_Click event handling routine of the About.aspx page type

 

try
  {
  CompanyEmployeesEntities ctx = new CompanyEmployeesEntities(new Uri
("http://localhost:4441/CompEmp.svc"));

  var query = from emp in ctx.Employees
              where emp.EmpFirstName.StartsWith("Le") && emp.Country == "Iran"
              orderby emp.Country descending
              select emp;

       foreach (var item in query)
       {

BulletedList1.Items.Add(string.Format("{0} {1}", item.EmpLastName, 
item.EmpFirstName));
       }


}

catch (Exception ex)
      {

      Response.Write(ex.Message);
      }


Run your application and see the results printed on the screen.

Hope this helps!!!

Using stored procedures with Entity Framework in an ASP.Net application

This is going to be the third post of a series of posts regarding ASP.Net and the Entity Framework and how we can use Entity Framework to access our datastore. You can find the first one here and the second one here.

I have a post regarding ASP.Net and EntityDataSource. You can read it here.I have 3 more posts on Profiling Entity Framework applications. You can have a look at them here,here and here.

In this post I will show you how to select,insert,update,delete data in the database using EF and stored procs. A lot of people use stored procedures when they use EF so they know exactly what hits their database.

I will be using Entity Framework version 4.0 that ships with Visual Studio 2010 and .Net 4.0.  

In this very first post we will create a simple website and use Entity Framework as our data access layer.

I assume that you have access to a version of SQL Server.If you do not, you can download and install the free SQL Server Express edition from here.

1) Launch Visual Studio 2010 (express edition will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) Add a new item to your site, a web form. Leave the default name, Default.aspx

3) Add a new project to your solution, a class library project.Remove the class1.cs file from the project.

4) Create a database in the instance of your SQL Server and name it CompanyEmployees . In this post you will find the SQL Script that creates a table named Employees and a table called Companies.They have various columns and they also have a one to many relationship among them (Foreign Key constraint).

5) Add a new item to your class library project, a ADO.Net Entity Data model. Choose a suitable name for it, e.g CompanyEmployees.edmx.

6) Then the Wizard pops up. Choose "Generate from Database".Choose the database which you will base the model on , CompanyEmployees.Follow exactly the steps 5-9 that you can see in this post to finish the steps of the wizard.Now you have your entity data model. Let's create a stored procedure to select some data from the Employees table.

We will have a stored procedure that takes as an input parameter the country of an employee and return information about this employee.

7) In the query window type,

USE [CompanyEmployees]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[GetEmployeesByCountry] @Country NVARCHAR(50)
AS
    BEGIN
        SET NOCOUNT ON ;
   
        SELECT  emp.EmpFirstName ,
                emp.EmpLastName ,
                emp.Email ,
                emp.City
        FROM    Employees emp
        WHERE   country = @Country
    END
GO
 

8) Execute the T-SQL and you have the GetEmployeesByCountry stored procedure.Now we want to import this new database object into our Entity Data Model.The stored procedure will become a method in our entity data model. We have to switch to the CompEmp.edmx file and our entity designer and right click on the designer and choose "Update model from database".Select the stored procedure and click "Finish" Have a look at the picture below

 

9) Now we will choose Model Browser. Select  Function Imports  and then choose Add Function Import... and choose the stored procedure GetEmployeesByCountry.

Have a look at the picture below


 

We have to create a custom type-New complex type since our stored procedure does not return a whole entity.

Custom type means basically that will create a custom class with just those properties defined in the procedure/method. When you click "OK", you will have imported the procedure in the model.

Have a look at the CompEmp.Designer.cs file to see the generated code for the procedure and the complex type.

10) Let's use the stored procedure now.The main idea is to have the dropdown list control filled in with all the countries and then as the user picks countries to pass this country as a parameter to the stored procedure and bind the resultset to the Gridview control

Add a dropdown list on the Default.aspx page.Add a GridView control on the page. In the Page_Load event handling routine type,

 

if (!IsPostBack)

 { 
  using (CompanyEmployeesEntities ctx = new CompanyEmployeesEntities())
  {
        

        var query = from myemp in ctx.Employees
                    select myemp.Country;

        DropDownList1.DataSource = query;

        DropDownList1.DataBind();
   }
 }

 11) In the type DropDownList1_SelectedIndexChanged event type,

 using (CompanyEmployeesEntities ctx = new CompanyEmployeesEntities())
        {
var query = ctx.GetEmployeesByCountry(DropDownList1.SelectedValue.ToString());

        GridView1.DataSource = query;
        GridView1.DataBind();
        }

12) Run your application and see all the countries in the dropdown list control. Choose one and see the employee's details for that country printed on the screen.Launch SQL Profiler and have a look at the stored procedure being called and executed.

In the following steps I will create the stored procedures I need to insert,delete,update data from the database.

You will need to bring the stored procedures into the model by hitting "Update model from database..." . Then you will need to map those functions to the Companies entity.

We know that the EF generates its own insert,update and delete statements. We can override the default behaviour by mapping functions to a specific entity. We always need to call the SaveChanges() method and the stored procedure is called instead of the native commands.

 Have a look at the picture below to see the complete mapping.


13) Now we will create another stored procedure to insert a record in the Companies table.In the Query window type,

 

USE [CompanyEmployees]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertCompany]
    @Companyname NVARCHAR(50)
AS
    BEGIN

        SET NOCOUNT ON ;

       

INSERT  INTO dbo.Companies
                ( CompanyName )
        VALUES  ( @CompanyName )
       

    END



GO

 

14) Add a new web form to the page.Name it StoredProcs.aspx. Add a button on the form. In the Button1_Click() event handling routine type,

 try
   {
    using (CompanyEmployeesEntities ctx = new CompanyEmployeesEntities())
      {
         Company mycompany = new Company()
          {

             CompanyName = "Ficticious Company"

          };

          ctx.AddToCompanies(mycompany);
          ctx.SaveChanges();
          Label1.Text = "Record Inserted";

      }

     }
     catch (Exception ex)
        {

        Response.Write(ex.InnerException.Message);
        }

 

15) Run your application and insert a new record. If you open SQL Profiler you will see that the InsertCompany stored procedure is used instead of the native sql commands.


16) Now we will create another stored procedure to update a record in the Companies table.In the Query window type,

USE [CompanyEmployees]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[UpdateCompanies]
    @compID INT ,
    @compname NVARCHAR(50)
AS
    BEGIN
        SET NOCOUNT ON ;
        UPDATE  Companies
        SET     CompanyName = @compname
        WHERE   CompanyID = @compID

    END


GO

17) Add a button on the form. In the Button2_Click() event handling routine type

try
  {
    using (CompanyEmployeesEntities ctx = new CompanyEmployeesEntities())
    {

    var mycompany = ctx.Companies.Where(comp => comp.CompanyName == 
"Ficticious Company").First();

    mycompany.CompanyName = "My Fictitious Company";
    ctx.SaveChanges();
    Label1.Text = "Record Update";

     }

  }
        catch (Exception ex)
        {

            Response.Write(ex.InnerException.Message);
        }

18) Run your application and update the existing record. If you open SQL Profiler you will see that the UpdateCompanies stored procedure is used instead of the native sql commands.

19) Now we will create another stored procedure to delete a record in the Companies table.In the Query window type,

USE [CompanyEmployees]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[DeleteCompanies] @compID INT
AS
    BEGIN

        DELETE  FROM dbo.Companies
        WHERE   CompanyID = @compID

    END

GO

20) Add a button on the form. In the Button3_Click() event handling routine type

try
 {
   using (CompanyEmployeesEntities ctx = new CompanyEmployeesEntities())
   {

 var mycompany = ctx.Companies.Where(comp => comp.CompanyID==1005)
.First();

      ctx.DeleteObject(mycompany);
      ctx.SaveChanges();
      Label1.Text = "Record Deleted";

            }

        }
        catch (Exception ex)
        {

            Response.Write(ex.InnerException.Message);
        }

 

 21) Run your application and delete an existing record.In this sample of mine I just hard coded the CompanyID. If you open SQL Profiler you will see that the DeleteCompanies stored procedure is used instead of the native sql commands.

Drop me an email if you want  the source code.

Hope it helps!!!

 

Creating an Entity Data Model using the Model First approach

This is going to be the second post of a series of posts regarding  Entity Framework and how we can use Entity Framework version 4.0 new features. You can read the first post here.

In order to follow along you must have some knowledge of C# and know what an ORM system is and what kind of problems Entity Framework addresses.It will be handy to know how to work inside the Visual Studio 2010 IDE.

I have a post regarding ASP.Net and EntityDataSource. You can read it here.I have 3 more posts on Profiling Entity Framework applications. You can have a look at them here,here and here.

I will be using Entity Framework version 4.0 that ships with Visual Studio 2010 and .Net 4.0.


 

In this post I will show you a new feature of EF version 4. In this version we can build the Entity data model first and then build the database from the model.

1) Launch Visual Studio 2010 (express edition will work fine). Create a new empty website and choose a suitable name for it.

2) Add a new project to your solution, a class library project.Remove the class1.cs file from the project.

3) Add a new item to your class library project, a ADO.Net Entity Data model. Choose a suitable name for it, e.g AdventureWorkLT.edmx.

4) In the Entity Data Model Wizard select "Empty Model" and click Next.

5) In the Entity Designer right click on the surface and Add a new entity. Give a name to the Entity e.g Company. Add the property names with the appropriate types.Always check that you have a Key Property selected.

In my Company Entity I have "CompanyID" and "CompanyName" properties. You can set the Fixed Length, the Max Length, the Default Value for each property.

6)  In the Entity Designer right click on the surface and Add a new entity. Give a name to the Entity e.g Employee.Add the property names with the appropriate types.Always check that you have a Key Property selected.

In my Employee Entity I have "EmpID","EmpFirstName","EmpLastName","Email","CompanyID","Street","City","Country","PostalCode","Phone" properties.You can set the Fixed Length, the Max Length, the Default Value for each property.

 

7) Now we want to create an association between the Company Entity and the Employee Entity.Right click on the designer and select "Add Association".For my example, in order to see how I set the association have a look at the code below

 

 

8) Now that we have the association ready we move on and generate the database. First you have to go to the local instance of SQL Server anc create an empty database.I named it CompanyEmployees.

9) Then right click on the designer and select "Generate database from model". In the wizard choose the database you just created and click Next.

Then you will see the T-SQL.In my case it looks like this


-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 03/01/2011 18:53:26
-- Generated from EDMX file: C:\Users\fofo\Documents\Visual Studio 2010\Projects\ModelFirst\ModelFirst\Company.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [CompanyEmployees];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_CompanyEmployee]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [FK_CompanyEmployee];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Companies]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Companies];
GO
IF OBJECT_ID(N'[dbo].[Employees]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Employees];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Companies'
CREATE TABLE [dbo].[Companies] (
    [CompanyID] int IDENTITY(1,1) NOT NULL,
    [CompanyName] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'Employees'
CREATE TABLE [dbo].[Employees] (
    [EmpID] int IDENTITY(1,1) NOT NULL,
    [EmpFirstName] nvarchar(50)  NOT NULL,
    [EmpLastName] nvarchar(50)  NOT NULL,
    [Email] nvarchar(50)  NOT NULL,
    [CompanyID] int  NOT NULL,
    [Street] nvarchar(max)  NOT NULL,
    [City] nvarchar(max)  NOT NULL,
    [Country] nvarchar(max)  NOT NULL,
    [PostalCode] nvarchar(max)  NULL,
    [Phone] nvarchar(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [CompanyID] in table 'Companies'
ALTER TABLE [dbo].[Companies]
ADD CONSTRAINT [PK_Companies]
    PRIMARY KEY CLUSTERED ([CompanyID] ASC);
GO

-- Creating primary key on [EmpID] in table 'Employees'
ALTER TABLE [dbo].[Employees]
ADD CONSTRAINT [PK_Employees]
    PRIMARY KEY CLUSTERED ([EmpID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [CompanyID] in table 'Employees'
ALTER TABLE [dbo].[Employees]
ADD CONSTRAINT [FK_CompanyEmployee]
    FOREIGN KEY ([CompanyID])
    REFERENCES [dbo].[Companies]
        ([CompanyID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CompanyEmployee'
CREATE INDEX [IX_FK_CompanyEmployee]
ON [dbo].[Employees]
    ([CompanyID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------

 

10) Then click Finish.In my Solution Explorer I have a new file, Company.edmx.sql

I open this file and right-click "Execute SQL, I connect to the SQL Server and all the tables are created in the server.Have a look at the database in your server and see all the objects(tables,key.columns) created.

Now if you want you can insert values in your tables using T-SQL statements. Then you can use LINQ to Entities queries to get back those values.

Model first approach gives us a way to generate the database schema when we do not have one.

Hope it helps.

More Posts