SQL Error on DbFunctions.CreateDateTime and DbFunctions.AddMonths

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
lgmaestrelli
Posts: 3
Joined: Fri 01 Jul 2016 13:37

SQL Error on DbFunctions.CreateDateTime and DbFunctions.AddMonths

Post by lgmaestrelli » Fri 01 Jul 2016 13:44

Hello,

I'm getting a SQL error when I execute this Linq query (Who works on Oracle):

Code: Select all

            
var query = (from p in entidades.Set<Rnc>()
                   where p.CodigoEmpresa == amb.idEmpresa
                      && p.CodigoFilial == amb.idFilial
                      && p.Data >= DbFunctions.CreateDateTime(DbFunctions.AddMonths(DateTime.Now, -4).Value.Year,
                                                              DbFunctions.AddMonths(DateTime.Now, -4).Value.Month, 1, 0, 0, 0)
                   group p by new {p.Data.Month, p.Data.Year} into e
                 orderby new {e.Key.Year, e.Key.Month}
                  select new MesValorDTO
                  {
                      Mes = DbFunctions.CreateDateTime(e.Key.Year, e.Key.Month, 1, 0, 0, 0).Value,
                      Valor = e.Count()
                  });
This code generates this SQL:

Code: Select all

SELECT 
Project1.C3 AS C1,
Project1.C4 AS C2,
Project1.C5 AS C3
FROM ( SELECT 
	GroupBy1.K1 AS C1,
	GroupBy1.K2 AS C2,
	1 AS C3,
	CAST('GroupBy1.K2-GroupBy1.K1-1 0:0:0' AS TIMESTAMP) AS C4,
	CAST(GroupBy1.A1 AS double precision) AS C5
	FROM ( SELECT EXTRACT(MONTH FROM (Extent1.DATA)) AS K1, EXTRACT(YEAR FROM (Extent1.DATA)) AS K2, Count(1) AS A1
		FROM Q2RNC AS Extent1
		WHERE ((Extent1.IDEMPRESA = (CAST(:p__linq__0 AS int))) AND (Extent1.IDFILIAL = (CAST(:p__linq__1 AS int)))) AND ((CAST(Extent1.DATA AS timestamp)) >= (CAST('EXTRACT(YEAR FROM ((CURRENT_TIMESTAMP + (-4 || ' MONTH')::INTERVAL)))-EXTRACT(MONTH FROM ((CURRENT_TIMESTAMP + (-4 || ' MONTH')::INTERVAL)))-1 0:0:0' AS TIMESTAMP)))
		GROUP BY EXTRACT(MONTH FROM (Extent1.DATA)), EXTRACT(YEAR FROM (Extent1.DATA))
	)  AS GroupBy1
)  AS Project1
ORDER BY Project1.C2 ASC, Project1.C1 ASC
I'm using PostgeSQL 9.5.3
dtoConnect for PostgreSQL 7.6.667
Visual Studio 2013

Any ideais about what is wrong?
Thank you all

lgmaestrelli
Posts: 3
Joined: Fri 01 Jul 2016 13:37

Re: SQL Error on DbFunctions.CreateDateTime and DbFunctions.AddMonths

Post by lgmaestrelli » Mon 04 Jul 2016 14:38

I upgrade my dotConnect to 7.6.677 and the error continuous happening.

Shalex
Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

Re: SQL Error on DbFunctions.CreateDateTime and DbFunctions.AddMonths

Post by Shalex » Mon 04 Jul 2016 17:48

Please specify the exact text of the error you are getting and its full stack trace.

lgmaestrelli
Posts: 3
Joined: Fri 01 Jul 2016 13:37

Re: SQL Error on DbFunctions.CreateDateTime and DbFunctions.AddMonths

Post by lgmaestrelli » Mon 04 Jul 2016 18:13

The error is not an exception, is the SQL result generated by the Linq expression.

Code: Select all

SELECT 
Project1.C3 AS C1,
Project1.C4 AS C2,
Project1.C5 AS C3
FROM ( SELECT 
   GroupBy1.K1 AS C1,
   GroupBy1.K2 AS C2,
   1 AS C3,
   CAST('GroupBy1.K2-GroupBy1.K1-1 0:0:0' AS TIMESTAMP) AS C4,
   CAST(GroupBy1.A1 AS double precision) AS C5
   FROM ( SELECT EXTRACT(MONTH FROM (Extent1.DATA)) AS K1, EXTRACT(YEAR FROM (Extent1.DATA)) AS K2, Count(1) AS A1
      FROM Q2RNC AS Extent1
      WHERE ((Extent1.IDEMPRESA = (CAST(:p__linq__0 AS int))) AND (Extent1.IDFILIAL = (CAST(:p__linq__1 AS int)))) AND ((CAST(Extent1.DATA AS timestamp)) >= (CAST('EXTRACT(YEAR FROM ((CURRENT_TIMESTAMP + (-4 || ' MONTH')::INTERVAL)))-EXTRACT(MONTH FROM ((CURRENT_TIMESTAMP + (-4 || ' MONTH')::INTERVAL)))-1 0:0:0' AS TIMESTAMP)))
      GROUP BY EXTRACT(MONTH FROM (Extent1.DATA)), EXTRACT(YEAR FROM (Extent1.DATA))
   )  AS GroupBy1
)  AS Project1
ORDER BY Project1.C2 ASC, Project1.C1 ASC
You will be able to see an weird string concat in the where clause.

Code: Select all

CAST('EXTRACT(YEAR FROM ((CURRENT_TIMESTAMP + (-4 || ' MONTH')::INTERVAL)))-EXTRACT(MONTH FROM ((CURRENT_TIMESTAMP + (-4 || ' MONTH')::INTERVAL)))-1 0:0:0' AS TIMESTAMP)

Shalex
Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

Re: SQL Error on DbFunctions.CreateDateTime and DbFunctions.AddMonths

Post by Shalex » Fri 08 Jul 2016 10:22

The bug with using the CreateDateTime and CreateTime canonical functions with expressions (not constants) as arguments is fixed in the newest (7.6.687) build of dotConnect for PostgreSQL.

Shalex
Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

Re: SQL Error on DbFunctions.CreateDateTime and DbFunctions.AddMonths

Post by Shalex » Fri 22 Jul 2016 12:06

The CurrentDateTimeOffset and CreateDateTimeOffset canonical functions are implemented in the newest (7.6.699) build of dotConnect for PostgreSQL.

Please note that tzoffset (the last parameter of the CreateDateTimeOffset function) is set in minutes (not hours).

Post Reply