Hi!

I'm trying to understand why the milliseconds of a DateTime isn't included in a predicate expression.

This is what I do. 1. Retrive datetime from DB by sp/ActionProcedure.

CREATE PROCEDURE [dbo].[GetDbDateAndTimeStamp]
    @timeStamp DATETIME OUTPUT
AS
BEGIN
SELECT @timeStamp = GETDATE()
END

1. Set this date on a range if entities that meets som critera with the UpdateEntitiesDirectly method of the DataAccessAdapter.

2. Later on I need to retrieve a subset of the updated entities. At this point I still have the DateTime variable in memory (which includes milliseconds) and I use this to create a new predicate expression. Now, here's the problem, I won't get any records that meet the criteria. Using the diagnostics tools of LLBLGen and looking at the trace output the milliseconds of the datetime is missing, which clearly explains why there's no match. I've had a look around in the forum (searched on milliseconds) and I can't find anyone having the same type of problem. A few threads are about the TIMESTAMP datatype. Do I have to use a TIMESTAMP datatype to achieve comparasion on millisecond level?

I'm using: LLBLGen Pro version + buildnr: 2.0.0.0 Final Runtime library version: 2.0.7.319 Template group + .NET version: Adapter + .NET 2.0 Database type: SQL Server 2005 (dev) SQL Server 2000 (prod)

Would you please post the corresponding code snippet and the generated SQL query.

Ok!

I must admit one thing first though. I would have preferred to do this in another way. Just like setting a new status for a group of records using the UpdateEntitiesDirectly I would have preferred to do the second update with the same approach. It's just that I find it very difficult to use the various FieldCompare... objects to achieve what I would normally write in SQL syntax with minimum effort. I realize this is a shortcomming of intelligence capacity on my part. It would however be very nice if you could add more examples or even better describe how one should think of how to transform SQL-syntax int LLBL-objects to the documentation so that some of us less intelligently equipped could create even greater code with LLBL. I must say that I have managed to put together a rather complex condition, but it took a day and a lot of pulling in my hair to get there. Enough of this and on to my case.

I'll add some info of the DB too.

I Have tables the Queue and QueueDetail where QueueDetail refers to Queue's primary key.

The purpose of this whole operation is to set a completion date on the master record when all the detail records have been handled.

Below is where I do the UpdateEntitiesDirectly. The last line of this method then calls the other method that shall make a fetch based on the datetime with milliseconds.

        public static void FailOldRequests(int processRequestTimetout)
        {
            QueueDetailEntity detail = new QueueDetailEntity();
            DateTime updated = DateTime.Now;
            ActionProcedures.GetDbDateAndTimeStamp(ref updated);
            detail.DateUpdated = updated;
            detail.FkStatusCode = StatusCodes.Failed;
            RelationPredicateBucket filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add((QueueDetailFields.FkStatusCode == StatusCodes.ForChange) | (QueueDetailFields.FkStatusCode == StatusCodes.Changing));
            filter.PredicateExpression.Add(QueueFields.DateCreated < updated.AddHours(-processRequestTimetout));
            filter.Relations.Add(QueueEntity.Relations.QueueDetailEntityUsingFkQueueId);

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.UpdateEntitiesDirectly(detail, filter);
            }

            PinHandler.SetQueueCompleted(updated, updated.AddHours(-processRequestTimetout), StatusCodes.Failed);
        }

... and heres the method of that last line and this is where the milliseconds are lost in the generated SQL.

        public static int SetQueueCompleted(DateTime updated, DateTime timedOut, string statusCode)
        {
            PredicateExpression filter = new PredicateExpression(QueueDetailFields.DateUpdated == updated);
            filter.Add(QueueDetailFields.FkStatusCode == statusCode);
            filter.Add(QueueFields.DateCreated == timedOut);
            return SetQueueCompleted(updated, filter);
        }

        public static int SetQueueCompleted(DateTime updated, PredicateExpression additionalFilter)
        {
            // Set queue info for completed requests
            EntityCollection<QueueEntity> queueCollection = new EntityCollection<QueueEntity>();
            RelationPredicateBucket filter = new RelationPredicateBucket(QueueFields.DateCompleted == DBNull.Value);
            filter.Relations.Add(QueueEntity.Relations.QueueDetailEntityUsingFkQueueId);
            filter.PredicateExpression.Add(additionalFilter);
            PrefetchPath2 prefetch = new PrefetchPath2((int)EntityType.QueueDetailEntity);
            prefetch.Add(QueueEntity.PrefetchPathQueueDetail);
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(queueCollection, filter, 0, null, prefetch);
            }

            List<int> matches;
            foreach (QueueEntity queueItem in queueCollection)
            {
                matches = queueItem.QueueDetail.FindMatches((QueueDetailFields.FkStatusCode == StatusCodes.Failed) | (QueueDetailFields.FkStatusCode == StatusCodes.Changed));
                if (matches.Count == queueItem.QueueDetail.Count)
                {
                    queueItem.DateCompleted = updated;
                    queueItem.IsCompleted = true;
                }
            }
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                return adapter.SaveEntityCollection(queueCollection);
            }
        }

... the generated SQL from the trace is as follows

Method Enter: CreateUpdateDQ(5)
Method Enter: CreateSingleTargetUpdateDQ(5)
Generated Sql query: 
    Query: UPDATE [IdCardPinExchanger].[dbo].[QueueDetail] SET [fkStatusCode]=@FkStatusCode,[dateUpdated]=@DateUpdated FROM ( [IdCardPinExchanger].[dbo].[Queue]  INNER JOIN [IdCardPinExchanger].[dbo].[QueueDetail]  ON  [IdCardPinExchanger].[dbo].[Queue].[pkQueueId]=[IdCardPinExchanger].[dbo].[QueueDetail].[fkQueueId]) WHERE ( ( ( ( [IdCardPinExchanger].[dbo].[QueueDetail].[fkStatusCode] = @FkStatusCode1 OR [IdCardPinExchanger].[dbo].[QueueDetail].[fkStatusCode] = @FkStatusCode2) AND [IdCardPinExchanger].[dbo].[Queue].[dateCreated] < @DateCreated3)))
    Parameter: @FkStatusCode : StringFixedLength. Length: 20. Precision: 0. Scale: 0. Direction: Input. Value: "failed              ".
    Parameter: @DateUpdated : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2008-02-25 10:58:48.
    Parameter: @FkStatusCode1 : StringFixedLength. Length: 20. Precision: 0. Scale: 0. Direction: Input. Value: "forchange           ".
    Parameter: @FkStatusCode2 : StringFixedLength. Length: 20. Precision: 0. Scale: 0. Direction: Input. Value: "changing            ".
    Parameter: @DateCreated3 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2008-02-23 10:58:48.


Method Exit: CreateSingleTargetUpdateDQ(5)
Method Exit: CreateUpdateDQ(5)
A first chance exception of type 'System.InvalidOperationException' occurred in System.dll
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT DISTINCT [IdCardPinExchanger].[dbo].[Queue].[pkQueueId] AS [PkQueueId], [IdCardPinExchanger].[dbo].[Queue].[operatorCode] AS [OperatorCode], [IdCardPinExchanger].[dbo].[Queue].[clientId] AS [ClientId], [IdCardPinExchanger].[dbo].[Queue].[dateCreated] AS [DateCreated], [IdCardPinExchanger].[dbo].[Queue].[dateCompleted] AS [DateCompleted], [IdCardPinExchanger].[dbo].[Queue].[cardIdExternal] AS [CardIdExternal], [IdCardPinExchanger].[dbo].[Queue].[isCompleted] AS [IsCompleted], [IdCardPinExchanger].[dbo].[Queue].[pin] AS [Pin] FROM ( [IdCardPinExchanger].[dbo].[Queue]  INNER JOIN [IdCardPinExchanger].[dbo].[QueueDetail]  ON  [IdCardPinExchanger].[dbo].[Queue].[pkQueueId]=[IdCardPinExchanger].[dbo].[QueueDetail].[fkQueueId]) WHERE ( ( [IdCardPinExchanger].[dbo].[Queue].[dateCompleted] IS NULL AND ( [IdCardPinExchanger].[dbo].[QueueDetail].[dateUpdated] = @DateUpdated1 AND [IdCardPinExchanger].[dbo].[QueueDetail].[fkStatusCode] = @FkStatusCode2 AND [IdCardPinExchanger].[dbo].[Queue].[dateCreated] = @
DateCreated3)))
    Parameter: @DateUpdated1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2008-02-25 10:58:48.
    Parameter: @FkStatusCode2 : StringFixedLength. Length: 20. Precision: 0. Scale: 0. Direction: Input. Value: "failed              ".
    Parameter: @DateCreated3 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2008-02-23 10:58:48.

Well... I just realized something. The trace doesn't show milliseconds even if they are there. Looking at the update statement one can see that there are no milliseconds in the trace, but looking in the DB (and in debug-mode of VS) they do exist. The problem seems to lie in creating a predicate expression with a datetime value containing milliseconds.

Values in the trace aren't necessarily the value send to the db, as they're a textual representation. LLBLGen Pro sends 'DateTime values' directly, no conversion.

Milliseconds are a problem with databases: they not always result in a match.

Would you please examine the queries in the SQL Profiler?

Thank you for the tip of studying the generated SQL in profiler. Of course I should have thought of this myself. Darn it! Now I appear even less intelligent

This actually lead me back to an error that I previously spotted, but completely forgot about, when being disturbed by colleagues.

So, I'm sorry for having disturbed you!

The error was that one of the conditions for the select statement should have been a "less than" instead of "equal to" comparasion.

No problem at all