How to execute raw SQL query using Entity Framework without having to use a model?
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();
^^^^^^
Related videos on Youtube
Junior
Updated on January 17, 2021Comments
-
Junior over 3 years
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?
-
Junior about 8 yearsThank 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";
-
Ivan Stoev about 8 years@MikeA Use
@p0
instead of@tableName
inside the query text. -
Junior about 8 years@IvanStoev thank you. that worked. so
@p0
represents the first parameter in the SqlQuery method? -
Mark Cidade about 8 yearsYes, and
@p1
would represent the next one. -
Ivan Stoev about 8 years@MikeA If you hover the
SqlQuery
in VS, you'll see a tooltip containing a whole help topic describing this and the alternative :) -
djack109 over 6 yearswhats 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 ?
-
teedyay over 4 years@djack109 This looks promising: stackoverflow.com/a/42419101/15825
-
Anthony Griggs over 4 yearsSWEET! 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!!!!!