Tag: EF

Optimizing EF4 and EDMGen2

Just reading (from Adrian Florea links ) how the EF4 could be optimized : http://www.codeproject.com/KB/database/PerfEntityFramework.aspx

First modification is to “Pre-generated Your View”. For this you must have .ssdl, .csdl, and .msl files – so you change the “Metadata Artifact Processing property to: Copy to Output Directory.”. Then you process the .ssdl, .csdl, and .msl with edmgen in order to can have the  views.

Until here , all ok.

But, in the next advice, is to keep “Metadata Artifact Processing property to: Embed in Output Assembly.”

One solution is to put “Metadata Artifact Processing property to: Copy to Output Directory.” , compile, put again “Metadata Artifact Processing property to: Embed in Output Assembly.” and compile again. But , if you change the edmx (add fields or tables ) you must redo the operation  – so you will have more things to do(if you remember)

A solution is to build them on the pre-build step .But how to generate the .ssdl, .csdl, and .msl files  ?

Edmgen2 , http://code.msdn.microsoft.com/EdmGen2 , to the rescue. Download, put into a lib folder under your solution folder and put a pre-build like this :

$(SolutionDir)lib\edmgen2\edmgen2 /FromEdmx $(ProjectDir)prod.edmx”

“%windir%\Microsoft.NET\Framework\v4.0.30319\EdmGen.exe” /mode:ViewGeneration /language:CSharp /nologo “/inssdl:prod.ssdl” “/incsdl:prod.csdl”   “/inmsl:prod.msl” “/outviews:$(ProjectDir)prod.Views.cs”

What you must change on your project?

1. IF you are on 64 bit, change Framework to Framework64

2. change the prod with your edmx name.

What is the performance?

Tested by loading a table with 301 rows by doing the steps :

1. open connection, load all table in objects(POCO), closing connection

2. open connection , find object with PK = 1, closing connection

3. open connection ,  loading 1 tables with 2 related (include ) , closing connection

The results are in milliseconds:

Without pre-compiled views

LoadTable LoadID LoadMultiple Total Time
579 800 172 1551
563 755 171 1489
559 754 169 1482
568 762 240 1570

With pre-compiled views:

LoadTable LoadID LoadMultiple Total Time
606 807 183 1596
509 706 177 1392
852 137 192 1181
530 733 221 1484
523 722 183 1428

The average / min / max results:

average max min
without 1523 1570 1482
with 1413.25 1596 1181

In the next picture the smaller the duration(milliseconds), is the better :

image

Conclusions:

1.  For the average and min the difference is 7%, respectively 20%. Please remember we are using only 3 queries.

For the max, it is vey curious : the with is more than without. The penalty is 1%, I think that is a measuring error ? – or maybe not. However , the penalty is small comparing with others.

2. Very curious, find after ID, with a table with 301 rows, took longer than loading the whole table.However, did not take into accound finding in the list the object( it is in memory also)

3. It may worth to add the pre-build step shown before to pre-compile views.

Links :

http://www.codeproject.com/KB/database/PerfEntityFramework.aspx

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

EF Profiler and investigating object context

Summary : EF Profiler worth his money !

Long Description :

I have finished a application with Entity Framework and POCO – and custom generated (.tt) files.

Now it was time to investigate performance  – and it was a terrific problem.

How EF Prof helped me :

1.  Displaying number of ObjectContext created and disposed in a Unit of Work and / or web page.

image

2. Displaying problem with the ObjectCOntext : using same context from multiple threads, alerts about code like  here :

image

The red dot is about querying the database from the view. The gray one is about selecting a whole table without selecting top 1000 rows(OK, it was a dictionary table, like the list of countries)

3. When you want caching on your site , you can fast see this by seeing the difference between the number of queries and/or number of ObjectContext for the first time and the second one ( first time : 10, second time:2 or 0 😉 )

4. See most expensive queries as time – usually this is a good option to put an index on the “where” columns.More , you can see also non-unique queries (that you perform more than 1 time)

5. Investigate easily all queries. Know what you want to optimize and where.

Improvements  :

1. Say where is the ObjectContext that is not closing.

Alternatives:

How to: View the Store Commands : http://msdn.microsoft.com/en-us/library/bb896348.aspx

Sql Profiler : http://msdn.microsoft.com/en-us/library/aa173918%28SQL.80%29.aspx

(free ones :

http://sites.google.com/site/sqlprofiler/

http://code.google.com/p/sqlexpressprofiler/

)

But the job will be done by yourself!

Generating history trigger with EF , edmx and TT files

I have wrote in an older post ( http://msprogrammer.serviciipeweb.ro/2010/06/28/ef-automatic-history-of-table-and-t4-files-tt-files/ ) how to generate history code for tables . The easy solution was to create a tt file that track for the ObjectContext the SaveChanges for each table that has a “history” in name. the limitation is that , when you raise an sql command such as “update table ” you must load from database a lot of rows for this….

Now I want to show the same thing, but generating triggers in database for that ! I start also from edmx file and with a template stealed from http://forums.asp.net/p/1599616/4083198.aspx ( to have type of fields in the database ) and another stealed and modified from StackOverflow(to generate trigger for after insert , update, delete) I manage to have a solution to generate sql tables and trigger code.

When is that good ?At the beginning stages of a project when the table structure changes by adding a new parameter.

Sample code generated for table Notes(ID, TextNote, PersonID)

print 'Create table Notes_History ';
Create Table Notes_History(
		ID_Notes_History BIGINT IDENTITY NOT NULL
		,History_Action varchar(50)
		,History_From varchar(100) default HOST_NAME()
		,History_User varchar(50) default SYSTEM_USER
		,History_Date varchar(50) default  getdate()
	   ,Id  int   NOT NULL
	   ,Textnote  nvarchar (255)  NULL
	   ,PersonId  int   NULL
	)
print 'end Create table Notes_History ';
GO
print 'create trigger for Notes'
GO
CREATE TRIGGER dbo.TR_IUP_Notes
   ON  Notes
   AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
	DECLARE @Ins int

DECLARE @Del int

SELECT @Ins = Count(*) FROM inserted

SELECT @Del = Count(*) FROM deleted
if(@Ins  + @Del = 0)
	return;

declare @operation varchar(50)
	set @operation ='update';
	if(@ins < @del)
		set @operation ='delete';
	if(@ins > @del)
		set @operation ='insert';

	if(@ins <= @del)
	begin
		INSERT INTO Notes_History(History_Action ,Id,Textnote,PersonId)
			select @operation ,Id,Textnote,PersonId from deleted
	end
    else
	begin
    INSERT INTO Notes_History(History_Action ,Id,Textnote,PersonId)
			select @operation ,Id,Textnote,PersonId from inserted
	end
END

The drawback of the code : the user that executes is not always the logged sql server user…
Anyway, what you have to do to use this automatically generated history/audit for tables ?

download historysql and modify

string inputFile = @”Model1.edmx”;

from historysql.tt to your edmx name. Then take the generated code and execute in sql server.

EF , automatic history of table and T4 files (TT files)

Usually the data of the tables should be tracking for who modified it.

Think about inserting/updating/deleting an employee :   you must know who did those actions and when. So you create another table, identically as structure, and you add another 3 fields , such as [ModifiedDate](when), [ModifiedBy](who), [ModifiedType] (what : insert, update, delete).

There are several methods to do it :

  1. from database :
  2. from programming code  – every time you modify an object, you remember to modify the history object with appropiate data.

The drawback with the database approach is that you can not retrieve who done the modifications ( usually the applications connect under a single account and have a roles table)

The drawback with the programming approach is that the programmer must REMEMBER doing so…If he does not(and does not wrote tests for history), you are stuck…

In the following I propose an automatically history – that maps convention over configuration in my template, but it is easy for you to modify.

The solution works with Entity Framework 4.0 and, for more easily spearation of concerns , with POCO generators.

Let’s say you have the following tables :

database diagram

As you see we have a Employee and a employee_history, an Department and Department_history

The conventions are:

the history table name = “object” table name  +  “_history” suffix

the history table fields = “object” table name  fields +[ModifiedDate], [ModifiedBy], [ModifiedType]

(if you change those conventions , please change the modelhistory.tt file)

If you want to see in action , please  download code history and do the following
1. create database tests
2. run history.sql
3. run project
4. if necessay, re-create the model1.edmx with the same name and replace the console application app.config with the new connection string

After works, please add any fields to department table  and to department_history table(same field names/type) .  Re-compile the application and modify the new field in department. You will see the modifications in the department_history table.

Ok,now how we do the magic :

We create two new tt file that points to the model.edmx .

The first one ModelHistory.tt , takes care of  creating the constructor for history entities by taking a parameter from the original entity :

public Department_History(Department original):this()
{
this.IDDepartment=original.IDDepartment;
this.Name=original.Name;
}

How it do this magic ? Simple : the ModelHistory.tt recognize the model and history in the name of tables:

</pre>
string inputFile = @"Model1.edmx";
string History = "_History";
<pre>

then it generate code for constructor :

	#>
		public <#=code.Escape(entity)#>():base()
		{
		}
		public <#=code.Escape(entity)#>(<#=NameEntityOriginal #> original):this()
		{
		<#
	foreach (EdmProperty edmProperty in entityOriginal.Properties.Where(p => p.TypeUsage.EdmType is PrimitiveType && p.DeclaringType == entityOriginal))
	{
		#>
				this.<#= code.Escape(edmProperty.Name) #>=original.<#= code.Escape(edmProperty.Name) #>;
		<#

	}
	#>
		}
	<#
</pre>

Ok, and then how to create the history entity ? I wish that the POCO template has had an event “Database saving” – but the only thing I can have is SaveChanges from the ObjectContext – so I create a new ObjectContext , derived from the default one that comes with the project, and creates a new history object :


public override int SaveChanges(SaveOptions options)
{
this.DetectChanges();
DateTime dtModified=DateTime.Now;
string UserModified=clsUser.UserName;
foreach (ObjectStateEntry ose in this.ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Deleted | EntityState.Modified))
{

//could do this way too
//if (ose.Entity != null && ose.Entity.GetType() == typeof(...))
//{
//}
if (ose.Entity != null)
{
string NameType=ose.EntitySet.ElementType.Name;

switch(NameType)
{

case "Department":
var itemDepartment_History = new Department_History(ose.Entity as Department);
//if compile error here, that means you keep tracking
//of which modified with another properties
//please modify the tt accordingly
itemDepartment_History.ModifiedType= ose.State.ToString();
itemDepartment_History.ModifiedDate= dtModified;
itemDepartment_History.ModifiedBy= UserModified;
base.Department_History.AddObject(itemDepartment_History);
break;

case "Employee":
var itemEmployee_History = new Employee_History(ose.Entity as Employee);
//if compile error here, that means you keep tracking
//of which modified with another properties
//please modify the tt accordingly
itemEmployee_History.ModifiedType= ose.State.ToString();
itemEmployee_History.ModifiedDate= dtModified;
itemEmployee_History.ModifiedBy= UserModified;
base.Employee_History.AddObject(itemEmployee_History);
break;

}
}
}

return base.SaveChanges(options);
}

Now all is ready and I made a console application for testing manually (ok, should make a NUnit / MSTest / xUnit )

 using (var ctx = new testsEntitiesHistory())
            {
                var dep = new Department();
                dep.Name = "IT";
                ctx.Departments.AddObject(dep);
                ctx.SaveChanges();
                id = dep.IDDepartment;
            }
            using (var ctx = new testsEntitiesHistory())
            {
                var dep = ctx.Departments.Where(depart => depart.IDDepartment == id).FirstOrDefault();
                dep.Name = "Information tehnology";
                ctx.SaveChanges();
                //
            }
            using (var ctx = new testsEntitiesHistory())
            {
                var dep = ctx.Departments.Where(depart => depart.IDDepartment == id).FirstOrDefault();
                ctx.Departments.DeleteObject(dep);
                ctx.SaveChanges();

            }
            using (var ctx = new testsEntitiesHistory())
            {
                foreach (var dephist in ctx.Department_History)
                {
                    Console.WriteLine("Found {0} with state {1}", dephist.Name,dephist.ModifiedType);
                }
            }

And the output is :

history saving
automatically saving history department

Now you can add more tables to the edmx or change the fields – all is done automatically when compiling

If you want to see in action , please download code history

Update : for another way to do it( generating trigger and tables ) please see : http://msprogrammer.serviciipeweb.ro/2010/09/27/generating-history-trigger-with-ef-edmx-and-tt-files/

ASP.NET MVC , ORM and ViewModels

Since I am active on asp.net/mvc I find many peoples asking about

  1. One to many relationship .
  2. Dropdownlist
  3. How to use MVC with other ORM frameworks instead of Linq To Sql(L2S) ?
  4. What is a ViewModel and what is his purpose ?

For answering, I have made a small application with MVC2 in order to detail my answer.

First the problem :

You have Employees and Department.Each Employee belongs to an Department (let’s forget the fact that the employee can belong for a final date to the Department or any other life ideas)

The request is to create and view Employees .(forget delete,update – if I will have time and/or by general request , I will do this also this)

The database:

image

The beginning of the application :

First Layer , Data Access Layer
I use EF 3.5 (not 4.0 with POCO style – because is not ready yet) and make an edmx file and let EF put his classes.
You can use also ADO.NET with StoredProc, L2S, NHibernate or any other ORM you want.
Second Layer,Business Logic :
This is the layer where the logic should intervene (e.g., the name should be not null ).

Here is the list for Employee :

namespace BusinessLogic

{

    /// <summary>

    /// TODO : add IDataErroInfo in order to validate

    /// TODO : add the department

    /// </summary>

    public class Employee

    {

        public long ID;

        public string Name { get; set; }

        public Department Department{ get; set; }

        public void SaveNew()

        {

            //TODO : validate before save

            //TODO : automapper

            using (DAL_EF.testsEntities te = new DAL_EF.testsEntities("name=myconnection"))

            {

                DAL_EF.Employee e = new DAL_EF.Employee();

                e.Name = this.Name;

                //TODO : what if dept. deleted ?

                e.Department =te.Department.Where(d=>d.ID== this.Department.ID).FirstOrDefault();

                te.AddToEmployee(e);

                te.SaveChanges();

            }

        }

    }

    public class EmployeeList : List<Employee >

    {

        public void Load()

        {

            using (DAL_EF.testsEntities te = new DAL_EF.testsEntities("name=myconnection"))

            {

                foreach (var emp in te.Employee.Include("Department"))

                {

                    //TODO : use automapper

                    Employee e=new Employee() { ID = emp.ID, Name = emp.Name };

                    e.Department=new Department(){ ID=emp.Department.ID,Name=emp.Department.Name};

                    this.Add(e);

                }

            }

        }

    }

}

Please pay attention for Load procedure in EmployeeList : it uses the first layer, DAL, but it can use any ORM framework.

Third layer, ViewModel:

Because all the views for employees will need a select list for department ( either for sorting, creating or updating) I have put a base ViewModel class that retrieves the list of Departments:

namespace MVCAppEmp.Classes
{
    public class ViewModelEmployee
    {
        public static SelectList ListOfDepartments
        {
            get
            {
                //TODO : put this into cache to not load every time
                DepartmentList dl = new DepartmentList();
                dl.Load();
                return dl.SelectFromList(x => x.ID.ToString(), y => y.Name);
            }
        }
    }
    public class ViewModelEmployeeCreate : ViewModelEmployee
    {
        public Employee emp=new Employee();
    }
    public class ViewModelEmployeeList : ViewModelEmployee
    {
        public EmployeeList employees;
        public void Load()
        {
            employees = new EmployeeList();
            employees.Load();
        }
    }
}


Fourth Layer , MVC itself – view and controller :

<% =Html.ValidationSummary() %>
<% using (Html.BeginForm())
   { %>
    <h2>Create</h2>
    List of departments :
    <% =Html.DropDownList("DepartmentID", MVCAppEmp.Classes.ViewModelEmployee.ListOfDepartments)%>
    <%=Html.EditorFor(model => model.emp)%>
<input type="submit" value="create" />
<%} %>

and the controller

// POST: /Employee/Create
       // TODO: make a binding for employees
       [HttpPost]
       public ActionResult Create(Employee emp,long DepartmentID)
       {
           try
           {

               emp.Department = new Department() { ID = DepartmentID };
               emp.SaveNew();
               return RedirectToAction("Index");
           }
           catch(Exception ex)
           {
               ModelState.AddModelError("", ex.Message);
               return View(new ViewModelEmployeeCreate(){emp=emp});
           }
       }

So to answer the questions :

  1. One to many relationship – easy to do, most difficult to program
  2. Dropdownlist – easy – just transform a list into a select list, provided you have all data belongs
  3. How to use MVC with other ORM frameworks instead of Linq To Sql(L2S) ? Does not matter ! Just create your BusinessLogic and use that ORM!
  4. What is a ViewModel and what is his purpose ? To fill the data for GUI that BusinessLogic does not comply and transfer data to BusinessLogic. Reciprocally too!

You will find attached the application and the sql to create the database :

For MVC2  /MVC 3
http://msprogrammer.serviciipeweb.ro/wp-content/uploads/ASP.NETMVCORMandViewModels_135E/emp.zip

For MVC 4

http://msprogrammer.serviciipeweb.ro/wp-content/uploads/ASP.NETMVCORMandViewModels_135E/empMVC4.zip

What to do next, in order to familiarize yourself :
1. Make the update of the employees .Create view, save.
2. Filter the list of the employees on department. Search for all employees beginning with A that belong to IT department.
3. Search for TODO’s : there are part of other tutorials – but it will help you in MVC (and other!) development

This post have a continuation here:

http://msprogrammer.serviciipeweb.ro/2010/07/05/asp-net-mvc-and-dos-re-using-the-viewmodels/