Entity Framework - Simplified by Mohd Manzoor Ahmed - HTML preview

Chapter 14: Working With Transactions

 In this chapter I’m going to show you the implementation of Transaction in

EntityFramework.

 I assume that you are aware of what are transactions. We’ve seen what are

transactions and the implementation of Transactions in ADO.NET.

 If you want you can go through www.manzoorthetrainer.com and in your ADO.NET

module last two videos

 Transactions in ADO.NET and Implementing Transactions in ADO.NET.

 You can go through these. The transactions in ADO.NET will explain you what a

transaction is and the later one will explain the implementation of transaction.

 Now, I hope that you might have gone through these videos and you have an

understanding of transaction. Let us see the implementation of transaction in Entity

Framework.

 And, my requirement is little different here.

 This is not what I had in ADO.NET. My requirement is I’ve department and employee

tables.

 I want to create a department and at the same time I want to create an employee

belonging to that particular department

www.ManzoorTheTrainer.com

65

Entity Framework - Simplified

 So, First of all let us see how to create a department. We create a department with

the help of tbl_Dept entity

OrganizationEntities OE = new OrganizationEntities();

tbl_Dept d = new tbl_Dept();

d.Dname = "PDG";

d.HOD = "Jill";

d.Gender = "M";

d.Active = true;

OE.AddTotbl_Dept(d);

OE.SaveChanges();

 In the same way I want to create one more object of tbl_Emp entity.

tbl_Emp emp = new tbl_Emp();

emp.EName = "Fari";

emp.ESal = 5365353536567000;

emp.EGen = "M";

emp.EDOB = new DateTime(1984, 11, 06);

//The department id should be the id that is generated from above tbl_Dept d object.

// We know that Did is an auto generated column.

//Will the d.Did bring the Did that is inserted?

emp.Did = d.Did;

www.ManzoorTheTrainer.com

66

Entity Framework - Simplified

 Let us see that. Now, I’ll say

OE.AddTotbl_Emp(emp);

OE.SaveChanges();

 I’ll save this and execute. As we know that the transaction is something if the above

two executions are successful I say that transaction is success.

 If the first execution is successful and the second one fails it should rollback. That

means it should undo the first one also.

 I’ll save this and execute. Now, let me go back to the database. Let me see whether

I got the department PDG

 Yes, I got the department PDG and Did is 108.

 In employee table

www.ManzoorTheTrainer.com

67

Entity Framework - Simplified

 I got Fari and I got the equivalent Did that means you Entity Framework is handling

the auto increment column automatical y. Because, In your OrganizationModel.edmx

you said the Did is auto generated column.

 If you see in properties window StoreGeneratedPattern is Identity. So, it is handling

the things automatically.

 Now, what if Department is executed successfully employee insertion fails.

 If department is executed I’ve one statement and I say OE.SaveChanges();

 If we want all these statements to run all of them under a single transaction.

 To achieve that I need to implement transaction in Entity Framework.

 For that I need to add a reference to a DLL called as

System.Transactions;

www.ManzoorTheTrainer.com

68

Entity Framework - Simplified

 I’ll say OK. Now, I get the reference to that DLL. Now I’ll say

using System.Transactions;

 And I’ll be working with TransactionScope class as shown below.

OrganizationEntities OE = new OrganizationEntities();

using (TransactionScope s = new TransactionScope())

{

tbl_Dept d = new tbl_Dept();

d.Dname = "PDG";

d.HOD = "Jill";

d.Gender = "M";

d.Active = true;

OE.AddTotbl_Dept(d);

OE.SaveChanges();

tbl_Emp emp = new tbl_Emp();

emp.EName = "Fari";

emp.ESal = 5365353536567000;

emp.EGen = "M";

emp.EDOB = new DateTime(1984, 11, 06);

www.ManzoorTheTrainer.com

69

Entity Framework - Simplified

emp.Did = d.Did;

OE.AddTotbl_Emp(emp);

OE.SaveChanges();

s.Complete();

}

 Once we say s.Complete() it is going to commit the transaction. If your control does

not execute s.Complete() It means that the transaction is incomplete and it will

rollback any operation that it has performed on Entity Data Model.

 Save this and execute.

 Now, I get the department PDG and I get Fari belonging to department 110

www.ManzoorTheTrainer.com

70

Entity Framework - Simplified

 This is very simple. Just you need to put your code inside TransactionScope and at

the end you need to call s.Complete() method.

 If at all there is any exception in department execution it is not going to call the

employee execution and it is going to rollback the transaction.

 Now, I’ll comment out s.Complete() that means if this method is not executed it is

going to rollback all these operations and

 I should not have any PDG and Fari in my database. I’ll execute and look into the

database.

 I have nither PDG department.

 Nor Fari as an employee.

www.ManzoorTheTrainer.com

71

Entity Framework - Simplified

 That means it has rolled back both the operations even though we say

OE.SaveChanges().

 If we say s.Complete() it should commit both the operations and I should have both

the records. I’ll look into the database. I get PDG department.

 I get Fari employee.

www.ManzoorTheTrainer.com

72

Entity Framework - Simplified

 So, this is all about the transactions in your Entity Framework which is very simple.

 Just create the object of TransactionScope and put all your queries or all your

operations one after the other and finally call s.Complete().

 It is going to commit the transaction.

 If this method is not called it will automatically rollback.

 So, this is all about transactions.

www.ManzoorTheTrainer.com

73

Entity Framework - Simplified

Chapter 15 : Working With Navigation Properties

 Hello everyone, in this chapter I’m going to show you what are navigation properties.

 Now, say I’ve a textbox and a button and I want to search the employee based on

the department name that is I’m going to insert department name here and I say

search.

 What is that it should do, it should get me all the employees belonging to the

department entered.

 So, what is our normal logic we take the department name and we try to find out

department id.

 Now, let’s see tbl_Dept and tbl_Emp tables. From tbl_Dept table I get DName

(Department Name). From DName I’ll try to find out Did.

 With that Did I’ll write a query on tbl_Emp table to get the information.

 Double click on search button. Import the namespace using OrganizationModel;

www.ManzoorTheTrainer.com

74

Entity Framework - Simplified

 Normal procedure

protected void btnSearch_Click(object sender, EventArgs e)

{

OrganizationEntities OE = new OrganizationEntities();

int did = OE.tbl_Dept.ToList().Where(x => x.Dname ==

txtDeptName.Text).FirstOrDefault().Did;

var emps = OE.tbl_Emp.ToList().Where(x => x.Did == did);

//By using where clause I’ll get all the employees belonging to that particular

department

grdEmps.DataSource = emps;

grdEmps.DataBind();

}

 Save this and execute. I’ll search for department QA.

 Let us achieve the same thing with the help of Navigation properties. Let us see how

simple it is.

www.ManzoorTheTrainer.com

75

Entity Framework - Simplified

 In this OrganizationModel tbl_Dept have a navigation property tbl_Emp. I’ll use this

property.

 It is going to find the department and extract all the employees of the department

and store it in emps.

protected void btnSearch_Click(object sender, EventArgs e)

{

OrganizationEntities OE = new OrganizationEntities();

var emps = OE.tbl_Dept.ToList().Where(x => x.Dname ==

txtDeptName.Text).FirstOrDefault().tbl_Emp;

grdEmps.DataSource = emps;

grdEmps.DataBind();

}

 Save this and execute. I’ll search for QA and I’ll get the same result.

 Instead of writing two queries in the normal procedure we can simplify it with

Navigation properties.

 This is the feature and the advantage of navigation properties.

 In the same way you can navigate from the employee table to the department table.

 So, this is all about your Navigation Properties.

www.ManzoorTheTrainer.com

76

Entity Framework - Simplified

Chapter 16 : Lazy Loading in Entity Framework

 In this chapter I’m going to show you what lazy loading is and also demonstrate the

topic that we already learned that is immediate mode and deferred mode as these

two things make developer confused.

 So, let’s say I’ve a gridview and I want to display all the departments at page load.

 We already implemented, the code is very simple.

using OrganizationModel;

OrganizationEntities OE = new OrganizationEntities(); //place a breakpoint here

var result = OE.tbl_Dept; //Deferred mode of execution

grdDept.DataSource = result;

grdDept.DataBind();

foreach (var d in result)

{

Response.Write(d.Dname + "<br/>");

}

 Execute it. Now, if you look at a window on the right that is IntelliTrace.

 Click on this window.

 This window I can get get it from Debug  IntelliTrace.

 This is the new window we got in VS 2010 which is used to examine our query and

flow.

 Now, I’ll say F10

www.ManzoorTheTrainer.com

77

Entity Framework - Simplified

Here I’m going to create a query OE.tbl_Dept and say F10.

 If you’ve observed in IntelliTrace you won’t find any query execution.

 Now, at the time of assigning the result to the gridview, It shoud execute a select

query.

www.ManzoorTheTrainer.com

78

Entity Framework - Simplified

 If you observe in the IntelliTrace we got a query that is Select Did,DName..

 This query got executed at the time of databinding to the gridview. Say F5.

 Now, I’m using this result one more time. If you observe in IntelliTrace you got one

more ExecuteReader.

 So, this is called as your deferred mode of execution.

 Making it to Immediate mode by .ToList().

www.ManzoorTheTrainer.com

79

Entity Framework - Simplified

OrganizationEntities OE = new OrganizationEntities();

var result = OE.tbl_Dept.ToList();

grdDept.DataSource = result;

grdDept.DataBind();

foreach (var d in result)

{

Response.Write(d.Dname + "<br/>");

}

 We are forcing it to execute the query here itself.

 In our earlier code it is hitting the database two times whereas here it’ll hit the

database only once.

 I get select query there and it is not at all going to hit the database at the time of

binding, neither at the time of iterations

www.ManzoorTheTrainer.com

80

Entity Framework - Simplified

 You can see that, we have only one select query.

 So, this is your immediate mode of execution.

 Now, I’ve all the departments listed.

www.ManzoorTheTrainer.com

81

Entity Framework - Simplified

 What I want is if I say search it should get all the employees whose department type

is InActive (QA & Admin).

 If I check Active and if I say search I should get all the employees whose department

is Active (Development, Testing and PDG).

 I’m going to implement this, in this you’ll see what lazy loading is and we are going

to make use of IntelliTrace window we saw just now. And that window is available

only when we go for debugging mode.

 Let us see.

protected void Button1_Click(object sender, EventArgs e)

{

OrganizationEntities OE = new OrganizationEntities();

var result = OE.tbl_Dept.Where(x => x.Active == ckbActive.Checked).ToList();

foreach (var d in result)

{

Response.Write(d.Dname + "<br/>");

}

}

 Save and execute.

 I’ll check Active and search. I get Active departments.

 I’ll uncheck Active and search and I get InActive departments.

www.ManzoorTheTrainer.com

82

Entity Framework - Simplified

 Now, what I want is I want all the employees belonging to the particular department

protected void Button1_Click(object sender, EventArgs e)

{

OrganizationEntities OE = new OrganizationEntities();

var result = OE.tbl_Dept.Where(x => x.Active == ckbActive.Checked).ToList();

foreach (var d in result)

{

Response.Write(d.Dname + "<br/>");

//using navigation properties that we learned

//It gives all the employees belonging to the particular department

foreach (var emp in d.tbl_Emp)

{

Response.Write(emp.EName + "<br/>");

}

}

}

 Save this and execute. I’ll say search.

www.ManzoorTheTrainer.com