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]