How to implement Raw SQL without model in ASP.NET Core MVC?

Question

In ASP.NET Core 5 MVC, I have:

dbContext = mfdDbContext

var myquery = select * from teachers order by id desc

The normal convention is this:

var results = mfdDbContext.SomeModel
                          .FromSqlRaw("select id, firstName, lastName from teachers order by id desc")
                          .ToList();

But I don't have a model class.

How do I implement the above raw query without a model? Then generate my own model.

Does this answer your question? Raw SQL Query without DbSet - Entity Framework Core — gunr2171
– gunr2171, Commented Nov 8, 2021 at 12:39

Maxian Nicu · Accepted Answer · 2021-11-08 12:41:13Z

2

You cannot achieve this with EF. You have to have model class anyway. That's why EF is so powerful.

Instead, you could use Dapper https://dapper-tutorial.net/query

string sql = "select * from teachers order by id desc";

using (var connection = new SqlConnection(connectionString))
{    
    var data = connection.Query(sql).ToList();

    // use data
}

but anyway, you have manually to get the columns. I would recommend to have a DB Model registered with EF, it will make your life easier.

answered Nov 8, 2021 at 12:41

Maxian Nicu

2,3443 gold badges20 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

midowu Over a year ago

Is there a way to bring in the DbContext here using (var connection = new SqlConnection(connectionString)) instead of the Connection string? Since I have this inside DBContext private readonly string Conn ="Server=myserver;Database=myDB;User Id=myuser;Password=mypw;";

Maxian Nicu Over a year ago

@midowu i think you can try _dbContext.Database.GetDbConnection().ConnectionString

midowu Over a year ago

Do you mean I replace using (var connection = new SqlConnection(connectionString)) with _dbContext.Database.GetDbConnection().ConnectionString

Maxian Nicu Over a year ago

@midowu use this using (var connection = new SqlConnection(mfdDbContext.Database.GetDbConnection().ConnectionString))

Collectives™ on Stack Overflow

How to implement Raw SQL without model in ASP.NET Core MVC?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related