LINQ to Entities in Entity Framework Core

LINQ to Entities in Entity Framework Core

In this article, I will discuss LINQ to Entities in Entity Framework Core (EF Core). Please read our previous article discussing 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:

LINQ (Language Integrated Query) is a powerful feature in .NET that allows developers to write queries directly using C# (or VB.NET) language. When working with Entity Framework Core (EF Core), we can use LINQ to query our entities in a way that feels like we are working with in-memory objects. This approach is called “LINQ to Entities”.

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.

LINQ to Entities Queries in Entity Framework Core

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, 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. Microsoft introduced LINQ with .NET Framework 3.5 and C# 3.0, available in the System.Linq namespace.

LINQ provides a Common Query Syntax that allows us to query the data from various sources. 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 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, 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 10,000, 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.

  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: Besides 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 Core:

The Find method belongs to the DbSet class. This method finds an entity with the given primary key values. Suppose an entity with the given primary key values exists in the context object. In that case, it is returned immediately without requesting the database, i.e., EF Core implements the first-level caching. If the value does not exist in the context object, a request is made to the database to fetch the entity with the given primary key values, and this entity, if found, is attached to the context and returned. Null is returned if no entity is found in the context object or the database.

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 ID 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();

                //It will return the data from the database by executing SELECT SQL Statement
                var student = context.Students.Find(1);
                Console.WriteLine($"FirstName: {student?.FirstName}, LastName: {student?.LastName}");
                
                //It will return the data from the context object as the context object tracking the same data
                var student2 = context.Students.Find(1);
                Console.WriteLine($"FirstName: {student2?.FirstName}, LastName: {student2?.LastName}");

                //It will return the data from the database by executing SELECT SQL Statement
                var student3 = context.Students.Find(2);
                Console.WriteLine($"FirstName: {student3?.FirstName}, LastName: {student3?.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 FirstName 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:

First and FirstOrDefault methods in LINQ 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, it will not throw an exception; rather, it will return the default value of the Student type, i.e., null.

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.

Parameterized Query in Entity Framework Core

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 per your business needs. 

Note: Deferred execution is a key feature of LINQ. The query is executed only when iterating over the query results. Methods like ToList(), ToArray(), Count(), and First() cause the query to execute immediately.

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 a process to manage the data in a particular order. It does not change the data or output. Rather, this operation arranges the data in a particular order, i.e., ascending or descending. In this case, the count will be the same, but the order of the elements 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 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.

The following example returns a list of anonymous objects that 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 an anonymous type.

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 returns 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 remember 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:

Let us learn the new querying features 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.

LINQ to Entities in Entity Framework Core

Note: LINQ to Entities in EF Core provides a convenient, type-safe way to create database queries. EF Core then translates the queries into corresponding SQL queries based on the EF Core Database Provider and backend database. However, it’s important to consider performance issues like the N+1 query problem and optimize complex queries by examining the generated SQL.

In the next article, I will 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.

Leave a Reply

Your email address will not be published. Required fields are marked *