Explicit Loading in ASP.NET Core Web API

Introduction

In ASP.NET Core Web API, managing database relationships effectively is crucial for optimizing performance. One of the approaches for handling related data is explicit loading, which allows you to retrieve related entities on demand instead of fetching them automatically.

What is Explicit Loading?

Explicit loading means manually retrieving related data from a database only when required. Unlike:

• Eager loading (Include()): loads related entities upfront in a single query.
• Lazy loading: automatically loads related data when accessed (requires proxies and additional configurations).

Explicit loading provides more control over database queries, which improves performance and reduces unnecessary data retrieval.

When to use explicit loading?

You should use explicit loading when:

• You don’t always need related data, preventing unnecessary database queries.
• You want to optimize API performance by retrieving only the required data.
• You need conditional data fetching based on business logic.
• Lazy loading is disabled or not recommended for performance reasons.
• You want to load large related datasets separately to avoid memory overhead.

Setting Up Explicit Loading in ASP.NET Core Web API

1. Prerequisites

To follow this tutorial, ensure you have:

• .NET Core SDK installed.
• ASP.NET Core Web API project set up.
• Entity Framework Core installed.

You can install Entity Framework Core using NuGet:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

2. Defining the Database Models

Consider an Author-Book relationship where:

• One Author can have multiple Books.
• Each Book belongs to only one Author.

Author Model

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    // Navigation property
    public List<Book> Books { get; set; } = new List<Book>();
}

Book Model

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int AuthorId { get; set; }

    // Navigation property
    public Author Author { get; set; }
}

3. Configuring DbContext

To use Entity Framework Core, define the DbContext:

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<Author> Authors { get; set; }
    public DbSet<Book> Books { get; set; }
}

Adding Database Connection

Add a connection string in:appsettings.json

"ConnectionStrings": {
  "DefaultConnection": "Server=(localdb)\\MSSQLLocalDB;Database=LibraryDb;Trusted_Connection=True;"
}

Registering DbContext in Program.cs

Modify Program.cs (for .NET 6+):

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

var app = builder.Build();
app.Run();

Implementing Explicit Loading in ASP.NET Core Web API

Explicit loading is done using the Entry().Collection().LoadAsync() or Entry().Reference().LoadAsync() methods.

1. Loading a Collection Property

To explicitly load all Books related to an Author, use:

[HttpGet("{id}")]
public async Task<IActionResult> GetAuthorWithBooks(int id)
{
    var author = await _context.Authors.FindAsync(id);

    if (author == null)
    {
        return NotFound();
    }

    // Explicitly load related books
    await _context.Entry(author).Collection(a => a.Books).LoadAsync();

    return Ok(author);
}

Explanation

• _context.Authors.FindAsync(id) retrieves the author without loading books.
• _context.Entry(author).Collection(a => a.Books).LoadAsync(); explicitly loads the Books collection.

2. Loading a Reference Property

To explicitly load an Author for a given Book, use:

[HttpGet("book/{id}")]
public async Task<IActionResult> GetBookWithAuthor(int id)
{
    var book = await _context.Books.FindAsync(id);

    if (book == null)
    {
        return NotFound();
    }

    // Explicitly load the related author
    await _context.Entry(book).Reference(b => b.Author).LoadAsync();

    return Ok(book);
}

Explanation

• _context.Books.FindAsync(id) retrieves the book without its author.
• _context.Entry(book).Reference(b => b.Author).LoadAsync(); explicitly loads the Author reference.

3. Conditional Explicit Loading

You can conditionally load related data only when needed:

[HttpGet("author/{id}")]
public async Task<IActionResult> GetAuthorConditionalLoading(int id, [FromQuery] bool includeBooks)
{
    var author = await _context.Authors.FindAsync(id);

    if (author == null)
    {
        return NotFound();
    }

    // Load books only if requested
    if (includeBooks)
    {
        await _context.Entry(author).Collection(a => a.Books).LoadAsync();
    }

    return Ok(author);
}

When to Choose Explicit Loading Over Other Approaches?

Performance Considerations

Minimize Database Queries

Explicit loading can result in multiple database calls, so avoid overusing it.

Use Asynchronous Loading

Always use LoadAsync() instead of Load() to prevent blocking the main thread.

Enable Logging for Query Monitoring

Enable logging to analyze SQL queries:

optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);

Optimize API Response with DTOs

Instead of returning full entities, create Data Transfer Objects (DTOs):

public class AuthorDto
{
    public string Name { get; set; }
    public List<string> BookTitles { get; set; }
}

Then, modify the controller:

[HttpGet("{id}")]
public async Task<IActionResult> GetAuthorWithBooksDto(int id)
{
    var author = await _context.Authors.FindAsync(id);
    
    if (author == null)
    {
        return NotFound();
    }

    await _context.Entry(author).Collection(a => a.Books).LoadAsync();

    var authorDto = new AuthorDto
    {
        Name = author.Name,
        BookTitles = author.Books.Select(b => b.Title).ToList()
    };

    return Ok(authorDto);
}

GitHub Project Link: https://github.com/SardarMudassarAliKhan/ExplictLoadingInAspNetCoreWebapi

Output

Conclusion

Explicit loading is a powerful tool in ASP.NET Core Web API that gives developers fine-grained control over related data retrieval. It helps optimize performance and prevents unnecessary data fetching, making it an ideal choice when data is conditionally required.

Would you like a sample project demonstrating these concepts?