locked
Appropriate Way of Creating a SQL View In ASP Core Code First Approach RRS feed

  • Question

  • User-357433193 posted

    Hi

    Googling around led me to deduce that one following the Code First approach can't have a view created for database in the SQL and a model based on the same for an MVC view. The only method I came across was to create a model referring to a similar structured table and having a SQL view with the same structure be created replacing the table so created. In regard of using Code First approach to get an Entity Framework mapped to a view, instead of a table, when an initial migration file was generated, I commented out the portion referring to the said imaginary table, and right at the same position inserted the query as follows:

    //Table Part Commented Out
    /*migrationBuilder.CreateTable(
        name: "D_XYZ",
        columns: table => new
        {
            ...
        },
        constraints: table =>
        {
            ...
        });*/
    
    //SQL For View Inserted
    migrationBuilder.Sql("CREATE VIEW [dbo].[D_XYZ] AS select ...");

    However, I find that if I run the Update-Database the said database creates successfully with the required View instead of a table. However, to check it again, I dropped the said database and this time, when I Run the said ASP Core app by F5, the database initialized by it had a Table instead of a View?

    • Why is it so, while I had commented out the relevant portion in the migration file?
    • Isn't the Migration file used upon the launch? or it is based only on the Models as defined?
    • Upon completion of a project and at the time of the deployment, would a manual separate running of the Migration or a Query to drop a Table and replacing it with a View would be required?

    Thanks

    Tuesday, February 20, 2018 5:49 PM

Answers

  • User283571144 posted

    Hi Faraz A. Qureshi, 

    As far as I know, when you run add-migration  in the EF core, it will generate the migration file.

    In this file, it will create the table.

    So If don't want to create the table, you could try to use below codes:

            protected override void OnModelCreating(ModelBuilder builder)
            {
    
                builder.Ignore<ContactsView>();
     
                base.OnModelCreating(builder);
                // Customize the ASP.NET Identity model and override the defaults if needed.
                // For example, you can rename the ASP.NET Identity table names and more.
                // Add your customizations after calling base.OnModelCreating(builder);
            }

    It will not auto generate the ContactsView table.

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 22, 2018 6:10 AM

All replies

  • User283571144 posted

    Hi Faraz A. Qureshi,

    However, I find that if I run the Update-Database the said database creates successfully with the required View instead of a table. However, to check it again, I dropped the said database and this time, when I Run the said ASP Core app by F5, the database initialized by it had a Table instead of a View?

    • Why is it so, while I had commented out the relevant portion in the migration file?
    • Isn't the Migration file used upon the launch? or it is based only on the Models as defined?
    • Upon completion of a project and at the time of the deployment, would a manual separate running of the Migration or a Query to drop a Table and replacing it with a View would be required?

    According to this github issue, views are not currently supported by Entity Framework Core. 

    You can trick EF into using a view by mapping your entity to the view as if it were a table. Details, you could refer to this article.

    This approach comes with limitations. e.g. you can't use migrations, you need to manually specific a key for EF to us, and some queries may not work correctly. 

    Best Regards,

    Brando

    Wednesday, February 21, 2018 5:58 AM
  • User-357433193 posted

    Thanks for your interest in this regard Brando. No doubt, Laboremus & Rowan Miller's & other's discussion on GitHub were the ones I had referred to. It is just that I have found that it is the manual adjustments and update-database via the migrations that leads to successful creation of the requisite View, while F5 leads to having the same in Table form.

    In this regard, I am just concerned about the same at the time of deployment once a project like this is ready. Would it be still required to update the database manually and such manual adjustment by tsql placed in migration would fail? or migration would succeed?

    Wednesday, February 21, 2018 6:26 AM
  • User283571144 posted

    Hi Faraz A. Qureshi, 

    As far as I know, when you run add-migration  in the EF core, it will generate the migration file.

    In this file, it will create the table.

    So If don't want to create the table, you could try to use below codes:

            protected override void OnModelCreating(ModelBuilder builder)
            {
    
                builder.Ignore<ContactsView>();
     
                base.OnModelCreating(builder);
                // Customize the ASP.NET Identity model and override the defaults if needed.
                // For example, you can rename the ASP.NET Identity table names and more.
                // Add your customizations after calling base.OnModelCreating(builder);
            }

    It will not auto generate the ContactsView table.

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 22, 2018 6:10 AM