LINQ to Entities Tutorial is the way to query the entities in Entity Framework & Entity Framework Core. This has not changed much from EF 6 to EF Core. Refer to the tutorial Entity Framework Tutorial or Entity Framework Core Tutorial, which covers the Entity Framework from start to finish. The following are the tutorials in this series
- Querying LINQ to Entities in Entity Framework
- Select Single Entity using Find, Single, SingleOrDefault, First, FirstOrDefault
- Projection Queries in Entity Framework
- Loading Related Data
- Join Query in Entity Framework
Table of Contents
Introduction to LINQ to Entities
LINQ to Entities is a simple and easy way to query the entities using C#. Learn how to Query, Add, update, delete records from the database using LINQ to entities in entity framework or entity framework core.
A Query is an expression, which is used to get the data from the data store. In Entity Framework Querying the database is done using the DBContext in EF 6 or DBContext in EF Core. DBContext class exposes the DBSet Property for the each of the entities in the model. The queries are written against the DBSet.
In Entity Framework queries are written against the classes and not against the database. The Database is completely abstracted by the entity framework. You can write queries, without knowing the persistence mechanism. Entity framework converts those queries to database SQL Queries
You can query the data from entity framework using the following three methods
- LINQ to Entities
- Entity SQL
- Native SQL
LINQ to Entities
LINQ stands for Language-INtegrated Query. It was introduced along with Visual Studio 2008. It uses the Features of Language (C# or Visual Basic) to query and update the data. It can be used and extended to support any Kind of Data Stores. LINQ to Entities is a subset of LINQ which allows us to write queries against the Entity Framework conceptual models.
Entity SQL
Entity SQL is a query language designed to be used with the Entity Framework. The Entity SQL Queries are database independent. EntitySQL looks similar to standard SQL Queries
Native SQL
Native SQL is the database dependent query language.
LINQ to Entities Example
The LINQ to Entities allows us to write queries (known as LINQ Queries) in C# and VB.NET. These LINQ queries are then transferred into the Expression Tree. The Database Provider (Entity Client in EF6) will then use the Entity Model to translate LINQ Queries into the SQL query.
The SQL query is then sent to the database using the Command object of the ADO.NET provider. The query is executed on the database. The result of the query is returned to back to the EF. The EF translates the result back to domain entities. This process is known as Materialisation. Finally, entities are returned to the application.
The LINQ to Entities supports two query syntaxes
- Query syntax
- Method syntax
Query Syntax
Query syntax consists of a set of clauses written in a declarative syntax and looks very similar to SQL Queries.
The .NET Framework CLR does not understand these queries. Hence, they are translated to method syntax at the compile time.
Query Syntax Example
Now let us write a simple LINQ query using both these approach
1 2 3 4 5 6 7 8 | using(SampleDbEntities db = new SampleDbEntities()) { var employees = from e in db.Employees where e.LastName == "Tendulkar" select e; } |
Context
The first step is to get the reference to the DbContext class.
Return Type
1 2 3 | var employees |
The return type of a query is called Query Variable. It stores the query rather than the results of the query. The Return type must be of type IEnumerable or IQueryable
From Clause
1 2 3 | from e in db.Employees |
The LINQ queries must begin with a From Clause. This From clause must specify
- The data source to be used.
In the above example db.Employees is the data source. - The range variable.
In the above example, e is the Range variable. The range variable temporarily holds a value from the data source. The Range variable can be compared to the iteration variable in a foreach loop. The data type of the range variable is inferred by the compiler from the data source. In the above example data source is db.Employees. Hence the data type of the range variable is the entity type represented by the Dbset db.Employees
Where Clause
1 2 3 | where e.LastName == "Singh" |
Where Clause is used to apply the filter. This Clause is similar to T-SQL Where clause. Only the boolean Expressions are allowed in the where clause. The above query returns the employees with the last Name as Singh
Select Clause
1 2 3 | select e; |
The select clause returns the final result. The LINQ Queries must end with a Select clause or a Join Clause
The normal SQL queries, which we used to write starts with the keyword Select. The LINQ Queries starts with the From Clause. The select clause comes last.
Method Syntax
The method syntax is a series of c# method calls chained together using Fluent Interface. The method calls take the lambda expressions as the parmeters. The method syntax looks more like C# statements.
Method Syntax Example
The Method syntax allows us to write queries based on standard query expressions. The above query can be written using the method syntax as follows.
1 2 3 4 5 6 7 8 9 10 11 12 | using (SampleDbEntities db = new SampleDbEntities()) { var employees = db.Employees .Where(employee => employee.LastName == "Tendulkar"); foreach (Employee e in employees ) { Console.WriteLine(e.FirstName + ' ' + e.LastName + ' ' + e.Title); } } |
In this example the Return type ( var employees) is an IEnumerable type as in the case of query syntax. Here the c# extension method Where takes the lambda expression employee => employee.LastName == “Tendulkar” and operates on the Dbset object. Note that there is no From clause & select clause as in case of query syntax
Query Syntax Vs Method Syntax
There is hardly any difference between the method syntax and the query syntax. The query syntax is converted to Method syntax when the code is compiled. Hence there is no performance
The Query syntax is more readable compared to the method syntax.
Further Reading
- Querying LINQ to Entities in Entity Framework
Learn how to Write a simple query, which retrieves the collection of data from the table. Learn how to Filter the data with where clause. Learn how to Sort the output using orderBy method. - LINQ to entities Finding the Single Entity
Learn how to return a Single entity from the table using Primary Key. Learn how to return the results from the database using the other fields using the methods like First, FirstOrDefault, Single, SingleOrDefault etc. - Projection Queries in Entity Framework
Projection queries in Entity Framework are used to create a query that selects from a set of entities in your model, but returns results that are of a different type. Find out how to return an Anonymous or Custom Type from the LINQ To Entities Queries - Loading Related Data
Find out how to retrieve Related data from the Entities using Navigation Properties. - Join Queries in Entity Framework
Learn to use Join Queries to Load Data from multiple tables using both query & Method syntax
