Entity framework - create model from view?

Question

I am new to ASPNET and MVC 4, so I think this can be a tricky issue. However, I could not correctly answer Google. I just wanted to display some sales information - I need to display it. I do not need to insert, update or delete from base tables.

There are 3 SQL Server tables to extract data from: CurrentSales, SalesPlans, and AverageSales. I created VIEW for this and put a unique clustered index on it; it contains some external connections, but has logic to handle any unlikely NULL values.

I go to MODELS, add a new Entity Data ADO.NET data model and add my view to the model. He returns and says, "The primary key is not specified in the table / view" vw_FullView. The key was output and the definition was created as a table / view only. " When I create the Controller class from this model and object, the view does not display data when the website loads.

However, if I create a completely empty table with the corresponding primary key, which acts as a model table, then use the stored procedure (as an import function) to extract the necessary data, everything works completely fine.

This obviously cannot be the right way to handle this. Is there a way to create a strongly typed model from a view? I would prefer that the Controller and View objects in ASP.NET be correctly automatically generated from the SQL view in the model, rather than having this empty table cheat on the system.

Thank you very much in advance.

Answer 1

The Entity framework really dislikes performance. He cannot understand what the primary keys to represent are, so he assumes that any field that cannot be reset is part of a composite primary key.

In general, avoid views when using the Entity Framework. Create a linq query that does what your look does and uses it.

Answer 2

a view is simply a projection of an object or combination of objects. By default, a Framework object allows read-only access, since there are no real objects in the view, that is, ET cannot track changes.

simmdan actually wrote Job well, explaining how to get around this in the MSDN forum

basically, as Mystere Man has already pointed out, if you use the Entity Framework, the easiest way would be to clone your view using real objects merged and filtered by linq.

Answer 3

It is true that the Entity Framework does not like SQL Server views because the primary key is not obvious in the view. Unfortunately, specifying [Key] in the model does not seem sufficient. However, this can be done and is very useful in 98% of cases when there is a view that joins tables, and you just want to display it in a grid. The key (pardon the pun) is to correctly define the performance.

My initial view contained the following columns:

DepartmentCode(varchar(8),not null) DepartmentName(varchar(60), not null) DivisionCode(varchar(8), null) DivisionName(varchar(8), null) StatusCode(char(1), not null) Virtual(varchar(1), not null) 

and in my model I indicated

 [Key] [Column("DepartmentCode")] [DatabaseGenerated(DatabaseGeneratedOption.None)] [StringLength(8)] [Display(Name = "Department Code")] public string DepartmentCode { get; set; } 

When I close this and display the Index view, I received an error message with a primary key. Apparently, the Entity Framework assumes that all non-empty fields contribute to the primary key.

Removing extra null values ​​did the trick. I made the columns in the view be NULL using the following:

 CREATE view [dbo].[Departments] as select DepartmentCode ,nullif(DepartmentName,'') as DepartmentName ,nullif(DivisionCode,'') as DivisionCode ,nullif(DivisionName,'') as DivisionName ,nullif(StatusCode,'') as StatusCode ,nullif(Virtual,'') as Virtual from .... 

Presentation columns now look like this

 DepartmentCode(varchar(8),not null) DepartmentName(varchar(60), null) DivisionCode(varchar(8), null) DivisionName(varchar(8), null) StatusCode(char(1), null) Virtual(varchar(1), null) 

As soon as I changed the view, the index view worked correctly.

I also confirmed that the view containing the composite (multi-column) key will also work as long as only these columns are not zero, and in your model you specify [Key] for each column, and also add Order = 1 and Order = 2 in Column annotations on these key columns.

Of course, the assumption is that you have permission to change the view (or you can create an alternative view). I can only confirm this on MVC 5 and Entity Framework 6.