Is there a way to change the field order in the LLBLGen entity to match the database fields order?

Why?

Since we have an extremely crappy connections we have to override OnFetchEntityCollection and OnFetchEntity that the generated select statements use wild card instead of the field list in the select clause. Smt like this:

 Select dbo.tablename.* from table...

(thread http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=2933)

Everything worked fine until the latest version. It seems that you have changed the way fields are mapped inside the FetchEntityCollection (you probably use indexes instead the original database field name).

The result: database fields are mapped into wrong entity fields The possible resolution: synchronise field order in the llbl gen and the database. Is it possible?

Just followed the link to where you mentioned the performance problems but I thought I'd post this here since this thread is currently active.

One of the things I tried to improve the speed for a remote site was to transmit the data through an SSH tunnel with compression set to maximum. Not sure how much cputime it requires on the server but the application started running MUCH faster.

It wont help the renaming problem but you might not need to resort to override the SQL statment to use 'SELECT *'

The fields are still in the same order. The fetch logic however never relied on the order of the fields and still doesn't: it calculates ordinals and uses these to retrieve the values. What you may encounter is the order of the fields in a subtype fetch. If a subtype is in a hierarchy of type TargetPerEntity, it will have its own fields and the fields of its supertype, with its own fields at the end. Is this what you're running into?

We don't use subtypes. Database schema didn't change at all in last couple of weeks, and everything worked ok on the 1.0.2004.2 version of the LLBLGen. Last week we decided to start using the new version and after generating the code with the new verion of LLBLGen we started having problems.

Example: We have a database table "Table1" with the colums in the following order: 1. Id (int) 2. OrdinalNumber (int) 3. Value1 (varchar) 4. Value2 (varchar) ...

These are mappped in the Entity "Table1Entity" in the following order: 1.Id (int) 2.Value1 (string) 3. Value2 (string) 4. OrdinalNumber (int) ...

The generated code that we use to fetch data from the table "Table1" into it's collection is as follows:

Select Table1.* from Table1
where ....

(We do it by overriding the OnFetchEntityColletion event)

The result is that values of the Id column are fetched ok, but if we try to access the value of the OrdinalNumber column we get the InvalidCastExsception because it tries to return the value of the Value1 column which is of datatype string instead of the OrdinalNumber column which is of datatype int.

The same changes that we made to the templates for version 1.0.2004.2 we made for the 1.0.2005.1 version, and no tables, views... were added afther we changed to the new version (we didn't start using any of the new features yet).

We are using LLBLGen for last 1.5 years and have a large database (over 180 tables and about 130 views) and we only have tables mapped into entiites and views mapped into typed views (no views mapped into entities, no typed lists, no subtypes). The code is generated using the adapter templates.

SaX wrote:

We don't use subtypes. Database schema didn't change at all in last couple of weeks, and everything worked ok on the 1.0.2004.2 version of the LLBLGen. Last week we decided to start using the new version and after generating the code with the new verion of LLBLGen we started having problems.

Example: We have a database table "Table1" with the colums in the following order: 1. Id (int) 2. OrdinalNumber (int) 3. Value1 (varchar) 4. Value2 (varchar) ...

These are mappped in the Entity "Table1Entity" in the following order: 1.Id (int) 2.Value1 (string) 3. Value2 (string) 4. OrdinalNumber (int) ...

The generated code that we use to fetch data from the table "Table1" into it's collection is as follows:

Select Table1.* from Table1
where ....

(We do it by overriding the OnFetchEntityColletion event)

The result is that values of the Id column are fetched ok, but if we try to access the value of the OrdinalNumber column we get the InvalidCastExsception because it tries to return the value of the Value1 column which is of datatype string instead of the OrdinalNumber column which is of datatype int.

The same changes that we made to the templates for version 1.0.2004.2 we made for the 1.0.2005.1 version, and no tables, views... were added afther we changed to the new version (we didn't start using any of the new features yet).

We are using LLBLGen for last 1.5 years and have a large database (over 170 tables and about 80 views) and we only have tables mapped into entiites and views mapped into typed views (no views mapped into entities, no typed lists, no subtypes). The code is generated using the adapter templates.

Could you please check the following: in the EntityFieldFactory, look up the Create method for the particular entity. The order of the fields listed there is the order in which the fields will appear in the query. The fields object is used to produce the select list (in that order) and to retrieve the fields.

What has changed is that no longer GetValue(ordinal) is used but GetValues(). This gives an array. The fields object is used to build an Alias to Ordinal hashtable. When a fields object has to be filled with values, the field's alias is looked up in that hashtable, and the ordinal is then used to index in the array returned by Getvalues(). Because the fields object is used for producing the query in the first place, this always works.

What I'm now interested in is what the column order is if you do select * from table. Does that order match the order in the EntityFieldFactory?

I'll use the names from one of the the table/entity we have problems with:

Database table name: dktVrstaDokumenta_Statusi

Column order in the database (order of the fields when we fetch them using the "select * from dktVrstaDokumenta_statusi" in the Sql Query Analyzer): 1. Id (Pk) 2. Vdk_Id (Fk to the table dktVrsteDokumenata) 3. Oznaka (varchar - Uk) 4. Rbr (Int) 5. Naziv (Varchar) 6. StatusStavke (bit) 7. KorisnikUnosa (varchar) 8. VrijemeUnosa (datetime) 9. KorisnikPromjene (Varchar) 10.VrijemePromjene (datetime) 11.Timestamp 12.ProvjeraPrava (bit)

The columns appear in the same order in the Sql Server Enterprise Manager Table designer.

This table is mapped in the "VrstaDokumenta_Status" entity. Order of the columns for this entity in LLBLGen is as follows 1. Id (Pk) 2. Vdk_Id (Fk to the table dktVrsteDokumenata) 3. Oznaka (varchar - Uk) 4. Naziv (Varchar) 5. StatusStavke (bit) 6. KorisnikUnosa (varchar) 7. VrijemeUnosa (datetime) 8. KorisnikPromjene (Varchar) 9. VrijemePromjene (datetime) 10.Timestamp 11.Rbr (Int) 12.ProvjeraPrava (bit)

So, the Rbr field is in the 11th place in the LLBL, not in 4th (as in the database)

EntityFieldFactory create method:

/// <summary> Creates a new IEntityField2 instance for usage in the EntityFields object for the VrstaDokumenta_StatusEntity. Which EntityField is created is specified by fieldIndex</summary>
/// <param name="fieldIndex">The field which IEntityField2 instance should be created</param>
/// <returns>The IEntityField2 instance for the field specified in fieldIndex</returns>
public static IEntityField2 Create(VrstaDokumenta_StatusFieldIndex fieldIndex)
{
    IEntityField2 fieldToReturn = null;
    switch(fieldIndex)
    {
        case VrstaDokumenta_StatusFieldIndex.Id:
            fieldToReturn = new EntityField2("Id", "VrstaDokumenta_StatusEntity", typeof(System.Decimal), TypeDefaultValue.GetDefaultValue(typeof(System.Decimal)), true, (int)fieldIndex, 0, 0, 15, false, true, false);
            break;
        case VrstaDokumenta_StatusFieldIndex.Vdk_Id:
            fieldToReturn = new EntityField2("Vdk_Id", "VrstaDokumenta_StatusEntity", typeof(System.Decimal), TypeDefaultValue.GetDefaultValue(typeof(System.Decimal)), false, (int)fieldIndex, 0, 0, 15, true, false, false);
            break;
        case VrstaDokumenta_StatusFieldIndex.Oznaka:
            fieldToReturn = new EntityField2("Oznaka", "VrstaDokumenta_StatusEntity", typeof(System.String), TypeDefaultValue.GetDefaultValue(typeof(System.String)), false, (int)fieldIndex, 10, 0, 0, false, false, false);
            break;
        case VrstaDokumenta_StatusFieldIndex.Naziv:
            fieldToReturn = new EntityField2("Naziv", "VrstaDokumenta_StatusEntity", typeof(System.String), TypeDefaultValue.GetDefaultValue(typeof(System.String)), false, (int)fieldIndex, 100, 0, 0, false, false, false);
            break;
        case VrstaDokumenta_StatusFieldIndex.StatusStavke:
            fieldToReturn = new EntityField2("StatusStavke", "VrstaDokumenta_StatusEntity", typeof(System.Boolean), TypeDefaultValue.GetDefaultValue(typeof(System.Boolean)), false, (int)fieldIndex, 0, 0, 1, false, false, false);
            break;
        case VrstaDokumenta_StatusFieldIndex.KorisnikUnosa:
            fieldToReturn = new EntityField2("KorisnikUnosa", "VrstaDokumenta_StatusEntity", typeof(System.String), TypeDefaultValue.GetDefaultValue(typeof(System.String)), false, (int)fieldIndex, 128, 0, 0, false, false, false);
            break;
        case VrstaDokumenta_StatusFieldIndex.VrijemeUnosa:
            fieldToReturn = new EntityField2("VrijemeUnosa", "VrstaDokumenta_StatusEntity", typeof(System.DateTime), TypeDefaultValue.GetDefaultValue(typeof(System.DateTime)), false, (int)fieldIndex, 0, 3, 23, false, false, false);
            break;
        case VrstaDokumenta_StatusFieldIndex.KorisnikPromjene:
            fieldToReturn = new EntityField2("KorisnikPromjene", "VrstaDokumenta_StatusEntity", typeof(System.String), TypeDefaultValue.GetDefaultValue(typeof(System.String)), false, (int)fieldIndex, 128, 0, 0, false, false, false);
            break;
        case VrstaDokumenta_StatusFieldIndex.VrijemePromjene:
            fieldToReturn = new EntityField2("VrijemePromjene", "VrstaDokumenta_StatusEntity", typeof(System.DateTime), TypeDefaultValue.GetDefaultValue(typeof(System.DateTime)), false, (int)fieldIndex, 0, 3, 23, false, false, false);
            break;
        case VrstaDokumenta_StatusFieldIndex.Timestamp:
            fieldToReturn = new EntityField2("Timestamp", "VrstaDokumenta_StatusEntity", typeof(System.Byte[]), TypeDefaultValue.GetDefaultValue(typeof(System.Byte[])), false, (int)fieldIndex, 8, 0, 0, false, true, false);
            break;
        case VrstaDokumenta_StatusFieldIndex.Rbr:
            fieldToReturn = new EntityField2("Rbr", "VrstaDokumenta_StatusEntity", typeof(System.Int32), TypeDefaultValue.GetDefaultValue(typeof(System.Int32)), false, (int)fieldIndex, 0, 0, 10, false, false, false);
            break;
        case VrstaDokumenta_StatusFieldIndex.ProvjeraPrava:
            fieldToReturn = new EntityField2("ProvjeraPrava", "VrstaDokumenta_StatusEntity", typeof(System.Boolean), TypeDefaultValue.GetDefaultValue(typeof(System.Boolean)), false, (int)fieldIndex, 0, 0, 1, false, false, false);
            break;
    }
    return fieldToReturn;
}

So, the order of the fields when we use select * statment doesn't match the order in the EntityFieldFactory (the Rbr column is in different position).

Ok. To find what has caused this, I'd like to you to perform a test. Please first backup your current .lgp file.

Please refresh the catalog. Is the Rbr field now in the right spot?

Has this Rbr field been added after the table was already created and the llblgen pro project was created? (so it was inserted by a catalog refresh) ?

In catalog explorer, is the Rbr field also at the 4th spot or not?

Ok. To find what has caused this, I'd like to you to perform a test. Please first backup your current .lgp file. Please refresh the catalog. Is the Rbr field now in the right spot?

Before refresh: Rbr field is in the 11th spot. After refresh: It's still in the 11th spot.

If I delete the entity from catalog an then add it againt, then it is in the correct place, but I can't do that because the relations between entities would have to be renemed/hidden the same way that they were before I deleted the entity and that would cause too much work.

Has this Rbr field been added after the table was already created and the llblgen pro project was created? (so it was inserted by a catalog refresh) ?

I'm not 100% sure, but I think it was.

In catalog explorer, is the Rbr field also at the 4th spot or not?

Yes it is.

Thanks. I'll check it out. I've to check back with the design documents as well to see why index fixing was dropped and to see if it can be re-added for non-hierarchical fields.

The re-ordering comes from a refresh with a beta I think. Refreshing the catalog now, and a new field is inserted into the table, it will be mapped and inserted at the spot of the ordinal, like unmap/remap does.

I've now added a sort on ordinal in target after the refresh action, which should fix the problem in an already mapped entity after a refresh of the catalog. This will be available in the next build.

Can we get this new version as soon as possible so we don't have to go back to the version 1.0.2004? We have an update of the application coming this friday.

SaX wrote:

Can we get this new version as soon as possible so we don't have to go back to the version 1.0.2004? We have an update of the application coming this friday.

The update will be released later today, so that should be on time. I can email you the applicationcore assembly if you like.

I'll download them as soon as they are available.

Thanks for the best support there can be.

SaX wrote:

I'll download them as soon as they are available. Thanks for the best support there can be.

You're welcome

After you've upgraded the designer, you have to refresh your current catalog. It then should be solved for all entities. If not let me know. I've 2 more issues to go before I can push out the installer, which is in about an hour I think.

(edit) fix is now available. (designer core)

A related question...Why did you change the fetch code to use the order of the generated fieldnames to match values? In the older versions, you just used datasource.getordinal to find the index of a specific field. Was this change for performance reasons?

The reason I ask is that we've made changes to the fetch select query so that the data comes back in an ordinal order that happens to be different from the order that LLBLGEN uses. To fix this in your latest version, we've had to hack into dataaccessadapterbase and change it to use datasource.getordinal.

I know you don't think its a good idea to hack into your ORMSupportClasses, but, unfortunately, we need to

mikeg22 wrote:

A related question...Why did you change the fetch code to use the order of the generated fieldnames to match values? In the older versions, you just used datasource.getordinal to find the index of a specific field. Was this change for performance reasons?

Yes. THe GetOrdinal wasn't necessary. THe reason is this: to build the query select list (i.e. the list of fields in SELECT ) an EntityFields(2) object is used. ALL fields in that object are used in the selectlist, and in that order.

This means that if you have the entityfields object, you already have the right order. This knowledge is used to build the name to ordinal hashtable. The row fetcher then reads a row in one go with GetValues, into an object array. The values are then read from that array using the fieldname to ordinal hashtable. It's similar to how a dataadapter does it.

It shouldn't make a difference for you however: the fields in the entityfields object are in the order in which they should be in the table/view, and thus they will be returned in that order in the select list. I still use field - ordinal pairs to read the values to be sure in adapter, because in adapter, you use the entity fields of the database generic project, but the data comes from a database which might be a different database than the one which is used for the database generic project.

The reason I ask is that we've made changes to the fetch select query so that the data comes back in an ordinal order that happens to be different from the order that LLBLGEN uses. To fix this in your latest version, we've had to hack into dataaccessadapterbase and change it to use datasource.getordinal.

I know you don't think its a good idea to hack into your ORMSupportClasses, but, unfortunately, we need to

What exactly do you need so I can give you hints where to look. Going back to getordinal is not what you need to do IMHO to achieve the same goal.

Well, you're not going to like this, but we've overriden the command you build to call a retrieval stored procedure instead of doing a normal SELECT.

The reason we do this is that we have very stringent security concerns with row level as well as column level security, which depends on the user as well as the appliction defined 'role' the user is in (not using sql server user/role, but our own custom one). One of the things we needed to be able to do was do a SELECT Case... when determining which columns came back with which values. We could not figure out how to implement this very complex level of SELECT security using predicate objects, so we went to a GetBusinessObject stored procedure. There is a filter parameter that goes into this stored procedure which is pulled out of the command.text that you generate in the DQE.

So, in short, we have our own custom SELECT running which does not do anything to take into account the order of the columns in the table it is selecting from.

By the way, I agree with your thoughts on using stored procedures for CRUD operations , but the security we implement was just too complex for predicates.

Ok Well, you have to use what you're allowed to use .

I still don't understand why the ordinals are wrong though.

Otis wrote:

I still don't understand why the ordinals are wrong though.

You changed it so the fieldnametovalues hashtable is created based on the order of the fields created by the fieldfactories. The order of fields that comes back from one of our SELECT storedprocedures is different than this, so we have to use use datasource.getordinal when creating the fieldnametovalue hashtable. Its no big deal though, just wondering why you changed it.

mikeg22 wrote:

Otis wrote:

I still don't understand why the ordinals are wrong though.

You changed it so the fieldnametovalues hashtable is created based on the order of the fields created by the fieldfactories. The order of fields that comes back from one of our SELECT storedprocedures is different than this, so we have to use use datasource.getordinal when creating the fieldnametovalue hashtable. Its no big deal though, just wondering why you changed it.

One reason is performance. As I already know the order of the fields (at least, when the generated query is used ) I don't have to call the expensive GetOrdinal(string) method on the datareader. I already cached the results for subsequential reads (so the first row got hit by this only) but still, in a large table this did burn cycles which were unnecessary.

I didnt foresee the consequence you ran into though. I've to say, the proc generated resultset mismatch is one reason why I, up till now, never included proc-based fetch logic in the framework, because the mismatches lead to strange errors which are hard to track down.