Create Database Views using EF Core Code First approach

Question

I am working on a new application that requires, the creation of DB Views. I am using EF core with MySql flavor and using the Code First approach to create the DB and tables. I didn't find a way to create a view though.If somehow, I could execute the raw sql in migration step, that might help in creation of view and later mapping that as the DbSet. I can't create the views manually as it would require the execution of the view script against the database, and that won't be possible in higher environment. Can someone please guide me. Help is really appreciated!

Answer 1

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)
                                                        }));
    }
}

Answer 2

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".