I get an exception when FETCH'ing a record with a PK and then trying to access one of the fields on the entity. This happens in LLBL Gen Pro 2.5 and 2.6.

=== LLBLGEN Version ======== LLBLGEN Pro: 2.5 Final Released September 24th, 2008 and 2.6 Final DEMO Released September 12th, 2008

=== Exception ========

SD.LLBLGen.Pro.ORMSupportClasses.ORMEntityOutOfSyncException was unhandled by user code Message="The entity is out of sync with its data in the database. Refetch this entity before using this in-memory instance." Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20" RuntimeBuild="09112008" RuntimeVersion="2.6.0.0" StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase2.GetValue(Int32 fieldIndex, Boolean returnDefaultIfNull) at FreightAccounting.Data.Sales5.EntityClasses.LookupBaTypeEntity.get_Name() in V:\DotNetApp\FreightAccounting\Src\FreightAccounting.Data.Sales5\EntityClasses\LookupBaTypeEntity.cs:line 705 at Interline_InterlineQueue.Page_Load(Object sender, EventArgs e) in v:\DotNetApp\FreightAccounting\Src\FreightAccounting\Interline\InterlineQueue.aspx.cs:line 24 at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

=== Output Log ========

SELECT sales5.dbo.lookup_ba_type.ba_type_id AS BaTypeId, sales5.dbo.lookup_ba_type.name AS Name, sales5.dbo.lookup_ba_type.description AS Description, sales5.dbo.lookup_ba_type.active_flag AS ActiveFlag FROM sales5.dbo.lookup_ba_type WHERE ( ( sales5.dbo.lookup_ba_type.ba_type_id = 2))

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.EnterpriseServices\2.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.Wrapper.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMEntityOutOfSyncException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.DLL

=== Database Info =========

Sybase Client 15.0 with Sybase.Data.AseClient 1.15.200.0 Tried against both Sybase Server 15.0 and 15.0.2

=== SQL DDL ===============

CREATE TABLE dbo.lookup_ba_type ( ba_type_id int IDENTITY, name varchar(20) NOT NULL, description varchar(255) NULL, active_flag bit DEFAULT 1 NOT NULL, CONSTRAINT PK_LOOKUP_BA_TYPE PRIMARY KEY CLUSTERED (ba_type_id) ) LOCK ALLPAGES go

=== CODE ==================

    LookupBaTypeEntity et = LookupBaTypeController.Load(2);
    string name = et.Name;

===

public class LookupBaTypeController
{
    public static EntityCollection<LookupBaTypeEntity> List()
    {
        EntityCollection<LookupBaTypeEntity> entities = new EntityCollection<LookupBaTypeEntity>();

        using (SqlDataAdapter adapter = new SqlDataAdapter())
        {
            adapter.FetchEntityCollection(entities, null);
        }
        return entities;
    }

    public static LookupBaTypeEntity Load(int baTypeId)
    {
        LookupBaTypeEntity entity = new LookupBaTypeEntity(baTypeId);

        using (SqlDataAdapter adapter = new SqlDataAdapter())
        {
            adapter.FetchEntity(entity);
        }

        return entity;
    }
}

Is that the real code? It's supposed you should use SqlDataAdapter, no SqlDataAdapter.

Yes. I am using this. (I should probably rename it). === Code =========== public class SqlDataAdapter : DataAccessAdapter { public SqlDataAdapter() : base(ConfigurationManager.ConnectionStrings["Main.ConnectionString"].ConnectionString) { }

    protected override IRetrievalQuery CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
    {
        try
        {
            IRetrievalQuery r = base.CreateSelectDQ(fieldsToFetch, persistenceInfoObjects, filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, pageNumber, pageSize);
            return r;
        }
        catch
        {
            throw;
        }
    }
}

Using a different table here as an example. If I try to retreive the record using the fetch collection with a predicate expression, I get the row and it works, but getting from PK using fetch entity doesn't work. Here is the code using the fetch collection that works. ==== Code ============ public static BillingArrangementEntity Load(int billingArrangementId) { EntityCollection<BillingArrangementEntity> entities = new EntityCollection<BillingArrangementEntity>();

        // Create the relationships and predicate filters
        IRelationPredicateBucket bucket = new RelationPredicateBucket();

        bucket.PredicateExpression.Add(
            BillingArrangementFields.BillingArrangementId == billingArrangementId
        );

        PrefetchPath2 prefetch = new PrefetchPath2(EntityType.BillingArrangementEntity);
        prefetch.Add(BillingArrangementEntity.PrefetchPathLookupBaType);

        using (SqlDataAdapter adapter = new SqlDataAdapter())
        {
            adapter.FetchEntityCollection(entities, bucket, prefetch);
        }

        if (entities.Count == 1)
        {
            return entities[0];
        }
        else
        {
            return new BillingArrangementEntity(billingArrangementId);
        }
    }

Here is the other table example where the fetch entity does not work. (don't know if this helps or not). ==== Code =========== public static BillingArrangementEntity Load(int billingArrangementId) { BillingArrangementEntity entity = new BillingArrangementEntity(billingArrangementId);

        using (SqlDataAdapter adapter = new SqlDataAdapter())
        {
            adapter.FetchEntity(entity);
        }
        return entity;
    }

The PersistanceInfoProvider.cs has... (Attached are the Tasks templates used that vary from the ones shipped with 2.6). === Code ============== /////////////////////////////////////////////////////////////// // This is generated code. ////////////////////////////////////////////////////////////// // Code is generated using LLBLGen Pro version: 2.6 // Code is generated on: Thursday, October 02, 2008 1:42:04 PM // Code is generated using templates: SD.TemplateBindings.SybaseAseSpecific.NET20 // Templates vendor: Solutions Design. // Templates version: ////////////////////////////////////////////////////////////// using System; using System.Collections; using System.Data; using Sybase.Data.AseClient;

using SD.LLBLGen.Pro.ORMSupportClasses;

. . . /// <summary>Inits LookupBaTypeEntity's mappings</summary> private void InitLookupBaTypeEntityMappings() { base.AddElementMapping( "LookupBaTypeEntity", "sales5", @"dbo", "lookup_ba_type", 4 ); base.AddElementFieldMapping( "LookupBaTypeEntity", "BaTypeId", "ba_type_id", false, (int)AseDbType.Integer, 10, 0, 10, true, "@@IDENTITY", null, typeof(System.Int32), 0 ); base.AddElementFieldMapping( "LookupBaTypeEntity", "Name", "name", false, (int)AseDbType.VarChar, 20, 0, 0, false, "", null, typeof(System.String), 1 ); base.AddElementFieldMapping( "LookupBaTypeEntity", "Description", "description", true, (int)AseDbType.VarChar, 255, 0, 0, false, "", null, typeof(System.String), 2 ); base.AddElementFieldMapping( "LookupBaTypeEntity", "ActiveFlag", "active_flag", false, (int)AseDbType.Bit, 1, 0, 0, false, "", null, typeof(System.Boolean), 3 ); }

. . .

Just to be sure:

SELECT sales5.dbo.lookup_ba_type.ba_type_id AS BaTypeId, sales5.dbo.lookup_ba_type.name AS Name, sales5.dbo.lookup_ba_type.description AS Description, sales5.dbo.lookup_ba_type.active_flag AS ActiveFlag FROM sales5.dbo.lookup_ba_type WHERE ( ( sales5.dbo.lookup_ba_type.ba_type_id = 2))

Running the above query directly against the database does return a record, right?

Yes. It returns a record.

mfreder wrote:

Yes. It returns a record.

But... is the 'IsNew' flag set AFTER the fetch routine ran? I.o.w.: was there data loaded INTO the entity object or not?

Yes the is new is set. Nothing is loaded when using the fetch entity. I execute the fetchentity and immediately inspecting the entity it shows outofsync and when I try to access a field then I get the out of sync exception. Yet the record is in the database table. Using FetchEntityCollection works.

mfreder wrote:

Yes. I am using this. (I should probably rename it). === Code =========== public class SqlDataAdapter : DataAccessAdapter { public SqlDataAdapter() : base(ConfigurationManager.ConnectionStrings["Main.ConnectionString"].ConnectionString) { }

    protected override IRetrievalQuery CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
    {
        try
        {
            IRetrievalQuery r = base.CreateSelectDQ(fieldsToFetch, persistenceInfoObjects, filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, pageNumber, pageSize);
            return r;
        }
        catch
        {
            throw;
        }
    }
}

I don't understand the necessity for this code, as it's unnecessary in this form, also because connectionstring reading from the connectionstrings section is built in.

Your error comes from the fact that the entity with the id specified isn't present in the DB, or better: it couldn't be loaded into an entity.

When you use the normal DataAccessAdapter class, it does work? You're connecting to the right db ? The thing is that the code to fill the entity is equal for all databases, and if the query is EQUAL (for the collection fetch and the normal fetch), the same data is returned and should be filled properly into the entity by the generic code... The queries look exactly the same including the parameters ? (please enable DQE tracing)

Yes, it appears redundant. That aside, even if I use this code, I get the same error. The row does exist in the database and cut-and-paste'ing the query from the output and running it in a query tool does, in fact, bring up the record. That what has me puzzled.

==== code =======

    LookupBaTypeEntity entity = new LookupBaTypeEntity(2);
    using (DataAccessAdapter adapter = new DataAccessAdapter())
    {
        adapter.FetchEntity(entity);   <=== IsNew equals true, (but should retireve row)
    }
    string s = entity.Name;   <=== OutOfSync error occurs here

===============

Changing my SybaseDriver to an older version (SybaseASE 1.15.50) and it works. The entity is fetched. However the newer version ADO.NET driver SybaseASE 1.15.200, does not fetch anything.

Hopefully this helps. Thanks. Here's the trace when using SybaseASE 1.15.200.

==== trace ==== Method Enter: DataAccessAdapterBase.FetchEntity(4) Method Enter: DataAccessAdapterBase.FetchEntityUsingFilter(5) Active Entity Description: Entity: FreightAccounting.Data.Sales5.EntityClasses.LookupBaTypeEntity. ObjectID: 7fc2c058-f217-4cf1-bd7b-c6ba001e785c PrimaryKey field: BaTypeId. Type: System.Int32. Value: 2

Method Enter: DataAccessAdapterBase.FetchEntityUsingFilter(3) 'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\freightaccounting\b67b4e71\964c5a94\assembly\dl3\f24b1a9e\00f11db0_1218c901\SD.LLBLGen.Pro.DQE.SybaseAse.NET20.DLL' 'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC\Sybase.Data.AseClient\1.15.200.0__26e0f1529304f4a7\Sybase.Data.AseClient.dll' Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT sales5.dbo.lookup_ba_type.ba_type_id AS BaTypeId, sales5.dbo.lookup_ba_type.name AS Name, sales5.dbo.lookup_ba_type.description AS Description, sales5.dbo.lookup_ba_type.active_flag AS ActiveFlag FROM sales5.dbo.lookup_ba_type WHERE ( ( sales5.dbo.lookup_ba_type.ba_type_id = @BaTypeId1)) Parameter: @BaTypeId1 : Int32. Length: 10. Precision: 10. Scale: 0. Direction: Input. Value: 2.

Method Exit: CreateSelectDQ

Method Exit: CreatePagingSelectDQ: no paging.

Method Enter: DataAccessAdapterBase.ExecuteSingleRowRetrievalQuery

Method Enter: DataAccessAdapterBase.OpenConnection

Connection physically opened. Method Exit: DataAccessAdapterBase.OpenConnection Executed Sql Query: Query: SELECT sales5.dbo.lookup_ba_type.ba_type_id AS BaTypeId, sales5.dbo.lookup_ba_type.name AS Name, sales5.dbo.lookup_ba_type.description AS Description, sales5.dbo.lookup_ba_type.active_flag AS ActiveFlag FROM sales5.dbo.lookup_ba_type WHERE ( ( sales5.dbo.lookup_ba_type.ba_type_id = @BaTypeId1)) Parameter: @BaTypeId1 : Int32. Length: 10. Precision: 10. Scale: 0. Direction: Input. Value: 2.

Method Exit: DataAccessAdapterBase.ExecuteSingleRowRetrievalQuery Method Exit: DataAccessAdapterBase.FetchEntityUsingFilter Method Enter: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.FetchEntityUsingFilter(5) Method Exit: DataAccessAdapterBase.FetchEntity(4) A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMEntityOutOfSyncException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.DLL

==== end of trace ======

It would appear this is a known issue with Sybase drivers, according to other LLBLGen users.

http://www.llblgen.com/tinyforum/PrintMessages.aspx?ThreadID=11677 (scroll to the end)

AH!

Thanks for looking that up, I should have done that search as well...

Yes, indeed there's no other option than to either - change SingleRow to SingleResult or - use an older ase provider version.

Instead of making changes in RetrievalQuery, Execute method, which is comman across the different data providers, I would rather make the change in my generated DataAccessAdapter class.

In theory, the best place would be to override ExecuteSingleRowRetrievalQuery, set a property, then call the base.

        public override void ExecuteSingleRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFields2 fieldsToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo)
        {
            queryToExecute.CommandBehavior = CommandBehavior.SingleResult;
            base.ExecuteSingleRowRetrievalQuery(queryToExecute, fieldsToFill, fieldsPersistenceInfo);
        }

Obviously this would require changes to RetrievalQuery, but it would give the end user flexiblity within their templates that would allow them to "work around" the Sybase issue w/o affecting other providers.

(another idea was for ExecuteSingleRowRetrievalQuery to call a Validate type method that could be overriden, which may make for fewer interface changes)

What I'm asking, would a change to the library like this be something you would consider adding to the codebase for future releases, or this something the Sybase users will need always hack in until Sybase fixes their provider?

It's indeed currently not possible to change fetch behavior through inheritance. In v2.6, the ExecuteSingleRowRetrievalQuery is the method to override but it doesn't allow you to set the commandbehavior.

The interface change you propose isn't really necessary, as IRetrievalQuery.Execute accepts a commandbehavior. I'll do the following: In the next build of the runtime of v2.6, this method is present in the DataAccessAdapterBase class:

/// <summary>
/// Performs the execute single row retrieval query action. This method simply calls Execute on the queryToExecute passed in. 
/// </summary>
/// <param name="queryToExecute">The query to execute.</param>
/// <param name="behavior">The commandbehavior to pass to Execute.</param>
/// <returns>live datareader created by the execute method</returns>
/// <remarks>Use this method to pass a different command behavior to queryToExecute.Execute(), which is necessary for ASE sybase for example, as the 
/// Sybase ASE provider has a critical bug in some versions where SingleRow doesn't work but SingleResult will</remarks>
protected virtual IDataReader PerformExecuteSingleRowRetrievalQuery(IRetrievalQuery queryToExecute, CommandBehavior behavior)
{
    return queryToExecute.Execute(behavior);
}

so, if you're using ASE with the specific provider and you need to control the command behavior, simply override this method and pass CommandBehavior.SingleResult to the base' method call. By default SingleRow is passed in.

THis method is also added to DaoBase, so if you're using selfservicing, you have to add an include template to the dao template to override this method in all dao classes. We can't change the templates, as that would be a breaking change (templates then rely on a specific build of the runtime which won't work)

I'll attach a debug build of this build to this post. (edit) attached.

That works great. Thanks.