Entity Framework 6 vs Entity Framework Core Raw Sql

entity-framework-6 entity-framework-core

Popular Answer

When there is just one table, I have done the "fromsql" right off of the context; however, I recognise that this is not what you want, even if it advances the cause.

var blogs = context.Blogs
    .FromSql("SELECT * FROM dbo.Blogs")
    .ToList();

But in a situation like yours, it is complicated and involves the combining of many tables and CTEs. I would advise you to construct a POCO C# custom object and add a DbSet to it in your model builder. Then, you may do the following action:

 var custom = context.YOURCUSTOMOBJECT.FromSql("(crazy long SQL)").ToList();

It could work if your return complies with the type. Similar to what you did, I just wrapped my whole methodology under a process. However, if you want to deploy EF Core, you must manually create a migration up and then include the manual creation of the proc in the migration's 'Up' function. If you choose that course, you would need to deploy your proc as described above or have it already installed on the server.

context.pGetResult.FromSql("pGetResult @p0, @p1, @p2", parameters: new[] { "Flight", null, null }).ToList()

The crucial point to keep in mind is that you must first establish a DBSet object in your model context so that the context you are calling is aware of the properly typed object it is returning from direct SQL. It must EXACTLY match the columns and return types.

EDIT 3-8 I'll list a few things you must do to be sure:

  1. a POCO class with [Key] as the Data Annotation over a unique attribute. This class precisely matches the columns that a method returns.
  2. In your environment, a DBSet(POCO)>.
  3. Dotnet ef Migrations add "yourname" to create a new Migration.
  4. Watch the updated migration scripts. Erase everything that produces a table for the POCO if it does. You do not need it. This is not for database storage, but rather for a result set.
  5. To manually script your SQL to the database, modify the "Up" portion as shown below. Also, be careful to remove the data from the "Down" area if you ever want to go back.

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
        "create proc POCONameAbove" +
        "( @param1 varchar(16), @Param2 int) as " +
        "BEGIN " +
        "Select * " +
        "From Table "
        "Where param1 = @param1 " +
        " AND param2 = @param2 "
        "END"
        );
     }
    
     protected override void Down(MigrationBuilder migrationBuilder)
     {
        migrationBuilder.Sql("drop proc POCONameAbove");
     }
    
  6. Therefore, you have effectively commandeered the migration to carry out your stated wishes. By deploying the modifications to the database using "dotnet ef database update 'yourmigrationname'," you can test it out.
  7. If the database update was successful and you did not unintentionally create a table during the migration, look in the database. It should contain your proc.
  8. What receives the data in EF Core is the area you claimed to not grasp. Let's separate it:

    context.pGetResult.FromSql("pGetResult @p0, @p1, @p2", parameters: new[] { "Flight", null, null }).ToList()
    

context. The DbSet you created is being used by pGetResult =. It maintains your kind to your procedure. "pGetResult @p0, @p1, @p2" indicates that I am referring to a method in the database that has three parameters. fromSQL(= informing the context that you are going to do some SQL straight in the string. , parameters: new[] "Flight", null, null ) = I'm simply creating an array of objects that are in the correct order according to the parameters. Of course, you must match the SQL types, but as long as that is alright, everything will be great. When troubleshooting, ToListAsync() is usually my first choice when I want a collection.

Hope that was useful. It opened up a whole new universe of possibilities for me once I realised that this would work. You may use a project I started but never completed as inspiration. In order to display the proc with pre-set values, I hard coded a controller. However, it could be easily altered to just insert them into the api. https://github.com/djangojazz/EFCoreTest/tree/master/EFCoreCodeFirstScaffolding

0
3/8/2018 5:52:33 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow