How to execute an sql view in mvc C# and store its result in a list?

Question

I am working on an asp.net mvc application using C#. For database, I am using Entity Framework and code-first approach. I have an sql view that I need to execute and store its result in a list. I know how to query data from DB using ApplicationDbContext, stored procedure and inline queries. But now I want to do the same using a view.

The view looks like this

CREATE VIEW [dbo].[GenresView]
AS SELECT * FROM Genres

I have a Genre class that has the same properties that the view will return.

Answer 1

Steps to handle a view:

• Create a GenresView class with properties mapped to view columns. You can use EF features like virtual properties and ForeignKey annotations to be able to load entities related to the view in your queries.
• Add the created entity as a new property DbSet GenresView to your database context.
• Add new migration using Package Manager Console and add migration command. As you can notice, the generated migration contains statements for creating a new table for the GenresView in the Up() method. Delete this code and replace it with Sql(@"CREATE VIEW dbo.GenresView AS SELECT * FROM Genres") statement.

IMPORTANT!!! It is better to define all fields which you want to get instead of * .

• You should also replace the code in the Down() method to be able to revert the migrations properly. Its code should contain call to Sql(@"DROP VIEW dbo.GenresView").
• Update the database.
• Now you can use the new entity in your LINQ statements as it were a regular table (e.g. dbContext.GenresView.).