DevExpress XAF is a framework for .NET and Visual Studio developers who wants to create Office-like LOB apps for desktop, web and mobile faster and without hiring a large team.
Thursday, March 19, 2015
Making sure a property value is unique with XAF and XPO
I wanted to share a link to a hot Support Center discussion on the subject (it has now been unpublished by the author, sorry) with the community members as I believe this business task is not unique:-) and many others should be interested in knowing how to properly handle this.
I am going to detail technical considerations of possible solutions at the application UI and database levels depending on various configurations of your data model including inheritance mapping options (Table per Hierarchy or Table per Type) and soft deletion.
Application UI level
First of all, let's start from the UI part. How would a developer of an XAF app ensure that its end-users are not allowed to save records whose property value or combination of values are unique?
As you know, XAF ships with a built-in validation module that provides a powerful and extendable validation engine and a large set of predefined attributes to configure common validation rules declaratively. To ensure uniqueness, you can first add the ValidationModule component into your XAF module or application via the designers and then choose from the two built-in rules: RuleCombinationOfPropertiesIsUnique and RuleUniqueValue (it is also possible to turn RuleObjectExists and RuleFromBoolPropert for the same task, but its use is not that straightforward use and I will not talk about it for now). Depending on your preferences, you can either annotate your data model classes with the corresponding code attributes, e.g.:
[RuleUniqueValue]
public string Name { ... }
or declare everything at the Application Model level via the Model Editor tool (learn more...). In addition, you can configure the rule's CriteriaEvaluationBehavior parameter, to specify whether to look for modified objects that are currently loaded in memory, in addition to objects in the database itself.
As a result, the XAF's validation engine will consider the rules you configured when a data record is being saved (technically, when the View.ObjectSpace.Committing event is raised) and will throw ValidationException when uniqueness is violated. This is a special exception type, which is caught by other system controllers to display the validation error in a nice way in the UI:
This would be our topmost or basic protection level as it handles user input only. It will not help avoid duplicates if the same data records are created in code by a developer who did not call one of the Validator.RuleSet.ValidateXXX methods.
Also, this will not be suitable when secured data is filtered with SecuredObjectSpaceProvider or middle-tier application server as the validation engine does not "see" all data, but rather only the data the current user has access to (something that can be improved in the future). Finally, the XAF validation module cannot guarantee unique values in a scenario where the validation check and insert data actions are separate operations executed sequentially, e.g., on different computers in parallel or when data is saved from several views within the same app (example).
To achieve the desired effect in the latter scenarios, validation checks on the client are insufficient and value uniqueness must be verified on the database server side. We will talk about this below.
Before we proceed to the database, I wanted to briefly describe an alternative way to perform validation at the application level, which may be helpful in XPO-based apps not using XAF for the UI part. The idea is to throw an exception from the overridden OnSaving method of your persistent class - this is a good and universal place for last-chance checking. Throwing exceptions from within setters of your persistent properties is not a good idea as these exceptions can be swallowed by the default WinForms data-binding mechanism.
Database level
Defining a unique index or multicolumn unique indexes within your database table would be our "zero ring" of protection - the most trusted one. Records with duplicate values will not be able to bypass this ring regardless if they are created via the UI by end-users or in code by developers themselves or in any other scenario we discussed above.
Consideration 1:
Creating a unique index at the database level should always accompany any UI-based data uniqueness validation.
Creating an index is done differently for each database engine (e.g., check this MSDN article to learn more on doing this for MS SQL Server), so this is where our DevExpress ORM - XPO, comes to rescue. XPO provides the Indexed and Indices code attributes in the DevExpress.Xpo namespace to allow developers easily create indexes/indices for a variety of database engines. For instance:
[RuleUniqueValue]
[Indexed(Unique = true)]
public string Name { ... }
Take special note that these attributes are considered only when creating the database schema based on your ORM data model classes for the first time. If you have already created a database and then added these attributes, no index will be added in this case.
When using these attributes to create an unique index, it is very important to consider the following ORM features: Inheritance mapping and Deferred/soft deletion. The both features are not unique and can be seen in other ORM libraries, but they must be used by ORM users wisely (with understanding how it looks at the database level and affects the app). For instance, if you google for the latter feature on the Web, then you will be able to locate many interesting debates (one, two, three) on whether to use it or not. In XPO, users have great flexibility of controlling this, depending on various business requirements. For instance, you can control this feature by using a different base class or a special DeferredDeletionAttribute attribute. The XAF's BaseObject class has the deferred deletion feature enabled by default, which means that that when you delete a persistent object, XPO does not physically delete the record in the underlying data store. Instead, it marks the record as deleted via a service GCRecord field storing an integer value. This technique is useful and helps to avoid database exceptions when deleting objects that are referenced by other objects, which happens quite often.
Why did I start this talk about deleted records? Because when creating a unique index you should consider those soft-deleted records as they can prevent you from saving data entered into your application. As far as the application is concerned, these soft-deleted records do not exist (all records are selected with the GCRecord is null condition by default) and all validation checks at the UI level will pass, while there may be unique constraint violation exceptions at the database level:
Modifying your attribute as follows will help you avoid such errors:
[RuleUniqueValue]
[Indexed("GCRecord", Unique = true)]
public string Name { ... }
BTW, you can handle such database (and not only) errors in a custom way as per this Support Center article.
Consideration 2:
When deferred deletion is enabled, include the service GCRecord column into a unique index to avoid handling database exceptions at the application UI level.
As for the case when your data model classes inherit from each other (it's very common in large apps), it is important to consider inheritance mapping settings. By default in XPO, each persistent object type is stored in its own table with a distinctive set of columns associated with this type. The second strategy allows you to store all persistent properties and fields of a class in the same table as the properties of its parent class. XPO users can control this via the MapInheritanceAttribute when designing their data models.
What mapping strategy is used is very important for our original task as the service GCRecord field is stored in the base class table by default. This may create difficulties when we would like to create a unique index for it and a property from the descendant class, because their corresponding columns can physically be located in different database tables, which makes multicolumn unique index creation impossible...Technically, it is still possible to work around this when every class uses own table by creating a copy of the GCRecord field within the descendant class table, but this solution is quite awkward and I would recommend it only if nothing else is possible. See the E1484 example and its DerivedPersistentClass class in particular for more details.
Consideration 3:
When deferred deletion and persistent classes inheritance is used and you need to create a unique index for a descendant class property, consider mapping data from this descendant into the parent table via the MapInheritance(MapInheritanceType.OwnTable) attribute.
I hope these detailed explanations and considerations will help you choose the best solution for guaranteeing value uniqueness based on your particular data model configuration and settings. Do not hesitate to contact me in case of any questions.
Subscribe to:
Post Comments (Atom)
Does the impact of SkipNullOrEmptyValues on RuleCombinationOfPropertiesIsUniqueAttribute and RuleUniqueValueAttribute deserve a mention Dennis ? IIRC this can be a bit of a gotcha.
ReplyDeleteNews, Tips, Tricks And More About Devexpress Application Framework (Xaf) Directly From The Lab: Making Sure A Property Value Is Unique With Xaf And Xpo >>>>> Download Now
Delete>>>>> Download Full
News, Tips, Tricks And More About Devexpress Application Framework (Xaf) Directly From The Lab: Making Sure A Property Value Is Unique With Xaf And Xpo >>>>> Download LINK
>>>>> Download Now
News, Tips, Tricks And More About Devexpress Application Framework (Xaf) Directly From The Lab: Making Sure A Property Value Is Unique With Xaf And Xpo >>>>> Download Full
>>>>> Download LINK iw
Thanks for your comment, Chris. Yes, I think it will be nice to consider for any rule type. I will leave the link to the docs (https://documentation.devexpress.com/#eXpressAppFramework/DevExpressPersistentValidationRuleBaseAttribute_SkipNullOrEmptyValuestopic) for readers of this blog blog.
ReplyDeleteDang it, GCRecord... How can I missed that part (facepalm). Thanks' Dennis, changing my codes now...
ReplyDeleteYou're always welcome!
DeleteI have an implementation with this scenario, but Mysql as database... In mysql UNIQUE keys ignore nullable fields. Thus causes a possibility to have duplicate registers in database. E.g.:
ReplyDeleteOid Key Gcrecord
1 1 null
2 1 null
For Mysql null values are different from other null values, so... I don't know if I should drop the deferred deletion from my application or if there is a simplier way to change the default null value of gcrecord for something like 0 or - 1 without reimplementing a custom objectspace, data layer, etc....
Do you know any better and faster solution without affecting the core so hard?
Thanks for the help.
While I do not have a ready solution for this MySQL behavior, I think it is best to start researching for possible solutions in MySQL-related support forums and documentation: https://www.google.com/search?q=mysql+UNIQUE+keys+ignore+nullable+fields
DeleteFor instance, I found this thread, which may be a good start:
https://stackoverflow.com/questions/3712222/does-mysql-ignore-null-values-on-unique-constraints
If you do not find a suitable solution at the MySQL level, contact our support team using the https://www.devexpress.com/ask service so that we can research other options at the XPO/XAF levels.
News, Tips, Tricks And More About Devexpress Application Framework (Xaf) Directly From The Lab: Making Sure A Property Value Is Unique With Xaf And Xpo >>>>> Download Now
ReplyDelete>>>>> Download Full
News, Tips, Tricks And More About Devexpress Application Framework (Xaf) Directly From The Lab: Making Sure A Property Value Is Unique With Xaf And Xpo >>>>> Download LINK
>>>>> Download Now
News, Tips, Tricks And More About Devexpress Application Framework (Xaf) Directly From The Lab: Making Sure A Property Value Is Unique With Xaf And Xpo >>>>> Download Full
>>>>> Download LINK