Back to: ASP.NET Core Tutorials For Beginners and Professionals
LINQ to Entities in Entity Framework Core
In this article, I am going to discuss LINQ to Entities in Entity Framework Core (EF Core). Please read our previous article, where we discussed Entity States in Entity Framework Core. We will work with the same example we have worked on so far.
LINQ to Entities in EF Core:
Querying in Entity Framework Core using LINQ to Entities remains the same as in Entity Framework 6.x, with more optimized SQL queries and the ability to include C#/VB.NET functions into LINQ-to-Entities queries.
Model or Entities Used in our Application:
In our application, we are using the following Student and Standard Entities.
Student.cs
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using System.ComponentModel.DataAnnotations.Schema;
namespace EFCoreCodeFirstDemo.Entities
{
public class Student
{
public int StudentId { get; set; }
public string? FirstName { get; set; }
public string? LastName { get; set; }
public DateTime? DateOfBirth { get; set; }
[Column(TypeName = "decimal(18,4)")]
public decimal Height { get; set; }
[Column(TypeName = "decimal(18,4)")]
public float Weight { get; set; }
public virtual Standard? Standard { get; set; }
}
}
Standard.cs
namespace EFCoreCodeFirstDemo.Entities
{
public class Standard
{
public int StandardId { get; set; }
public string? StandardName { get; set; }
public string? Description { get; set; }
public ICollection<Student>? Students { get; set; }
}
}
Context Class:
The following is our EFCoreDbContext class.
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
namespace EFCoreCodeFirstDemo.Entities
{
public class EFCoreDbContext : DbContext
{
//Constructor calling the Base DbContext Class Constructor
public EFCoreDbContext() : base()
{
}
//OnConfiguring() method is used to select and configure the data source
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
//To Display the Generated the Database Script
optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);
//Configuring the Connection String
optionsBuilder.UseSqlServer(@"Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=EFCoreDB1;Trusted_Connection=True;TrustServerCertificate=True;";);
}
//OnModelCreating() method is used to configure the model using ModelBuilder Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
//use this to configure the model
}
//Adding Domain Classes as DbSet Properties
public DbSet<Student> Students { get; set; }
public DbSet<Standard> Standards { get; set; }
}
}
LINQ to Entities Queries in Entity Framework Core
Please ensure the following records are inside the Students and Standards table.
Please use the below SQL Scripts to create and populate the above in the database.
USE EFCoreDB1
GO
Truncate table Students;
GO
INSERT INTO Standards VALUES('1st', 'First-Standard');
INSERT INTO Standards VALUES('2nd', 'Second-Standard');
INSERT INTO Standards VALUES('3rd', 'Third-Standard');
GO
INSERT INTO Students VALUES('Pranaya', 'Rout', '1988-02-29', 5.10, 72, 1);
INSERT INTO Students VALUES('Mahesh', 'Kumar', '1992-12-15', 5.11, 75, 2);
INSERT INTO Students VALUES('Hina', 'Sharma', '1986-10-20', 5.5, 65, 3);
GO
LINQ-to-Entities Queries in Entity Framework Core:
The Entity Framework Core 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 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 a common query syntax that allows us to query the data from various 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. Please read the Architecture of LINQ article.
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.
For example, let’s assume in the database table; there is a Salary column. If you want, you can get the Salary Column Values as it is, i.e., in their original form. But, if you want to modify the Salary column values by adding a bonus amount of 10000, then it is also possible. Don’t worry if this is unclear now; we will understand this with real-time examples.
LINQ Query Methods:
The LINQ Standard query methods 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 methods.
- First() or FirstOrDefault()
- Single()or SingleOrDefault()
- ToList()
- Count()
- Min() and Max()
- Sum()
- Distinct()
- Last() or LastOrDefault()
- 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.
DbSet Find() Method in Entity Framework:
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, it is returned immediately without requesting 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 in the context object or in the database, null is returned.
Example to Understand DbSet Find() Method in EF Core:
In our example, EFCoreDbContext 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 Students table. So, modify the Main method of the Program class as follows.
using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
using var context = new EFCoreDbContext();
var student = context.Students.Find(1);
Console.WriteLine($"FirstName: {student?.FirstName}, LastName: {student?.LastName}");
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
}
}
First or FirstOrDefault Method in EF Core
These two methods are used when we 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 Pranaya, but our requirement is only to fetch the first student whose name is Pranaya. In this case, we need to use the First or FirstOrDefault method, as shown in the example below. Again, we can write the LINQ queries in two different ways, i.e., using Method Syntax and using Query Syntax,
using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
using var context = new EFCoreDbContext();
//FirstOrDefault method using Method Syntax
var student1 = context.Students.FirstOrDefault(s => s.FirstName == "Pranaya");
Console.WriteLine($"FirstName: {student1?.FirstName}, LastName: {student1?.LastName}");
//First method using Method Syntax
var student2 = context.Students.First(s => s.FirstName == "Pranaya");
Console.WriteLine($"FirstName: {student2?.FirstName}, LastName: {student2?.LastName}");
//FirstOrDefault method using Query Syntax
var student3 = (from s in context.Students
where s.FirstName == "Pranaya"
select s).FirstOrDefault();
Console.WriteLine($"FirstName: {student3?.FirstName}, LastName: {student3?.LastName}");
//First method using Query Syntax
var student4 = (from s in context.Students
where s.FirstName == "Pranaya"
select s).First();
Console.WriteLine($"FirstName: {student4?.FirstName}, LastName: {student4?.LastName}");
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
}
}
Differences Between First and FirstOrDefault Methods in LINQ:
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 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 element’s data type.
In our database, the Students table does not have any student with the name Smith, and if we try to fetch the student whose FirstName is Smith using the LINQ First method, then it will throw an exception, as shown in the below example. But, if we use the FirstOrDefault method, then it will not throw an exception; rather, it will return the default value of the Student type.
using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
using var context = new EFCoreDbContext();
//FirstOrDefault method using Method Syntax
var student1 = context.Students.FirstOrDefault(s => s.FirstName == "Smith");
Console.WriteLine($"FirstName: {student1?.FirstName}, LastName: {student1?.LastName}");
//First method using Method Syntax
var student2 = context.Students.First(s => s.FirstName == "Smith");
Console.WriteLine($"FirstName: {student2?.FirstName}, LastName: {student2?.LastName}");
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
}
}
Output: Error: Sequence contains no elements
Parameterized Query in Entity Framework Core:
The Entity Framework Core can build and execute a parameterized query in the database if the LINQ-to-Entities query uses parameters. For a better understanding, please have a look at the following example. In the below example, we use the parameter FirstName.
using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
//Creating an Instance of Context class
using var context = new EFCoreDbContext();
//Creating a Variable
string FirstName = "Pranaya";
//Using the Variable
var student = context.Students
.FirstOrDefault(s => s.FirstName == FirstName);
Console.WriteLine($"FirstName: {student?.FirstName}, LastName: {student?.LastName}");
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
}
}
Run the above code, and you should get the following output. Please observe the generated SELECT SQL Statement; it uses the parameter @__FirstName_0.
LINQ ToList Method:
The ToList Method is used to create a System.Collections.Generic.List<T> collection. 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 EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
//Creating an Instance of Context class
using var context = new EFCoreDbContext();
//Fetching All the Students from Students table
var studentList = context.Students.ToList();
//Displaying all the Student information
foreach (var student in studentList)
{
Console.WriteLine($"FirstName: {student?.FirstName}, LastName: {student?.LastName}");
}
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
}
}
Note: Along with ToList, you can also use ToArray, ToDictionary, or ToLookup as per your business need.
LINQ OrderBy Method:
We use the OrderBy method 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 will be the same, but the order of the element will change.
In the following example, we sorted the students based on their First Names. Here, I am showing you how to use Order By using both the Method and Query syntax.
using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
//Creating an Instance of Context class
using var context = new EFCoreDbContext();
//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();
//Displaying the Records
foreach (var student in studentsQS)
{
Console.WriteLine($"\tFirstName: {student?.FirstName}, LastName: {student?.LastName}");
}
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
}
}
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 or all of an entity’s properties as a result.
The following example returns a list of anonymous objects which contain only the FirstName, LastName, and Height properties. The projection Result in the example below will be anonymous because no class/entity has these properties. So, the compiler will mark it as anonymous.
using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
//Creating an Instance of Context class
using var context = new EFCoreDbContext();
// Query Syntax to Project the Result to an Anonymous Type
var selectQuery = (from std in context.Students
select new
{
firstName = std.FirstName,
lastName = std.LastName,
height = std.Height
});
//Displaying the Record
foreach (var student in selectQuery)
{
Console.WriteLine($"FirstName: {student?.firstName}, LastName: {student?.lastName}, Height: {student?.height}");
}
//Method Syntax to Project the Result to an Anonymous Type
var selectMethod = context.Students.
Select(std => new
{
firstName = std.FirstName,
lastName = std.LastName,
height = std.Height
}).ToList();
//Displaying the Record
foreach (var student in selectMethod)
{
Console.WriteLine($"FirstName: {student?.firstName}, LastName: {student?.lastName}, Height: {student?.height}");
}
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
}
}
LINQ 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 Students and Standards, and when you perform the LINQ inner join, only the matching records, i.e., the records in both Students and Standards tables, are included in the result set. For a better understanding, please have a look at the following example.
using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
//Creating an Instance of Context class
using var context = new EFCoreDbContext();
//Join using Method Syntax
var JoinUsingMS = context.Students //Outer Data Source
.Join(
context.Standards, //Inner Data Source
student => student.Standard.StandardId, //Inner Key Selector
standard => standard.StandardId, //Outer Key selector
(student, standard) => new //Projecting the data into an anonymous type
{
StudentName = student.FirstName + " " + student.LastName,
StandrdId = standard.StandardId,
StandardDescriptin = standard.Description,
StudentHeight = student.Height
}).ToList();
foreach (var student in JoinUsingMS)
{
Console.WriteLine($"Name: {student?.StudentName}, StandrdId: {student?.StandrdId}, Height: {student?.StudentHeight}");
}
//Join using Query Syntax
var JoinUsingQS = (from student in context.Students
join standard in context.Standards
on student.Standard.StandardId equals standard.StandardId
select new
{
StudentName = student.FirstName + " " + student.LastName,
StandrdId = standard.StandardId,
StudentHeight = student.Height
}).ToList();
foreach (var student in JoinUsingQS)
{
Console.WriteLine($"Name: {student?.StudentName}, StandrdId: {student?.StandrdId}, Height: {student?.StudentHeight}");
}
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
}
}
Note: You need to keep in mind that there are no differences in the generated SQL whether you use Method syntax or Query Syntax using LINQ to Entities query.
Entity Framework Core New Features for Querying:
Now, let us learn the new features of querying introduced in Entity Framework Core.
C#/VB.NET Functions in Queries
Entity Framework Core has a new feature in LINQ-to-Entities where we can include C# or VB.NET functions in the query. This was not possible in EF 6. For a better understanding, please have a look at the following example.
using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
//Creating an Instance of Context class
using var context = new EFCoreDbContext();
var studentsWithSameName = context.Students
.Where(s => s.FirstName == GetName())
.ToList();
foreach (var student in studentsWithSameName)
{
Console.WriteLine($"FirstName: {student?.FirstName}, LastName: {student?.LastName}, StandardId: {student?.Standard?.StandardId}");
}
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
public static string GetName()
{
return "Pranaya";
}
}
}
In the above example, we have included the GetName() C# function in the Where clause. Run the above code, and you should get the following output.
In the next article, I am going to discuss Eager Loading in Entity Framework Core. In this article, I try to explain LINQ to Entities in Entity Framework Core (EF Core). I hope you enjoy this LINQ to Entities in EF Core article.
