如何在不使用模型的情况下使用Entity Framework执行原始SQL查询? [英] How to execute raw SQL query using Entity Framework without having to use a model?

问题描述

我正在尝试学习C#ASP.NET MVC5.并且我正在尝试将Entity Framework用于我所做的所有事情.

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

但是，我需要运行原始SQL查询并将结果返回到数组中.

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.

这是我的上下文课程

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);
        }
    }
}

在这里如何建立连接

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

这会引发错误

不能从用法中推断出方法'System.Data.Entity.Database.SqlQuery(string，params object [])'的类型参数.尝试显式指定类型参数. C:.NET Projects \ ScripterEngine \ ScripterEngine \ Controllers \ CampaignController.cs 42 27 ScripterEngine

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

如何执行此原始查询?

推荐答案

指定string作为类型参数.

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

这篇关于如何在不使用模型的情况下使用Entity Framework执行原始SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！