Entity Framework - Projection Queries

LINQ to Entities

One of the most important concepts to understand LINQ to Entities is that it’s a declarative language. The focus is on defining what information you need, rather than on how to obtain the information.

• It means that you can spend more time working with data and less time trying to figure out the underlying code required to perform tasks such as accessing the database.

• It’s important to understand that declarative languages don’t actually remove any control from the developer, but it helps the developer focus attention on what’s important.

LINQ to Entities Essential Keywords

It’s important to know the basic keywords used to create a LINQ query. There are only a few keywords to remember, but you can combine them in various ways to obtain specific results. The following list contains these basic keywords and provides a simple description of each one.

Projection

Projection queries improve the efficiency of your application, by only retrieving specific fields from your database.

• Once you have the data, you may want to project or filter it as needed to shape the data prior to output.

• The main task of any LINQ to Entities expression is to obtain data and provide it as output.

The “Developing LINQ to Entities queries” section of this chapter demonstrates the techniques for performing this basic task.

Let’s take a look at the following code in which list of students will be retrieved.

using (var context = new UniContextEntities()) {

   var studentList = from s in context.Students select s;

   foreach (var student in studentList) {
      string name = student.FirstMidName + " " + student.LastName;
      Console.WriteLine("ID : {0}, Name: {1}", student.ID, name);
   }
}

Single Object

To retrieve a single student object you can use First() or FirstOrDefault enumerable methods which returns the first element of a sequence. The difference between First and FirstOrDefault is that First() will throw an exception, if there is no result data for the supplied criteria whereas FirstOrDefault() returns default value null, if there is no result data. In the below code snippet first student from the list will be retrieved whose first name is Ali.

using (var context = new UniContextEntities()) {

   var student = (from s in context.Students where s.FirstMidName 
      == "Ali" select s).FirstOrDefault<Student>();

   string name = student.FirstMidName + " " + student.LastName;
   Console.WriteLine("ID : {0}, Name: {1}", student.ID, name);
}

You can also use Single() or SingleOrDefault to get a single student object which returns a single, specific element of a sequence. In the following example, a single student is retrieved whose ID is 2.

using (var context = new UniContextEntities()) {

   var student = (from s in context.Students where s.ID 
      == 2 select s).SingleOrDefault<Student>();
   string name = student.FirstMidName + " " + student.LastName;
	
   Console.WriteLine("ID : {0}, Name: {1}", student.ID, name);
   Console.ReadKey();
}

List of Objects

If you want to retrieve the list of students whose first name is Ali then you can use ToList() enumerable method.

using (var context = new UniContextEntities()) {

   var studentList = (from s in context.Students where s.FirstMidName 
      == "Ali" select s).ToList();

   foreach (var student in studentList) {
      string name = student.FirstMidName + " " + student.LastName;
      Console.WriteLine("ID : {0}, Name: {1}", student.ID, name);
   }

   Console.ReadKey();
}

Order

To retrieve data/list in any particular order you can used orderby keyword. In the following code, snippet list of student will be retrieved in ascending order.

using (var context = new UniContextEntities()) {

   var studentList = (from s in context.Students orderby
      s.FirstMidName ascending select s).ToList();

   foreach (var student in studentList) {
      string name = student.FirstMidName + " " + student.LastName;
      Console.WriteLine("ID : {0}, Name: {1}", student.ID, name);
   }

   Console.ReadKey();
}

Standard Vs Projection Entity Framework Query

Let’s suppose, you have a Student model that contains the ID, FirstMidName, LastName and EnrollmentDate. If you want to return a list of Students, a standard query would return all the fields. But if you only want to get a list of students that contain ID, FirstMidName, and LastName fields. This is where you should use a projection query. Following is the simple example of projection query.

using (var context = new UniContextEntities()) {

   var studentList = from s in context.Students
      orderby s.FirstMidName ascending
      where s.FirstMidName == "Ali"

   select new {s.ID, s.FirstMidName, s.LastName};

   foreach (var student in studentList) {
      string name = student.FirstMidName + " " + student.LastName;
      Console.WriteLine("ID : {0}, Name: {1}", student.ID, name);
   }

   Console.ReadKey();
}

The projection query above excludes the EnrollmentDate field. This will make your application much quicker.