DbContext class is the one among others that the Entity Framework team has created, in order to facilitate developers when accessing and manipulating database data. In a nutshell, DbContext, DbSet, and DbQuery are the classes that have encapsulated the most frequently used patterns and functions while working with Entity Framework, replacing the old ObjectContext, ObjectSet and ObjectQuery respectively. In this post we will show how to use the DbContext class in order to retrieve data from database. We will use LINQ to Entities and LINQ to Objects queries to get data from database. Before continuing reading this post, if you are new either to Entity Framework or the LINQ I would strongly recommend you to read the related posts on this blog, Starting with Code First in Entity Framework and LINQ to Objects queries. Here’s what we are gonna look at this post:
- Retrieve all data from a DbSet
- Sort or filter retrieved data, using LINQ
- Retrieve a single entity
- How to use DbContext Local data
Let’s start. I have created a new solution in Visual Studio 2012 named DbContextQueryData and added a class library project named Model. This Domain Model project has at the moment the following two classes, Professor and Course.
namespace Model
{
public class Professor
{
public Professor()
{
this.Courses = new List<Course>();
}
public int ProfessorId { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public List<Course> Courses { get; set; }
}
}
namespace Model
{
public class Course
{
public int CourseId { get; set; }
public string CourseName { get; set; }
public string Description { get; set; }
public int ProfessorId { get; set; }
public Professor Professor { get; set; }
}
}
Each professor can be assigned with many courses while each course can be assigned in only one professor (One-To-Many relationship). The relationship between these two classes will be required. Continue by adding a new class library project named DataAccess. Install Entity Framework from Nuget Packages Manager and add a reference to the Model project. I have added a configuration class file named ModelConfigurations.cs where I declared the Code First Configurations for the model classes, using the Fluent API.
using Model;
using System.Data.Entity.ModelConfiguration;
namespace DataAccess
{
public class ProfessorConfiguration : EntityTypeConfiguration<Professor>
{
public ProfessorConfiguration()
{
Property(p => p.Name).IsRequired().HasMaxLength(50);
Property(p => p.Age).IsRequired();
}
}
public class CourseConfiguration : EntityTypeConfiguration<Course>
{
public CourseConfiguration()
{
Property(c => c.CourseName).IsRequired().HasMaxLength(40);
Property(c => c.Semester).IsRequired();
}
}
}
Next, create a new class file named UnivercityContext and paste the following code, in order to let Entity Framework know about our classes.
using System.Data.Entity;
using Model;
namespace DataAccess
{
public class UnivercityContext : DbContext
{
public DbSet<Professor> Professors { get; set; }
public DbSet<Course> Courses { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new ProfessorConfiguration());
modelBuilder.Configurations.Add(new CourseConfiguration());
}
}
}
Finally, I have created a new class file in the DataAccess project again, to ensure that each time you run the application the database will be re-created and seed with some mock data.
using Model;
using System.Data.Entity;
namespace DataAccess
{
public class InitializeUnivercitySeedData : DropCreateDatabaseAlways<UnivercityContext>
{
protected override void Seed(UnivercityContext context)
{
context.Professors.Add(new Professor
{
Name = "Christos S.",
Age = 27,
Courses = new List<Course>
{
new Course
{
CourseName = "ASP.NET MVC",
Description = "Programing with ASP.NET MVC",
Semester = 1
},
new Course
{
CourseName = "Entity Framework",
Description = "Learning the Entity Framework",
Semester = 2
}
}
});
context.Professors.Add(new Professor
{
Name = "John K.",
Age = 56,
Courses = new List<Course>
{
new Course
{
CourseName = "C# ",
Description = "Programing with C#",
Semester = 3
}
}
});
context.Professors.Add(new Professor
{
Name = "Mary C.",
Age = 24,
Courses = new List<Course>
{
new Course
{
CourseName = "Delphi ",
Description = "Programing with Delphi",
Semester = 2
}
}
});
context.Professors.Add(new Professor
{
Name = "Catherine R.",
Age = 29,
Courses = new List<Course>
{
new Course
{
CourseName = "C++ ",
Description = "Programing with C++",
Semester = 4
}
}
});
}
}
}
Retrieve all data from a DbSet
It’s time to start queering data through UnivercityContext class. Add a new console application project named UnivercitySecretary, add to it references both for the Model and the DataAccess projects and finally make sure you install the Entity Framework too. Modify the Program.cs file to print all available professors as follow.
using Model;
using DataAccess;
using System.Data.Entity;
namespace UnivercitySecretary
{
class Program
{
static void Main(string[] args)
{
Database.SetInitializer(new InitializeUnivercitySeedData());
DisplayAllProfessors();
Console.ReadKey();
}
private static void DisplayAllProfessors()
{
using (var context = new UnivercityContext())
{
foreach (var professor in context.Professors)
{
Console.WriteLine(professor.Name);
}
}
}
}
}
Set the console application as the start up project, build and run your application. By the time you run your application, when it’s time to retrieve all professors, the database will be created and filled with the seed data. All professors should be displayed in the console.
Mind thought, that in the way DisplayAllProfessors is coded at the moment, professor records are retrieved from database on demand (when needed) while iterating, which means a different SQL query is sent to the database for each professor. To retrieve all professors at once, change slightly the DisplayAllProfessors as follow.
private static void DisplayAllProfessors()
{
using (var context = new UnivercityContext())
{
var professors = context.Professors.ToList();
foreach (var professor in professors)
{
Console.WriteLine(professor.Name);
}
}
}
Using ToList() function, will cause to fill professors with all professor records at once.
Sort or filter retrieved data, using LINQ
Sorting retrieved entities is quite easy using LINQ. Add the following function and call it in your main method to get all professors ordered by name.
private static void DisplayAllProfessorsSortedByName()
{
using (var context = new UnivercityContext())
{
var query = from p in context.Professors
orderby p.Name
select p;
foreach (var professor in query)
{
Console.WriteLine(professor.Name);
}
}
}
Again, each result is retrieved as needed from the application. You could replace the query code to get all professors ordered by name at once, as follow.
var query = (from p in context.Professors
orderby p.Name
select p).ToList();
You can use the Where LINQ clause to filter retrieved results. For example, assume you want to get all professors with AGE < 30.
private static void DisplayProfessorsFilterByAge()
{
using (var context = new UnivercityContext())
{
var query = (from p in context.Professors
where p.Age < 30
orderby p.Name
select p).ToList();
foreach (var professor in query)
{
Console.WriteLine(professor.Name);
}
}
}
In the previous examples, we retrieved professor objects but all we wanted was to display their names. So why querying all of their data when we can get-project just those we want?
private static void DisplayProfessorsNamesOnly()
{
using (var context = new UnivercityContext())
{
var professorNames = (from p in context.Professors
select p.Name).ToList();
foreach (var name in professorNames)
{
Console.WriteLine(name);
}
}
}
What is actually sent to database this time is:
SELECT [Extent1].[Name] AS [Name] FROM [dbo].[Professors] AS [Extent1]
Retrieve a single entity
Instead of requesting a collection of data, some time you will only need to get a single entity. You can use the DbSet<Entity>.Find method to retrieve a record based on it’s key. If the entity is not found Null value will be returned. In the following example we will try to get two Entities, one with ID=2 and another with ID=8. The second one should return null value.
private static void FindProfessors()
{
using (var context = new UnivercityContext())
{
var secondProfessor = context.Professors.Find(2);
var eighthProfessor = context.Professors.Find(8);
if (secondProfessor == null)
Console.WriteLine("Professor with ID 2 not found!");
else
Console.WriteLine(secondProfessor.Name);
if (eighthProfessor == null)
Console.WriteLine("Professor with ID 8 not found!");
else
Console.WriteLine(eighthProfessor.Name);
}
}
Mind that Find method will look for entities loaded in memory from database, objects added in the context but not saved yet in database and finally in the database. You use the Find method to find an entity based on it’s key but what if you want to retrieve a record based on another property? You can use the Single or the SingleOrDefault methods. The difference between them is that the second one will return null value if record not found while the first one will raise an exception. Both will raise an exception if two rows found. Assuming we want to get a professor with AGE=27..
private static void FindSingleProfessorByAge()
{
using (var context = new UnivercityContext())
{
var query = from p in context.Professors
where p.Age == 27
select p;
// This will raise an exception if entity not found
// Use SingleOrDefault instead
var professor = query.Single();
Console.WriteLine(professor.Name);
}
}
How to use DbContext Local data
DbSet class has a property named Local that returns the in-memory data. What this means is that it will return all data loaded from database plus those that were added but yet not committed to database. To make this clearer we will code an example where we’ ll use the ToList function which always gets data from database in contrast with the Local property.
private static void UnderstandDbSetLocalProperty()
{
using (var context = new UnivercityContext())
{
var countLocalBefore = context.Professors.Local.Count;
Console.WriteLine("Local professor objects in memory: {0}",
countLocalBefore);
var countProfessorsInDb = context.Professors.ToList().Count;
Console.WriteLine("Professor objects in database: {0}",
countProfessorsInDb);
// Add a new professor but not save in database
context.Professors.Add(new Professor
{
Name = "Jason M.",
Age = 35
});
// Make the same calculations again
var countLocalAfter = context.Professors.Local.Count;
Console.WriteLine("Local professor objects in memory: {0}",
countLocalAfter);
var countProfessorsInDb2 = context.Professors.ToList().Count;
Console.WriteLine("Professor objects in database: {0}",
countProfessorsInDb2);
}
}
We usually use the Local property that returns a collection after we have loaded entities from database. We saw how we can iterate through a DbSet in order to query records from database but there is another way to load data into memory at once. Using the Load method.
private static void LoadAndDisplayProfessors()
{
using (var context = new UnivercityContext())
{
// This will load ALL professors in memory
context.Professors.Load();
// Now that professors are loaded in memory
// we can use the Local property
var professorNames = from p in context.Professors.Local
select p.Name;
foreach (var name in professorNames)
Console.WriteLine(name);
}
}
That’s it for now. We have seen lots of interesting things on querying data with DbContext and LINQ. What’s coming next is even more interesting cause we are gonna see how to get related data and how to insert, update and delete entities. I hope you have enjoyed the post. Download the project we created from here.
Categories: ADO.NET
chsakell's Blog 
how can i retrieve all the entity from any dbcontext?
Thanks!
Great post, some nice points. Thank-you! (small observation, looks like you left out “semester” property in Course class!)
Great post, Thanks a lot.
Easy to follow and understand. Awesome.