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
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 theIsIdentity
property in this particular datastore we can find the following code in DataSetStoreHelpers.csprivate 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: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 anIsKey
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 totrue
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:
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.