Raw SQL Query Without Dbset - Entity Framework Core

Raw SQL Query without DbSet - Entity Framework Core

If you're using EF Core 3.0 or newer

You need to use keyless entity types, previously known as query types:

This feature was added in EF Core 2.1 under the name of query types.
In EF Core 3.0 the concept was renamed to keyless entity types. The
[Keyless] Data Annotation became available in EFCore 5.0.

To use them you need to first mark your class SomeModel with [Keyless] data annotation or through fluent configuration with .HasNoKey() method call like below:

public DbSet<SomeModel> SomeModels { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<SomeModel>().HasNoKey();
}

After that configuration, you can use one of the methods explained here to execute your SQL query. For example you can use this one:

var result = context.SomeModels.FromSqlRaw("SQL SCRIPT").ToList();
var result = await context.SomeModels.FromSql("SQL_SCRIPT").ToListAsync();

---

If you're using EF Core 2.1

If you're using EF Core 2.1 Release Candidate 1 available since 7 may 2018, you can take advantage of the proposed new feature which is query types:

In addition to entity types, an EF Core model can contain query types,
which can be used to carry out database queries against data that
isn't mapped to entity types.

When to use query type?

Serving as the return type for ad hoc FromSql() queries.

Mapping to database views.

Mapping to tables that do not have a primary key defined.

Mapping to queries defined in the model.

So you no longer need to do all the hacks or workarounds proposed as answers to your question. Just follow these steps:

First you defined a new property of type DbQuery<T> where T is the type of the class that will carry the column values of your SQL query. So in your DbContext you'll have this:

public DbQuery<SomeModel> SomeModels { get; set; }

Secondly use FromSql method like you do with DbSet<T>:

var result = context.SomeModels.FromSql("SQL_SCRIPT").ToList();
var result = await context.SomeModels.FromSql("SQL_SCRIPT").ToListAsync();

Also note that DbContexts are partial classes, so you can create one or more separate files to organize your 'raw SQL DbQuery' definitions as best suits you.

Entity Framework Core Stored Proc without DbSet

Presently, in EF Core 2.0 RTM version, FromSql can be used only with the types which are defined as EntityType in your model. The restriction is there because, EF Core has metadata about the EntityType and knows how to materialize them. So for any other type (like custom DTO/result type), FromSql does not work.

This is tracking issue on EF Core GitHub repository which is tracking exactly what you are looking for.

EF Core 2.1.0 is planning to add some support for querying views. Tracking issue. Which could enable scenario like yours, if you are ok with letting EF Core know about the custom type you want to use during model building. It wouldn't mapped as EntityType/Table but EF Core would still compute metadata around it to support queries like above.

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

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.

How to return a list of int in Entity Framework Core using FromSqlRaw?

In EF 3.0 you need to declare a dedicated type which is known as Keyless entity type

[Keyless]
public class SomeType
{
    public int Ids { get; set; }
}   

Then you define a DbSet for it as usual

public DbSet<SomeType> SomeTypes { get; set; }

Note: [Keyless] attribute became available in EFCore 5.0. For EF 3.0 You can use fluent configuration calling .HasNoKey() method on it:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<SomeType>().HasNoKey();
}

Then you can do something like:

var result = context.SomeTypes.FromSqlRaw(sqlString).ToList();

C# Entity Framework Core write raw SQL without model

Sorry for bothering you all. I have found solution. Here it is:

public class AA
{
    public string One { get; set; }
    public string Two { get; set; }
}

private async Task pokus()
{
    List<AA> groups = new List<AA>();
    var conn = _db.Database.GetDbConnection();
    try
    {
        await conn.OpenAsync();
        using (var command = conn.CreateCommand())
        {
            string query = "SELECT * FROM TABLE";
            command.CommandText = query;
            DbDataReader reader = await command.ExecuteReaderAsync();

            if (reader.HasRows)
            {
                while (await reader.ReadAsync())
                {
                    try
                    {
                        var row = new AA { One = reader.GetString(1), Two = reader.GetString(2) };
                        groups.Add(row);
                    }
                    catch { }
                }
            }
            else
            {
                Console.WriteLine("Dont have rows");
            }
            reader.Dispose();
        }
    }
    finally
    {
        conn.Close();
    }
    foreach(AA s in groups)
    {
        Console.WriteLine(s.One);
        Console.WriteLine(s.Two);
    }
}

I hope it will help somebody.

How is raw SQL run against an Entity Framework Core context?

"I was wondering if I could use my existing Entity Framework Core context for this or not":

Yes you can use your existing databaseContext but you have to execute that query on your dbContext Entity see the example below:

var sqlCommand = $"SELECT * FROM Students";
var executeSQL = await _context.Students.FromSqlRaw(sqlCommand).ToListAsync();
return Ok(executeSQL);

Output:

Note: As you can see I am executing sqlCommand on Students dbContext this is valid. But using DbContext you cannot pass the
table name dynamically. You must need to define it explicitly.

Hope above steps guided you accordingly, You can have a look on official document for more details here

Update Using Ado.Net Connection:

     using (var connection = _context.Database.GetDbConnection())
            {
                connection.Open();
                var tableName = "Students";
                List<Student> _listStudent = new List<Student>();
                var command = connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = string.Format("SELECT * FROM [{0}];", tableName);
                SqlDataReader reader = (SqlDataReader)command.ExecuteReader();
                while (reader.Read())
                {
                    var student = new Student(); // You have to bind dynamic property here based on your table entities
                    student.FirstName = reader["FirstName"].ToString(); // Remember Type Casting is required here it has to be according to database column data type
                    student.LastName = reader["LastName"].ToString();
                    _listStudent.Add(student);

                }
                reader.Close();
                command.Dispose();
                connection.Close();

            }

---

Related Topics