Mapping Database Views to EF 5.0 Code First w/Migrations

Question

I'm trying to map a SQL View to an entity in EF 5.0 Code First w/Migrations for displaying some basic information on a page without having to query multiple tables for that information (which currently takes ~20 seconds to load. NOT GOOD.). I've heard that it is possible to do, but I haven't been able to figure out or find online a way to properly do so.

EDIT: For a more in-depth look at my solution to this problem, read this blog post on the subject.

Here is my View:

CREATE VIEW [dbo].[ClientStatistics]
AS
SELECT       ROW_NUMBER() OVER (Order By c.ID) as Row, c.LegacyID, c.ID, c.ClientName, slc.AccountManager, slc.Network,
                             (SELECT        MAX(CreatedDate) AS Expr1
                               FROM            dbo.DataPeriods
                               WHERE        (ClientID = c.ID)) AS LastDataReceived,
                             (SELECT        MAX(ApprovedDate) AS Expr1
                               FROM            dbo.DataPeriods AS DataPeriods_2
                               WHERE        (ClientID = c.ID)) AS LastApproved,
                             (SELECT        MAX(ReportProcessedDate) AS Expr1
                               FROM            dbo.DataPeriods AS DataPeriods_1
                               WHERE        (ClientID = c.ID)) AS LastReportProcesssed
FROM            dbo.Clients AS c INNER JOIN
                         dbo.SLClients AS slc ON c.ID = slc.ClientID

Here is the entity:

public class ClientStatisticsView
{
    [Key]
    public int Row { get; set; }
    public int LegacyID { get; set; }
    public int ClientID { get; set; }
    public string ClientName { get; set; }
    public string AccountManager { get; set; }
    public string Network { get; set; }
    public DateTime LastDataReceived { get; set; }
    public DateTime LastApproved { get; set; }
    public DateTime LastReportProcessed { get; set; }
}

And finally my mapping in DbContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

    modelBuilder.Entity<ClientStatisticsView>().ToTable("ClientStatistics");

    base.OnModelCreating(modelBuilder);
}

All of this gives me the following error:

There is already an object named 'ClientStatistics' in the database.

What am I doing wrong? Is there any way to me to accomplish this, or should I be doing something else instead?

Flywheel

Remove the "Create Table" code that has been generated by EF in the migration file. As an aside your sql looks odd to me and that may be a source of the inefficiency - you should be able to use a group by to get rid of the sub queries. Or perhaps MAX OVER technet.microsoft.com/en-us/library/ms189461.aspx – Armil

@Armil - You are quite correct (on both points). The solution you have provided works wonderfully. Please make it an answer so that I make mark it as the correct answer. – Flywheel

Answer 1

You have specified that the ClientStatisticsView entity should be mapped to a table named "ClientStatistics". So entity framework will generate a migration containing an instruction to create that table. But you have independently created that view in the database so to prevent the error you are getting you should remove the CreateTable instruction from the Up migration.

I think a better way to do it is to create the view in the migration by running sql like this:

public override void Up()
{
    Sql("EXEC ('CREATE View [dbo].[ClientStatistics] AS --etc"
}

public override void Down()
{

    Sql(@"IF  EXISTS (SELECT
                        *
                    FROM sys.views
                    WHERE object_id = OBJECT_ID(N'dbo.ClientStatistics'))
                    DROP VIEW dbo.ClientStatistics)")
}

That way your views and tables are specified in one place and you can safely migrate up and down

Reference

http://elegantcode.com/2012/04/12/entity-framework-migrations-tips/

Armil

I just want to add, for anyone seeing this answer, that you will need to use the add-migration command at the beginning in order for it to map properly. – Flywheel

If you're fastidious about making all schema changes via migrations, then the IF EXISTS clause of the Down method is not required. – Scull

@Scull that is true. I use the default scripting from Sql Server, which puts in the IF EXISTS clause automatically. I don't feel the need to remove it – Armil

Answer 2

I'm actually working with Entity Framework "Code First" and views, the way I do it is like this:

1) Create a class

[Table("view_name_on_database")]
public class ViewClassName {
    // View columns mapping
    public int Id {get; set;}
    public string Name {get; set;}
    // And a few more...
}

2) Add the class to the context

public class ContextName : DbContext {

    // Tables
    public DbSet<SomeTableClassHere> ATable { get; set; }
    // And other tables...

    // Views
    public DbSet<ViewClassName> ViewContextName { get; set; }

    // This lines help me during "update-database" command
    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        // Remove comments before "update-database" command and 
        // comment this line again after "update-database", otherwise 
        // you will not be able to query the view from the context.
        // Ignore the creation of a table named "view_name_on_database"
        modelBuilder.Ignore<ViewClassName>();
    }
}

A little bit late but I hope this helps somebody.

If there was a way to ignore the creation of a table named as the view during "update-database" and not-ignore after this that would be great.

Shirleeshirleen

That's the only I found to resolve the migration with views issue, there is another missing bit in your code. When you remove comments/comment the ignore line, EF will throw the exception, I add the following line before the ignore to get it working finally. Database.SetInitializer<RegulatoryPortalDataContext>(null); – Fanion

Answer 3

Add the ToTable attribute to your entity and include it as a DbSet in your context.

[ToTable("ClientStatics")]
public class ClientStaticsView{}

public class DataContext : DbContext
{
    public DbSet<ClientStaticsView> ClientStatics { get; set; }
}

Or if you don't want to add the DbSet to your context, create an EntityTypeConfiguration for your ClientStatisView and include the ToTable attribute with the fluent api instead of an attribute. You can then add the entity to your context in your context's OnModelCreating:

public class ClientStaticsViewConfiguration : EntityTypeConfiguration<ClientStaticsView>
{
    public ClientStatusViewConfiguration
    {
       ToTable("ClientStatics");
    }
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Configurations.Add(new ClientStatisViewConfiguration());
}

Pyridine