Hi,

I am trying to retrieve items in DB, based on a criteria: (aDateTime + aTimeSpan) >= anotherDateTime

It works fine, except that only the [year-month-day] of the DateTimes are taken into account. For instance, if (aDateTime + aTimeSpan) is at time 1am and anotherDateTime is at time 2am on the same day, then the above expression is evaluated to true, when I think it should be evaluated to false.

I have checked what is the generated SQL query, and actually the parameter types is "Date" so that's probably why the time of the days are ignored. Could you please indicate a way to write the Linq filter so that the SQL query uses DateTimes?

Here is my current Linq filter code:

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.ProjectSchemaNameToUse = study.TimePoint.Subject.ProjectSite.Project.SchemaName;

                IPathEdge[] path = PathFactories.PathFactoryTimeFrame.WithDefault();

                LinqMetaData metaData = new LinqMetaData(adapter);

                Expression<System.Func<TimeFrameEntity, bool>> filter = PredicateBuilder.Null<TimeFrameEntity>();
                filter = filter.And(e => (e.RefTimePointTypeId == study.TimePoint.TimePointType.ID));
                filter = filter.And(e => (e.ModalityId == study.Modality.ID));
                filter = filter.And(e => (((DateTime)(study.StudyDate.Value + e.StartRange)) >= ((DateTime)minimumStartDate)));
                filter = filter.And(e => (((DateTime)(study.StudyDate.Value + e.StartRange)) <= ((DateTime)maximumStartDate)));
                IQueryable<TimeFrameEntity> entities = (
                    from e in metaData.TimeFrame.Where(filter).WithPath(path)
                    select e);

                var collection = ((ILLBLGenProQuery)entities).Execute<EntityCollection<TimeFrameEntity>>();

And here is the generated SQL query. Note that on the last 4 lines, the parameter types is "Date" which I think is my problem. I don't know why the type is "Date" and not "DateTime" or "TimeStamp"

Generated Sql query: 
    Query: SELECT "LPLA_1"."TimeFrameID" AS "TimeFrameId", "LPLA_1"."TimePointTypeID" AS "TimePointTypeId", "LPLA_1"."RefTimePointTypeID" AS "RefTimePointTypeId", "LPLA_1"."ModalityID" AS "ModalityId", "LPLA_1"."StartRange", "LPLA_1"."Duration", "LPLA_1"."GracePeriod" FROM "teststudy"."TimeFrame" "LPLA_1" WHERE ( ( ( ( ( ( ( ( ( "LPLA_1"."RefTimePointTypeID" = :RefTimePointTypeId1) AND ( "LPLA_1"."ModalityID" = :ModalityId2)) AND ( :LLBLEP4 + "LPLA_1"."StartRange" >= :LPFA_13)) AND ( :LLBLEP6 + "LPLA_1"."StartRange" <= :LPFA_25)))))))
    Parameter: :RefTimePointTypeId1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2.
    Parameter: :ModalityId2 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: :LLBLEP4 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 12/10/2009 4:15:00 PM.
    Parameter: :LPFA_13 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 12/14/2009 4:19:49 PM.
    Parameter: :LLBLEP6 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 12/10/2009 4:15:00 PM.
    Parameter: :LPFA_25 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 12/15/2009 4:19:49 PM.

And last, here is the TimeFrameEntity.StartRange property definition in the LLBLgen generated entity:

        /// <summary> The StartRange property of the Entity TimeFrame<br/><br/>
        /// </summary>
        /// <remarks>Mapped on  table field: "TimeFrame"."StartRange"<br/>
        /// Table field type characteristics (type, precision, scale, length): Interval, 0, 0, 0<br/>
        /// Table field behavior characteristics (is nullable, is PK, is identity): false, false, false</remarks>
        public virtual System.TimeSpan StartRange
        {
            get { return (System.TimeSpan)GetValue((int)TimeFrameFieldIndex.StartRange, true); }
            set { SetValue((int)TimeFrameFieldIndex.StartRange, value); }
        }

The Database is PostgreSql.

Thanks in advance for your help,

Yves.

Yves, what is your LLBLGen runtime library version and your ngpsql version? (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=12769)

Hi, The NPGSQL version is 2.0.6.0. The LLBLGen version is 2.6.9.903.

I actually have found a work-around for the problem. Instead of comparing DateTimes as per my last post, with one DateTime being the sum of a DateTime and an Interval, I compare the interval to a difference of DateTimes. Here is the code (difference is in the last two lines), and now it works fine, i.e. the time of the day of the DateTimes is taken into account:

            Expression<System.Func<TimeFrameEntity, bool>> filter = PredicateBuilder.Null<TimeFrameEntity>();
            filter = filter.And(e => (e.RefTimePointTypeId == study.TimePoint.TimePointType.ID));
            filter = filter.And(e => (e.ModalityId == study.Modality.ID));
            filter = filter.And(e => e.StartRange >= (minimumStartDate - study.StudyDate.Value));
            filter = filter.And(e => e.StartRange <= (maximumStartDate - study.StudyDate.Value));

However I still don't understand why the following resulted in the conversion of the DateTimes to dates in the generated SQL query:

filter = filter.And(e => (((DateTime)(study.StudyDate.Value + e.StartRange)) >= ((DateTime)minimumStartDate)));

Best regards,

Yves.

Please also note that it looks like Postgresql supports adding Intervals to TimeStamps:

http://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL

What's the dataType of "e.StartRange"?

Hi,

"e.StartRange" is of .Net type System.Timespan. In database, the type is PostgreSQL Interval.

Here is the LLBLgen property declaration:

        /// <summary> The StartRange property of the Entity TimeFrame<br/><br/>
        /// </summary>
        /// <remarks>Mapped on table field: "TimeFrame"."StartRange"<br/>
        /// Table field type characteristics (type, precision, scale, length): Interval, 0, 0, 0<br/>
        /// Table field behavior characteristics (is nullable, is PK, is identity): false, false, false</remarks>
        public virtual System.TimeSpan StartRange
        {
            get { return (System.TimeSpan)GetValue((int)TimeFrameFieldIndex.StartRange, true); }
            set { SetValue((int)TimeFrameFieldIndex.StartRange, value); }
        }

I am using .NET 3.5.

Best regards,

Yves.

A datetime value is converted to a 'date' parameter in an Expression. The reason is that Postgresql doesn't have a datatype which combines date and time, only either Date or Time. While that separation is a good thing, you've to consider the fact that in .NET a DateTime value combines the two, and it's unclear for the DQE which part you want to convert to a parameter: the date or the time value? As time is specified using timespans as well, we chose for Date.

Hi Otis, Thanks for your note. Actually in Postgresql there are 3 types: date (for date only), time (for time only) and timestamp which combines date and time. This is documented here:

http://www.postgresql.org/docs/8.2/static/datatype-datetime.html

Also in the LINQ code I have posted, the DateTimes aren't Database fields. They are provided from memory and only the Interval is a database field.

Best regards,

Yves.

filter = filter.And(e => (((DateTime)(study.StudyDate.Value + e.StartRange)) >= ((DateTime)minimumStartDate)));

They are provided from memory and only the Interval is a database field

I think since you are adding the field's value to a dateTime variable and comparing it to another dateTime variable, the expression is sent to the database comparing 2 dates.

But if you use the entityField in one side of the expression as you did later on, the parameter holding the data for comparison will have a type to match the entityField database type.

I think since you are adding the field's value to a dateTime variable and comparing it to another dateTime variable, the expression is sent to the database comparing 2 dates.

I don't understand why when you add a DateTime to an interval entityField, then the DateTime is "casted" as a Date. It would make sense to me only if the DateTime actually is an entity field of type Date in PostgreSQL. I think that when DateTime are provided from memory, they should be used as PostgreSQL timestamp type.

The problem is that the postgresql DQE creates for a DateTime value in an expression a Date parameter. So if the '+' operator is ending up as the operator in an expression between two DateTime typed elements, (constants or db fields, doesn't matter), the DateTime typed values are converted to Date typed parameters.

The '+' operator is the DateTime + operator, so the linq query contains an op_Addition call on DateTime.

About Timestamp, indeed I didn't look closely enough.

It's a bit of a problem, as with expressions, the values dictate the type of the parameters, and this leads to obvious problems, as the DateTime value can be used in 3 types.

OK, thanks for the detailed explanation.