Efficient Data Manipulation in ASP.NET Core with LINQ

Building a web application can be challenging, especially for beginners. To simplify the common tasks of accessing and manipulating data in this type of application, developers can use resources such as LINQ, which has functions for efficient data manipulation.

In this post, we will cover the process of creating a simple application using ASP.NET Core that leverages the power of LINQ to manipulate data simply and effectively. By the end of this tutorial, you will have a solid understanding of how to build a simple yet functional web application using the best of LINQ.

What Is LINQ?

LINQ, short for Language Integrated Query, is a language feature in C# that allows developers to write queries directly in the language's syntax. In the context of ASP.NET Core, LINQ has become an invaluable tool for working with data collections.

LINQ provides a consistent model for querying and manipulating data, regardless of the data source. It allows developers to write queries using familiar syntax, making code more expressive and readable. LINQ is not limited to working with databases—it can be used with various data sources such as arrays, collections, XML and more.

In ASP.NET Core, LINQ is commonly used to query databases, especially with Entity Framework Core, a popular object-relational mapping (ORM) framework.

EF Core allows developers to interact with databases using C# objects, and LINQ provides a natural and efficient way to query and transform this data. Another advantage of LINQ is that it is part of the native ASP.NET Core namespace (System.Linq). This means that all its resources are available without the use of third-party libraries, receiving updates with each new version of .NET.

The image below demonstrates how it is possible to optimize the C# syntax using the LINQ approach instead of the traditional approach.

Practicing LINQ in an ASP.NET Core Application

To practice using LINQ, we will create a simple application using SQLite as a database and EF Core as an ORM to register students. Then we will see the main LINQ resources available for data manipulation.

To create the example application, you need to have the latest version of .NET. This post uses Version 8. For an IDE, in this post uses Visual Studio Code. It is also necessary to have EF Core installed locally; to install it, just run the command dotnet tool install --global dotnet-ef in the terminal.

Open a terminal and execute the following commands to create the app and install the NuGet packages.

dotnet new web -o StudentHub

cd StudentHub

dotnet add package Microsoft.EntityFrameworkCore.Sqlite

dotnet add package Microsoft.EntityFrameworkCore.Design

dotnet add package Swashbuckle.AspNetCore

Now let's create classes to represent the student, course and enrollment entities. Create a new folder called "Models," and within it create the classes below:

Student

namespace StudentHub.Models;

public class Student
{
  public Guid Id { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
  public DateTime DateOfBirth { get; set; }
  public string Address { get; set; }
  public string PhoneNumber { get; set; }
  public string Email { get; set; }
  public string Gender { get; set; }
  public int Age { get; set; }
  public List<string> Courses { get; set; }
  public double Score { get; set; }
  
  public Student()
  {
  }
}

Course

namespace StudentHub.Models;

public class Course
{
  public Guid CourseId { get; set; }
  public Guid CourseName { get; set; }
}

Enrollment

namespace StudentHub.Models;

public class Enrollment
{
  public Guid EnrollmentId { get; set; }
  public Guid StudentId { get; set; }
  public Guid CourseId { get; set; }
}

The next step is to create the context class that will implement the connection to the database and instantiate the list of student records. So, create a folder called "Data" and inside it create the class below:

StudentDbContext

using Microsoft.EntityFrameworkCore;
using StudentHub.Models;

namespace StudentHub.Data;

public class StudentDbContext : DbContext
{
  public DbSet<Student> Students { get; set; }
  public DbSet<Course> Courses { get; set; }
  public DbSet<Enrollment> Enrollments { get; set; }

  public StudentDbContext(DbContextOptions<StudentDbContext> options)
    : base(options)
  {
  }
}

LINQ Standard Query Operators

LINQ Standard Query Operators are methods and expressions that allow you to perform query operations on data collections. These operators are used to filter, sort, group and project data in LINQ queries. They are an integral part of the C# language and other .NET languages that support LINQ.

Standard query operators are divided into several categories, each performing a specific operation on a sequence (collection) of elements.

Next, we will get to know each of the patterns and check a practical example of each of them. Don't worry about the project for now—at the end of the explanation we will implement everything into the example code.

1. Filtering

To filter elements using LINQ, we use the "Where" extension method. Example:

public IEnumerable<Student> GetMaleStudents()
{
  var result = from student in _db.Students
    where student.Gender == "Male"
    select student;
  
  return result;
}

Note that in this code the variable "result" is created to store the result of the LINQ query from student in _db.Students where student.Gender == "Male", which filters students to include only those whose Gender attribute is equal to " Male". Then the code select student is declared, which indicates that the query must return the student object for each item that meets the specified criteria.

2. Projection

Projection refers to the ability to transform or select specific properties of objects in a sequence. In LINQ, projection is mainly performed using the Select operator. The goal is to create a new form of data containing only the information needed for the task.

The Select operator allows you to specify the projection of data from a collection to a new composition.

public IEnumerable<string> GetStudentFullNames()
{
  var result = from student in _db.Students
select $"{student.FirstName} {student.LastName}";

  return result;
}

The code above creates a sequence of strings containing the students' full names, selecting only the FirstName and LastName properties through the LINQ select operator.

3. Ordering

Element ordering in LINQ is done using the OrderBy and OrderByDescending operators, which classify the elements of a sequence based on specific criteria. Both operators are used in conjunction with the select clause to perform the projection of the ordered elements.

OrderBy:

The OrderBy operator is used to sort the elements of a sequence in ascending order based on a specified key. The key is usually a property of the object or an expression that returns a value that will be used to determine the order.

public IEnumerable<Student> GetStudentsOrderedByName()
{
  var result = _db.Students.OrderBy(student => student.FirstName);

  return result;
}

In this example, OrderBy(student => student.FirstName) sorts the students in ascending order. In this case, it will sort the students alphabetically from A to Z.

OrderByDescending:

The OrderByDescending operator works similarly to OrderBy but sorts elements in descending order.

public IEnumerable<Student> GetStudentsByDescendingName()
{
  var result = _db.Students.OrderByDescending(student => student.FirstName);

  return result;
}

In this example, OrderByDescending(student => student.FirstName) sorts numbers in descending order.

ThenBy:

The ThenBy method is used in LINQ to perform a secondary sort on an already sorted sequence. In other words, you can use ThenBy to specify a second ordering criterion when the elements have the same value as the first criterion.

public IEnumerable<Student> GetStudentsOrderedByNameAndThenByAge()
{
  var result = _db.Students.OrderBy(student => student.FirstName).ThenBy(student => student.Age);

  return result;
}

In this example, students are first ordered by last name (OrderBy(student => student.FirstName)), and then, if two students have the same name, they will be ordered by age (ThenBy(student => student.Age)).

4. Grouping

The grouping operator in LINQ is represented by the GroupBy method. It is used to group elements of a sequence based on a specific key. The grouping operation creates groups of elements that share the same key. Example:

public IEnumerable<IGrouping<int, Student>> GroupStudentsByAge()
{
  var result = _db.Students.GroupBy(student => student.Age);

  return result;
}

In the example above, the GroupBy operator is used to group students based on their age. It takes a lambda expression that specifies the grouping key, in this case student => student.Age.

The GroupStudentsByAge method, when called, will return a sequence of groups, where each group represents an age range and contains a collection of students with that age.

GroupJoin:

Performs a join between two sequences and groups the results.

public IEnumerable<IGrouping<string, Student>> GroupStudentsByCourses()
{
  return _db.Students
    .GroupJoin(
      _db.Enrollments,
      student => student.Id,
      enrollment => enrollment.StudentId,
      (student, enrollments) => new { student, enrollments }
    )
    .SelectMany(
      x => x.enrollments.DefaultIfEmpty(),
      (x, enrollment) => new { Student = x.student, Enrollment = enrollment }
    )
    .GroupBy(x => x.Enrollment.CourseId.ToString(), x => x.Student);
}

ToLookup: Converts a string to a Lookup object.

public ILookup<Guid, Student> ToLookupByStudentId()
{
  return _db.Students.ToLookup(student => student.Id);
}

5. Junction

The join operation in LINQ is performed using the join operator. It allows you to combine elements from two different sequences based on a specified condition, producing a new sequence of corresponding elements. Example:

public IEnumerable<string> GetStudentCourseNames()
{
  var result = from student in _db.Students
    join enrollment in _db.Enrollments on student.Id equals enrollment.StudentId
    join course in _db.Courses on enrollment.CourseId equals course.CourseId
    select $"{student.FirstName} {student.LastName} - {course.CourseName}";
  return result;
}

The GetStudentCourseNames method uses the join operation between three tables: Students, Enrollments and Courses. The objective is to obtain a sequence of strings that represent the names of students and the names of the courses in which they are enrolled. This is an effective way to get related data from multiple tables in a single LINQ query.

6. Aggregation

In LINQ, aggregation refers to the application of operations that combine elements of a sequence into a single value. Aggregation operators are used to perform calculations on a sequence, such as adding, counting, finding the minimum or maximum, calculating the average, etc. Some of the most common aggregation operators in LINQ include Count, Sum, Min, Max and Average.

Example:

var numbers = new List<int> { 1, 2, 3, 4, 5 };

var sum = numbers.Sum(); // 1 + 2 + 3 + 4 + 5 = 15
var count = numbers.Count(); // Number of elements = 5
var minimum = numbers.Min(); // Minimum value = 1
var maximum = numbers.Max(); // Maximum value = 5
var average = numbers.Average(); // Average = (1 + 2 + 3 + 4 + 5) / 5 = 3

7. Partitioning

The "Partitioning" category in LINQ refers to operators that allow you to divide a sequence into smaller partitions. The main operator in this category is Skip and Take.

Take:

Returns a specified number of elements from the beginning of a sequence.

Example:

public IEnumerable<Student> GetFirstTwoStudents()
{
  var result = _db.Students.Take(2);

  return result;
}

Skip:

Skips a specified number of elements at the beginning of a sequence and returns the remaining elements.

Example:

public IEnumerable<Student> GetStudentsAfterSkippingFirstTwo()
{
  var result = _db.Students.Skip(2);

  return result;
}

8. Additional Filtering Operators

There are additional filtering operators such as OfType and Distinct.

OfType:

Filters the elements of a sequence to include only those of a certain type.

public IEnumerable<Student> FilterStudentsByType()
{
  return _db.Students.OfType<Student>();
}

Distinct:

Returns distinct elements from a sequence.

public IEnumerable<string> GetDistinctCourses()
{
  return _db.Students
    .AsEnumerable()
    .SelectMany(student => student.Courses)
    .Distinct();
}

9. Set Operators

Set Operators in LINQ perform operations between sets of elements, such as union, intersection and difference. They are particularly useful when working with two or more sequences and want to perform operations that involve combining or comparing elements between them.

Union:

Returns the union of two sequences.

public IEnumerable<string> UnionStudentNames(IEnumerable<Student> firstList, IEnumerable<Student> secondList)
{
  var unionList = firstList.Union(secondList);
  return unionList.Select(student => $"{student.FirstName} {student.LastName}");
}

Intersect:

Returns the intersection of two sequences.

public IEnumerable<string> IntersectStudentNames(IEnumerable<Student> firstList, IEnumerable<Student> secondList)
{
  var intersectList = firstList.Intersect(secondList);
  return intersectList.Select(student => $"{student.FirstName} {student.LastName}");
}

Except:

Returns elements that are in the first sequence but not in the second.

public IEnumerable<string> ExceptStudentNames(IEnumerable<Student> firstList, IEnumerable<Student> secondList)
{
  var exceptList = firstList.Except(secondList);
  return exceptList.Select(student => $"{student.FirstName} {student.LastName}");
}

10. Quantification Operators

Quantification operators in LINQ are used to evaluate conditions concerning all or part of the elements in a sequence. There are two main operators in this category: All and Any.

All:

Checks whether all elements meet a condition.

public bool CheckIfAllStudentsPassed()
{
  return _db.Students.All(student => student.Score >= 60);
}

Any:

Checks whether at least one element meets a condition.

public bool CheckIfAnyStudentFailed()
{
  return _db.Students.Any(student => student.Score < 60);
}

Continuing the Example Application

These were the main LINQ Standard Query Operators. Now that we have seen an example of each one, let's implement them in the application and create endpoints to access them.

Create a new folder called "Services" and inside it create the class below:

StudentService

using StudentHub.Data;
using StudentHub.Models;

namespace StudentHub.Services;
public class StudentService
{
  private readonly StudentDbContext _db;

  public StudentService(StudentDbContext db)
  {
    _db = db;
  }

  public async Task CreateStudent(Student student)
  {
    _db.Students.Add(student);
    await _db.SaveChangesAsync();
  }

  // Filtering
  public IEnumerable<Student> GetMaleStudents()
  {
    var result = from student in _db.Students
      where student.Gender == "Male"
      select student;

    return result;
  }

  public IEnumerable<Student> GetFemaleStudents()
  {
    var result = from student in _db.Students
      where student.Gender == "Female"
      select student;
   
    return result;
  }

  public IEnumerable<Student> GetOthersStudents()
  {
    var result = from student in _db.Students
      where student.Gender == "Others"
      select student;

    return result;
  }
  
  // OfType
  public IEnumerable<Student> GetMaleStudentsOnly()
  {
    var result = _db.Students.OfType<Student>().Where(s => s.Gender == "Male");

    return result;
  }

  public IEnumerable<Student> GetFemaleStudentsOnly()
  {
    var result = _db.Students.OfType<Student>().Where(s => s.Gender == "Female");

    return result;
  }

  public IEnumerable<Student> GetOthersStudentsOnly()
  {
    var result = _db.Students.OfType<Student>().Where(s => s.Gender == "Others");

    return result;
  }

  // Projection
  public IEnumerable<string> GetStudentFullNames()
  {
    var result = from student in _db.Students
      select $"{student.FirstName} {student.LastName}";

    return result;
  }

  // SelectMany
  public IEnumerable<string> GetCoursesForAllStudents()
  {
    var result = _db.Students
      .AsEnumerable()
      .SelectMany(student => student.Courses)
      .ToList();

    return result;
  }

  // Partitioning Take
  public IEnumerable<Student> GetFirstTwoStudents()
  {
    var result = _db.Students.Take(2);

    return result;
  }

  // Partitioning Skip
  public IEnumerable<Student> GetStudentsAfterSkippingFirstTwo()
  {
    var result = _db.Students.Skip(2);

    return result;
  }

  // Ordering OrderBy
  public IEnumerable<Student> GetStudentsOrderedByName()
  {
    var result = _db.Students.OrderBy(student => student.FirstName);

    return result;
  }

  // Ordering ByDescending
  public IEnumerable<Student> GetStudentsByDescendingName()
  {
    var result = _db.Students.OrderByDescending(student => student.FirstName);

    return result;
  }

  // ThenBy
  public IEnumerable<Student> GetStudentsOrderedByNameAndThenByAge()
  {
    var result = _db.Students.OrderBy(student => student.FirstName).ThenBy(student => student.Age);

    return result;
  }

  // Reverse
  public IEnumerable<Student> GetStudentsReversed()
  {
    var result = _db.Students
      .AsEnumerable()
      .Reverse();

    return result;
  }

  // Grouping
  public IEnumerable<IGrouping<int, Student>> GroupStudentsByAge()
  {
    var result = _db.Students.GroupBy(student => student.Age);

    return result;
  }

  public IEnumerable<IGrouping<string, Student>> GroupStudentsByCourses()
  {
    return _db.Students
      .GroupJoin(
        _db.Enrollments,
        student => student.Id,
        enrollment => enrollment.StudentId,
        (student, enrollments) => new { student, enrollments }
      )
      .SelectMany(
        x => x.enrollments.DefaultIfEmpty(),
        (x, enrollment) => new { Student = x.student, Enrollment = enrollment }
      )
      .GroupBy(x => x.Enrollment.CourseId.ToString(), x => x.Student);
  }

  public ILookup<Guid, Student> ToLookupByStudentId()
  {
    return _db.Students.ToLookup(student => student.Id);
  }

  //Join
  public IEnumerable<string> GetStudentCourseNames()
  {
    var result = from student in _db.Students
      join enrollment in _db.Enrollments on student.Id equals enrollment.StudentId
      join course in _db.Courses on enrollment.CourseId equals course.CourseId
      select $"{student.FirstName} {student.LastName} - {course.CourseName}";

    return result;
  }

  // Conversion
  public Student[] ConvertToStudentArray()
  {
    var result = _db.Students.ToArray();

    return result;
  }

  // Element
  public Student GetFirstStudent()
  {
    var result = _db.Students.First();

    return result;
  }

  // Aggregation
  public double GetAverageScore()
  {
    var result = _db.Students.Average(student => student.Score);

    return result;
  }

  // Filtering aditional Operators
  public IEnumerable<Student> FilterStudentsByType()
  {
    return _db.Students.OfType<Student>();
  }

  public IEnumerable<string> GetDistinctCourses()
  {
    return _db.Students
      .AsEnumerable()
      .SelectMany(student => student.Courses).Distinct();
  }

  // Set Operators
  public IEnumerable<string> UnionStudentNames(IEnumerable<Student> firstList, IEnumerable<Student> secondList)
  {
    var unionList = firstList.Union(secondList);
    return unionList.Select(student => $"{student.FirstName} {student.LastName}");
  }

  public IEnumerable<string> IntersectStudentNames(IEnumerable<Student> firstList, IEnumerable<Student> secondList)
  {
    var intersectList = firstList.Intersect(secondList);
    return intersectList.Select(student => $"{student.FirstName} {student.LastName}");
  }

  public IEnumerable<string> ExceptStudentNames(IEnumerable<Student> firstList, IEnumerable<Student> secondList)
  {
    var exceptList = firstList.Except(secondList);
    return exceptList.Select(student => $"{student.FirstName} {student.LastName}");
  }

  // Quantification Operators
  public bool CheckIfAllStudentsPassed()
  {
    return _db.Students.All(student => student.Score >= 60);
  }

  public bool CheckIfAnyStudentFailed()
  {
    return _db.Students.Any(student => student.Score < 60);
  }
}

The code above contains all the methods discussed previously.

Now let's create the endpoints that will call these methods, in addition to the dependency injection configurations. To do this, replace the existing code in the Program.cs file with the code below:

using Microsoft.EntityFrameworkCore;
using Microsoft.OpenApi.Models;
using StudentHub.Data;
using StudentHub.Models;
using StudentHub.Services;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<StudentDbContext>(options =>
{
  options.UseSqlite("Data Source=students_db.db");
});
builder.Services.AddTransient<StudentService>();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen(c =>
{
  c.SwaggerDoc("v1", new OpenApiInfo { Title = "StudentHub", Version = "v1" });
});

var app = builder.Build();

app.UseSwagger();
app.UseSwaggerUI(c =>
{
  c.SwaggerEndpoint("/swagger/v1/swagger.json", "StudentHub API V1");
  c.RoutePrefix = string.Empty;
});

// Map API endpoints
app.MapPost("/api/students", async (Student student, StudentService studentService) =>
{
  await studentService.CreateStudent(student);
  return Results.Ok();
});

app.MapGet("/api/students/male", (StudentService studentService) => studentService.GetMaleStudents());

app.MapGet("/api/students/female", (StudentService studentService) => studentService.GetFemaleStudents());

app.MapGet("/api/students/others", (StudentService studentService) => studentService.GetOthersStudents());

app.MapGet("/api/students/male-only", (StudentService studentService) => studentService.GetMaleStudentsOnly());

app.MapGet("/api/students/female-only", (StudentService studentService) => studentService.GetFemaleStudentsOnly());

app.MapGet("/api/students/others-only", (StudentService studentService) => studentService.GetOthersStudentsOnly());

app.MapGet("/api/students/full-names", (StudentService studentService) => studentService.GetStudentFullNames());

app.MapGet("/api/students/courses", (StudentService studentService) => studentService.GetCoursesForAllStudents());

app.MapGet("/api/students/first-two", (StudentService studentService) => studentService.GetFirstTwoStudents());

app.MapGet("/api/students/ordered-by-name", (StudentService studentService) => studentService.GetStudentsOrderedByName());

app.MapGet("/api/students/ordered-by-name-age", (StudentService studentService) => studentService.GetStudentsOrderedByNameAndThenByAge());

app.MapGet("/api/students/reversed", (StudentService studentService) => studentService.GetStudentsReversed());

app.MapGet("/api/students/grouped-by-age", (StudentService studentService) => studentService.GroupStudentsByAge());

app.MapGet("/api/students/to-array", (StudentService studentService) => studentService.ConvertToStudentArray());

app.MapGet("/api/students/first", (StudentService studentService) => studentService.GetFirstStudent());

app.MapGet("/api/students/average-score", (StudentService studentService) => studentService.GetAverageScore());

app.MapGet("/api/students/filterByType", (StudentService studentService) => studentService.FilterStudentsByType());

app.MapGet("/api/students/distinctCourses", (StudentService studentService) => studentService.GetDistinctCourses());

app.MapGet("/api/students/unionNames", (StudentService studentService, IEnumerable<Student> firstList, IEnumerable<Student> secondList) => studentService.UnionStudentNames(firstList, secondList));

app.MapGet("/api/students/intersectNames", (StudentService studentService, IEnumerable<Student> firstList, IEnumerable<Student> secondList) => studentService.IntersectStudentNames(firstList, secondList));

app.MapGet("/api/students/exceptNames", (StudentService studentService, IEnumerable<Student> firstList, IEnumerable<Student> secondList) => studentService.ExceptStudentNames(firstList, secondList));

app.MapGet("/api/students/allPassed", (StudentService studentService) => studentService.CheckIfAllStudentsPassed());

app.MapGet("/api/students/anyFailed", (StudentService studentService) => studentService.CheckIfAnyStudentFailed());

app.MapGet("/api/students/groupByCourses", (StudentService studentService) => studentService.GroupStudentsByCourses());

app.MapGet("/api/students/toLookupByStudentId", (StudentService studentService) => studentService.ToLookupByStudentId());

app.MapGet("/api/students/studentCourseNames", (StudentService studentService) => studentService.GetStudentCourseNames());

app.MapGet("/api/students/descendingNames", (StudentService studentService) => studentService.GetStudentsByDescendingName());

app.MapGet("/api/students/skipFirstTwo", (StudentService studentService) => studentService.GetStudentsAfterSkippingFirstTwo());

app.Run();

Our application is almost ready. We still need to execute the EF Core migration commands to generate the database and tables. Open a terminal within the application and execute the following commands.

dotnet ef migrations add InitialCreate
dotnet ef database update

Finally, we are ready to test the LINQ functions. To do this, simply run the application with the command dotnet run and access the Swagger interface in the browser: http://localhost:PORT/index.html.

Then we can perform some operations as shown in the GIF below:

Considerations When Using LINQ

LINQ is a powerful extension of the C# language that allows integrated queries on collections of data and is often used for database queries together with the Entity Framework. Although LINQ offers many advantages, there are also some disadvantages to consider:

Performance:

In some situations, LINQ queries may result in less optimized SQL queries than manual SQL queries written by an experienced developer. This can lead to lower performance on complex or heavy queries.

Complexity of Understanding:

For less experienced developers, LINQ syntax can be more complex to understand than writing traditional SQL queries. Depending on the scenario, LINQ queries can increase the learning curve and make code maintenance difficult.

Expressivity Limitations:

Although LINQ is quite expressive, there may be cases where the LINQ syntax is not flexible enough to express complex or specific queries. In such situations, developers may need to resort to traditional SQL queries.

Difficulty in Debugging:

Debugging LINQ queries can be more challenging than debugging traditional C# code or SQL queries. Viewing and inspecting LINQ queries while debugging may not be as intuitive as examining SQL queries directly.

Memory Performance Limitations:

In some cases, especially when dealing with large in-memory data sets, using LINQ can result in excessive resource consumption and affect application performance.

Compatibility with Specific Databases:

Some specific database functionalities may not be fully supported by LINQ. In some cases, it may be necessary to resort to direct SQL queries.

Conclusion

It is important to highlight that, despite these disadvantages, LINQ offers many advantages, such as better code readability, greater productivity and the possibility of writing queries more securely. The choice between using direct LINQ and SQL queries will depend on the specific needs of the project and the preferences of the development team.

In this post, we learned the main LINQ query methods with simple and more complex examples. Despite the considerations above, always consider using LINQ to increase your productivity when working with a database.