Avviso di inoltro n. 0: l'aggiornamento a EF core non è un'opzione nel prossimo futuro.
Avviso di inoltro n. 1: Non riesco a modificare il tipo di colonna in bit
perché questo potrebbe potenzialmente interrompere le app legacy VB che impiegano lo stesso db per cui sviluppo una nuova app.
Forward warning n. 2: Non posso nemmeno utilizzare l' int
property ==> approccio della proprietà bool nascosta perché lo stesso modello deve funzionare quando si mira a un database Oracle (in Oracle decimal(1,0)
viene effettivamente mappato a bool
senza problemi - Ho bisogno di fare accadere la stessa cosa in SQL Server).
Supponiamo di avere una tabella semplice come questa:
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);
Una semplice classe poco:
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;}
}
E una classe di configurazione fluente altrettanto semplice:
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
}
}
Come menzionato nei commenti, qualsiasi tentativo di convincere ef6 a mappare <bool>
nella colonna <numeric(1,0)>
nella tabella fallisce miseramente in fase di runtime. Ho anche provato a ottenere l'effetto desiderato tramite le convenzioni EF:
public sealed class MsSqlConventions : Convention
{
public MsSqlConventions()
{
Properties<bool>().Configure(p => p.HasColumnType("numeric(1,0)")); //fails
}
}
Questo fallisce con il seguente messaggio:
Impossibile trovare il tipo di archivio 'numerico (1,0)' nel manifest del provider di SQL Server
Mentre questo:
public sealed class MsSqlConventions : Convention
{
public MsSqlConventions()
{
Properties<bool>().Configure(p => p.HasColumnType("numeric").HasPrecision(1, 0)); //fails
}
}
Questo fallisce con il seguente messaggio:
Precisione e scala sono state configurate per la proprietà "FB_YN". Precisione e scala possono essere configurate solo per le proprietà decimali.
Ho anche provato a giocare con (arricchire) il provider di SQL Server manifest a la:
DbProviderServices.GetProviderManifest();
ma non riesco a farne testa o croce (ancora). Qualsiasi comprensione apprezzata.
Ecco un modo per armare l'EF6 nella gestione di colonne numeriche (1.0) come colonne BIT. Non è la cosa migliore di sempre e l'ho solo testato negli scenari mostrati in basso, ma funziona in modo affidabile per quanto riguarda i miei test. Se qualcuno rileva un caso in cui le cose non vanno come previsto, sentitevi liberi di lasciare un commento e io migliorerò questo approccio:
<!-- add this to your web.config / app.config -->
<entityFramework>
[...]
<interceptors>
<interceptor type="[Namespace.Path.To].MsSqlServerHotFixerCommandInterceptor, [Dll hosting the class]">
</interceptor>
</interceptors>
</entityFramework>
E l'implementazione dell'intercettatore:
// 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);
}
E alcuni test rapidi:
{
// -- 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();
}