LINQ to Entities – Tutorial with Examples

When we think about querying databases, the first thing that pops to mind is usually some SQL query. Then other questions arise in regards to the database type, connection, query design, etc.

LINQ to Entities combined with Entity Framework allows the developers to skip a big portion of these questions and worries.

Entity Framework handles the database structure and connection while LINQ to Entities gives the possibility to write database queries in the same way as for any other data collection operation with LINQ.

Table of contents

• What is LINQ to Entities?
• Construction and Execution
• LINQ Query Operators
• LINQ Query Examples
• LINQ to Entities Limitations
• Conclusion

What is LINQ to Entities?

In Entity Framework, the collections of entities are represented by the DbSet class which is derived from IQueriable. That means that we can use LINQ queries to query DbSet.

LINQ to Entities converts the LINQ queries to the command tree queries. Command tree is a query representation that the Entity Framework can understand.

Then the command tree queries get converted to the SQL queries and executed against the target database. The result-set is converted back to the list of appropriate entity objects.

Construction and Execution

The processes of creation and execution of LINQ to Entities queries takes five steps:

1. Construct the ObjectQuery instance from ObjectContext.
2. Compose the LINQ to Entities query using ObjectQuery instance.
3. Convert the LINQ query to the command tree query.
4. Execute the command tree query against the data source.
5. Retrieve the query results back to the client.

LINQ to Entities Query Operators

The below table shows the list of standard query methods available in LINQ to Entities queries.

Note that some of the standard query operators are not supported in LINQ to Entities queries. Also, the behavior of some operators in LINQ to Entities queries differs from the standard LINQ behavior. We will not go into details here, but you should consider these differences and limitations when writing the LINQ to entities queries.

Examples of LINQ to Entities Query

After talking, we are finally ready to see examples. Let’s get back to our Faculty data model from the previous articles on LINQ.

public class FacultyContext : DbContext 
 { 
public FacultyContext() : base("name=FacultyContext") 
  { 
  } 
     public DbSet<Student> Students { get; set; } 
     public DbSet<Teacher> Teachers { get; set; } 
     public DbSet<Subject> Subjects { get; set; } 
  } 
     public class Student 
  { 
     public int Id { get; set; } 
     public string FirstName { get; set; } 
     public string LastName { get; set; } 
     public int BirthYear { get; set; } 
     public enum GenderType { Female, Male }; 
     public GenderType Gender { get; set; } 
     public string Interests { get; set; } 
     public virtual ICollection<Subject> Subjects { get; set; } 
 }

public class Subject 
 { 
     public int Id { get; set; } 
     public DateTime DateAdded { get; set; } = DateTime.Now; 
     public string Name { get; set; } 
     public int Capacity { get; set; } 
     public DateTime StartDate { get; set; } 
     public int TeacherId { get; set; } 
     public Teacher Teacher { get; set; } 
     public virtual ICollection<Student> Students { get; set; } 
 } 
     public class Teacher 
{ 
     public int Id { get; set; } 
     public string FirstName { get; set; } 
     public string LastName { get; set; } 
 }

To start, let’s take a very simple example. Say we want to get all students born in 1984.

using (var db = new FacultyContext()) 
 { 
    var students = db.Students.Where(m => m.BirthYear == 1984).ToList(); 
  }

SQL query that is executed against the underlying database is:

SELECT 
     [Extent1].[Id] AS [Id], 
     [Extent1].[FirstName] AS [FirstName],
     [Extent1].[LastName] AS [LastName], 
     [Extent1].[BirthYear] AS [BirthYear], 
     [Extent1].[Gender] AS [Gender], 
     [Extent1].[Interests] AS [Interests] 
    FROM [dbo].[Students] AS [Extent1] 
    WHERE 1984 = [Extent1].[BirthYear]

Now we want to find all female students born in 1984. The query is below:

using (var db = new FacultyContext()) 
 { 
    var students = db.Students.Where(m => m.BirthYear == 1984 && m.Gender == 
    Student.GenderType.Female).ToList(); 
 }

And the corresponding SQL query is:

SELECT 
     [Extent1].[Id] AS [Id], 
     [Extent1].[FirstName] AS [FirstName], 
     [Extent1].[LastName] AS [LastName], 
     [Extent1].[BirthYear] AS [BirthYear], 
     [Extent1].[Gender] AS [Gender], 
     [Extent1].[Interests] AS [Interests] 
FROM [dbo].[Students] AS [Extent1] 
WHERE (1984 = [Extent1].[BirthYear]) AND (0 = [Extent1].[Gender])

In C#, if you want to get a corresponding SQL query for any LINQ to Entities query, simply use the following method on it:

.ToString()

For example, the following code is a query for retrieving all students with the last name Matec and generating the corresponding SQL query:

using (var db = new FacultyContext()) 
 { 
    var query = db.Students.Where(m => m.LastName == "Matec"); 
    var sqlQuery = query.ToString(); 
    var students = query.ToList(); 
  Console.WriteLine(sqlQuery); 
  foreach (var student in students) 
  Console.WriteLine("{0} {1}", student.FirstName, student.LastName); 
 }

The below table provides more examples of the LINQ queries with their corresponding SQL queries generated.

LINQ to Entities Limitations

We already mentioned that LINQ to Entities queries do not support all standard LINQ operators. If an error occurs during еру query execution, an exception is thrown and passed directly to the client.

Here is an example of a query that would result in such an exception.

using (var db = new FacultyContext()) 
 { 
    var subjects = db.Subjects.Where(m => (m.StartDate - m.DateAdded).TotalDays < 7).ToList(); 
 }

The exception message in this situation is:

DbArithmeticExpression arguments must have a numeric common type

If the LINQ expressions used in a query are not translatable to an SQL query, an exception is thrown.

Conclusion

We can consider LINQ to Entities to be a kind of bridge between LINQ queries and Entity Framework. While Entity Framework does the database stuff for us, LINQ provides us with advanced object collection handling functionalities. But, like all bridges, LINQ to Entities has some limitations too, and we should keep them in mind.

Regardless of these limitations, I hope that the examples provided in this article will demonstrate how simple and yet powerful this technology is. LINQ to Entities is a decent tool to have in your programming toolbox.

Thank you for sticking to the end of the article and following the series about the LINQ technologies.