How to execute RAW SQL Query with EF Core?

Question

How to execute RAW SQL Query with EF Core?

asp.net-core c# entity-framework-core

Question

Basically the problem i have is that i want to run a query in a database that it's not a representation of my model.

This is my code to create the connection to another database:

public static OtherContext GetNewContextGeneric(string connectionString)
        {
            var builder = new DbContextOptionsBuilder();
            builder.UseSqlServer(connectionString);

            OtherContext db = new OtherContext(builder.Options);

            return db;
        }

And this is my code to execute the query:

public List<IQueryble> Query (string connectionString, string query)
        {
            try
            {
                using(var contextGeneric = ContextFactory.GetNewContextGeneric(connectionString))
                {
                    //I want something like this
                    return contextGeneric.Query(query).ToList();
                }
            }
            catch(System.Data.SqlClient.SqlException ex)
            {
                throw new SQLIncorrectException(ex);
            }
            catch(System.InvalidOperationException ex)
            {
                throw new NotImplementedException();
            }   
        }

Can somebody help me?

Daniel Acevedo

Popular Answer

In the question you say:

Basically the problem i have is that i want to run a query in a database that it's not a representation of my model.

and then in comments you add:

Because i don't know how is created the database, i don't know what tables are in the database i want to insert the sql query

Well, if you don't know the database, then you cannot use Entity Framework, as it requires you to have a detailed knowledge of the database you are connecting to.
You should use plain ADO.NET (or Dapper if you want to map results back to a known class) for this.

Camilo Terevinto

View more on Stack Overflow

Daniel Acevedo · Accepted Answer · 4/21/2019 12:29:18 AM

It worked like this:

private void SqlCommand (string connectionString, string query)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        var a = reader[0];
                    }
                }
                finally
                {
                    // Always call Close when done reading.
                    reader.Close();
                }
            }
        }

Or

using (var connection = ContextFactory.GetNewContextGeneric(connectionString).Database.GetDbConnection())
                {
                    connection.Open();
                    DbCommand command = connection.CreateCommand();
                    command.CommandText = query;

                    using (var reader = command.ExecuteReader())
                    {
                        // Do something with result
                        reader.Read(); // Read first row
                        var firstColumnObject = reader.GetValue(0);
                        /*var secondColumnObject = reader.GetValue(1);

                        reader.Read(); // Read second row
                        firstColumnObject = reader.GetValue(0);
                        secondColumnObject = reader.GetValue(1);*/
                        connection.Close();
                        return firstColumnObject.ToString();
                    }
                }

How to execute RAW SQL Query with EF Core?

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related