Loading


Microsoft Entity Framework

  • Home
  • Microsoft Entity Framework
  • What is LINQ to Entities Queries in Entity Framework ?. The Complete Microsoft Entity Framework Developer Course 2023 [Videos].
Microsoft Entity Framework

What is LINQ to Entities Queries in Entity Framework ?. The Complete Microsoft Entity Framework Developer Course 2023 [Videos].

By admin / 11-Feb-2022 / in Blog, Microsoft Entity Framework / 444 Comments / 37 Likes / Share This Post

In this Video, I am going to discuss LINQ to Entities Queries in Entity Framework. Please read our previous Video where we discussed Different Approaches of Querying in Entity Framework.

Note: We are going to work with the same example that we created in our Introduction to Entity Framework Database First Video. Please read our introduction to Entity Framework Database First Video before proceeding to this Video.

LINQ-to-Entities Queries

In this Video, I will show you how to write LINQ-to-Entities queries and get the result in Entity Framework. Please visit our LINQ Tutorials to learn LINQ step by step in detail.

The Entity Framework DbSet class is derived from the IQuerayable interface. As the DbSet class is derived from the IQuerayable interface, so, we can use LINQ (Language Integrated Query) for querying against DbSet and the DbSet then covert the queries into the SQL queries that the underlying database can understand and execute and gets the result set, converts it into appropriate entity type objects and returns it as a query result.

What is LINQ?

The LINQ (Language Integrated Query) is a part of a language but not a complete language. It was introduced by Microsoft with .NET Framework 3.5 and C# 3.0 and is available in System.Linq namespace.

LINQ provides us common query syntax which allows us to query the data from various data sources. That means using a single query we can get or set the data from various data sources such as SQL Server database, XML documents, ADO.NET Datasets, and any other in-memory objects such as Collections, Generics, etc. For a better understanding of how LINQ works please read the following Video where we explain the architecture of LINQ in detail.

The architecture of LINQ in detail

What is Projection?

Projection is nothing but the mechanism which is used to select the data from a data source. You can select the data in the same form (i.e. the original data in its original state). It is also possible to create a new form of data by performing some operations on it.

LINQ Query Operators:

The LINQ Standard query operators are implemented as extension methods and those methods can be used with LINQ-to-Entities queries. The following are examples of some of the standard LINQ Query Operators.

  1. First() or FirstOrDefault()
  2. Single()or SingleOrDefault()
  3. ToList()
  4. Count()
  5. Min() and Max()
  6. Sum()
  7. Distinct()
  8. Last() or LastOrDefault()
  9. Average(), and many more

Note: In addition to the above LINQ Extension methods, you can use the DbSet Find() method to search an entity based on the primary key value.

Find() Method:

The Find method belongs to the DbSet class. This method is used to Find an entity with the given primary key values. If an entity with the given primary key values exists in the context, then it is returned immediately without making a request to the database. Otherwise, a request is made to the database for an entity with the given primary key values and this entity, if found, is attached to the context and returned. If no entity is found either in the context or in the database, then null is returned.

Example:

In our example, EF_Demo_DBEntities is our DbContext class and Students is the DbSet property and we want to find the student whose StudentId is 1. Here, StudentId is the primary key in our Student table.

using System;
namespace DBFirstApproach
{
class Program
{
static void Main(string[] args)
{
using (EF_Demo_DBEntities context = new EF_Demo_DBEntities())
{
var student = context.Students.Find(1);
Console.WriteLine($"FirstName: {student?.FirstName}, LastName: {student?.LastName}");
Console.Read();
}
}
}
}
First or FirstOrDefault Method

These two methods are used when you want to fetch a single entity from the list of entities. For example, it might be possible that there is more than one student with the FirstName Rohit, but our requirement is only to fetch the first student whose name is Rohit. In this case, we need to use either the First or FirstOrDefault method as shown in the below example. Again, we can write the LINQ queries in two different ways i.e. using Method Syntax and using Query Syntax,

LINQ Method Syntax:
using System;
using System.Linq;
namespace DBFirstApproach
{
class Program
{
static void Main(string[] args)
{
using (EF_Demo_DBEntities context = new EF_Demo_DBEntities())
{
//FirstOrDefault method using Method Syntax
var student1 = context.Students
.FirstOrDefault(s => s.FirstName == "Rohit");
Console.WriteLine($"FirstName: {student1?.FirstName}, LastName: {student1?.LastName}");
//First method using Method Syntax
var student2 = context.Students
.First(s => s.FirstName == "Rohit");
Console.WriteLine($"FirstName: {student2?.FirstName}, LastName: {student2?.LastName}");
Console.Read();
}
}
}
}

Output:

LINQ to Entities Queries in Entity Framework 

LINQ Query Syntax:
using System;
using System.Linq;
namespace DBFirstApproach
{
class Program
{
static void Main(string[] args)
{
using (EF_Demo_DBEntities context = new EF_Demo_DBEntities())
{
//FirstOrDefault method using Query Syntax
var student1 = (from s in context.Students
where s.FirstName == "Rohit"
select s).FirstOrDefault();
Console.WriteLine($"FirstName: {student1?.FirstName}, LastName: {student1?.LastName}");
//First method using Query Syntax
var student2 = (from s in context.Students
where s.FirstName == "Rohit"
select s).First(); ;
Console.WriteLine($"FirstName: {student2?.FirstName}, LastName: {student2?.LastName}");
Console.Read();
}
}
}
}

Here, you will also get the same output as the previous example.

Note: Both First and FirstOrDefault methods in LINQ are used to return the first element from a data source. But if the data source is null or if the specified condition does not return any data, then the First method will throw an exception while the FirstOrDefault method will not throw an exception instead it returns a default value based on the data type of the element.

Example: In our database, the Student table does not have any student with the name James and if we try to fetch the student whose FirstName is James using the LINQ First method, then it will throw an exception as shown in the below example.

using System;
using System.Linq;
namespace DBFirstApproach
{
class Program
{
static void Main(string[] args)
{
using (EF_Demo_DBEntities context = new EF_Demo_DBEntities())
{
//First method using Query Syntax
var student = (from s in context.Students
where s.FirstName == "James"
select s).First(); ;
Console.WriteLine($"FirstName: {student?.FirstName}, LastName: {student?.LastName}");
Console.Read();
}
}
}
}

Output: System.InvalidOperationException: "Sequence contains no elements"

Parameterized Query:

The Entity Framework builds and executes a parameterized query in the database if the LINQ-to-Entities query uses parameters. In the following example, we use the parameter FirstName.

using System;
using System.Linq;
namespace DBFirstApproach
{
class Program
{
static void Main(string[] args)
{
using (EF_Demo_DBEntities context = new EF_Demo_DBEntities())
{
string FirstName = "Rohit";
var student = context.Students
.FirstOrDefault(s => s.FirstName == FirstName);
Console.WriteLine($"FirstName: {student?.FirstName}, LastName: {student?.LastName}");
Console.Read();
}
}
}
}

When we execute the above program, the following SQL query is executed in the database and capture this query using a tool called SQL Profiler.

LINQ-to-Entities Queries

ToList Method:

The ToList Method is used to create a System.Collections.Generic.List<T> collection from a System.Collections.Generic.IEnumerable<T>. This method causes the query to be executed immediately. For example, if you want to list all the students in a collection of type List, then you need to use the ToList() method as shown in the below example.

using System;
using System.Linq;
namespace DBFirstApproach
{
class Program
{
static void Main(string[] args)
{
using (EF_Demo_DBEntities context = new EF_Demo_DBEntities())
{
var studentList = context.Students.Where(x => x.StandardId == 1).ToList();
foreach(var student in studentList)
{
Console.WriteLine($"FirstName: {student?.FirstName}, LastName: {student?.LastName}");
}
Console.Read();
}
}
}
}

Note: Along with ToList, you can also use ToArray, ToDictionary or ToLookup as per your business need. 

GroupBy Method:

The group by operator or GroupBy extension method exactly does the same thing as the Group By clause does in SQL Query. This method takes a flat sequence of elements and then organizes the elements into groups (i.e. IGrouping<TKey, TSource>) based on a given key. The following example gets the results grouped by each Standard.

using System;
using System.Linq;
namespace DBFirstApproach
{
class Program
{
static void Main(string[] args)
{
using (EF_Demo_DBEntities context = new EF_Demo_DBEntities())
{
//GroupBy using Method syntax
Console.WriteLine("GroupBy using Method syntax");
var studentsMS = context.Students.GroupBy(s => s.StandardId);
foreach (var groupItem in studentsMS)
{
Console.WriteLine($"StandardId : {groupItem.Key}");
foreach (var student in groupItem)
{
Console.WriteLine($" FirstName: {student?.FirstName}, LastName: {student?.LastName}");
}
}
Console.WriteLine();
//GroupBy using Query syntax
Console.WriteLine("GroupBy using Query syntax");
var studentsQS = from s in context.Students
group s by s.StandardId into studentsByStandard
select studentsByStandard;
foreach (var groupItem in studentsQS)
{
Console.WriteLine($"StandardId : {groupItem.Key}");
foreach (var student in groupItem)
{
Console.WriteLine($" FirstName: {student?.FirstName}, LastName: {student?.LastName}");
}
}
Console.Read();
}
}
}
}

When you execute the above program, the following SQL Query will execute in the database for both Method and Query Syntax. We captured this query using the SQL Profiler tool.

SELECT
[Project2].[C1] AS [C1],
[Project2].[StandardId] AS [StandardId],
[Project2].[C2] AS [C2],
[Project2].[StudentId] AS [StudentId],
[Project2].[FirstName] AS [FirstName],
[Project2].[LastName] AS [LastName],
[Project2].[StandardId1] AS [StandardId1]
FROM ( SELECT
[Distinct1].[StandardId] AS [StandardId],
1 AS [C1],
[Extent2].[StudentId] AS [StudentId],
[Extent2].[FirstName] AS [FirstName],
[Extent2].[LastName] AS [LastName],
[Extent2].[StandardId] AS [StandardId1],
CASE WHEN ([Extent2].[StudentId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
FROM (SELECT DISTINCT
[Extent1].[StandardId] AS [StandardId]
FROM [dbo].[Student] AS [Extent1] ) AS [Distinct1]
LEFT OUTER JOIN [dbo].[Student] AS [Extent2] ON ([Distinct1].[StandardId] = [Extent2].[StandardId]) OR (([Distinct1].[StandardId] IS NULL) AND ([Extent2].[StandardId] IS NULL))
) AS [Project2]
ORDER BY [Project2].[StandardId] ASC, [Project2].[C2] ASC
OrderBy Method:

We use the OrderBy operator with ascending/descending keywords in the LINQ query to get the sorted entity list. In simple terms, we can say that Ordering is nothing but a process to manage the data in a particular order. It is not changing the data or output rather this operation arranges the data in a particular order i.e. either ascending order or descending order. In this case, the count is going to be the same but the order of the element is going to change.

In the following example, we sorted the students based on the FirstName. Here, I am showing you how to use Order By using both the Method and Query syntax.

using System;
using System.Linq;
namespace DBFirstApproach
{
class Program
{
static void Main(string[] args)
{
using (EF_Demo_DBEntities context = new EF_Demo_DBEntities())
{
//Order By using Query syntax
var studentsQS = from s in context.Students
orderby s.FirstName ascending
select s;
//Order By using Method syntax
var studentsMS = context.Students.OrderBy(s => s.FirstName).ToList();
foreach (var student in studentsQS)
{
Console.WriteLine($" FirstName: {student?.FirstName}, LastName: {student?.LastName}");
}
Console.Read();
}
}
}
}

When you execute the above program, the following SQL Script is created and executed in the database for both Method and Query syntax.

SELECT
[Extent1].[StudentId] AS [StudentId],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[StandardId] AS [StandardId]
FROM [dbo].[Student] AS [Extent1]
ORDER BY [Extent1].[FirstName] ASC
Anonymous Object Result:

Instead of projecting the data to a particular type like Student, we can also project the data to an anonymous type using LINQ-to-Entities queries. Here, you may choose some of the properties or all of the properties of an entity as a result. The following example returns a list of anonymous objects which contain only the FirstName and LastName properties. The projection Result in the below example will be an anonymous type because there is no class/entity which has these properties. So, the compiler will mark it as anonymous.

using System;
using System.Linq;
namespace DBFirstApproach
{
class Program
{
static void Main(string[] args)
{
using (EF_Demo_DBEntities context = new EF_Demo_DBEntities())
{
//Query Syntax
var selectQuery = (from std in context.Students
select new
{
firstName = std.FirstName,
lastName = std.LastName
});
foreach (var student in selectQuery)
{
Console.WriteLine($"FirstName: {student?.firstName}, LastName: {student?.lastName}");
}
//Method Syntax
var selectMethod = context.Students.
Select(std => new
{
firstName = std.FirstName,
lastName = std.LastName
}).ToList();
foreach (var student in selectMethod)
{
Console.WriteLine($"FirstName: {student?.firstName}, LastName: {student?.lastName}");
}
Console.Read();
}
}
}
}

When you execute the above program, the following SQL Script is executed in the database for both Method and Query syntax.

SELECT
1 AS [C1],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName]
FROM [dbo].[Student] AS [Extent1]
Join Method:

The LINQ Inner join is used to return only the matching records from both the data sources while the non-matching elements are removed from the result set. So, if you have two data sources let us say Student and StudentAddress, and when you perform the LINQ inner join, then only the matching elements i.e. the records which exist in both Student and StudentAddress table are included in the result set. For better understanding, please have a look at the following example.

using System;
using System.Linq;
namespace DBFirstApproach
{
class Program
{
static void Main(string[] args)
{
using (EF_Demo_DBEntities context = new EF_Demo_DBEntities())
{
//Join using Method Syntax
var JoinUsingMS = context.Students //Outer Data Source
.Join(
context.StudentAddresses, //Inner Data Source
student => student.StudentId, //Inner Key Selector
studentaddress => studentaddress.StudentId, //Outer Key selector
(student, studentaddress) => new //Projecting the data into an anonymous type
{
StudentName = student.FirstName +" "+ student.LastName,
AddressLine1 = studentaddress.Address1,
AddressLine2 = studentaddress.Address2,
}).ToList();
foreach (var student in JoinUsingMS)
{
Console.WriteLine($"Name: {student?.StudentName}, AddressLine1: {student?.AddressLine1}, AddressLine2: {student?.AddressLine2}");
}
//Join using Query Syntax
var JoinUsingQS = (from student in context.Students
join studentaddress in context.StudentAddresses
on student.StudentId equals studentaddress.StudentId
select new
{
StudentName = student.FirstName + " " + student.LastName,
AddressLine1 = studentaddress.Address1,
AddressLine2 = studentaddress.Address2,
}).ToList();
foreach (var student in JoinUsingQS)
{
Console.WriteLine($"Name: {student?.StudentName}, AddressLine1: {student?.AddressLine1}, AddressLine2: {student?.AddressLine2}");
}
Console.Read();
}
}
}
}

When you execute the above program, the following SQL Script is executed in the database for both Method and Query Syntax.For a better understanding of LINQ Joins and how to perform the left join, how to join more than two tables, please read the following Video.

  1. LINQ Inner JOIN
  2. Joining Multiple Tables in LINQ
  3. Left Outer Join in LINQ

Note: The point that you need to keep in mind is that there are no differences in the generated SQL whether you use Method syntax or Query Syntax using LINQ to Entities query.

Read more
See All

Comments (444 Comments)

Submit Your Comment

See All Posts

Related Posts

Microsoft Entity Framework / Blog

What is the Entity Framework?

Entity Framework is an Open-Source Object-Relational Mapping (ORM) Framework for .NET applications that enables .NET developers to work with relational data using domain-specific objects without focusing on the underlying database tables and columns where actually the data is stored. That means the Entity Framework eliminates the need for writing the data-access code that developers usually need to write.
11-Feb-2022 /37 /444

Microsoft Entity Framework / Blog

What is Entity Framework Architecture?

In this article, I am going to discuss the Entity Framework Architecture in Detail. Please read our previous article where we discussed the Overview of the Entity Framework. At the end of this article, you will understand the following pointers in detail.
11-Feb-2022 /37 /444

Microsoft Entity Framework / Blog

What is Context Class in Entity Framework?

In this article, I am going to discuss the Entity Framework Context Class with an example. Please read our previous article where we discussed the Architecture of Entity Framework in Detail. At the end of this article, you will understand what exactly the Context Class is and when and how to use this Context Class in Entity Framework with an example.
11-Feb-2022 /37 /444