SQL Error on DbFunctions.CreateDateTime and DbFunctions.AddMonths

SQL Error on DbFunctions.CreateDateTime and DbFunctions.AddMonths

Postby 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

Postby lgmaestrelli » Mon 04 Jul 2016 14:38

I upgrade my dotConnect to 7.6.677 and the error continuous happening.
lgmaestrelli
 
Posts: 3
Joined: Fri 01 Jul 2016 13:37

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

Postby Shalex » Mon 04 Jul 2016 17:48

Please specify the exact text of the error you are getting and its full stack trace.
Shalex
Devart Team
 
Posts: 7659
Joined: Thu 14 Aug 2008 12:44

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

Postby 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)
lgmaestrelli
 
Posts: 3
Joined: Fri 01 Jul 2016 13:37

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

Postby 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
Devart Team
 
Posts: 7659
Joined: Thu 14 Aug 2008 12:44

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

Postby 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).
Shalex
Devart Team
 
Posts: 7659
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL