DateTime parameter

[Emil Lenngren]

Hi. I'm trying to implement some date/time functions in Entity Framework SQL generation.

I notice that when I have parameters of type DateTime, the SQL will contain a parameter: @p__linq__1 which will later be converted to a string literal like (('2014-07-11 11:11:53.677724')). Since this is a string and not a timestamp data type, I get some exceptions for wrong data types.

Is there a reason it is not serialized as (('2014-07-11 11:11:53.677724')::timestamp) or (timestamp '2014-07-11 11:11:53.677724')?

I see that in AppendParameterValue in NpgsqlCommand.Rewrite.cs, parameter.UseCast is false instead of true.

If I instead write an inline DateTime directly in the Linq query instead of having a variable, I get TIMESTAMP '2014-12-29T14:01:00.0000000' which works.

[Emil Lenngren]

Same thing happens with TimeSpan, a literal string is generated for parameterized query, without a cast.

In the EF sql generator, when having a TimeSpan as a literal, it is now generated as the literal string TIME '01:02:03' (in VisitedExpression.cs). Wouldn't it be better to use interval instead so days can be included too?

[Emil Lenngren]

I think the cast is missing also when EF is not used:

            using (var cmd = new Npgsql.NpgsqlCommand("select :ts", conn))

            {

                cmd.Connection.Open();

                cmd.Parameters.Add(new Npgsql.NpgsqlParameter("ts", new TimeSpan(1, 2, 3, 4)));

                using (var rdr = cmd.ExecuteReader())

                {

                    while (rdr.Read())

                    {

                        Console.Out.WriteLine(rdr.GetTimeSpan(0));

                    }

                }

            }

That throws an exception because String could not be converted to TimeSpan.

--
You received this message because you are subscribed to the Google Groups "Npgsql Dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to npgsql-dev+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Francisco Figueiredo Jr.]

Hmmmm, this is strange. It should be being cast. Unless its DbType  is being set to DbType.Object. Npgsql checks for that and if it is, no cast is added.

As Glen Parker once said, Npgsql type system isn't very good. In fact, this casting system isn't work very well in some edge cases.

I even opened an issue to discuss that: https://github.com/npgsql/Npgsql/issues/125

I'll check what is happening with  Timespan types.

Thanks for heads up, Emil !

On Saturday, July 19, 2014 5:17:10 PM UTC-3, Emil Lenngren wrote:

I think the cast is missing also when EF is not used:

            using (var cmd = new Npgsql.NpgsqlCommand("select :ts", conn))

            {

                cmd.Connection.Open();

                cmd.Parameters.Add(new Npgsql.NpgsqlParameter("ts", new TimeSpan(1, 2, 3, 4)));

                using (var rdr = cmd.ExecuteReader())

                {

                    while (rdr.Read())

                    {

                        Console.Out.WriteLine(rdr.GetTimeSpan(0));

                    }

                }

            }

That throws an exception because String could not be converted to TimeSpan.

2014-07-19 2:00 GMT+02:00 Emil Lenngren <>:

Same thing happens with TimeSpan, a literal string is generated for parameterized query, without a cast.

In the EF sql generator, when having a TimeSpan as a literal, it is now generated as the literal string TIME '01:02:03' (in VisitedExpression.cs). Wouldn't it be better to use interval instead so days can be included too?

Den lördagen den 19:e juli 2014 kl. 00:02:36 UTC+2 skrev Emil Lenngren:

Hi. I'm trying to implement some date/time functions in Entity Framework SQL generation.

I notice that when I have parameters of type DateTime, the SQL will contain a parameter: @p__linq__1 which will later be converted to a string literal like (('2014-07-11 11:11:53.677724')). Since this is a string and not a timestamp data type, I get some exceptions for wrong data types.

Is there a reason it is not serialized as (('2014-07-11 11:11:53.677724')::timestamp) or (timestamp '2014-07-11 11:11:53.677724')?

I see that in AppendParameterValue in NpgsqlCommand.Rewrite.cs, parameter.UseCast is false instead of true.

If I instead write an inline DateTime directly in the Linq query instead of having a variable, I get TIMESTAMP '2014-12-29T14:01:00.0000000' which works.

--
You received this message because you are subscribed to the Google Groups "Npgsql Dev" group.

To unsubscribe from this group and stop receiving emails from it, send an email to npgsql-dev+unsubscribe@googlegroups.com.

[Francisco Figueiredo Jr.]

Indeed, the dbtype of the timespan is really object and its NpgsqlDbType is set to Interval:
https://github.com/npgsql/Npgsql/blob/master/Npgsql/NpgsqlTypes/NpgsqlTypesHelper.cs#L446

Currently, Npgsql checks if cast has to be used or not based in the set property of DbType or NpgsqlDbType. As none of those properties are changed, the useCast use its default value of false.
Obviously this behavior didn't contemplate the types which need explicit cast to work.

By default, NpgsqlParameter set the useCast to false. It seems it is set like that because of this:
https://github.com/npgsql/Npgsql/commit/0dc8a34d2437eb9da5a4631c01a3ccad575c1058

I'll work on this.