Using LINQ for Advanced Data Queries in .NET Core

Introduction

Language Integrated Query (LINQ) is a powerful feature in .NET that allows developers to write queries directly in C# to access various data sources like collections, databases, XML, and more. LINQ provides a unified way to query data using query syntax or method syntax while ensuring type safety, which significantly reduces bugs and makes code more readable.

In this blog, we’ll walk through how to use LINQ for advanced data queries in .NET Core, explaining each step with detailed code examples and explanations.

---

Table of Contents

1. What is LINQ?
2. Why Use LINQ?
3. Setting Up a .NET Core Project
4. Basic LINQ Query Syntax
5. Advanced LINQ Query Operations: Filtering with Where, Sorting with OrderBy and ThenBy, Grouping with GroupBy, Joining with Join, Aggregating with Sum, Count, Average
6. Real-World Example: LINQ with Collections
7. Conclusion

---

1. What is LINQ?

LINQ (Language Integrated Query) is a querying language built into the .NET framework. It offers a consistent model for working with data from different sources (like arrays, lists, databases, or XML), allowing developers to filter, project, and aggregate data with a simple, readable syntax.

---

2. Why Use LINQ?

• Consistency: LINQ provides a consistent way to query data regardless of the source (SQL, XML, in-memory collections).
• Readability: LINQ allows you to express complex data manipulation in a clean, declarative style.
• Power: LINQ allows you to do advanced operations, like joins, groupings, and aggregations, without writing complex loops or manual logic.

---

3. Setting Up a .NET Core Project

Before we dive into code examples, let’s create a simple .NET Core console application.

Step 1: Create a New .NET Core Console Project

1. Open Visual Studio or Visual Studio Code.
2. Create a new .NET Core Console Application:

  dotnet new console -n LINQAdvancedQueries
   cd LINQAdvancedQueries

3. Open the project in your IDE:

   code .

4. Add a simple data model that we will query using LINQ.

Sample Model (Employee Class)

We’ll start by creating a simple Employee class, which represents the data we’ll query using LINQ:

public class Employee
{
    public int EmployeeId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Department { get; set; }
    public decimal Salary { get; set; }
    public DateTime JoiningDate { get; set; }
}

Now that we have our model, we can populate it with sample data for our LINQ operations.

---

4. Basic LINQ Query Syntax

Basic Example: Query Syntax

LINQ provides two ways to write queries: query syntax (similar to SQL) and method syntax (using extension methods).

Let’s start with the query syntax example, where we filter employees based on their salary.

var employees = GetEmployees();

// LINQ query to filter employees with Salary > 50,000
var query = from e in employees
            where e.Salary > 50000
            select e;

foreach (var employee in query)
{
    Console.WriteLine($"{employee.FirstName} {employee.LastName} earns {employee.Salary}");
}

Explanation:

• from e in employees: Iterates through each employee in the employees collection.
• where e.Salary > 50000: Filters employees with a salary greater than 50,000.
• select e: Selects the employee that matches the condition.

---

5. Advanced LINQ Query Operations

Let’s now explore more advanced LINQ operations using method syntax.

A. Filtering Data with Where

The Where method allows you to filter data based on conditions. In the following example, we’ll filter employees whose salary is greater than 60,000:

var highEarners = employees.Where(e => e.Salary > 60000);

foreach (var employee in highEarners)
{
    Console.WriteLine($"{employee.FirstName} earns more than 60,000");
}

Explanation:

• Where(e => e.Salary > 60000): Filters employees whose salary is greater than 60,000.
• We iterate through the result using foreach and print out the employees’ first names.

B. Sorting Data with OrderBy and ThenBy

Sorting can be done easily with LINQ using OrderBy (for ascending order) and ThenBy (for secondary sorting).

var sortedEmployees = employees
    .OrderBy(e => e.Department)
    .ThenByDescending(e => e.Salary);

foreach (var employee in sortedEmployees)
{
    Console.WriteLine($"{employee.Department}: {employee.FirstName} {employee.LastName} - {employee.Salary}");
}

Explanation:

• OrderBy(e => e.Department): Sorts employees alphabetically by their department.
• ThenByDescending(e => e.Salary): Within each department, employees are sorted by salary in descending order.

C. Grouping Data with GroupBy

You can group data by any key using the GroupBy method. Here, we group employees by department.

var employeesByDepartment = employees.GroupBy(e => e.Department);

foreach (var group in employeesByDepartment)
{
    Console.WriteLine($"Department: {group.Key}");

    foreach (var employee in group)
    {
        Console.WriteLine($"- {employee.FirstName} {employee.LastName}");
    }
}

Explanation:

• GroupBy(e => e.Department): Groups employees by their department.
• group.Key: Refers to the department name.
• We then iterate over each group and print the employees within each department.

D. Joining Data with Join

LINQ allows you to join two collections based on a common key. Here, we join employees with department managers:

var departments = new List<(string Dept, string Manager)>
{
    ("HR", "Alice"),
    ("IT", "Bob"),
    ("Finance", "Charlie")
};

var employeeDepartment = employees
    .Join(departments, 
          e => e.Department, 
          d => d.Dept, 
          (e, d) => new 
          {
              EmployeeName = e.FirstName + " " + e.LastName,
              e.Department,
              Manager = d.Manager
          });

foreach (var item in employeeDepartment)
{
    Console.WriteLine($"{item.EmployeeName} works in {item.Department} (Manager: {item.Manager})");
}

Explanation:

• Join: Combines two collections (employees and departments) based on a common key (Department in employees and Dept in departments).
• (e, d): Represents the employee and department being joined.
• The result is a new anonymous object containing the employee’s name, department, and manager’s name.

E. Aggregating Data with Sum, Count, and Average

LINQ also supports aggregation methods like Sum, Count, and Average.

var totalSalary = employees.Sum(e => e.Salary);
Console.WriteLine($"Total Salary Expense: {totalSalary}");

var avgSalary = employees.Average(e => e.Salary);
Console.WriteLine($"Average Salary: {avgSalary}");

var employeeCount = employees.Count();
Console.WriteLine($"Total Employees: {employeeCount}");

Explanation:

• Sum(e => e.Salary): Sums up all employees’ salaries.
• Average(e => e.Salary): Calculates the average salary.
• Count(): Returns the total number of employees in the collection.

---

6. Real-World Example: LINQ with Collections

Let’s tie everything together with a practical example that applies various LINQ queries on a collection of employees.

using System;
using System.Collections.Generic;
using System.Linq;

public class Program
{
    public static void Main(string[] args)
    {
        var employees = GetEmployees();

        // 1. Filter: Employees with salary greater than 60,000
        var highEarners = employees.Where(e => e.Salary > 60000);
        Console.WriteLine("High Earners (Salary > 60,000):");
        foreach (var employee in highEarners)
        {
            Console.WriteLine($"{employee.FirstName} {employee.LastName} - {employee.Salary}");
        }

        // 2. Sort: Order employees by department and salary descending
        var sortedEmployees = employees
            .OrderBy(e => e.Department)
            .ThenByDescending(e => e.Salary);
        Console.WriteLine("\nSorted Employees by Department and Salary:");
        foreach (var employee in sortedEmployees)
        {
            Console.WriteLine($"{employee.Department}: {employee.FirstName}

 {employee.LastName} - {employee.Salary}");
        }

        // 3. Group: Group employees by department
        var groupedByDepartment = employees.GroupBy(e => e.Department);
        Console.WriteLine("\nGrouped by Department:");
        foreach (var group in groupedByDepartment)
        {
            Console.WriteLine($"Department: {group.Key}");
            foreach (var employee in group)
            {
                Console.WriteLine($"- {employee.FirstName} {employee.LastName}");
            }
        }

        // 4. Join: Employees and Managers
        var departments = new List<(string Dept, string Manager)>
        {
            ("HR", "Alice"),
            ("IT", "Bob"),
            ("Finance", "Charlie")
        };
        var employeeDepartment = employees.Join(departments,
            e => e.Department,
            d => d.Dept,
            (e, d) => new { e.FirstName, e.LastName, e.Department, d.Manager });
        Console.WriteLine("\nEmployees and their Managers:");
        foreach (var item in employeeDepartment)
        {
            Console.WriteLine($"{item.FirstName} {item.LastName} works in {item.Department} (Manager: {item.Manager})");
        }

        // 5. Aggregate: Total, Average, and Count
        Console.WriteLine($"\nTotal Salary Expense: {employees.Sum(e => e.Salary)}");
        Console.WriteLine($"Average Salary: {employees.Average(e => e.Salary)}");
        Console.WriteLine($"Total Employees: {employees.Count()}");
    }

    private static List<Employee> GetEmployees()
    {
        return new List<Employee>
        {
            new Employee { EmployeeId = 1, FirstName = "John", LastName = "Doe", Department = "IT", Salary = 75000, JoiningDate = DateTime.Parse("2020-01-15") },
            new Employee { EmployeeId = 2, FirstName = "Jane", LastName = "Smith", Department = "HR", Salary = 65000, JoiningDate = DateTime.Parse("2019-03-20") },
            new Employee { EmployeeId = 3, FirstName = "Sam", LastName = "Johnson", Department = "Finance", Salary = 80000, JoiningDate = DateTime.Parse("2018-05-25") },
            new Employee { EmployeeId = 4, FirstName = "Lisa", LastName = "Brown", Department = "IT", Salary = 70000, JoiningDate = DateTime.Parse("2021-09-01") }
        };
    }
}

public class Employee
{
    public int EmployeeId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Department { get; set; }
    public decimal Salary { get; set; }
    public DateTime JoiningDate { get; set; }
}

---

7. Conclusion

In this blog, we explored the power of LINQ and how to use it to perform advanced data queries in .NET Core. With LINQ, we can filter, sort, group, join, and aggregate data efficiently. The combination of query readability and advanced operations makes LINQ an invaluable tool for developers working with any type of data in .NET.

By mastering these LINQ techniques, you can simplify complex data queries and make your code more maintainable. Whether you’re working with databases, in-memory collections, or external data sources, LINQ offers a clean and unified approach to data querying.

---