I have used Entity Framework for a long time, but have an edge case where I need to use SQL. I was wondering if I could use my existing Entity Framework Core context for this or not. Here is what I have currently, but the queryResults variable contains a "-1" value, instead of a list of Students, after running it:

string tableName = "Students";
var queryResults = db.Database.ExecuteSqlRaw(@"SELECT * FROM {0}", tableName);

Any ideas?

• Entity Framework Core 3.1
• .NET Core 3.1
• Linq-to-SQL

CodePudding user response：

"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();

            }