Ticket A2615
Visible to All Users

XPO - How to map persistent objects to database tables with compound or composite multi-column primary keys

created 20 years ago (modified 5 years ago)

Description:
Does XPO support tables, whose primary key is composed of several columns?

Answer:
Yes, XPO does support clustered key tables. You need to create a struct (Structure in VB.NET), which represents a multi-column key and an XPBaseObject derived class with a Key property definition. Here is the code for mapping to the Sales table in the MS SQL Pubs sample database:

C#
using System; using DevExpress.Xpo; using System.ComponentModel; public struct SalesKey { [Persistent("stor_id")] public string StorId {get; set;} [Persistent("ord_num")] public string OrdNum {get; set;} [Persistent("title_id")] public string TitleId {get; set;} } [Persistent("sales"), OptimisticLocking(false)] public class Sales : XPBaseObject { [Key, Persistent, Browsable(false)] public SalesKey Key {get; set;} [Persistent("payterms")] public string PayTerms {...} ... // other properties }
Visual Basic
Imports DevExpress.Xpo Public Structure SalesKey <Persistent("stor_id")> _ Public Property StorId As String <Persistent("ord_num")> _ Public Property OrdNum As String <Persistent("title_id")> _ Public Property TitleId As String End Structure <Persistent("sales"), OptimisticLocking(False)>_ Public Class Sale Inherits XPBaseObject <Key(), Persistent()> _ Public Property Key As SalesKey <Persistent("payterms")> _ Public Property PayTerms As String ... ' other properties End Class

IMPORTANT NOTES

  1. Composite keys are supported for legacy databases only and using them imposes some limitations on the default functionality. You shouldn't use them in new XPO applications, because a) it is always possible to avoid this by changing your database schema; b) it often indicate bad database design. In most cases while developing a new XPO-based application, e.g., using an Object Oriented model, you won't need compound keys (this is true for other popular ORMs like Entity Framework). If you cannot avoid composite keys in your database and these limitations do not meet your needs, read and manipulate this data using ADO.NET means and present it using custom controls in XAF views: Using a Custom Control that is not Integrated by Default.
  2. To assign a key value to a new object, override the AfterConstruction method.
  3. To let users assign key values using editors, create non-persistent wrapper-properties as shown below. Please note that validation is required in this scenario:
  • Do not allow changing key values if an object is already saved. Use the Session.IsNewObject method to check whether an object is saved.
  • Do not allow saving an object if a user did not assign all key values.
C#
using System; using DevExpress.Xpo; using System.ComponentModel; namespace YourProjectName { public struct TestKey { [Persistent, Browsable(false)] public string Key1 { get; set; } [Persistent, Browsable(false)] public string Key2 { get; set; } public TestKey(string key1, string key2) : this() { Key1 = key1; Key2 = key2; } } public class TestObject : XPLiteObject { public TestObject(Session session) : base(session) { } public override void AfterConstruction() { base.AfterConstruction(); Key = new TestKey("new value 1", "new value 2"); } [Key, Persistent, Browsable(false)] public TestKey Key { get; set; } [PersistentAlias("Key.Key1")] public string Key1 { get { return Key.Key1; } //set { Key = new TestKey(value, Key2); } // If you need to edit the key parts. } [PersistentAlias("Key.Key2")] public string Key2 { get { return Key.Key2; } //set { Key = new TestKey(Key1, value); } // If you need to edit the key parts. } } }
  1. Note that the ASPxGridView control requires additional adjustments to work with composite keys:
    ASPxGridView.KeyFieldName Property
    ASPxGridView.FindVisibleIndexByKeyValue Method
  2. We do not recommend including persistent objects to the composite key, as this design is not supported in some scenarios (e.g., when NestedUnitOfWork or Object Access Layer is used). If this is possible, store plain key values using simple type properties in the structure instead. See also: Support Persistent objects as a part of a composite key with NestedUnitOfWork.
    6. In the Middle Tier Security - WCF Service scenario, you should additionally register your structure type via the static WcfDataServerHelper.AddKnownType method. Call this method on the both server and client sides before the data server and client application are initialized:
C#
WcfDataServerHelper.AddKnownType(typeof(YourStructKeyType)); WcfDataServerHelper.AddKnownType(typeof(DevExpress.Xpo.Helpers.IdList));
  1. Starting with XAF v17.1.4, if you use business objects in an XAF application, decorate struct key properties with the TypeConverterAttribute with the StructTypeConverter<T> type to enable built-in DevExpress.Web.ASPxGridView functions.
  2. You may receive the "The entity type 'XXX' does not have any key properties. Please make sure that one or more key properties are defined for this entity type. " error with OData Service (XpoDataServiceV3) if you map your persistent class to a table or view with a multi-column key. This configuration is not supported. Since the XAF Mobile UI uses XPO OData Service, this will not work there as well.

See Also:
How to: Map a Database View to a Persistent Class
Basics of Creating Persistent Objects for Existing Data Tables
When and Why XPO Extends the Database Schema
How to prevent altering the legacy database schema when creating an XAF application
How to define a multi-field unique index in a persistent object
How to make user-friendly object identifiers

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.