I would like to use the Code-First DropCreateDatabaseAlways and DropCreateDatabaseIfModelChanges , because I built a series of integration tests around this function. Is there a more elegant desire to connect to an SQL view than create a table, drop the table, and then create the view using the sql command?
[Using VS2010 Professional, ASP.NET 4, MVC3, EF4, SQL Server 2008 R2 Express, Win7]
public MyContext : DbContext { public DbSet<Person> Persons {get; set;} public DbSet<Worker> Workers {get; set;} public DbSet<Signin> Signins {get; set;} public DbSet<SigninView> SigninView {get; set;} } public class Person { public int ID { get; set; } public virtual Worker Worker { get; set; } } public class Worker { public int ID { get; set; } public int barcodenumber {get; set;} public virtual Person Person { get; set; } public virtual ICollection<WorkerSignin> workersignins { get; set; } } public class WorkerSignin { public int ID { get; set; } public virtual Worker worker {get; set;} public int barcodenumber {get; set;} }
Person.ID == Worker.ID. They are in the ratio from 1 to 0..1. There will always be a man; there can be no employee record.
public class PersonBuilder : EntityTypeConfiguration<Person> { public PersonBuilder() { ToTable("Persons"); HasKey(k => k.ID); HasOptional(p => p.Worker).WithRequired().WillCascadeOnDelete(); } } public class WorkerBuilder : EntityTypeConfiguration<Worker> { public WorkerBuilder() { HasKey(k => k.ID); HasMany(s => s.workersignins) .WithOptional(s => s.worker) .HasForeignKey(s => s.WorkerID); } }
The Signin table receives input from a barcode scanner. Workers hold a card and register during the day. The client requires that the Signin be recorded, even if there is no corresponding record in the Worker table during the scan. Due to customer requirements, I plan to use an identifier that I can control as a primary key and map the barcode programmatically or with the ability to view, if possible.
SigninView combines information from the Person, Worker, and Signin tables for presentation on a web page where the identifier gets swiped. I assume that the SQL server view will be faster than the view than my C # matching 3 data cost tables. (I don’t feel like I have time to stop and check this out).
So ... what I want to do is attach to the view. I can connect to the view using DbSet <>, but I also use CodeFirst to recreate my table structure when developing the application.
Now I have kludge, where Code-First creates a SigninView table, then discards it and creates a view using SqlCommands:
public class MyInitializer : DropCreateDatabaseIfModelChanges<MyContext> { protected override void Seed(MyContext myDB) { myDB.Database.SqlCommand(@"drop table machete.dbo.WorkerSigninView"); myDB.Database.SqlCommand(@"CREATE VIEW [dbo].[WorkerSigninView] AS SELECT dbo.WorkerSignins.ID, dbo.WorkerSignins.barcodenumber FROM dbo.Persons INNER JOIN dbo.Workers ON dbo.Persons.ID = dbo.Workers.ID RIGHT OUTER JOIN dbo.WorkerSignins ON dbo.Workers.barcodenumber = dbo.WorkerSignins.barcodenumber"); }