Map a Persistent Class to a Database View Which Has No Key Field

• Jun 19, 2024
• 5 minutes to read

With XAF, you can build new applications from scratch or maintain existing databases. The How to: Generate XPO Business Classes for Existing Data Tables topic describes how to use the design-time wizard that generates business classes for one or more data tables in the specified database at the same time. Additionally, the existing database can contain views (stored queries), which need to be accessed in an XAF application’s List Views, Analysis, and Reports. If the database view has a key column, you can map a persistent class to it using the same approach as mapping to a regular table.

This topic describes how to implement a persistent class mapped to a database view without a key. We used the “Northwind Traders” demo database, shipped with DXperience Suite and installed in %PUBLIC%\Documents\DevExpress Demos 24.1 \Components\Data\nwind.mdb by default, in this topic. Besides tables filled with demo data, this database includes several database views.

Note

This approach uses composite keys, which are not supported in the ASP.NET Core Blazor applications, and works only for WinForms and ASP.NET Web Forms applications.

1. Create a new XAF solution and follow the steps from the How to: Generate XPO Business Classes for Existing Data Tables topic. Do not forget to modify the connection string that connects your application to the nwind.mbd database.

2. Open the nwind.mbd database to see what views (queries) it contains. You can use Microsoft Office Access or any other MDB viewer application. In this example, the CustomerReports view is mapped to the CustomerReports persistent class:

   

   Note

   This view contains the ProductName, CompanyName, OrderDate and ProductAmount fields. These names are used when implementing the CustomerReports class.

3. Create a new CustomerReports persistent class (you can use the DevExpress 24.1 ORM Persistent Object template). Replace the automatically generated class declaration with the following code:

   C#

   using System;
   using System.ComponentModel;
   
   using DevExpress.ExpressApp.Xpo.Utils;
   using DevExpress.Persistent.Base;
   using DevExpress.Xpo;
   // ...
   [DefaultClassOptions]
   public class CustomerReports : XPLiteObject {
       public CustomerReports(Session session) : base(session) { }
       CustomerReportsViewKey fKey;
       [Key, Persistent]
       public CustomerReportsViewKey Key {
           get { return fKey; }
           set { SetPropertyValue(nameof(Key), ref fKey, value); }
       }
       [PersistentAlias("Key.ProductName")]
       public string ProductName { get { return Key.ProductName; } }
       [PersistentAlias("Key.CompanyName")]
       public string CompanyName { get { return Key.CompanyName; } }
       [PersistentAlias("Key.OrderDate")]
       public DateTime OrderDate { get { return Key.OrderDate; } }
       [PersistentAlias("Key.ProductAmount")]
       public string ProductAmount { get { return Key.ProductAmount; } }
   }
   [TypeConverter(typeof(StructTypeConverter<CustomerReportsViewKey>))]
   public struct CustomerReportsViewKey {
       [Persistent("ProductName"), Browsable(false)]
       public string ProductName;
       [Persistent("CompanyName"), Browsable(false)]
       public string CompanyName;
       [Persistent("OrderDate"), Browsable(false)]
       public DateTime OrderDate;
       [Persistent("ProductAmount"), Browsable(false)]
       public string ProductAmount;
   }
   

   VB.NET

   Imports System
   Imports System.ComponentModel
   
   Imports DevExpress.ExpressApp.Xpo.Utils
   Imports DevExpress.Persistent.Base
   Imports DevExpress.Xpo
   ' ...
   <DefaultClassOptions> _
   Public Class CustomerReports
       Inherits XPLiteObject
       Public Sub New(ByVal session As Session)
           MyBase.New(session)
       End Sub
       Private fKey As CustomerReportsViewKey
       <Key, Persistent, Browsable(False)> _
       Public Property Key() As CustomerReportsViewKey
           Get
               Return fKey
           End Get
           Set(ByVal value As CustomerReportsViewKey)
               SetPropertyValue(nameof(Key), fKey, value)
           End Set
       End Property
       <PersistentAlias("Key.ProductName")> _
       Public ReadOnly Property ProductName() As String
           Get
               Return Key.ProductName
           End Get
       End Property
       <PersistentAlias("Key.CompanyName")> _
       Public ReadOnly Property CompanyName() As String
           Get
               Return Key.CompanyName
           End Get
       End Property
       <PersistentAlias("Key.OrderDate")> _
       Public ReadOnly Property OrderDate() As DateTime
           Get
               Return Key.OrderDate
           End Get
       End Property
       <PersistentAlias("Key.ProductAmount")> _
       Public ReadOnly Property ProductAmount() As String
           Get
               Return Key.ProductAmount
           End Get
       End Property
   End Class
   <TypeConverter(GetType(StructTypeConverter(Of CustomerReportsViewKey)))> _
   Public Structure CustomerReportsViewKey
       <Persistent("ProductName")> _
       Public ProductName As String
       <Persistent("CompanyName")> _
       Public CompanyName As String
       <Persistent("OrderDate")> _
       Public OrderDate As DateTime
       <Persistent("ProductAmount")> _
       Public ProductAmount As String
   End Structure
   

   Each persistent class requires a primary key. The CustomerReports class is an XPLiteObject class descendant, which has no auto-generated key property. So, the Key property representing a composite key (a key formed by combining at least two or more columns) was implemented.

   The CustomerReportsViewKey struct defines columns which form a composite key. The struct requires the TypeConverter attribute, which enables ASPxGridView to recognize the key value and to process objects.

   Additionally, the CustomerReports class exposes properties corresponding to all the view’s columns. You can omit any property, but the composite key should still include all columns.

   Note

   • A database view can already have a key column. In this case, you do not need the composite key, and you should decorate the key property with the KeyAttribute.
   • The number of columns included in the composite key is limited. For instance, Microsoft SQL Server allows a maximum of 16 columns.

   If you do not want your class to have the same name as the database view, you can use a custom name, and decorate the class with the PersistentAttribute:

   C#

   [DefaultClassOptions, Persistent("CustomerReports")]
   public class MyCustomerReports : XPLiteObject {
       // ...
   }
   

   VB.NET

   <DefaultClassOptions, Persistent("CustomerReports")> _
   Public Class MyCustomerReports
       Inherits XPLiteObject
       ' ...
   End Class
   

4. Run the application. The “Customer Reports” object is available.

   

   You can use the CustomerReports class as the Data Type in Reports V2 and Analysis: