Mapping Database Views to EF 5.0 Code First w/Migrations

Viewed 14346

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?

3 Answers
Related