Issue with DbFunctions.AddMinutes and DbFunctions.DiffMinutes when using a .net DateTime

[Mark OMeara]

Hello

I have an issue with the SQL code generated by the ngpsql EntityFramework when using some date related DbFunctions. It would appear when a supplied .net DateTime value is used in the method the generated postgressql code uses:

date_trunc('minute',(('2014-01-01 00:00:00')))

instead of:

date_trunc('minute',((TIMESTAMP '2014-01-01 00:00:00')))

The error from Postgres is:

ERROR: function date_trunc(unknown, unknown) is not unique

An example of my .net method that exhibits this behavior:

// Below method attempts to group date category data by a supplied time period

//

public IQueryable<objectProjection> SearchFor_objectProjection(Expression<Func<pellet, bool>> predicate, TimeSpan Period)

        {

            DateTime ReferenceDate = new DateTime(2014, 1, 1);

            int TotalMinutes = Convert.ToInt32(Period.TotalMinutes);

            return DbSet.Where(predicate)

                .GroupBy(s => DbFunctions.AddMinutes(ReferenceDate, TotalMinutes * (DbFunctions.DiffMinutes(ReferenceDate, s.sampledate) / TotalMinutes)))

                .Select(g => new PelletsProjection

                {

                    sampledate = g.Key.Value,

                    mean = g.Sum(info => info.mean)

                });

        }

The generated sql is:

SELECT 1 AS "C1", "GroupBy1"."K1" AS "C2", "GroupBy1"."A1" AS "C3" FROM (SELECT (('2014-01-01 00:00:00')) + ((1)) * (extract(epoch from date_trunc('minute',"Extent1"."sampledate") - date_trunc('minute',(('2014-01-01 00:00:00'))))::int4 / 60 / ((1))) * INTERVAL '1 Minutes' AS "K1", CAST (sum("Extent1"."mean") AS float8) AS "A1" FROM "statistics"."pellets" AS "Extent1" WHERE "Extent1"."sampledate" >= (('2014-12-10 06:51:00')) AND "Extent1"."sampledate" < (('2014-12-10 23:00:00')) GROUP BY (('2014-01-01 00:00:00')) + ((1)) * (extract(epoch from date_trunc('minute',"Extent1"."sampledate") - date_trunc('minute',(('2014-01-01 00:00:00'))))::int4 / 60 / ((1))) * INTERVAL '1 Minutes') AS "GroupBy1"

The versions I am using are:

<package id="EntityFramework" version="6.1.2" targetFramework="net45" />

<package id="Npgsql" version="2.2.3" targetFramework="net45" />

 <package id="Npgsql.EntityFramework" version="2.2.3" targetFramework="net45" /> 

PostgresSQL version 9.3

Any assistance would be appreciated 

[Emil Lenngren]

Hi. I think your problem doesn't appear in the master branch. Try that if you don't want to wait until the next release.

--
You received this message because you are subscribed to the Google Groups "Npgsql Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to npgsql-help...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/npgsql-help/7932bb8e-ad7c-4996-a463-977274ff6846%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Mark OMeara]

Thanks Emil

 

However I assume that means I shall have to compile the master source from the GitHub repository? If so I may well wait until the next release.

Thanks

Mark

[Emil Lenngren]

See http://build.npgsql.org and login as anonymous user. You can find "artifacts" = compiled dlls to download.

--

You received this message because you are subscribed to the Google Groups "Npgsql Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to npgsql-help...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/npgsql-help/68c17a98-14fa-40e6-b607-f9bcc76a50d3%40googlegroups.com.

[Mark OMeara]

Thanks, however that link is currently showing a failed build with 0 artifacts.

Perhaps I shall check in again tomorrow to see if it has successfully built.

[Shay Rojansky]

Unfortunately our master branch won't be stabilizing (at least not enough) anytime soon, I think...

Emill, any chance this fix is easily backportable to 2.2?

[Emil Lenngren]

It is this commit that is the fix: https://github.com/npgsql/npgsql/commit/accfa1a7695284b30cb1c8b6d042ca2ef190825a
One thing that might not be that good for a bugfix release is the change of the return type of "char"...

To view this discussion on the web visit https://groups.google.com/d/msgid/npgsql-help/052dd87c-595d-48a2-a6d5-453aacef76fe%40googlegroups.com.

[Shay Rojansky]

Ah OK... that's why I always try to cleanly separate changes and to not to group more than one logical change in a commit...

I guess it depends on you Emil, if you feel like backporting only the bugfix from this commit we can release 2.2.4...

On Saturday, January 3, 2015 12:13:47 PM UTC+2, Emil Lenngren wrote:

It is this commit that is the fix: https://github.com/npgsql/npgsql/commit/accfa1a7695284b30cb1c8b6d042ca2ef190825a
One thing that might not be that good for a bugfix release is the change of the return type of "char"...

Den 3 jan 2015 10:58 skrev "Shay Rojansky" <ro...@roji.org>:

Unfortunately our master branch won't be stabilizing (at least not enough) anytime soon, I think...

Emill, any chance this fix is easily backportable to 2.2?

On Saturday, January 3, 2015 11:04:52 AM UTC+2, Mark OMeara wrote:

Thanks, however that link is currently showing a failed build with 0 artifacts.

Perhaps I shall check in again tomorrow to see if it has successfully built.

On Friday, January 2, 2015 9:31:45 PM UTC+11, Emil Lenngren wrote:

See http://build.npgsql.org and login as anonymous user. You can find "artifacts" = compiled dlls to download.

Den 2 jan 2015 10:49 skrev "Mark OMeara" <mark.g...@gmail.com>:

Thanks Emil

 

However I assume that means I shall have to compile the master source from the GitHub repository? If so I may well wait until the next release.

Thanks

Mark

--
You received this message because you are subscribed to the Google Groups "Npgsql Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to npgsql-help...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/npgsql-help/68c17a98-14fa-40e6-b607-f9bcc76a50d3%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

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

[Mark OMeara]

I just wait until the official release.

Thanks