Querying in Entity Framework Core (EF Core) - Sorting, Filtering and Grouping

 Entity Framework Core uses Language-Integrated Query (LINQ) to query the database. You can use C# or any .NET language of your choice to write LINQ queries. The queries you write in LINQ are passed to the database provider. The database provider translates it to the actual SQL, which will be executed against the database. LINQ queries can be written using Query syntax or method syntax. Many people who know SQL find query syntax easier to learn as both share a resemblance.

Here is the structure of the entity  I use for the demo purpose. I use SQL Server to run the queries. 

Customer Entity

    public class Customer
    {    
       public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public string Gender{ get; set; }
      
    }

 

Retrieving all Records/Objects

The following example uses method syntax to get all records from the Customer table. The ToList method returns a collection of Customer Objects.

 var Customers = context.Customers.ToList();

Query Syntax for the above query is the following 

 var Customers = from T in context.Customers
                            select T;

The above queries generate the following SQL

SELECT [c].[Id], [c].[Email], [c].[FirstName], [c].[Gender], [c].[LastName]
FROM [Customers] AS [c]

You can iterate through the Customers' collection using foreach loop 

foreach(var customer in Customers)
      {
      }

 

Filtering 

We use the Where clause in SQL to filter records. The following example shows how customer records can be filtered based on the FirstName field value.

Method Syntax

 var Customers = context.Customers.Where(m => m.FirstName == "Melvin").ToList();

Query Syntax 

var Customers = from T in context.Customers
                            where T.FirstName =="melvin"
                            select T;

Generated SQL

SELECT [c].[Id], [c].[Email], [c].[FirstName], [c].[Gender], [c].[LastName]
FROM [Customers] AS [c]
WHERE [c].[FirstName] = N'melvin'

Multiple fields can be added to the where condition as follows. 

 var Customers = context.Customers.Where(m => m.FirstName=="melvin" && m.LastName=="zacharias").ToList();

 

Sorting

The records can be sorted based on one or more fields. In the following example, I sort it based on the Id field

Method Syntax

  var Customers = context.Customers.Where(m => m.FirstName == "Tom").OrderBy(m=>m.Id);

Query Syntax 

 var Customers = from T in context.Customers
                            where T.FirstName == "tom"
                            orderby T.Id
                            select T;

Generated SQL

SELECT [c].[Id], [c].[Email], [c].[FirstName], [c].[Gender], [c].[LastName]
FROM [Customers] AS [c]
WHERE [c].[FirstName] = N'tom'
ORDER BY [c].[Id]

Records can be sorted based on multiple fields as follows. 

 var Customers = context.Customers.OrderBy(m => m.FirstName).ThenByDescending(m => m.Id).ToList();

 

Grouping 

The GroupBy method can be used to group the records. The following query groups Customers based on the FirstName field.

Method Syntax

  var CustomerGroup= context.Customers.GroupBy(m => m.Gender);

Query Syntax 

var CustomerGroup = from T in context.Customers
                            group T by T.Gender;

Generated SQL

SELECT [c].[Id], [c].[Email], [c].[FirstName], [c].[Gender], [c].[LastName]
FROM [Customers] AS [c]

As you can see the generated SQL  doesn't contain Group By clause. This means that grouping is done by EF Core after retrieving the records from the database.

The result is a collection of objects that implements IGrouping interface having a common key. The key field is the property used for grouping. You can loop through the collection of objects under each group as follows. 

  foreach (var group in CustomerGroup)
        {        //the key is Gender field value  
            foreach (var customer in group)
            { 
               //You can loop through items in the particular group here
            }
        }

If you use multiple keys for grouping you have to use anonymous type to represent the keys.

 var CustomerGroup = context.Customers.ToList().GroupBy(m => new {Gender=m.Gender,Name=m.FirstName });

You can loop through the collection as follows

  foreach (var group in CustomerGroup)
        {    
           // group.Key.Gender
            //group.Key.Name
        }

Consider the following example which groups records based on gender and gives the number of records under each group

 var CustomerGroup = context.Customers.GroupBy(m =>m.Gender).Select(g=> new {GroupId=g.Key,Count=g.Count() }).ToList();

The above code generates the following SQL. As you can see the SQL contains Group By clause.

SELECT [c].[Gender] AS [GroupId], COUNT(*) AS [Count]
FROM [Customers] AS [c]
GROUP BY [c].[Gender]

 


Search