Ticket T1021991
Visible to All Users

XPO - Auto generated field / Identity column generation support - Suggestion

created 4 years ago

Hi DevExpress Team,

Issue

a long standing issue with XPO seems to be that there is no possibility to generate a autogenerated field without also making it the primary key of the table (see KeyAttribute). In contrast, entity framework has the [DatabaseGenerated] attribute. I don't want to start a XPO vs EF battle here. I think in many ways XPO is better than EF, but this is one such area where I think EF is ahead and I don't really know what's the problem with supporting this using data-annotation/code-first.

My interpretation

Please correct me if I'm wrong as I'm actually looking for answers as to why this isn't supported still, since it get's asked quite often: For a long time, I guess the main reason was with respect to UPDATE (or INSERT) Statements where XPO would simply create a SET AutoIncrementColumn = @p statement for this column and thus try to set it's value. With the introduction of the FetchOnlyAttribute this problem seems to be alleviated and this attribute can be used with database-calculated columns.

So it seems part of the work was already achieved with the FetchOnlyAttribute. The feature that allows creating identity columns as primary keys is also present via the KeyAttribute.

Suggestion

It would be really nice if XPO could introduce a DatabaseGeneratedAttribute counterpart to achieve auto-incremenation without having to resort to altering the database schema manually.

The usual answers

How to generate a sequential number for a business object within a database transaction
How to make user-friendly object identifiers
XAF & XPO best practices: Unique auto increment/sequence number generation

I understand and have read these suggestions multiple times already. I'm basically just asking why this feature isn't available, if it's on the roadmap or not and basically what was the reason to not implement it yet.

Kind regards,
Chris

Comments (1)
C C
Christoph Weinzierl-Heigl [msg] 4 years ago

    I just wanted to add a couple more interesting findings on my endeavor to get non-primary-key identity columns that are created by XPO working.

    From what I can gather XPO uses its own DBTable, DBColumn, etc. classes. From instances of these classes an IDataStore calls UpdateSchema in order to create the Schema inside the DataStore (and this could be ANY datastore, a SQL Database, an In-Memory DataSet, etc.).

    Now here is the real deal:
    The XPO DBColumn actually has a member called IsIdentity. Stepping further into the source code it appears that this member could be what we're looking for. The actual behavior however depends on the datastore in use.

    For instance, I myself am using a DataSetDataStore in my Unit-Tests. Looking for usings of the IsIdentity property in this particular datastore we can find the following code in DataSetStoreHelpers.cs

    C#
    private static void Create(DataTable table, DBColumn column) { DataColumn dataColumn = table.Columns.Add(column.Name, DBColumn.GetType(column.ColumnType)); dataColumn.AutoIncrement = column.IsIdentity; if (column.ColumnType != DBColumnType.String || column.Size == 0) return; dataColumn.MaxLength = column.Size; }

    This actually creates a DataColumn with auto-increment. Yay :)

    BUT sadly this same behavior does not appear to work with the MSSqlConnectionProvider as columns that are created by this datastore use the following source code:

    C#
    public override string GetSqlCreateColumnFullAttributes( DBTable table, DBColumn column, bool forTableCreate) { string columnFullAttributes = this.GetSqlCreateColumnFullAttributes(table, column); if (!string.IsNullOrEmpty(columnFullAttributes)) return columnFullAttributes; string createColumnType = this.GetSqlCreateColumnType(table, column); string str1 = column.IsKey || !column.IsNullable ? createColumnType + " NOT NULL" : createColumnType + " NULL"; if (!column.IsIdentity) { if (!string.IsNullOrEmpty(column.DbDefaultValue)) { str1 = str1 + " DEFAULT " + column.DbDefaultValue; if (!forTableCreate) str1 += " WITH VALUES"; } else if (column.DefaultValue != null && column.DefaultValue != DBNull.Value) { MsSqlFormatterHelper.MSSqlServerVersion sqlServerVersion = new MsSqlFormatterHelper.MSSqlServerVersion(this.is2000, this.is2005, this.is2008, this.isAzure); string str2 = MsSqlFormatterHelper.FormatConstant(column.DefaultValue, sqlServerVersion); str1 = str1 + " DEFAULT " + str2; if (!forTableCreate) str1 += " WITH VALUES"; } } if (column.IsKey) { if (column.IsIdentity && (column.ColumnType == DBColumnType.Int32 || column.ColumnType == DBColumnType.Int64) && ConnectionProviderSql.IsSingleColumnPKColumn(table, column)) str1 += this.GetIsAzure() ? " IDENTITY" : " IDENTITY NOT FOR REPLICATION"; else if (column.ColumnType == DBColumnType.Guid && ConnectionProviderSql.IsSingleColumnPKColumn(table, column) && !this.GetIsAzure()) str1 += " ROWGUIDCOL"; } return str1; }

    We can see that here, the IsIdentity property is only used if the column is also an IsKey column, i.e., a primary key in XPO notation :(


    Conclusion

    So much for support of identity columns in XPO while creating the schema via XPO. It appears to be a mixed bag.
    For my scenario, i.e., unit-testing with the in-memory datastore it at least gives me the possibility to make this work although it's very "hacky". Basically we would have to create the XPO DBTable[] array and alter the columns we want to work as identity columns in that array by setting the IsIdentity property to true and then pass it to the UpdateSchema method mentioned at beginning. This creates a dataset with datatables and columns that actually use AutoIncrementation.

    Here's a small example of how this could be done in a XAF XPO unit-testing scenario:

    C#
    var dictionary = new ReflectionDictionary(); var assemblies = new[] { /* put assemblies of the types you want to register here */ }; // register enums and types EnumProcessingHelper.RegisterEnums(assemblies); XpoTypesInfoHelper.ForceInitialize(); var typesInfo = XpoTypesInfoHelper.GetTypesInfo(); var classInfos = dictionary.CollectClassInfos(assemblies); foreach (XPClassInfo item in dictionary.Classes) { typesInfo.RegisterEntity(item.ClassType); } // get the DBTable[] array var tableSchemas = dictionary.GetDataStoreSchema(classInfos); foreach (var table in tableSchemas) { // look for your table and column here if (table.Name == "<your-table-name>") { var column = table.Columns[0]; // find your column here column.IsIdentity = true; // set it as an identity column } }

    Afterwards you have to make sure that you actually call UpdateSchema on the datastore manually with the table array from above to make this work.

    Hope this does help anybody else out there.

    Answers approved by DevExpress Support

    created 4 years ago

    Hi Chris,

    I'm basically just asking why this feature isn't available, if it's on the roadmap or not

    Thank you for the detailed feedback. I agree that native support for auto-generated unique columns is useful in some usage scenarios. Unfortunately, XPO does not support this functionality out of the box and we have no plans to implement it.

    As you know, we prioritize our activity based on available resources, feedback from roadmap surveys, and suggestions we receive in our DevExpress Support Center.

    Please let me know if you require any clarification.

      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.