Entity Framework 4.1 – Querying objects

Part 1: First Steps
Part 2: Managing Relations
Part 3: Collection Relations

In the first three parts of this little tutorial I showed how easy is to save objects to database with EF 4.1 code first approach, now it is time to understand how to retrieve objects from the database.

Querying objects from database is really easy in EF, because you have full LINQ support; as an example suppose you want to retrieve all warriors with a name that contains a particular search string.

   1: using (var db = new BattlefieldContext())

   2: {

   3:     List<Warrior> searchResult = db.Warriors

   4:         .Where(w => w.Name.Contains("am"))

   5:         .ToList();

   6:     foreach (var warrior in searchResult)

   7:     {

   8:         Console.WriteLine("Found warrior:" + warrior.Name);

   9:     }

  10: }

Ad you can see you are expressing the query against the object model, you are in fact asking for all warriors whose name Contains the string (“am”). If you analyze the LINQ query you can notice that I used the method System.String::Contains but Entity Framework Profiler shows the real query issued to the database.

19-07-2011 16-37-29

Figure 1: The query issued to the database intercepted by Entity Framework Profiler.

This is the real power of LINQ: the original query expressed in terms of Object model gets analyzed from the EF LINQ provider and translated to the equivalent SQL syntax, another interesting feature is that the query returns objects, not a DataReader or some Database related structure. Now look at this other piece of code.

   1: using (var db = new BattlefieldContext())

   2: {

   3:     IQueryable<Warrior> searchResult = db.Warriors

   4:         .Where(w => w.Name.Contains("am"));


   6:     foreach (var warrior in searchResult)

   7:         Console.WriteLine("Found warrior:" + warrior.Name);


   9:     foreach (var warrior in searchResult)

  10:         Console.WriteLine("Found warrior:" + warrior.Id);

  11: }

This code is really similar to the previous one, but with the only difference that I’m not using ToList() method in the query, instead the result is stored into a IQueryable<Warrior> object. Once the query is created I iterate two times the IQueryable object and the result is that the same query is issued two times to the database. To understand why you need to understand the difference from a Deferred and Non-Deferred operator in LINQ.

In the above example the Where operator, used to specify the criteria for object retrieval is a Deferred Operator, this means that the operator gets executed only when we iterate through its content. This is why each time you iterate the IQueryable<T> object another query gets issued to the database. To avoid this you can call ToList() non-deferred operator, that executes immediately the LINQ query, returning all objects inside a List<T> object; now objects are in memory and you can iterate the list how many time you want without issuing further queries to the database.

This is a really interesting concept that you can use for your advantage, like in the following example.

   1: using (var db = new BattlefieldContext())

   2: {

   3:     IQueryable<Warrior> searchResult = db.Warriors

   4:         .Where(w => w.Name.Contains("am"));


   6:     Console.WriteLine("Found N°:" + searchResult.Count() + " warriors.");


   8:     foreach (var warrior in searchResult)

   9:         Console.WriteLine("Found warrior:" + warrior.Id);

  10: }

This piece of code creates a IQueryable<T> that filters all warrior whose name contains the “am” string, then print the number of warriors that satisfy that criteria (line 6), and then iterate through all records to print details. This piece of code actually creates two queries to the database.

19-07-2011 17-12-24

Figure 2: The two queries issued by the previous snippet of code

The interesting aspect is that the first query actually issue a select COUNT to the database, it does not load all objects. Suppose that you need to do server side pagination, loading three warriors for each iteration.

   1: using (var db = new BattlefieldContext())

   2: {

   3:     IQueryable<Warrior> searchResult = db.Warriors

   4:         .Where(w => w.Name.Length > 1)

   5:         .OrderBy(w => w.Name);


   7:     int count = searchResult.Count();

   8:     Console.WriteLine("Found N°:" + count + " warriors.");


  10:     Console.WriteLine("Printing in page of three");

  11:     Int32 pageNumber = 0;

  12:     Int32 pageSize = 3;

  13:     while (pageNumber * pageSize < count)

  14:     {

  15:         Console.WriteLine("Printing page " + (pageNumber + 1));

  16:         foreach (var warrior in searchResult

  17:             .Skip(pageNumber * pageSize)

  18:             .Take(pageSize))

  19:             Console.WriteLine("Found warrior:" + warrior.Name);

  20:         pageNumber++;

  21:     }


  23: }

This time the query on line 3 has an order clause because you cannot use server side paging without ordering, then I issue a Count()query (line 7) to know all records that satisfy the condition, then I begin a cycle to load a page at a time, since I know the total number of records. Thanks to Skip and Take operators loading objects a page at a time is really super simple. Here is the output of the above snippet.

   1: Found N°:5 warriors.

   2: Printing in page of three

   3: Printing page 1

   4: Found warrior:Alhana

   5: Found warrior:Caramon

   6: Found warrior:Dalamar

   7: Printing page 2

   8: Found warrior:Raistlin

   9: Found warrior:Tanis

Being able to load object with server side pagination can tremendously increase the speed of programs; in this example I load all objects a page at a time, but in real situation you load the first page, show the result to the user and shows other pages only when the user ask explicitly to view data for another page.

The above piece of code issues three queries to the database, the first one is a simple count, the other two are more complex, because they should implement server-side pagination.

19-07-2011 17-23-29

Figure 3: The query that loads the second page of the records

Actually you could create a more readable query if you hand-code server side pagination, but being able to do this with Skip() and Take(), makes possible even for a Sql Server novice developer to write efficient, but simple code.



Entity Framework 4.1–Collection Relations

Part 1: First Steps
Part 2: Managing Relations

Relations between objects are not limited to an object that keep a reference to another object (as seen in part 2), but I can have an object that contains a list of other objects. This is the classic RDBMS rough equivalent of the One-To-Many relation. I decided that my Warrior can carry a certain number of items, so I defined an Item class and a property on Warrior class to hold reference to a list of Items.

   1: private IList<Item> _items;

   2: public IList<Item> Items

   3: {

   4:     get { return _items ?? (_items = new List<Item>()); }

   5:     set { _items = value; }

   6: }

This is a standard implementation of a property that permits me to establish that a Warrior can have a list of Items. In the Getter part I use lazy initialization, so I can simply add an IList<Item> from external code, or I can simply let the object auto-initialize a standard list for me. This is the code that actually adds some item to a Warrior.

   1: Warrior Caramon;

   2: using (var db = new BattlefieldContext())

   3: {

   4:     Caramon = new Warrior { Name = "Caramon", ExperiencePoints = 342553 };

   5:     Caramon.ActiveWeapon = new Weapon() { Damage = 10, Size = 3 };

   6:     Caramon.Items.Add(new Item() { Name = "Ring of invisibility" });

   7:     Caramon.Items.Add(new Item() { Name = "Health Potion" });

   8:     db.Warriors.Add(Caramon);

   9:     db.SaveChanges();

  10: }

Again I want to point out that I did not make any other changes to the BattlefieldContext or to something related to persistence, I just defined the Item class and the Items property on the Warrior class, but I’m able to save everything to the database without writing any other line of code. Here is the query generated by EF.


Figure 1: Generated query to insert a warrior with two items.

This is the Database Schema that EF created to persist my objects.


Figure 2: The database schema to persist Warrior and Items

As you can see, EF created the Warrior_id column on the Items table to be able to keep the relation between items and warriors.

I want to strongly point out that this is nor DDD nor Domain Modeling, I’m simply using EF4.1 like a Super Dataset, to avoid writing CRUD. My primary reason for this little tutorial is moving people from HandWritten SQL code or from old style Dataset to something more flexible and more object oriented.



Entity Framework 4.1 managing relations

Part 1: First Steps

In previous post I showed how you can persist an object to the database thanks to EF41 and few lines of code, now it is time to understand how to manage relations. First of all create another class called weapon and then create a property of type Weapon into the Warrior class.


Figure 1: New model, the warrior has a property called ActiveWeapon of type Weapon

Now I can write the following code to save a warrior to the database with an associated weapon.

   1: Warrior Caramon;

   2: using (var db = new BattlefieldContext())

   3: {

   4:     Caramon = new Warrior { Name = "Caramon", ExperiencePoints = 342553 };

   5:     Caramon.ActiveWeapon = new Weapon() { Damage = 10, Size = 3 };

   6:     db.Warriors.Add(Caramon);

   7:     db.SaveChanges();

   8: }

But if I ran previously the code of the first part of the sample, I got this exception because the database was already created.

The model backing the ‘BattlefieldContext’ context has changed since the database was created. Either manually delete/update the database, or call Database.SetInitializer with an IDatabaseInitializer instance. For example, the DropCreateDatabaseIfModelChanges strategy will automatically delete and recreate the database, and optionally seed it with new data.

This happens because the database already exists, and the schema is not compatible with this new version of the classes. So we need to add this line of code in the project to make EF update the db schema to reflect the change in object model.

   1: Database.SetInitializer<BattlefieldContext>(

   2:     new DropCreateDatabaseIfModelChanges<BattlefieldContext>());

This single line of code tells EF to recreate the database if the model has changed and the schema of the db is outdated. This will actually Drop and recreate the database, so if you need to maintain the data you should manage the update with other tool as a DatabaseProject or some third party tool. If you intercept the queries done to the db you should see something like this.


Figure 2: The query issued to verify if the actual database structure is good for the current model

This query simply check if the database structure is up to date. Now back to the code to add the warrior with the weapon, as you can see I create a weapon and assigned to the ActiveWeapon property of the Warrior object. This is a really important concept, I do not care about ForeignKey or Id, I simply assign an object to another object and EF takes care for me of this. The query issued to insert the object into the database is the following one.


Figure 3: Inserting a Warrior with a Weapon into the database

As you can see we have two distinct queries, the first one is used to insert the Weapon into the database and retrieve the generated identity (the id of the object), immediately followed by an INSERT into the Weapon table, where the ActiveWeapon_id is set to the value of the id of the weapon generated in the previous query.

This is the advantage of using an ORM tool, you design object using standard object relations in mind, and the ORM has the duty of persisting this model into a database. As you can verify from Figure 4, the Warrior table has a column ActiveWeapon_id to store the foreign key to the Weapon table.


Figure 4: Database schema generated to persist the Warrior-Weapon model

The cool part is that I did not write any single line of code related to the persistence of the Weapon class, I just added the class, added the relation to the Warrior object and all the rest is managed by EF41.


Entity Framework 4.1 first steps

I use NHibernate since its first versions and I really love it :), but after Entity Framework 4.1 is out, I started to consider using EF for people that actually does know anything of ORM and still work with dataset or use handwritten SQL CRUD, but could not use open source project or prefer using MS technologies.

I tend not to agree with this path, but sometimes it is easier to tell people, “hey, just install this update of Microsoft and you can use EF code first, or if you want a designer you can simple add a new EF Model to the project”. People tend to consider less work using something that is already included in the framework, instead of going toward some external open source library. I must also admit that using EF 4.1 is quite fun (I used in really little projects, but 4.1 version gave me a good impression).

If you want to use EF4.1 in your application you need to do very little steps, suppose you have this class to persist.


You need to reference the Ef4.1 assembly and then create a Context to be able to persist this class.

   1: public class BattlefieldContext : DbContext

   2: {

   3:     public DbSet<Warrior> Warriors { get; set; }

   4: }

This is really simple, just create a class named BattlefieldContext, inherit from DbContext, then insert a public property of type DbSet<YourEntityThatYouWantToPersist>.

Now go to app.config (or web.config) and create the connectionstring to the database with the same name of the context class (thus it is named BattlefieldContext)

   1: <connectionStrings>

   2:   <add

   3:     name="BattlefieldContext"

   4:     providerName="System.Data.SqlClient"

   5:     connectionString="Server=localhost\sql2008;Database=Battlefield;Trusted_Connection=true;"/>

   6: </connectionStrings>

Now write the code that insert a Warrior into the database.

   1: Warrior Raistlin;

   2: using (var db = new BattlefieldContext())

   3: {

   4:     Raistlin = new Warrior { Name = "Raistlin", ExperiencePoints=342553 };

   5:     db.Warriors.Add(Raistlin);

   6:     db.SaveChanges();

   7: }

Run these few lines of code, everything works, but wait…. you never created the database but no exception occurs. This happens because the current user has administrative rights on the Sql Server, so, after the connection to the database engine was opened, EF41 verified that the Battlefield database did not exists, so he created one to persist the Warrior class.


Generation of the database is straightforward, EF41 generates a table with a column for each property of the object that needs to be persisted, and he got special care for property named Id, because it is the primary key in the table. Since the Id property is an integer, the default convention creates a primary key with Identity Specification turned on. If you read all rows in the table you can verify that the warrior Raistlin was really saved into database.


You should admit that using EF41 in such a simple scenario is damn simple and efficient, very few lines of code and you are ready to use it.