Hi Guys it's me again ... sorry i've been working on another project for a bit.
I've just upgraded and have a bit of a problem.
I've got a fairly involved esuserdata.xml it includes around 80 aliases
<Table Name="Reservations"> <Columns> <Column Name="Date" Alias="[Date]" /> <Column Name="Table" Alias="TableNumber" /> </Columns> </Table> <Table Name="VATTransaction"> <Columns> <Column Name="Date" Alias="[Date]" /> </Columns> </Table>
I've just updated my project from the 0902 to this version and gone through and updated the references to included the new DynamicQuery and then as stated gone and regenerated my Generated Classes.
But it seems that the Aliases aren't being respected. I've gone and selected the metadata nodes and traced down to the table/column and it shows the alias correctly.
What have I missed in the process to end up with over 400 errors based on the Alias names now being VB keywords (like when I first started on the project).
Found a few extra bits and pieces of interest.
Once you have created a template part for your Project you provide No way to Edit these lines in the UI.
<esSetting Name="PreserveUnderscores" Value="False" /> <esSetting Name="UseRawNames" Value="False" />
<Table Name="Reservations"> <Columns> <Column Name="Date" Alias="__Date___" /> <Column Name="Table" Alias="TableNumber" /> </Columns> </Table> <Table Name="VATTransaction"> <Columns> <Column Name="Date" Alias="__Date___" /> </Columns> </Table>
Also because this conversion project stated as an Access Database that I'm building from Doesn't Have Primary keys on a lot of tables (and no I can't change it and YES I know it SHOULD have primary keys).
So now I have to go through an manually edit out the LoadByPrimaryKey code in all the generated classes. Would it be possible to maybe make it that it doesn't insert that block of code if the table doesn't have any primary keys defined.
According to this the Aliases should work but of course they don't.
I know I could have become involved during your beta cycle, but of course I was in the middle of getting this ready for people to test with and they were chomping at the bit waiting for stuff so I would have been in a much worse place if I'd embarked on these changes during that phase.
I must apologize for being cranky but this has caused me no end of pain today tracing through and trying to get this to work, and I'm still not there.
ES2009 Q3 should utilize the Aliases set up in esUserData.xml. The default values for "PreserveUnderscores" and "UseRawNames" should not need to be hand-edited in your ES project file, since they default to ES2009 Q1 (and earlier) behavior.
I believe what may have happened is that when ES converted your esUserData.xml file to the ES2009 Q3 format on first use, it placed your MS Access Aliases under the SQL Server driver section. When you generate for MS Access those Aliases are being ignored.
The old esUserData format did not store provider specific user metadata. If you had two databases with the same name using two different providers, then they had to share the same user metadata. With the new ES2009 Q3 esUserData format, provider specific metadata can be stored. This gives us a great deal of flexibility when it comes to storing different Concurrency and AutoIncrementing PK information for different providers.
Here is an example of an old ES2009 Q1 esUserData.xml file:
<esUserData> <Databases> <Database p="AggregateDb.mdb" n=""> <Tables> <Table p="VB_AliasTest" n="VB_AliasTest"> <Columns> <Column p="Table" n="TableName" /> <Column p="Date" n="[Date]" /> </Columns> </Table> </Tables> </Database> </Databases> </esUserData>
Depending on the Driver selected on the ES Settings tab, it is possible that it converted like below:
<esUserData Version="2009.2.0831.0"> <Drivers> <Driver Name="SQL"> <Databases> <Database Name="AggregateDb.mdb"> <Tables> <Table Name="VB_AliasTest"> <Columns> <Column Name="Table" Alias="TableName" /> <Column Name="Date" Alias="[Date]" /> </Columns> </Table> </Tables> </Database> </Databases> </Driver> </Drivers> </esUserData>
This is what that ES2009 Q3 file should look like:
<esUserData Version="2009.2.0831.0"> <Drivers> <Driver Name="ACCESS"> <Databases> <Database Name="AggregateDb.mdb"> <Tables> <Table Name="VB_AliasTest"> <Columns> <Column Name="Table" Alias="TableName" /> <Column Name="Date" Alias="[Date]" /> </Columns> </Table> </Tables> </Database> </Databases> </Driver> </Drivers> </esUserData>
Notice the "Driver Name" tells ES what provider it applies to at generation time.
ES2009 Q3 should recognize your square brackets without replacing them with double/triple underscores or then searching/replacing them back to square brackets. But, they need to be under the correct driver section in esUserData.
If you need assistance getting it sorted out, you may email your ES2009 Q3 esUserData.xml file to our support email account and reference this thread in your email. We will take a look and see if there may have been some conversion issues.
David Neal Parsonswww.entityspaces.net
Regarding some of the other issues mentioned in your posts:
There are plans for greatly improving ES projects in ES2010. Being able to edit the Settings within our UI is one of the big-ticket items. Another is support for relative paths.
If you drill down to a Column on the ES Metadata tab, there is a property called "IsInPrimaryKey". That is read-only for now. To provide support for tables with no PK defined in the database schema, we plan to let you change that to true and save it. Instead of removing the LoadByPrimaryKey method, it would be generated using the user metadata defined column.
I've checked this further and now know exactly what's going on.
The problem is specifically with the generation of the FindByPrimaryKey, LoadByPrimaryKey, LoadByPrimaryKeyDynamic, LoadByPrimaryKeyStoredProcedure
If the alias contains [Keyword] then the Square Brackets [ ] are removed and just the keyword is placed in these locations.
If the Keyword isn't actually a Language keyword (VB.net) Default, Index, Date etc, then it doesn't present any error.
I've gone through the rest of the generated class and the [Keyword] is in place, but the Square Brackets [ ] aren't.
I really need these aliases because of the way that the program reads the data from Access and Converts it to SQL by using the fields in the generated data.
Here is hopefully enough info to reproduce this issue.
esuserdata.xml
<esUserData Version="2009.2.1012.0"> <Drivers> <Driver Name="ACCESS"> <Databases> <Database Name="IPS.MDB"> <Tables> <!-- Everything is fine in here--> </Tables> </Database> </Databases> </Driver> <Driver Name="SQL"> <Databases> <Database Name="IPSTransaction"><!--Everything has been culled for clarity--> <Table Name="aaaTestTable"> <Columns> <Column Name="Date" Alias="[Date]" /> <Column Name="Index" Alias="[Index]" /> </Columns> </Table> </Tables> </Database> </Databases> </Driver> </Drivers></esUserData>
SQL Script
CREATE TABLE [dbo].[aaaTestTable]( [Date] [datetime] NOT NULL, [Index] [int] NOT NULL, [NormalColumn] [varchar](20) NOT NULL, CONSTRAINT [PK_aaaTestTable] PRIMARY KEY CLUSTERED ( [Date] ASC, [Index] ASC))
The region from the Generated aaaTestTable.vb class, note the lack of the Square Brackets [ ] around date and index in the Function Definition etc
#Region "LoadByPrimaryKey" Public Overridable Function LoadByPrimaryKey(ByVal date As System.DateTime, ByVal index As System.Int32) As Boolean If Me.es.Connection.SqlAccessType = esSqlAccessType.DynamicSQL Return LoadByPrimaryKeyDynamic(date, index) Else Return LoadByPrimaryKeyStoredProcedure(date, index) End If End Function Public Overridable Function LoadByPrimaryKey(ByVal sqlAccessType As esSqlAccessType, ByVal date As System.DateTime, ByVal index As System.Int32) As Boolean If sqlAccessType = esSqlAccessType.DynamicSQL Return LoadByPrimaryKeyDynamic(date, index) Else Return LoadByPrimaryKeyStoredProcedure(date, index) End If End Function Private Function LoadByPrimaryKeyDynamic(ByVal date As System.DateTime, ByVal index As System.Int32) As Boolean Dim query As esaaaTestTableQuery = Me.GetDynamicQuery() query.Where(query.[Date].Equal(date), query.[Index].Equal(index)) return query.Load() End Function Private Function LoadByPrimaryKeyStoredProcedure(ByVal date As System.DateTime, ByVal index As System.Int32) As Boolean Dim parms As esParameters = New esParameters() parms.Add("Date", date) parms.Add("Index", index) Return Me.Load(esQueryType.StoredProcedure, Me.es.spLoadByPrimaryKey, parms) End Function#End Region
Note that in the Properties and so forth, the Aliases are being used.
#Region "Properties" Public Overrides Sub SetProperties(values as IDictionary) Dim propertyName As String For Each propertyName In values.Keys Me.SetProperty(propertyName, values(propertyName)) Next End Sub Public Overrides Sub SetProperty(name as string, value as object) If Me.Row Is Nothing Then Me.AddNew() End IF Dim col As esColumnMetadata = Me.Meta.Columns.FindByPropertyName(name) If Not col Is Nothing Then If value Is Nothing OrElse value.GetType().ToString() = "System.String" Then ' Use the strongly typed property Select Case name Case "[Date]" Me.str.[Date] = CType(value, string) Case "[Index]" Me.str.[Index] = CType(value, string) Case "NormalColumn" Me.str.NormalColumn = CType(value, string) End Select Else Select Case name Case "[Date]" If value Is Nothing Or value.GetType().ToString() = "System.DateTime" Then Me.[Date] = CType(value, Nullable(Of System.DateTime)) End If Case "[Index]" If value Is Nothing Or value.GetType().ToString() = "System.Int32" Then Me.[Index] = CType(value, Nullable(Of System.Int32)) End If Case Else End Select End If Else If Me.Row.Table.Columns.Contains(name) Then Me.Row(name) = value Else throw New Exception("SetProperty Error: '" + name + "' not found") End If End Sub ' <summary> ' Maps to aaaTestTable.Date ' </summary> Public Overridable Property [Date] As Nullable(Of System.DateTime) Get Return MyBase.GetSystemDateTime(aaaTestTableMetadata.ColumnNames.[Date]) End Get Set(ByVal value As Nullable(Of System.DateTime)) MyBase.SetSystemDateTime(aaaTestTableMetadata.ColumnNames.[Date], value) End Set End Property ' <summary> ' Maps to aaaTestTable.Index ' </summary> Public Overridable Property [Index] As Nullable(Of System.Int32) Get Return MyBase.GetSystemInt32(aaaTestTableMetadata.ColumnNames.[Index]) End Get Set(ByVal value As Nullable(Of System.Int32)) MyBase.SetSystemInt32(aaaTestTableMetadata.ColumnNames.[Index], value) End Set End Property ' <summary> ' Maps to aaaTestTable.NormalColumn ' </summary> Public Overridable Property NormalColumn As System.String Get Return MyBase.GetSystemString(aaaTestTableMetadata.ColumnNames.NormalColumn) End Get Set(ByVal value As System.String) MyBase.SetSystemString(aaaTestTableMetadata.ColumnNames.NormalColumn, value) End Set End Property #End Region
It is our *STRONG* recommendation that any Alias that you provide on the ES Metadata tab be a properly PascalCased name without any special characters. We understand that you can use VB keywords in code by surrounding them with square brackets. You can do the same in C# by preceding the keyword with an at-sign "@". But, column names/aliases are not just used for ES property names and LoadByPrimaryKey signatures. ES uses them to create ADO.NET parameter names and hierarchical property names.
Our templates currently do not give any special consideration to aliases with square brackets for VB, or at-signs for C#. The simple solution to an alias that produces compile errors, is to give the table/column an alias that does not, and re-generate.
While multi-provider mode requires that the table/column names be the same in both database schema, it does not require that the generated property name match the column name using escape characters for reserved words. The alias can be any valid property name. The code from the Generated master will handle mapping it to the column name, and the MetadataMap will map to the correct parameter data type for each database.
We appreciate the time you took to narrow down the issue. And, since this is apparently a breaking change that we did not anticipate, we may be able to slip in a fix to get LoadByPrimaryKey compiling in your case. But, a true solution that supports aliases that contain VB/C# keyword escape characters would need:
That would mean a lot of time and testing to support a special case that is neither necessary nor recommended. I do not see this happening any time soon.
For now, if you can change the aliases, re-generate, and do a search/replace in your application code for those properties, then you would not have to wait for our next maintenance release, and you would have a long term solution.
I understand that's it's a strong recommendation but on a conversion project, you can't go back in time and change the column names of things that you are converting. The way I wrote the project uses a lot of the generated objects that ES has for columns etc so I can dynamically read them from Access DB and move them to my SQL DB. If I needed to manually code each of the aliases, then I would have to create a lot of code to handle all the standard transformations.
This worked fine in the previous release. I upgraded as I've already seen 3 maintenance releases on it already and these aren't new features so I thought they would continue to work.
I know that the Database shouldn't contain Keywords but when the problem domain that I'm working in uses those words all the time. (Restaurants and Hospitality, Table, Date etc are words that relate to this domain)
For the collection stuff you don't need Aliases, as the issue only arises when you have the Keyword alias out on it's own.
Could you please give me an ETA on a fix so that I know what I need to do. Please let me know if you can't fix it in which case I will need to roll back to the older version of the product, but that's going to make me stuck between a rock and a hard place because I won't be able to take up the new versions. We really would like to stay up to date, as there is no point releasing our new product with nearing 10month old versions of your software, when an up-to-date version exists.