与实体框架核心删除dbData.Database。我找不到一个解决方案来构建一个原始的SQL查询为我的全文搜索查询,将返回表数据和排名。
我所见过的在实体框架核心中构建原始SQL查询的唯一方法是通过dbData.Product。FromSql(“SQL脚本”);这是没有用的,因为我没有DbSet,将映射我在查询中返回的排名。
有什么想法?
与实体框架核心删除dbData.Database。我找不到一个解决方案来构建一个原始的SQL查询为我的全文搜索查询,将返回表数据和排名。
我所见过的在实体框架核心中构建原始SQL查询的唯一方法是通过dbData.Product。FromSql(“SQL脚本”);这是没有用的,因为我没有DbSet,将映射我在查询中返回的排名。
有什么想法?
当前回答
你可以用这个:
public static class SqlQueryExtensions
{
public static IList<T> SqlQuery<T>(this DbContext db, string sql, params object[] parameters) where T : class
{
using (var db2 = new ContextForQueryType<T>(db.Database.GetDbConnection()))
{
// share the current database transaction, if one exists
var transaction = db.Database.CurrentTransaction;
if (transaction != null)
db2.Database.UseTransaction(transaction.GetDbTransaction());
return db2.Set<T>().FromSqlRaw(sql, parameters).ToList();
}
}
private class ContextForQueryType<T> : DbContext where T : class
{
private readonly DbConnection connection;
public ContextForQueryType(DbConnection connection)
{
this.connection = connection;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(connection, options => options.EnableRetryOnFailure());
base.OnConfiguring(optionsBuilder);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<T>().HasNoKey();
base.OnModelCreating(modelBuilder);
}
}
}
以及用法:
using (var db = new Db())
{
var results = db.SqlQuery<ArbitraryType>("select 1 id, 'joe' name");
//or with an anonymous type like this
var results2 = db.SqlQuery(() => new { id =1, name=""},"select 1 id, 'joe' name");
}
其他回答
在Core 2.1中,你可以这样做:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Query<Ranks>();
}
然后定义SQL过程,像这样:
public async Task<List<Ranks>> GetRanks(string value1, Nullable<decimal> value2)
{
SqlParameter value1Input = new SqlParameter("@Param1", value1?? (object)DBNull.Value);
SqlParameter value2Input = new SqlParameter("@Param2", value2?? (object)DBNull.Value);
List<Ranks> getRanks = await this.Query<Ranks>().FromSql("STORED_PROCEDURE @Param1, @Param2", value1Input, value2Input).ToListAsync();
return getRanks;
}
这样就不会在数据库中创建rank模型。
现在在你的控制器/动作中你可以调用:
List<Ranks> gettingRanks = _DbContext.GetRanks(value1,value2).Result.ToListAsync();
这样就可以调用原始SQL过程。
在其他答案的基础上,我写了这个助手来完成这个任务,包括示例用法:
public static class Helper
{
public static List<T> RawSqlQuery<T>(string query, Func<DbDataReader, T> map)
{
using (var context = new DbContext())
{
using (var command = context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
context.Database.OpenConnection();
using (var result = command.ExecuteReader())
{
var entities = new List<T>();
while (result.Read())
{
entities.Add(map(result));
}
return entities;
}
}
}
}
用法:
public class TopUser
{
public string Name { get; set; }
public int Count { get; set; }
}
var result = Helper.RawSqlQuery(
"SELECT TOP 10 Name, COUNT(*) FROM Users U"
+ " INNER JOIN Signups S ON U.UserId = S.UserId"
+ " GROUP BY U.Name ORDER BY COUNT(*) DESC",
x => new TopUser { Name = (string)x[0], Count = (int)x[1] });
result.ForEach(x => Console.WriteLine($"{x.Name,-25}{x.Count}"));
我计划在添加内置支持后尽快摆脱它。根据EF核心团队的Arthur Vickers的声明,这是2.0后的优先级。这个问题正在被追踪。
我的案例使用了存储过程而不是原始SQL
创建一个类
Public class School
{
[Key]
public Guid SchoolId { get; set; }
public string Name { get; set; }
public string Branch { get; set; }
public int NumberOfStudents { get; set; }
}
下面在我的DbContext类上添加
public DbSet<School> SP_Schools { get; set; }
执行存储过程。
var MySchools = _db.SP_Schools.FromSqlRaw("GetSchools @schoolId, @page, @size ",
new SqlParameter("schoolId", schoolId),
new SqlParameter("page", page),
new SqlParameter("size", size)))
.IgnoreQueryFilters();
查询数据:不存在实体
string query = "SELECT r.Name as roleName, ur.roleId, u.Id as userId FROM dbo.AspNetUserRoles AS ur INNER JOIN dbo.AspNetUsers AS u ON ur.UserId = u.Id INNER JOIN dbo.AspNetRoles AS r ON ur.RoleId = r.Id ";
ICollection<object> usersWithRoles = new List<object>();
using (var command = _identityDBContext.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
await _identityDBContext.Database.OpenConnectionAsync();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
usersWithRoles.Add(new {
roleName = reader.GetFieldValueAsync<string>(0).Result,
roleId = reader.GetFieldValueAsync<string>(1).Result,
userId = reader.GetFieldValueAsync<string>(2).Result
});
}
}
}
详细:
[HttpGet]
[Route("GetAllUsersWithRoles")]
public async Task<IActionResult> GetAllUsersWithRoles()
{
string query = "SELECT r.Name as roleName, ur.roleId, u.Id as userId FROM dbo.AspNetUserRoles AS ur INNER JOIN dbo.AspNetUsers AS u ON ur.UserId = u.Id INNER JOIN dbo.AspNetRoles AS r ON ur.RoleId = r.Id ";
try
{
ICollection<object> usersWithRoles = new List<object>();
using (var command = _identityDBContext.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
await _identityDBContext.Database.OpenConnectionAsync();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
usersWithRoles.Add(new {
roleName = reader.GetFieldValueAsync<string>(0).Result,
roleId = reader.GetFieldValueAsync<string>(1).Result,
userId = reader.GetFieldValueAsync<string>(2).Result
});
}
}
}
return StatusCode(200, usersWithRoles); // Get all users
}
catch (Exception e)
{
return StatusCode(500, e);
}
}
结果如下所示:
[
{
"roleName": "admin",
"roleId": "7c9cb1be-e987-4ec1-ae4d-e4c9790f57d8",
"userId": "12eadc86-6311-4d5e-8be8-df30799df265"
},
{
"roleName": "user",
"roleId": "a0d5ef46-b1e6-4a53-91ce-9ff5959f1ed8",
"userId": "12eadc86-6311-4d5e-8be8-df30799df265"
},
{
"roleName": "user",
"roleId": "a0d5ef46-b1e6-4a53-91ce-9ff5959f1ed8",
"userId": "3e7cd970-8c52-4dd1-847c-f824671ea15d"
}
]
不是直接针对OP的场景,但因为我一直在努力解决这个问题,我想放弃这些让DbContext更容易执行原始SQL的ex.方法:
public static class DbContextCommandExtensions
{
public static async Task<int> ExecuteNonQueryAsync(this DbContext context, string rawSql,
params object[] parameters)
{
var conn = context.Database.GetDbConnection();
using (var command = conn.CreateCommand())
{
command.CommandText = rawSql;
if (parameters != null)
foreach (var p in parameters)
command.Parameters.Add(p);
await conn.OpenAsync();
return await command.ExecuteNonQueryAsync();
}
}
public static async Task<T> ExecuteScalarAsync<T>(this DbContext context, string rawSql,
params object[] parameters)
{
var conn = context.Database.GetDbConnection();
using (var command = conn.CreateCommand())
{
command.CommandText = rawSql;
if (parameters != null)
foreach (var p in parameters)
command.Parameters.Add(p);
await conn.OpenAsync();
return (T)await command.ExecuteScalarAsync();
}
}
}