SQL Server上的实体框架6代码优先:改为“数字(1,0)"；而不是“位" [英] Entity Framework 6 Code First on SQL Server: Map "bool" to "numeric(1,0)" instead of "bit"
问题描述
警告#0:在不久的将来不能升级到EF核心.
Forward warning #0: upgrading to EF core is not an option in the near future.
前警告#1:我无法将列类型更改为bit，因为这可能会破坏使用与我正在为其开发新应用程序相同的数据库的旧版VB应用程序.
Forward warning #1: I can't change the column type to bit because this could potentially break legacy VB apps that employ the very same db I'm developing a new app for.
前警告#2:我也不能采用int属性==>隐藏的bool属性方法，因为在针对Oracle数据库时，需要使用相同的模型(在Oracle decimal(1,0)中确实映射到了) bool没问题-我需要在SQL Server中进行同样的操作.
Forward warning #2: I also can't employ the int property ==> hidden bool property approach because the very same model needs to work when targeting an Oracle database (in Oracle decimal(1,0) does indeed get mapped to bool without issues - I need to make the same thing happen in SQL Server).
假设我们有一个像这样的简单表:
Let's assume we have a simple table like this one:
CREATE TABLE FOOBAR
(
FB_ID NUMERIC(11,0) PRIMARY KEY,
FB_YN NUMERIC(1,0) NOT NULL
);
INSERT INTO FOOBAR (FB_ID, FB_YN)
VALUES (1, 1), (2, 0);
一个简单的poco类:
A simple poco class:
public class FOOBAR
{
public long FB_ID {get; set;}
// [Column(TypeName = "numeric(1,0)")]
// ^--- doesn't work in ef6 => 'The store type 'numeric(1,0)' could not be found in the SQL Server provider manifest'
// ^--- allegedly this works in EF core with Microsoft.EntityFrameworkCore.Relational nuget package installed
// ^--- https://docs.microsoft.com/en-us/ef/core/modeling/relational/data-types
// ^--- but I couldn't find anything similar for EF 6
public bool FB_YN {get; set;}
}
同样简单的流利配置类:
And an equally simple fluent config class:
public class FOOBAR_FluentConfiguration : EntityTypeConfiguration<FOOBAR>
{
public FOOBAR_FluentConfiguration()
{
ToTable(tableName: "FOOBAR");
HasKey(x => x.FB_ID);
// Property(x => x.FB_YN).HasColumnType("numeric(1,0)");
// ^--- doesn't work in ef6 => 'The store type 'numeric(1,0)' could not be found in the SQL Server provider manifest'
// ^--- allegedly this works in EF core with Microsoft.EntityFrameworkCore.Relational nuget package installed
// ^--- but I couldn't find anything similar for EF 6
}
}
如注释中所述，任何试图说服ef6将<bool>映射到表中的<numeric(1,0)>列的尝试在运行时都会失败.我还尝试通过EF约定达到预期的效果:
As mentioned in the comments any of the attempt to convince ef6 to map <bool> to the <numeric(1,0)> column in table fail miserably at runtime. I have also tried achieving the desired effect via EF conventions:
public sealed class MsSqlConventions : Convention
{
public MsSqlConventions()
{
Properties<bool>().Configure(p => p.HasColumnType("numeric(1,0)")); //fails
}
}
此操作失败，并显示以下消息:
This fails with the following message:
在SQL Server提供程序清单中找不到存储类型'numeric(1,0)'
The store type 'numeric(1,0)' could not be found in the SQL Server provider manifest
与此同时:
public sealed class MsSqlConventions : Convention
{
public MsSqlConventions()
{
Properties<bool>().Configure(p => p.HasColumnType("numeric").HasPrecision(1, 0)); //fails
}
}
此操作失败，并显示以下消息:
This fails with the following message:
已为属性"FB_YN"配置了精度和小数位数.只能为小数属性配置精度和小数位数.
Precision and scale have been configured for property 'FB_YN'. Precision and scale can only be configured for Decimal properties.
我还尝试弄弄(丰富)SQL Server提供程序清单la:
I also tried to toy around with (enrich) the SQL Server provider manifest a la:
DbProviderServices.GetProviderManifest();
但是我还不能做出正面或反面的结果.任何见解都表示赞赏.
but I can't make heads or tails out of it (yet). Any insights appreciated.
推荐答案
这是一种将EF6扭曲成数字(1,0)列作为BIT列的方法.这不是最好的事情，我只在底部显示的场景中对其进行了测试，但是就我的测试而言，它可以可靠地运行.如果有人发现情况无法按计划进行的极端情况，请随时发表评论，我将改进这种方法:
Here's a way to arm-twist EF6 into handling numeric(1,0) columns as BIT columns. It's not the best thing ever and I've only tested it in the scenarios shown at the bottom but it works reliably as far as my testing goes. If someone detects a corner case where things do not go as planned feel free to drop a comment and I will improve upon this approach:
<!-- add this to your web.config / app.config -->
<entityFramework>
[...]
<interceptors>
<interceptor type="[Namespace.Path.To].MsSqlServerHotFixerCommandInterceptor, [Dll hosting the class]">
</interceptor>
</interceptors>
</entityFramework>
拦截器的实现:
// to future maintainers the reason we introduced this interceptor is that we couldnt find a way to persuade ef6 to map numeric(1,0) columns in sqlserver into bool columns
// to future maintainers we want this sort of select statement
// to future maintainers
// to future maintainers SELECT
// to future maintainers ...
// to future maintainers [Extent2].[FB_YN] AS [FB_YN],
// to future maintainers ...
// to future maintainers FROM ...
// to future maintainers
// to future maintainers to be converted into this sort of select statement
// to future maintainers
// to future maintainers SELECT
// to future maintainers ...
// to future maintainers CAST ([Extent2].[FB_YN] AS BIT) AS [FB_YN], -- the BIT cast ensures that the column will be mapped without trouble into bool properties
// to future maintainers ...
// to future maintainers FROM ...
// to future maintainers
// to future maintainers note0 the regex used assumes that all boolean columns end with the _yn postfix if your boolean columns follow a different naming scheme you
// to future maintainers note0 have to tweak the regular expression accordingly
// to future maintainers
// to future maintainers note1 notice that special care has been taken to ensure that we only tweak the columns that preceed the FROM part we dont want to affect anything
// to future maintainers note1 after the FROM part if the projects involved ever get upgraded to employ efcore then you can do away with this approach by simply following
// to future maintainers note1 the following small guide
// to future maintainers
// to future maintainers https://docs.microsoft.com/en-us/ef/core/modeling/relational/data-types
// to future maintainers
public sealed class MsSqlServerHotFixerCommandInterceptor : IDbCommandInterceptor
{
public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
HotfixFaultySqlCommands(command, interceptionContext);
}
public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
HotfixFaultySqlCommands(command, interceptionContext);
}
public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
HotfixFaultySqlCommands(command, interceptionContext);
}
public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
}
public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
}
public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
}
static private void HotfixFaultySqlCommands<TResult>(IDbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
{
if (!command.CommandText.TrimStart().StartsWith("SELECT", ignoreCase: true, culture: CultureInfo.InvariantCulture))
return;
command.CommandText = BooleanColumnSpotter.Replace(command.CommandText, "CAST ($1 AS BIT)");
}
static private readonly Regex BooleanColumnSpotter = new Regex(@"((?<!\s+FROM\s+.*)([[][a-zA-Z0-9_]+?[]][.])?[[][a-zA-Z0-9_]+[]])(?=\s+AS\s+[[][a-zA-Z0-9_]+?_YN[]])", RegexOptions.IgnoreCase);
}
一些快速测试:
{
// -- DROP TABLE FOOBAR;
//
// CREATE TABLE FOOBAR (
// FB_ID NUMERIC(11,0) PRIMARY KEY,
// FB_YN NUMERIC(1,0) NOT NULL,
// FB2_YN NUMERIC(1,0) NULL
// );
//
// INSERT INTO FOOBAR (FB_ID, FB_YN, FB2_YN)
// VALUES (1, 0, 0);
//
// INSERT INTO FOOBAR (FB_ID, FB_YN, FB2_YN)
// VALUES (2, 1, 1);
//
// INSERT INTO FOOBAR (FB_ID, FB_YN, FB2_YN)
// VALUES (3, 1, null);
var mainDatabaseContext = new YourContext(...);
var test1 = mainDatabaseContext.Set<FOOBAR>().ToList();
var test2 = mainDatabaseContext.Set<FOOBAR>().Take(1).ToList();
var test3 = mainDatabaseContext.Set<FOOBAR>().Take(10).ToList();
var test4 = mainDatabaseContext.Set<FOOBAR>().FirstOrDefault();
var test5 = mainDatabaseContext.Set<FOOBAR>().OrderBy(x => x.FB_ID).ToList();
var test6 = mainDatabaseContext.Set<FOOBAR>().Take(10).Except(mainDatabaseContext.Set<FOOBAR>().Take(10)).SingleOrDefault();
var test7 = mainDatabaseContext.Set<FOOBAR>().Where(x => x.FB_ID == 1).ToList();
var test8 = mainDatabaseContext.Set<FOOBAR>().Where(x => x.FB_YN).ToList();
var test9 = (
from x in mainDatabaseContext.Set<FOOBAR>()
join y in mainDatabaseContext.Set<FOOBAR>() on x.FB_ID equals y.FB_ID into rightSide
from r in rightSide.DefaultIfEmpty()
select r
).ToList();
var test10 = (
from x in mainDatabaseContext.Set<FOOBAR>()
join y in mainDatabaseContext.Set<FOOBAR>() on new {x.FB_YN, FB_YN2 = x.FB2_YN} equals new {y.FB_YN, FB_YN2 = y.FB2_YN} into rightSide
from r in rightSide.DefaultIfEmpty()
select r
).ToList();
}
这篇关于SQL Server上的实体框架6代码优先:改为“数字(1,0)"；而不是“位"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
