How to execute raw SQL query using Entity Framework without having to use a model?

Question

I am trying to learn C# ASP.NET MVC 5. And I am trying to use Entity Framework for everything I do.

However, I need to run a raw SQL query and return the results into an array.

Here is what I have done so far.

I created my context class which allows me to connect to a server and it also allows me to change the database at run time.

Here is my context class

using ScripterEngine.Models;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Core.EntityClient;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace ScripterEngine.DataAccessLayer
{
    public class BaseContext : DbContext
    {
        protected string connectionName;
        public DbSet<Campaign> Campaign { get; set; }

        /**
         * Created the connection to the server using the giving connection string name
         * 
         * @param connName
         */
        public BaseContext(string connName = "BaseConnection")
            : base(connName)
        {
            connectionName = connName;
        }

        /**
         * Changes the default database
         * 
         * @param databaseName
         */
        public BaseContext setDatabase(string databaseName)
        {
            var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;

            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);

            //change the database before creating the new connection
            builder.InitialCatalog = databaseName;

            string sqlConnectionString = builder.ConnectionString;

            return new BaseContext(sqlConnectionString);
        }
    }
}

And how to make the connection here is what I do

BaseContext db1 = new BaseContext("server1");
var db1New = db1.setDatabase("someTableName");
string tableName = "SomeTableName";

var results = db1New.Database.SqlQuery("SELECT LOWER(column_name) AS column_name FROM information_schema.columns WHERE table_name = @tableName", tableName).ToArray();

This throws an error

The type arguments for method 'System.Data.Entity.Database.SqlQuery(string, params object[])' cannot be inferred from the usage. Try specifying the type arguments explicitly. C:.NET Projects\ScripterEngine\ScripterEngine\Controllers\CampaignController.cs 42 27 ScripterEngine

How can I execute this raw query?

Mark Cidade · Accepted Answer · 2016-02-03T18:32:21.443

61

Specify string as the type argument.

var results = db1New.Database.SqlQuery<string>("SELECT LOWER(column_name) AS column_name FROM information_schema.columns WHERE table_name = @p0", tableName).ToArray();
                                       ^^^^^^

edited Feb 03 '16 at 18:32

answered Feb 03 '16 at 18:11

Mark Cidade

98,437
31
224
236

1

Thank you for that. now I get a new error Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@tableName" I declared the tableName variable like so `var tableName = "SomeTableName";` – Junior Feb 03 '16 at 18:15
4

@MikeA Use `@p0` instead of `@tableName` inside the query text. – Ivan Stoev Feb 03 '16 at 18:28
1

@IvanStoev thank you. that worked. so `@p0` represents the first parameter in the SqlQuery method? – Junior Feb 03 '16 at 18:32
2

Yes, and `@p1` would represent the next one. – Mark Cidade Feb 03 '16 at 18:32
@MikeA If you hover the `SqlQuery` in VS, you'll see a tooltip containing a whole help topic describing this and the alternative :) – Ivan Stoev Feb 03 '16 at 18:38
9

whats happens when you return multiple columns ? I have a dynamic type that I return for each row retrieving the value as a string my key name. But is that the right way to do it ? – djack109 Aug 16 '17 at 07:19
@djack109 This looks promising: https://stackoverflow.com/a/42419101/15825 – teedyay Aug 15 '19 at 11:03
SWEET! I use EF all the time but I am not yet familiar with EF quering of complex queries especially with aggregates. For this I always defaulted back to Datatables and TSQL. I can now ditch Datatables!!!!! – Anthony Griggs Jan 15 '20 at 21:09

How to execute raw SQL query using Entity Framework without having to use a model?

1 Answers1

Linked