SQL Error on DbFunctions.CreateDateTime and DbFunctions.AddMonths

[lgmaestrelli]

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]

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

[Shalex]

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

[lgmaestrelli]

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]

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]

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).