这样做的实体框架核心5，需要安装

Microsoft.EntityFrameworkCore.Relational

助手扩展方法

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Storage;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;


public static class EfHelper
{
    public static DbTransaction GetDbTransaction(this IDbContextTransaction source)
    {
        return (source as IInfrastructure<DbTransaction>).Instance;
    }

    private class PropertyMapp
    {
        public string Name { get; set; }
        public Type Type { get; set; }

        public bool IsSame(PropertyMapp mapp)
        {
            if (mapp == null)
            {
                return false;
            }
            bool same = mapp.Name == Name && mapp.Type == Type;
            return same;
        }
    }

    public static IEnumerable<T> FromSqlQuery<T>(this DbContext context, string query, params object[] parameters) where T : new()
    {
        const BindingFlags flags = BindingFlags.Public | BindingFlags.Instance | BindingFlags.NonPublic;
        List<PropertyMapp> entityFields = (from PropertyInfo aProp in typeof(T).GetProperties(flags)
                                           select new PropertyMapp
                                           {
                                               Name = aProp.Name,
                                               Type = Nullable.GetUnderlyingType(aProp.PropertyType) ?? aProp.PropertyType
                                           }).ToList();
        List<PropertyMapp> dbDataReaderFields = new List<PropertyMapp>();
        List<PropertyMapp> commonFields = null;

        using (var command = context.Database.GetDbConnection().CreateCommand())
        {
            if (command.Connection.State != ConnectionState.Open)
            {
                command.Connection.Open();
            }
            var currentTransaction = context.Database.CurrentTransaction;
            if (currentTransaction != null)
            {
                command.Transaction = currentTransaction.GetDbTransaction();
            }
            command.CommandText = query;
            if (parameters.Any())
            {
                command.Parameters.AddRange(parameters);
            }
            using (var result = command.ExecuteReader())
            {
                while (result.Read())
                {
                    if (commonFields == null)
                    {
                        for (int i = 0; i < result.FieldCount; i++)
                        {
                            dbDataReaderFields.Add(new PropertyMapp { Name = result.GetName(i), Type = result.GetFieldType(i) });
                        }
                        commonFields = entityFields.Where(x => dbDataReaderFields.Any(d => d.IsSame(x))).Select(x => x).ToList();
                    }

                    var entity = new T();
                    foreach (var aField in commonFields)
                    {
                        PropertyInfo propertyInfos = entity.GetType().GetProperty(aField.Name);
                        var value = (result[aField.Name] == DBNull.Value) ? null : result[aField.Name]; //if field is nullable
                        propertyInfos.SetValue(entity, value, null);
                    }
                    yield return entity;
                }
            }
        }
    }

    /*
     * https://entityframeworkcore.com/knowledge-base/35631903/raw-sql-query-without-dbset---entity-framework-core
     */
    public static IEnumerable<T> FromSqlQuery<T>(this DbContext context, string query, Func<DbDataReader, T> map, params object[] parameters)
    {
        using (var command = context.Database.GetDbConnection().CreateCommand())
        {
            if (command.Connection.State != ConnectionState.Open)
            {
                command.Connection.Open();
            }
            var currentTransaction = context.Database.CurrentTransaction;
            if (currentTransaction != null)
            {
                command.Transaction = currentTransaction.GetDbTransaction();
            }
            command.CommandText = query;
            if (parameters.Any())
            {
                command.Parameters.AddRange(parameters);
            }
            using (var result = command.ExecuteReader())
            {
                while (result.Read())
                {
                    yield return map(result);
                }
            }
        }
    }
}

模型

public class UserModel
{
    public string Name { get; set; }
    public string Email { get; set; }
    public bool? IsDeleted { get; set; }
}

手动映射

List<UserModel> usersInDb = Db.FromSqlQuery
(
    "SELECT Name, Email FROM Users WHERE Name=@paramName",
    x => new UserModel 
    { 
        Name = (string)x[0], 
        Email = (string)x[1] 
    },
    new SqlParameter("@paramName", user.Name)
)
.ToList();

usersInDb = Db.FromSqlQuery
(
    "SELECT Name, Email FROM Users WHERE Name=@paramName",
    x => new UserModel 
    { 
        Name = x["Name"] is DBNull ? "" : (string)x["Name"], 
        Email = x["Email"] is DBNull ? "" : (string)x["Email"] 
    },
    new SqlParameter("@paramName", user.Name)
)
.ToList();

使用反射自动映射

List<UserModel> usersInDb = Db.FromSqlQuery<UserModel>
(
    "SELECT Name, Email, IsDeleted FROM Users WHERE Name=@paramName",
    new SqlParameter("@paramName", user.Name)
)
.ToList();

你也可以使用QueryFirst。和Dapper一样，这完全不在EF的范围内。与Dapper(或EF)不同的是，您不需要维护POCO，只需在真实环境中编辑sql sql，并根据DB不断对其进行重新验证。免责声明:我是QueryFirst的作者。

现在，直到EFCore有新的东西，我将使用命令 手动映射

  using (var command = this.DbContext.Database.GetDbConnection().CreateCommand())
  {
      command.CommandText = "SELECT ... WHERE ...> @p1)";
      command.CommandType = CommandType.Text;
      var parameter = new SqlParameter("@p1",...);
      command.Parameters.Add(parameter);

      this.DbContext.Database.OpenConnection();

      using (var result = command.ExecuteReader())
      {
         while (result.Read())
         {
            .... // Map to your entity
         }
      }
  }

尝试使用SqlParameter避免Sql注入。

 dbData.Product.FromSql("SQL SCRIPT");

FromSql不支持全查询。例如，如果你想包含一个WHERE子句，它将被忽略。

一些链接:

使用实体框架核心执行原始SQL查询

原始SQL查询

在实体框架6中，你可以执行如下内容

创建模态类为

Public class User
{
        public int Id { get; set; }
        public string fname { get; set; }
        public string lname { get; set; }
        public string username { get; set; }
}

执行Raw DQL SQl命令如下所示:

var userList = datacontext.Database.SqlQuery<User>(@"SELECT u.Id ,fname , lname ,username FROM dbo.Users").ToList<User>();

我使用Dapper绕过了Entity框架Core的这个约束。

IDbConnection.Query

正在处理带有多个参数的SQL查询或存储过程。 顺便说一下，它更快一些(参见基准测试)

Dapper很容易学。编写和运行带参数的存储过程需要15分钟。无论如何，你可以同时使用EF和Dapper。下面是一个例子:

 public class PodborsByParametersService
{
    string _connectionString = null;


    public PodborsByParametersService(string connStr)
    {
        this._connectionString = connStr;

    }

    public IList<TyreSearchResult> GetTyres(TyresPodborView pb,bool isPartner,string partnerId ,int pointId)
    {

        string sqltext  "spGetTyresPartnerToClient";

        var p = new DynamicParameters();
        p.Add("@PartnerID", partnerId);
        p.Add("@PartnerPointID", pointId);

        using (IDbConnection db = new SqlConnection(_connectionString))
        {
            return db.Query<TyreSearchResult>(sqltext, p,null,true,null,CommandType.StoredProcedure).ToList();
        }


        }
}

你可以在EF Core中执行原始sql -将这个类添加到你的项目中。 这将允许您执行原始SQL并获得原始结果，而不必定义POCO和DBSet。 参见https://github.com/aspnet/EntityFramework/issues/1862#issuecomment-220787464获取原始示例。

using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using System.Threading;
using System.Threading.Tasks;

namespace Microsoft.EntityFrameworkCore
{
    public static class RDFacadeExtensions
    {
        public static RelationalDataReader ExecuteSqlQuery(this DatabaseFacade databaseFacade, string sql, params object[] parameters)
        {
            var concurrencyDetector = databaseFacade.GetService<IConcurrencyDetector>();

            using (concurrencyDetector.EnterCriticalSection())
            {
                var rawSqlCommand = databaseFacade
                    .GetService<IRawSqlCommandBuilder>()
                    .Build(sql, parameters);

                return rawSqlCommand
                    .RelationalCommand
                    .ExecuteReader(
                        databaseFacade.GetService<IRelationalConnection>(),
                        parameterValues: rawSqlCommand.ParameterValues);
            }
        }

        public static async Task<RelationalDataReader> ExecuteSqlQueryAsync(this DatabaseFacade databaseFacade, 
                                                             string sql, 
                                                             CancellationToken cancellationToken = default(CancellationToken),
                                                             params object[] parameters)
        {

            var concurrencyDetector = databaseFacade.GetService<IConcurrencyDetector>();

            using (concurrencyDetector.EnterCriticalSection())
            {
                var rawSqlCommand = databaseFacade
                    .GetService<IRawSqlCommandBuilder>()
                    .Build(sql, parameters);

                return await rawSqlCommand
                    .RelationalCommand
                    .ExecuteReaderAsync(
                        databaseFacade.GetService<IRelationalConnection>(),
                        parameterValues: rawSqlCommand.ParameterValues,
                        cancellationToken: cancellationToken);
            }
        }
    }
}

下面是一个如何使用它的例子:

// Execute a query.
using(var dr = await db.Database.ExecuteSqlQueryAsync("SELECT ID, Credits, LoginDate FROM SamplePlayer WHERE " +
                                                          "Name IN ('Electro', 'Nitro')"))
{
    // Output rows.
    var reader = dr.DbDataReader;
    while (reader.Read())
    {
        Console.Write("{0}\t{1}\t{2} \n", reader[0], reader[1], reader[2]);
    }
}