Entity Framework Code First makes Views unpleasant
Getting an SQL View into Entity Framework Code First is a real bastard. Especially if said view doesn't expose any non-nullable fields you can combine to create a primary key. Here's how I ended up achieving it.
Write the View in SQL
CREATE VIEW MyView AS SELECT ROW_NUMBER() OVER(ORDER BY Created, StashId) AS Id, -- this will be primary key * FROM MyTable -- and whatever else, Joins, CTEs, Views etc.
Add the C# representation
public class MyView { public Int64 Id { get; set; } // ROW_NUMBER() returns Int64, and not Int32 // other properties } public class ApplicationDataContext { public DbSetMyViews { get; set; } // attach to DataContext }
Add the migration
Running the following will create an empty migration:
AddMigration AddMyView -IgnoreChanges
Now you can edit the empty migration to CREATE/DROP the view:
public partial class AddMyView : DbMigration { public override void Up() { var sql = @" CREATE VIEW MyView AS SELECT ROW_NUMBER() OVER(ORDER BY Created, StashId) AS Id, * FROM MyTable"; Sql(sql); } public override void Down() { Sql("DROP VIEW dbo.MyView"); } }
And finally, update the database and you're done!
Update-Database