In the v3.0 generated code, can the ordinal position of a field be determined?

I see from the thread below, that ordinal cannot be specified, but it seems like it should still be able to be read, especially when using the database first approach.

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=18006&HighLight=1

Thanks for any info.

To know the ordinal position of a column in a DB table is not possible. I don't even know if this is available in DB metadata. I also don't see anything in FieldPersistenceInfo class that can help you here.

You can know the ordinal of a field in an entity via the FieldIndex property of an entity field. The fields in the entityfields object are in the order in which they should be in the table/view, so I think that is sufficient for you.

However, what happens if you have inheritance hierarchies? 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.

My advise is that you never rely on the order of the fields (they can change). And one of the things on OR/M world is that you don't have to worry for these kind of things. Why do you need this btw?

(Edit) Note for V3 (from manual):

Fields have no index anymore. This leads to a problem in which order PK fields and UC fields are emitted into the method signatures. The fields are ordered by their name, ascending. This could lead to a breaking change when migrating v2.x code to v3.x when a compound PK or UC with two or more fields with the same type are used. You have to examine calls to the fetch methods using PK and UC directives as well as CTor calls to entities with a PK of 2 or more fields.

The fields in the entityfields object are in the order in which they should be in the table/view, so I think that is sufficient for you.

That's my issue. They fields are now alphabetical whereas prior to 3.0 they matched the order of a table or view in the database.

In ASP.Net, I'm generating a list of columns for an html table at runtime.

Here's what used to work

For i As Integer = 0 To MyEntity.Fields.Count - 1 Response.Write(MyEntity.Fields(i).fieldAlias) Next

The order of the fields matched the order in the actual database prior to 3.0. I guess I could specify an attribute on each field for the ordinal, but it was nice to be able to infer that information.

Also thanks for the documentation quote. I just upgraded to 3.0 today and didn't realize that the documentation for the runtime now has its own document, so my next question was going to be where the list of breaking changes was located.

It would be nice to have links to each of the .chm files available from the help menu. Also, it might be nice to reference them on the Welcome page of the Designer documentation, so people don't forget about them.

Thanks

so my next question was going to be where the list of breaking changes was located.

Right there in the Migrating your code section.

Thanks for the info. I'm a big fan of LLBLGen and have been a customer since version 2.0

I found this thread which explains the reason for the change regarding field indexes. http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=18277

Frans says this:

In which order should the fields in the entity be stored? If we pick one, or the first one, and things change, e.g. we picked 'SQL Server' and you remove that from the project, what's the order become after that?

My suggestion is that it should definitely be possible to pick something. And when things change, pick whatever is left in the project, and if there are no databases left, then it can go to alphabetical.

David: regarding this statement

To know the ordinal position of a column in a DB table is not possible. I don't even know if this is available in DB metadata.

It is definitely possible to obtain an ordinal position from db metadata, here's the query for sql server: "select table_name, column_name, ordinal_position from information_schema.columns"

I have noticed that the ordinal is also written as an attribute in the xml project file in the "Tables" sections.

The fact that fields no longer have an index is not only a breaking change, but in some sense a loss of a feature. Previously, no additional work was required to bind data to grids which match the column order of the target database. Sometimes the database ordinal really is important, and especially so in working with legacy databases.

Prior to v3.0, A call to GetMulti(Nothing) would give you an a representation of your data which matched the database, equivalent to a SELECT *.

There is no longer a way to do that, or is there?

I would really appreciate any suggestions. Maybe the ordinal information from the project file could be made available in the generated classes?

Thanks

This won't be rolled back like it was, not for v3.0 and not in the future.

What we will add in the future is a way to specify an ordering of the entity fields in the designer. If that's based on an ordinal list or your own preference / grouping needs, that's up to you.

The ordinals are in the meta-data, they're just not affecting the generated code. The order in which properties are enlisted is also not necessarily the one of the fields list: in .NET there is no ordering of properties when you reflect over them. At the moment, the runtime looks into the fields list and this is the order in which the properties are shown in the 'automatic' scenario, but in many cases you want to change this anyway: some fields aren't suitable to show up, others need to be repositioned etc. That a legacy database has the columns in a certain order is not something an application should be based on. One ordering of the columns in the db will have a big impact on your application at runtime all of a sudden.

Hi

before every thing i should thank you for your great job.

One of the problems which occur with this change(change field order) is that: order of columns in select result is different from field index of code that is generated. so this cause some problems, for example for this method:

//TypedListDAO dao //IDataReader reader //List<IDataValueProjector> valueProjectors //DataProjectorToIEntityCollection projector typedListDAO.GetAsProjection(valueProjectors, projector, reader);

this method get a DataReader that is get from database. so the order of columns is like database selects. but valueProjectors and projector have alphabetic order. so this cause an exception in projection.

So i think you should consider this problems and have a solution for them.

Thanks So much

Best Regard Rahman

The query is generated based on the projections, I don't see how the db select can be different?

if you use a SP for query it will happen. look at this code:

query = RetrievalProcedures.GetaTableSpCallAsQuery(null);

IDataReader reader = dao.GetAsDataReader(transactionManager, query, CommandBehavior.CloseConnection)

DataProjectorToIEntityCollection projector = new DataProjectorToIEntityCollection(entityCollection);

typedListDAO.GetAsProjection(valueProjectors, projector, reader);

query retrieve a table. 'reader' has a database Field order, but valueProjectors and projector have alphabetic Field order.

Yes, if you return a resultset from a proc, then you have to re-order the projectors to match the proc resultset. That has always been the case: the proc resultset doesn't have to match the order of the fields in the entity, this also was the case in v2.

It's a breaking change in v3, and therefore needs attention. It might be cumbersome to overcome, however a necessary change.

Thanks so much for your attention Otis

you're right but everyone use "SELECT * FROM TABLE1" in a proc and nobody reorder Fields by alphabetic order. in v2 we used this and we hadn't any problem because the order of fields in "Select *" was equal the order of entity.

also you should consider for example a project by 100 Table and 100 proc for retrieve them (for security issue we just use proc for retrieving data ), then you should reorder 100 projectors to match the proc resultsets and it's very very cumbersome.

Best Wishes Rahman

I see, but how do you create your IDataValueProjector lists? You have to have code for that.

If you have written that code by hand, you can also generate them using a template: use a .lpt template to generate these IDataValueProjector list creation methods per entity, using the GroupableModelElementMapping object (which is the mapping of the entity) of the entity and it contains the FieldMapping objects. You can use that (as you have the mapped field and the mapped target field (and thus its ordinal)) to create the proper IDataValueProjector lists with the proper indexes. If you get stuck writing the template, please let us know (you can post in this thread).

The template will allow you to generate the projector code together with the entity code, and you don't have to maintain it by hand.

this is my code for converting proc to a collection, it is a Generic Method:

**  public EntityCollection ConvertStoredProcedureToCollection<EntityObject, EntityCollection>(IRetrievalQuery query, Transaction transactionManager)
        where EntityObject : EntityBase, new()
        where EntityCollection : EntityCollectionBase<EntityObject>, new()
    {
        //Create new instances of the EntityObject and EntityCollection.
        EntityObject entityObject = new EntityObject();
        EntityCollection entityCollection = new EntityCollection();
        //Get all the Fields of the EntityObject. These fields are necessary for fetching all the data from the resultset.
        IEntityFields entityFields = entityObject.Fields;
        using (query)
        {
            //Get the resultset of the Stored Procedure as an IDataReader.
            TypedListDAO dao = new TypedListDAO();
            using (IDataReader reader = dao.GetAsDataReader(transactionManager, query, CommandBehavior.CloseConnection))
            {
                //Create a new instance of List<IDataValueProjector>. In this the values which will be fetched will be stored.
                List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>(entityFields.Count);
                foreach (EntityField entityField in entityFields)
                {
                    //Put each entityField in List<IDataValueProjector>.
                    valueProjectors.Add(new DataValueProjector(entityField.Alias, entityField.FieldIndex, entityField.DataType));
                }
                //Create the projector and fill the collection.
                DataProjectorToIEntityCollection projector = new DataProjectorToIEntityCollection(entityCollection);

                dao.GetAsProjection(valueProjectors, projector, reader);
                reader.Close();
            }
        }
        return entityCollection;
    }**

i don't know how to change it to use GroupableModelElementMapping, can i?

There was an example posted here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=18035&StartAtMessage=0&#102445 For how to use the GetGroupableModelElementMapping() in a template.

r_khanipour wrote:

this is my code for converting proc to a collection, it is a Generic Method:

**  public EntityCollection ConvertStoredProcedureToCollection<EntityObject, EntityCollection>(IRetrievalQuery query, Transaction transactionManager)
        where EntityObject : EntityBase, new()
        where EntityCollection : EntityCollectionBase<EntityObject>, new()
    {
        //Create new instances of the EntityObject and EntityCollection.
        EntityObject entityObject = new EntityObject();
        EntityCollection entityCollection = new EntityCollection();
        //Get all the Fields of the EntityObject. These fields are necessary for fetching all the data from the resultset.
        IEntityFields entityFields = entityObject.Fields;
        using (query)
        {
            //Get the resultset of the Stored Procedure as an IDataReader.
            TypedListDAO dao = new TypedListDAO();
            using (IDataReader reader = dao.GetAsDataReader(transactionManager, query, CommandBehavior.CloseConnection))
            {
                //Create a new instance of List<IDataValueProjector>. In this the values which will be fetched will be stored.
                List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>(entityFields.Count);
                foreach (EntityField entityField in entityFields)
                {
                    //Put each entityField in List<IDataValueProjector>.
                    valueProjectors.Add(new DataValueProjector(entityField.Alias, entityField.FieldIndex, entityField.DataType));
                }
                //Create the projector and fill the collection.
                DataProjectorToIEntityCollection projector = new DataProjectorToIEntityCollection(entityCollection);

                dao.GetAsProjection(valueProjectors, projector, reader);
                reader.Close();
            }
        }
        return entityCollection;
    }**

i don't know how to change it to use GroupableModelElementMapping, can i?

GroupableModelElementMapping is for generating code, so to use it in a template, not at runtime.

I don't really know what the above code has to do with call procs, as the method above creates a dynamic query and the query result should be the same as the fields used to produce the query.

What I'd like to know is how you create the projectors for the call to a stored procedure. The thing is that the datareader contains a list of fields (the resultset) which can be used to build the projectors.

Otis wrote:

This won't be rolled back like it was, not for v3.0 and not in the future.

Understood.

Otis wrote:

What we will add in the future is a way to specify an ordering of the entity fields in the designer. If that's based on an ordinal list or your own preference / grouping needs, that's up to you.

Great!

Otis wrote:

The ordinals are in the meta-data, they're just not affecting the generated code.

It would be very helpful if that information (the ordinal) could be made available as a property of an entity field. Is there a reason not to do that?

Thanks!

It's not available as it's not needed: all ordinals in resultsets are known as the resultsets come from queries created using field sets, so everything is known at runtime. Keeping indexes around eats memory and thus we keep that at a minimum.

I call previous method like this:

ConvertStoredProcedureToCollection<Table1TEntity,Table1TCollection>(RetrievalProcedures.GetGetTable1SpCallAsQuery(null),null);

And that query is a "SELECT * FROM Table1"

Otis,you see, this is a common issue to use the ordinal of a Table, because we use "SELECT *" in database and so the ordinal of output is like a Table.

you say Resultset but why we create EntityCollection? if we use proc for security issue to fill EntityCollection how could we????? we should use template????? we should arrange alphabetic for every entity????? Shouldn't be a way to fill a EntityCollection by a proc without difficulty????

I don't know but i think, it's NEEDED!

Thanks So Much and Best Wishes for you. Rahman

r_khanipour wrote:

I call previous method like this:

ConvertStoredProcedureToCollection<Table1TEntity,Table1TCollection>(RetrievalProcedures.GetGetTable1SpCallAsQuery(null),null);

And that query is a "SELECT * FROM Table1"

Otis,you see, this is a common issue to use the ordinal of a Table, because we use "SELECT *" in database and so the ordinal of output is like a Table.

I know, but that doesn't mean things can't break due to that, and IF they do, you also should look into why that is.

you say Resultset but why we create EntityCollection? if we use proc for security issue to fill EntityCollection how could we????? we should use template????? we should arrange alphabetic for every entity????? Shouldn't be a way to fill a EntityCollection by a proc without difficulty????

I don't know but i think, it's NEEDED! Thanks So Much and Best Wishes for you. Rahman

We change things sometimes, and they break code sometimes. I'll give you a piece of code below which might help. I haven't test it in full, so it might fail somewhere but it should give you an idea how to fix it (my test with an entity returning proc worked). I gave you a hint how to fix it too with a template (which generates code for you to produce the projectors for you). Anyway, since you use this method you posted above, it's not hard to fix it. See below.

**NOTE: ** this one works for Selfservicing only. For adapter, a similar method has to be added to the DataAccessAdapter class, which obtains the persistence info objects first.

private TEntityCollection ConvertStoredProcedureToCollection<TEntityObject, TEntityCollection>(IRetrievalQuery query, Transaction transactionManager)
            where TEntityObject : EntityBase, new()
            where TEntityCollection : EntityCollectionBase<TEntityObject>, new()
{
    //Create new instances of the EntityObject and EntityCollection.
    TEntityObject entityObject = new TEntityObject();
    TEntityCollection entityCollection = new TEntityCollection();
    //Get all the Fields of the EntityObject. These fields are necessary for fetching all the data from the resultset.
    IEntityFields entityFields = entityObject.Fields;
    using (query)
    {
        //Get the resultset of the Stored Procedure as an IDataReader.
        TypedListDAO dao = new TypedListDAO();
        using (IDataReader reader = dao.GetAsDataReader(transactionManager, query, CommandBehavior.CloseConnection))
        {
            // create an ordinal lookup per field in the resultset. 
            Dictionary<string, int> fieldIndexLookup = new Dictionary<string, int>();
            for(int i=0;i<reader.FieldCount;i++)
            {
                fieldIndexLookup[reader.GetName(i)] = i;
            }
            //Create a new instance of List<IDataValueProjector>. In this the values which will be fetched will be stored.
            List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>(entityFields.Count);
            foreach (EntityField entityField in entityFields)
            {
                //Put each entityField in List<IDataValueProjector>.
                int index = 0;
                if(!fieldIndexLookup.TryGetValue(entityField.SourceColumnName, out index))
                {
                    index = entityField.FieldIndex;
                }
                valueProjectors.Add(new DataValueProjector(entityField.Alias, index, entityField.DataType));
            }
            //Create the projector and fill the collection.
            DataProjectorToIEntityCollection projector = new DataProjectorToIEntityCollection(entityCollection);
        
            dao.GetAsProjection(valueProjectors, projector, reader);
            reader.Close();
        }
    }
    return entityCollection;
}

Note 2: you might want to cache the lookup dictionaries, as they're always the same.

thanks you, it worked!

I know, but that doesn't mean things can't break due to that, and IF they do, you also should look into why that is.

I know, but what i mentioned before was NOT for my code and changing it, because in programming many thing change.what i wanted to say was: it's needed to have an easy way in LLBL to fill a EntityCollection by a proc.

i have an idea : Like "Reverse-Engineer Resultsets to TypedView Definition", it have an option for "Map Resultset to an Entity Definition" and when User select an Entity for mapping, LLBL should check Type and FieldName of them for mapping. if it's possible so LLbL will generate a method code by return type of EntityCollection of that entity.

Best Wishes Rahman