Request: I posted this to the wrong forum. Can it be moved to the appropriate one? thanks.

4.0.13.523 SD.LLBLGen.Pro.DQE.SqlServer.dll 4.0.13.0725 SD.LLBLGen.Pro.ORMSupportClasses.dll 4.0.13.406 SD.LLBLGen.Pro.ORMSupportClasses.Web.dll DotNet 4.0 vs2010 project Adapter template SQL Server 2008 R2

I am investigating various ways to achieve something tricky with my database and LLB and have a possible solution that I could use if I could override the FetchEntity method to ignore one of the primary keys. This pk is not needed because a column filter on EndDate in the view driving the Entity ensures a unique row is returned.

The sql that is output from FetchEntity is:

EXEC sp_executesql N'
SELECT [dbo].[v2_Topic_Text].[endDate] AS [EndDate] ,       
        [dbo].[v2_Topic_Text].[IDTopic] ,
        [dbo].[v2_Topic_Text].[LanguageCode] ,
        [dbo].[v2_Topic_Text].[startDate] AS [StartDate] ,
FROM    [dbo].[v2_Topic_Text]
WHERE   ( ( [dbo].[v2_Topic_Text].[IDTopic] = @p1
          AND [dbo].[v2_Topic_Text].[LanguageCode] = @p2
          AND [dbo].[v2_Topic_Text].[startDate] IS NULL)
        )
',
    N'@p1 int,@p2 varchar(6)', @p1 = 1, @p2 = 'en'

It is the 'AND [dbo].[v2_Topic_Text].[startDate] IS NULL' I need to eliminate.

I have looked at entityToFetch.SetExpression and .ExpressionToApply but don't see how to use either to make the adapter ignore this primary key. Maybe there is a way I haven't found? Perhaps setting it to NOT NULL or to equal itself would do it?

Here is something I tried but it crashes with an infinite loop error

        public override bool FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse,
                                         ExcludeIncludeFieldsList excludedIncludedFields)
        {
            if (entityToFetch is ITemporalized)
            {
                if (entityToFetch.Fields["StartDate"].CurrentValue == null)
                {
                    var expressionToToApply = new Expression(entityToFetch.Fields["StartDate"], ExOp.None, entityToFetch.Fields["StartDate"]);
                    entityToFetch.Fields["StartDate"].SetExpression(expressionToToApply);
                }
            }
            return base.FetchEntity(entityToFetch, prefetchPath, contextToUse, excludedIncludedFields);
        }

The benefit of the StartDate primary key is big in the rest of the applications so I don't want to lose it everywhere. When the entity is refetched after a save, an update trigger might have created a new row with a newer StartDate.

(I am also trying the Fetch with UC discussed in another thread of mine, but that is turning out to be complex to abstract to a few lines of code as I can't get polymorphism from the .ConstructFilterForUC... method. Still looking into that one.)

Why not use a collection fetch with the filter you want and grab the first element?

Makes sense. However, that means I need to police it amongst other developers.

I have worked out how to override FetchEntity to do it in abstract way though after a tip from Walaa on another thread:

        public override bool FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse,
                                         ExcludeIncludeFieldsList excludedIncludedFields)
        {
            if (entityToFetch is ITemporalized)
            {
                // override the normal fetch by primary key to use the original primary key plus the endDate.
                var filter = new PredicateExpression();
                var endDate = (EntityField2) entityToFetch.Fields["EndDate"];
                filter.Add(endDate == DateTime.Parse("9999-12-31"));
                foreach (var pk in entityToFetch.PrimaryKeyFields.Where(a => a.Name != "StartDate"))
                {
                    var entityField2 = (EntityField2) entityToFetch.Fields[pk.Name];
                    filter.Add(entityField2 == pk.CurrentValue);
                }
                return FetchEntityUsingUniqueConstraint(entityToFetch, filter);
            }
            return base.FetchEntity(entityToFetch, prefetchPath, contextToUse, excludedIncludedFields);
        }

All this does is replace the startDate with endDate. It is working so far:-) Thanks to all.