Translation Behind

Naming translation

Name of a DbSet in a DbContext should correspond to the table name in database. In other words, EFCore searches the table by the name of DbSet, so you should make sure it exists.

public class MoviesContext : DbContext
{
    // corresponds to a table named `Movies` in database
    public DbSet<Movie> Movies => Set<Movie>();
}

public class Movie
{
    // corresponds to field named `Id` in a table
    public int Id { get; set; }
    public string? Title { get; set; }    
    public DateTime ReleaseDate { get; set; }
    public string? Synopsis { get; set; }
}

Custom naming convention conversion

Some database providers might ship with different naming convention extension to adapt with common scenarios. For example, snake case is commonly used in PostgresSQL:

protected override void OnConfiguring(DbContextOptionsBuilder builder)
{
    builder.UseNpgsql($"Host={Host};Database={DefaultDatabase};Username={UserId};Password={Password}")
        .UseSnakeCaseNamingConvention();
    base.OnConfiguring(builder);
}

Data type translation

EFCore also handles default data type mapping, it always uses matchable type with the largest range to fit with the worst scenario.

SQL translation

EFCore translates LINQ to SQL, before checking the translated SQL, we need to setup a dummy logger. Simply use Console.WriteLine as logger delegate,

public class MoviesContext : DbContext
{
    public DbSet<Movie> Movies => Set<Movie>();
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        string connectionString = "...";
        optionsBuilder.UseSqlServer(connectionString); // use MSSQL here
        optionsBuilder.LogTo(Console.WriteLine);
        base.OnConfiguring(optionsBuilder);
    }
}
[ApiController]
[Route("[controller]")]
public class MoviesController(MoviesContext context) : Controller
{
    [HttpGet]
    [ProducesResponseType(typeof(List<Movie>), StatusCodes.Status200OK)]
    public async Task<IActionResult> GetAll()
    {
        return Ok(await context.Movies.ToListAsync());
    }
}

the ToListAsync invoked in GetAll api is translated to:

SELECT [m].[Id], [m].[ReleaseDate], [m].[Synopsis], [m].[Title]
  FROM [Movies] AS [m]

Object translation

Though C# is a static type language, it also offer a anonymous way to declare the object. EFCore only cares the shape of the object instead of the type name. So you can use anonymous object or a DTO(Data Transfer Object) to do projection.

record class MovieTitle(int Id, string? Title);

[HttpGet("by-year/{year:int}")]
[ProducesResponseType(typeof(List<MovieTitle>), StatusCodes.Status200OK)]
public async Task<IActionResult> GetAllByYear([FromRoute] int year)
{
    var filteredTitles = await context.Movies
        .Where(movie => movie.ReleaseDate.Year == year)
        .Select(movie => new { Id = movie.Id, Title = movie.Title }) 
        .ToListAsync();
    return Ok(filteredTitles);
}

will be translated to the following where only Id and Title were seleted.

SELECT [m].[Id], [m].[Title]
  FROM [Movies] AS [m]
  WHERE DATEPART(year, [m].[ReleaseDate]) = @__year_0