efcore / efcore.checkconstraints Goto Github PK
View Code? Open in Web Editor NEWAn Entity Framework Core plugin to automatically add check constraints in various situations
License: Apache License 2.0
An Entity Framework Core plugin to automatically add check constraints in various situations
License: Apache License 2.0
Attribute | Expression |
---|---|
[Compare("Y")] | X == Y |
[MinLength(8)] | X.Length >= 8 |
[Range(0, 10)] | X >= 0 && X <= 10 |
[RegularExpression("[0-9A-F]+") | Regexp.IsMatch(X, "[0-9A-F]+") |
[DataType(...)] | (predefined regular expressions) |
Originally dotnet/efcore#15410
CHECK
constraint for MinLengthAttribute
and StringLength.MinimumLength
not implemented.
\ref: /dotnet/efcore/issues/22180
Our team has orders not to use data annotation attributes and Check Constraints can be set up using the fluent configuration, but I hate hard-coded magic strings with a passion.
Hi are you going to add support for ef core 5 (not rc versions)?
I have a class hierarchy with an abstract ProductBase
class and two concrete classes inheriting from it: ProductType1
and ProductType2
.
The following (Postgres) DDL is generated for the products
table:
discriminator text not null
constraint "CK_products_Discriminator"
check (discriminator = ANY
(ARRAY ['ProductBase'::text, 'ProductType1'::text, 'ProductType2'::text]))
As you can see, a discriminator value for the abstract base class ProductBase
is included in the list while that discriminator value should never be in the table since the class is abstract.
Unless there is a special case I'm missing, I think only discriminators for concrete types should be added to the CHECK
constraint.
The link on the homepage
isn't working.
sftp:// ftps:// file://
(Happy to create a PR)
I haven't run exact example but looking at the code, we add enum check constraints for each entity type for each declared properties. If a property is declared on 2 siblings in a hierarchy and they are mapped to same column in database, it could potentially lead to adding same check constraint twice, which throws error in CheckConstraint ctor.
The regexp implied by the naming of the URLAttribute is a complete check of the URL
The current regular expression is ^(http://|https://|ftp://), which only determines the protocol type, and cannot validate a wrong http url such as "http://hogehoge".
Can I work on this correction?
I would like to make a revision and make a PR, please review.
Problem:
I tried everything I had in mind, but I couldn't get this package to work(generate the check constraints) with a mariadb database using Pomelo's nuget package.
After debugging the code of this library I found that in the file ValidationCheckConstraintConvention.cs there are some switches that make a comparison with the database provider name(image below for example)
The Pomelo nuget package has a different name (last two letters are lower case)
This makes the comparison to fail and the switch statement returns null, effectively preventing any check constraint from being created.
Possible solutions:
Change the string in the switch to match exactly the Pomelo nuget name.
-- or --
Make the comparison case invariant but this would require to change the switch statement in a series of if statements.
-- or --
Make the string uppercase and cast the _databaseProvider.Name to uppercase in the switch statement.
I'm using postgresql and usually everything is in lower case.
I've got a few properties of the type decimal
on my entities and they have a RangeAttribute
declared on them.
I don't see any CHECK
constraints in my migrations though.
I suspect this is due to this check:
I understand the rationale behind it but decimal
is kind of a special case, since it is not a primitive (CLR) type but a struct instead. There is no constructor overload on the RangeAttribute
for decimal
because attribute declarations can only have constant values (this is a CLR limitation) and since decimal
is not a primitive type, it can not be expressed as a constant in an attribute constructor.
This is why I declare RangeAttribute
on decimal
properties with float
or double
constants passed into the attribute as a workaround.
Perhaps the check could be extended with the following: if the range value type mismatches the mapped type but the range values can be converted back and forth to the mapped type successfully, the check constaint can be rendered after all. The idea is that this proves that no precision is lost in the conversion.
I believe this will cover most cases since often you'd want to limit the range to positive values or between 0 and 1 etc.
The UseEnumCheckConstraints doesn't generate a constraint for nullable enum (both as int or string) properties.
public class YourEntity
{
YourEnum? YourProperty { get ;set ;}
}
public enum YourEnum { Value1, Value2 }
The required attribute has a property that let's you choose if empty strings are allowed or not
I suggest that if the property is set to false, a lenght > 0 or lenght >=1 should be generated.
Example about a use case of the required parameter with the AllowEmptyStrings property set to false(which should be the default value):
seems that today's (8 Sept) EF Community Standup produced "CHECK ([OrderStatus] = 0 OR [OrderStatus] = 1)" and not the "CHECK ([OrderStatus] IN (0, 1))" described in this README.md
Originally raised by @henrikdahl8240 in dotnet/efcore#20880.
Note that we should only do this for discriminators marked as complete.
Trying to use the DB context in an integration test fails on initialization with System.InvalidOperationException
: Unable to resolve service for type 'Microsoft.EntityFrameworkCore.Storage.IRelationalTypeMappingSource' while attempting to activate 'EFCore.CheckConstraints.Internal.CheckConstraintsConventionSetPlugin'.
IRelationalTypeMappingSource
is not configured by Microsoft.Extensions.DependencyInjection.InMemoryServiceCollectionExtensions.AddEntityFrameworkInMemoryDatabase
It is configured by Microsoft.Extensions.DependencyInjection.SqlServerServiceCollectionExtensions.AddEntityFrameworkSqlServer
and other EF Core providers like this:
new EntityFrameworkRelationalServicesBuilder(serviceCollection)
.TryAdd<IRelationalTypeMappingSource, SqlServerTypeMappingSource>()
public class MyAppDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseEnumCheckConstraints();
}
}
// MyWebApplicationFactory for integration tests
public class MyWebApplicationFactory : WebApplicationFactory<Startup>
{
protected override void ConfigureTestServices(IServiceCollection s)
{
s.AddDbContext<MyAppDbContext>((_, opt) => opt.UseInMemoryDatabase("InMemory"));
}
}
Do not configure EFCore.CheckConstraints in tests.
Not sure, but it would be helpful if this plugin "just worked" with in-memory DB, even if it is not actually used for anything. This helps write integration tests without having to add workarounds.
Can this plugin maybe take a dependency on IServiceProvider
and manually try to resolve the dependencies, and log a warning if not found?
It would be nice to have validations for some official data, like CPF, CPNJ etc..
I willl work on this two first validations - CPF and CNPJ.
It seems like Range check constraints aren't always generated. If the datatype is int
it always works, however if we use byte
or float
then it's not always generating them.
Are you aware of this limitation? I can probably create a sample that demonstrates this.
I have a table-per-type hierarchy and added an enum value to a descendent class. The column is created on the descendent table, but the check constraint is being added to the base table in the resulting migrations, so SQL throws an error. I have similar constraints elsewhere, and they do not exhibit this behavior.
Applying migration '20201216161805_Initial migration'.
Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [dbo].[Activity] (
[Id] bigint NOT NULL IDENTITY,
[Description] nvarchar(1000) NULL,
[Name] nvarchar(50) NOT NULL,
CONSTRAINT [PK_Activity] PRIMARY KEY ([Id]),
CONSTRAINT [CK_Exercise_ExerciseTypeId_Enum_Constraint] CHECK ([ExerciseTypeId] IN (0, 1, 2, 3)),
);
Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'ExerciseTypeId'.
Invalid column name 'ExerciseTypeId'.
Invalid column name 'ExerciseTypeId'.
Invalid column name 'ExerciseTypeId'.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:4958c8d0-4066-4eae-aa93-a220e1a0b4da
Error Number:207,State:1,Class:16
Invalid column name 'ExerciseTypeId'.
Invalid column name 'ExerciseTypeId'.
Invalid column name 'ExerciseTypeId'.
Invalid column name 'ExerciseTypeId'.
The description of the model has been simplified, but the relevant parts are still there.
In this case there is a base type called "Activity", and a derived type called "Exercise". Exercise has an ExerciseType property, which is an enumeration. The property is being generated on the proper table, but the constraint is being created on the base table.
Hi Shay,
With 5.0.0, I'm getting a null reference exception adding a migration to an EFCore 5.01 context under .NET5. Target database is SQLServer.
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
---> System.NullReferenceException: Object reference not set to an instance of an object.
at EFCore.CheckConstraints.Internal.CheckConstraintsOptionsExtension.ExtensionInfo.PopulateDebugInfo(IDictionary`2 debugInfo)
at Microsoft.EntityFrameworkCore.Internal.ServiceProviderCache.<>c__DisplayClass4_0.<GetOrAdd>g__BuildServiceProvider|3()
at Microsoft.EntityFrameworkCore.Internal.ServiceProviderCache.<>c__DisplayClass4_0.<GetOrAdd>b__2(Int64 k)
at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
at Microsoft.EntityFrameworkCore.Internal.ServiceProviderCache.GetOrAdd(IDbContextOptions options, Boolean providerRequired)
at Microsoft.EntityFrameworkCore.DbContext..ctor(DbContextOptions options)
at Include.Domain.Models.Context.IncludeContext..ctor(DbContextOptions`1 options) in C:\Code\_Clients\AWH\IH\include-api\Include.Domain\Models\Context\Generated\IncludeContext.generated.cs:line 259
at Include.Domain.Models.Context.IncludeContextFactory.CreateDbContext(String[] args) in C:\Code\_Clients\AWH\IH\include-api\Include.Domain\Models\Context\Generated\IncludeContextFactory.generated.cs:line 47
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContextFromFactory(Type factory, Type contextType)
at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.<>c__DisplayClass13_2.<FindContextTypes>b__9()
at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(Func`1 factory)
at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(String contextType)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.AddMigration(String name, String outputDir, String contextType, String namespace)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigrationImpl(String name, String outputDir, String contextType, String namespace)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigration.<>c__DisplayClass0_0.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
No issues with 5.0.0-rc1.1; I've had to downgrade back to that one.
When enum is derived from ulong
and used as a type of a property that translates to a table column with TypeName
configured using
protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
configurationBuilder.Properties<ulong>(p => p.HaveColumnType("bigint"));
}
(regardless ColumnAttribute
presence), then enum values for constraint are generated as floating point numbers (double
or decimal
it is not clear).
When ColumnAttribute
and its TypeName
property are provided explicitly then enum values are generated correctly.
Attempt to create new migration for snapshot generated with string length constraints applied to owned entity property causes:
System.InvalidOperationException: Sequence contains no elements
at System.Linq.ThrowHelper.ThrowNoElementsException()
at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.DiffContext.GetTable(IEntityType entityType)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.<>c.<Diff>b__63_0(ICheckConstraint s, ICheckConstraint t, DiffContext c)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.DiffCollection[T](IEnumerable`1 sources, IEnumerable`1 targets, DiffContext diffContext, Func`4 diff, Func`3 add, Func`3 remove, Func`4[] predicates)+MoveNext()
at System.Linq.Enumerable.ConcatIterator`1.MoveNext()
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.Diff(ITable source, ITable target, DiffContext diffContext)+MoveNext()
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.DiffCollection[T](IEnumerable`1 sources, IEnumerable`1 targets, DiffContext diffContext, Func`4 diff, Func`3 add, Func`3 remove, Func`4[] predicates)+MoveNext()
at System.Linq.Enumerable.ConcatIterator`1.MoveNext()
at System.Linq.Enumerable.Any[TSource](IEnumerable`1 source)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationsModelDiffer.HasDifferences(IRelationalModel source, IRelationalModel target)
at Microsoft.EntityFrameworkCore.Migrations.Design.MigrationsScaffolder.RemoveMigration(String projectDir, String rootNamespace, Boolean force, String language)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.RemoveMigration(String contextType, Boolean force)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.RemoveMigrationImpl(String contextType, Boolean force)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.RemoveMigration.<>c__DisplayClass0_0.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Any migration with this constraints creates normally, but next actions always fail. Removing just created migration too.
I have two columns StartDate
EndDate
and I'd like to express the constraint with the expression:
HasCheckContaint(entity => entity.StartDate < entity.EndDate);
Sure, I can do this just with a sql string, but then I need to specify the exact column names properties are mapped to.
It would be great if this library could also provide fluent mapping APIs to allow mapping of check constraints without using attributes.
Example:
There is no HasMinLength()
fluent mapping method. So you have to use a [StringLength(2, MinimumLength = 2)]
attribute to get a min length check constraints.
Introduced in .NET 8.0 (docs).
Probably only feasible for databases where there's a matching SQL function to do this. Putting in the backlog, can investigate based on user demand.
Versions used:
6.0.0-preview.3.21201.2
5.0.1
Running dotnet ef migrations add Initial
results in:
Build started...
Build succeeded.
System.MissingMethodException: Method not found: 'System.String Microsoft.EntityFrameworkCore.RelationalEntityTypeExtensions.GetTableName(Microsoft.EntityFrameworkCore.Metadata.IEntityType)'.
at EFCore.CheckConstraints.Internal.EnumCheckConstraintConvention.ProcessModelFinalizing(IConventionModelBuilder modelBuilder, IConventionContext`1 context)
at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelFinalizing(IConventionModelBuilder modelBuilder)
at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.OnModelFinalizing(IConventionModelBuilder modelBuilder)
at Microsoft.EntityFrameworkCore.Metadata.Internal.Model.FinalizeModel()
at Microsoft.EntityFrameworkCore.ModelBuilder.FinalizeModel()
at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, ModelDependencies modelDependencies)
at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, ModelCreationDependencies modelCreationDependencies)
at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel(IModel modelFromOptions)
at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServicesBuilder.<>c.<TryAddCoreServices>b__7_3(IServiceProvider p)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactory(FactoryCallSite factoryCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.ResolveService(ServiceCallSite callSite, RuntimeResolverContext context, RuntimeResolverLock lockType, ServiceProviderEngineScope serviceProviderEngine)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.ResolveService(ServiceCallSite callSite, RuntimeResolverContext context, RuntimeResolverLock lockType, ServiceProviderEngineScope serviceProviderEngine)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(ServiceCallSite callSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine.<>c__DisplayClass1_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
at Microsoft.EntityFrameworkCore.DbContext.Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure<System.IServiceProvider>.get_Instance()
at Microsoft.EntityFrameworkCore.Infrastructure.Internal.InfrastructureExtensions.GetService[TService](IInfrastructure`1 accessor)
at Microsoft.EntityFrameworkCore.Infrastructure.AccessorExtensions.GetService[TService](IInfrastructure`1 accessor)
at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(Func`1 factory)
at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(String contextType)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.AddMigration(String name, String outputDir, String contextType, String namespace)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigrationImpl(String name, String outputDir, String contextType, String namespace)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigration.<>c__DisplayClass0_0.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Method not found: 'System.String Microsoft.EntityFrameworkCore.RelationalEntityTypeExtensions.GetTableName(Microsoft.EntityFrameworkCore.Metadata.IEntityType)'.
I'm very keen to use EFCore.CheckConstraints on SQL Server, yet I wouldn't want to use or require Regular Expression constraints on an on-premise SQL Server installation because that would involve additional setup efforts.
Can I disable regular expression check constraints even when RegularExpressionAttribute
is present in entity property?
When updating to version 8, EFCore.CheckConstraints
breaks EF code first usage.
I mainly use Postgres.
It turned out, that the usage of [Required]
attribute creates additional constraints, which did not show up in version 6 (and probably in version 7).
[Range(1, 5)]
on type int, MinLength
will added, at least with Postgres this will fail, because LENGTH()
is only supported for stringsId
property of type int, the same will happenStringLength()
two MinLength
constraints will be added, which fails immediately.This can be tested easily with EFCore.CheckConstraints.Test
unit tests:
Add [Required]
to Blog.Id
, Blog.Rating
and Blog.Required
.
Add this test as well:
[Fact]
public virtual void RequiredNoString()
{
var entityType = BuildEntityType<Blog>();
var constraints = entityType.GetCheckConstraints();
Assert.DoesNotContain(constraints, c => c.Name == "CK_Blog_Id_MinLength");
Assert.DoesNotContain(constraints, c => c.Name == "CK_Blog_Rating_MinLength");
}
It would be great, if this situation can be improved.
Originally raised by @henrikdahl8240 in dotnet/efcore#20868.
This could have some perf impact (not confirmed), and would make for much tighter SQL, especially for big enums. Could go all-out and detect multiple ranges of values too.
EntityFramework Core 7 (released Nov 2022) implements the support to JSON data soring/querying via Linq.
https://devblogs.microsoft.com/dotnet/announcing-ef7-release-candidate-2/
Microsoft suggests the use of the ISJSON CHECK to improve performance:
https://learn.microsoft.com/en-us/sql/relational-databases/json/optimize-json-processing-with-in-memory-oltp
EF does not seem to provide an option to enable the ISJSON check. Any plan to provide it in this extension?
Hi,
As implied in the readme I did initial setup for SQL Server and now this statement works:
SELECT dbo.RegexMatch('\b(\w+)\s+\1\b','this has has been repeated')
1
however migration script throws at this line:
CONSTRAINT [CK_Blogs_Address_Url] CHECK (dbo.RegexMatch('^(http://|https://|ftp://)', [Address])),
Msg 4145, Level 15, State 1, Line 25
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
It appears that the problem is that the CHECK constraint expects a boolean condition but the dbo.RegexMatch function from the red-gate article returns an integer.
Could the correct sql setup script be included somewhere in the readme? Or is it something else that may cause this error?
My (non-working) setup:
-- https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/tsql-regular-expression-workbench/#third
IF OBJECT_ID (N'dbo.RegexMatch') IS NOT NULL
DROP FUNCTION dbo.RegexMatch
GO
CREATE FUNCTION dbo.RegexMatch
(
@pattern VARCHAR(2000),
@matchstring VARCHAR(MAX)--Varchar(8000) got SQL Server 2000
)
RETURNS INT
AS BEGIN
DECLARE @objRegexExp INT,
@objErrorObject INT,
@strErrorMessage VARCHAR(255),
@hr INT,
@match BIT
SELECT @strErrorMessage = 'creating a regex object'
EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
--Specifying a case-insensitive match
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
--Doing a Test'
IF @hr = 0
EXEC @hr= sp_OAMethod @objRegexExp, 'Test', @match OUT, @matchstring
IF @hr <> 0
BEGIN
RETURN NULL
END
EXEC sp_OADestroy @objRegexExp
RETURN @match
END
GO
Commands completed successfully.
-- permissions
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ole Automation Procedures' changed from 1 to 1. Run the RECONFIGURE statement to install.
SELECT @@VERSION
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
Oct 28 2016 18:17:30
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 22621: )
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.