Topic
  • 2 replies
  • Latest Post - ‏2019-04-03T11:34:07Z by Tapan_Kumar_Ghosh
Kappas
Kappas
23 Posts

Pinned topic Canonical Functions for EF Core

‏2018-10-17T12:09:43Z | core ef linq

Hey,

 

We have IBM i v 7.1

 

In previous version there was support for canonical functions and there was DB2 driver specific db functions too available.

 

If I have a table that has date column, how do I compare it in LINQ correctly in dotnet core 2.1 + IBM. EntityFrameworkCore 1.2.2.100 ?

 

For example:

var dateToBeCompared = new DateTime(2018,1,1).Date;

var result = _dbContext.SomeTable.Where(t => t.SomeFieldAsDate > dateToBeCompared).ToList();

 

It executes the query against Db2 in IBM i, but job log has diagnostic message:

"SQL0181 - Value in date, time, or timestamp string not valid"

 

Above message comes from the fact that after linq is resolved there is string with both date and time. 

 

Can you give me example how to resolve this correctly that our job logs are not full of these diagnostic messages ?

 

Br,

Kappas

 

 

 

  • Kappas
    Kappas
    23 Posts

    Re: Canonical Functions for EF Core

    ‏2018-10-17T13:11:49Z  

    I found some interesting stuff from IBM.EntityFrameworkCore.Query.ExpressionTranslators.Internal -namespace ...

     

    Db2DateTimeNowTranslator cannot handle IBM i as it uses "GETDATE" which is not supported by IBM i

     

    Do you have any docs for this stuff ?

  • Tapan_Kumar_Ghosh
    Tapan_Kumar_Ghosh
    3 Posts

    Re: Canonical Functions for EF Core

    ‏2019-04-03T11:34:07Z  
    • Kappas
    • ‏2018-10-17T13:11:49Z

    I found some interesting stuff from IBM.EntityFrameworkCore.Query.ExpressionTranslators.Internal -namespace ...

     

    Db2DateTimeNowTranslator cannot handle IBM i as it uses "GETDATE" which is not supported by IBM i

     

    Do you have any docs for this stuff ?

    Hi Kappas,

    I have tried to reproduce the issue against IBM.EntityFrameworkCore version 1.2.2.100.

    1) Here is my table definition which contains one date column i.e. "COLDATE"

    CREATE TABLE "NEWTON"."DATETAB" (
        "COLINT" INTEGER NOT NULL PRIMARY KEY,
        "COLVARCHAR" VARCHAR ( 40 ) ,
        "COLDATE" DATE )

    2) Inserted few records:

    insert into datetab values(1, 'albert', '2011-01-02');
    insert into datetab values(2, 'john', '2014-05-04');
    insert into datetab values(3, 'robert', '2016-06-12');
    insert into datetab values(4, 'mathew', '2011-04-08');
    insert into datetab values(5, 'nancy', '2019-01-02');
    insert into datetab values(6, 'allen', '2020-05-04');
    insert into datetab values(7, 'lewis', '2018-06-12');
    insert into datetab values(8, 'maria', '2016-04-08');

    3) And created Entity Data Model from existing database table "DATETAB" using IBM Data Server providers for Entity Framework Core version 1.2.2.100 using "Scaffold-DbContext" command as mentioned in
    link https://www.ibm.com/developerworks/community/blogs/96960515-2ea1-4391-8170-b0515d08e4da/entry/Creating_Entity_Data_Model_using_IBM_Data_Server_providers_for_Entity_Framework_Core?lang=en
     
    4) Used that table entity in LINQ application and executed without any exception.

                D700060XContext ctx = new D700060XContext();
                var dateToBeCompared = new DateTime(2018, 1, 1).Date;            
                var result = ctx.Datetab.Where(t => t.Coldate > dateToBeCompared).ToList();
                Console.WriteLine("result={0}", result.Count());

    Output:
    ----------------------
    DB2APP was not found side by side with the Application Entry Assembly C:\Users\tghosh\source\repos\CoreApp\CoreApp\bin\Debug\netcoreapp2.0\clidriver\bin\db2app64.dll
    Loading the DB2APP from C:\Users\tghosh\.nuget\packages\ibm.data.db2.core\1.2.2.100\build\clidriver\bin\db2app64.dll
    LIbrary handle written value is a non zero 140709659803648
    result=3
    ----------------------
    5) The LINQ application gives result = 3 as there are 3 records which is having date greater than '2018-01-01'.

    Could you please share table definition, inserted records in that table, with which you are getting that exception.

     

    Thanks,
    Tapan