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.

Sr. No.	Keyword & Description
1	Ascending Specifies that a sorting operation takes place from the least (or lowest) element of a range to the highest element of a range. This is normally the default setting. For example, when performing an alphabetic sort, the sort would be in the range from A to Z.
2	By Specifies the field or expression used to implement a grouping. The field or expression defines a key used to perform the grouping task.
3	Descending Specifies that a sorting operation takes place from the greatest (or highest) element of a range to the lowest element of a range. For example, when performing an alphabetic sort, the sort would be in the range from Z to A.
4	Equals Used between the left and right clauses of a join statement to join the primary contextual data source to the secondary contextual data source. The field or expression on the left of the equals keyword specifies the primary data source, while the field or expression on the right of the equals keyword specifies the secondary data source.
5	From Specifies the data source used to obtain the required information and defines a range variable. This variable has the same purpose as a variable used for iteration in a loop.
6	Group Organizes the output into groups using the key value you specify. Use multiple group clauses to create multiple levels of output organization. The order of the group clauses determines the depth at which a particular key value appears in the grouping order. You combine this keyword with by to create a specific context.
7	In Used in a number of ways. In this case, the keyword determines the contextual database source used for a query. When working with a join, the in keyword is used for each contextual database source used for the join.
8	Into Specifies an identifier that you can use as a reference for LINQ query clauses such as join, group, and select.
9	Join Creates a single data source from two related data sources, such as in a master/detail setup. A join can specify an inner, group, or left-outer join, with the inner join as the default. You can read more about joins at msdn.microsoft.com
10	Let Defines a range variable that you can use to store subexpression results in a query expression. Typically, the range variable is used to provide an additional enumerated output or to increase the efficiency of a query (so that a particular task, such as finding the lowercase value of a string, need not be done more than one time).
11	On Specifies the field or expression used to implement a join. The field or expression defines an element that is common to both contextual data sources.
12	Orderby Creates a sort order for the query. You can add the ascending or descending keyword to control the order of the sort. Use multiple orderby clauses to create multiple levels of sorting. The order of the orderby clauses determines the order in which the sort expressions are handled, so using a different order will result in different output.
13	Where Defines what LINQ should retrieve from the data source. You use one or more Boolean expressions to define the specifics of what to retrieve. The Boolean expressions are separated from each other using the && (AND) and || (OR) operators.
14	Select Determines the output from the LINQ query by specifying what information to return. This statement defines the data type of the elements that LINQ returns during the iteration process.

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.