Create Database Views using EF Core Code First approach

As far as I know, you can't create views with EF Core directly. However, you can use migrations to execute arbitrary SQL when upgrading.

1. Generate a migration in the package manager console, even if there is nothing really to do: add-migration CreatingTheView
2. Open the generated migration class, file name: Migrations\yyyyMMdd_CreatingTheView.cs
3. Add raw SQL to execute when upgrading by modifying the Up method: migrationBuilder.Sql("CREATE VIEW etc.");
4. If you want to support downgrading, modify the Down method: migrationBuilder.Sql("DROP VIEW etc.");

The answer from relatively_random almost worked for me, but I had to use this syntax to get it working:

migrationBuilder.Sql(@"exec('create view dbo.MyViewName as ....');");

and

migrationBuilder.Sql("exec('drop view dbo.MyViewName');");

Without that I got a sql error "Create View must be the only statement in batch".

Since you're using Code First, maybe the database is just an extension to the application, and not a primary goal of its own? In that case, you probably don't really need a view.

The database won't care if you just send it normal queries, and you have alternatives for abstracting them in the application layer. The most basic hack is to just create an IQueryable<T> property in your DbContext implementation (or an extension method), which queries the data you wish displayed. Something like this:

public sealed class DatabaseContext : DbContext
{
    public DbSet<Transaction> Transactions { get; set; }

    public IQueryable<PerPersonSum> PerPersonSums
        => Transactions.GroupBy(t => t.Person,
                                (k, g) => new PerPersonSum
                                {
                                    Person = k,
                                    TotalAmount = g.Sum(t => t.Amount)
                                });
}

A more proper solution would be a keyless entity type:

public sealed class DatabaseContext : DbContext
{
    public DbSet<Transaction> Transactions { get; set; }

    public DbSet<PerPersonSum> PerPersonSums { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<PerPersonSum>(e =>
        {
            e.HasNoKey();
            e.ToQuery(() => Transactions.GroupBy(t => t.Person,
                                                 (k, g) => new PerPersonSum
                                                 {
                                                     Person = k,
                                                     TotalAmount = g.Sum(t => t.Amount)
                                                 }));
        });
    }
}

Pre-3.0, it used to be called a Query Type and it could be used like this:

public sealed class DatabaseContext : DbContext
{
    public DbSet<Transaction> Transactions { get; set; }

    public DbQuery<PerPersonSum> PerPersonSums { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<PerPersonSum>()
                    .ToQuery(() => Transactions.GroupBy(t => t.Person,
                                                        (k, g) => new PerPersonSum
                                                        {
                                                            Person = k,
                                                            TotalAmount = g.Sum(t => t.Amount)
                                                        }));
    }
}

It's not directly supported (yet), but all the building blocks are already there. Essentially, a view is the same as "projection", which EF already supports. We can even get the SQL for it, and make EF map a view to a DbSet. We just have to create the view ourselves.

1. Design your view entity

For example:

public class UserStatsView
{
    public bool IsActive { get; set; }
    public int Count { get; set; }
}

2. Define the view and map to a DbSet
Define a separate DbSet for this view and map it inside OnModelCreating. Also, design the query which represents the "body" of the view:

public class AppDbContext : DbContext
{
    
    public DbSet<User> Users { get; set; } // "real" table
    public DbSet<UserStatsView> UserStats { get; set; } // mapped to our view

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<UserStatsView>()
            .ToView("View_UserStats")
            .HasNoKey(); // Required if the view has no primary key
    }

    // the query that represents your view
    public IQueryable<UserStatsView> GetUserStatsQuery() => this.Users
        .GroupBy(u => u.IsActive)
        .Select(g => new UserStatsView
        {
            IsActive = g.Key,
            Count = g.Count()
        });
}

3. Create the view in a migration

Get the raw SQL for the query from your DbContext and use it to create the view manually inside a migration.

protected override void Up(MigrationBuilder migrationBuilder)
{
    // at design-time only!
    using var context = new AppDbContext(); 

    // get the raw sql of the query
    var sql = context.GetUserStatsQuery().ToQueryString();

    // create the view
    migrationBuilder.Sql($"""
        CREATE VIEW View_UserStats AS
        {sql}
        """);
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql("DROP VIEW IF EXISTS View_UserStats");
}