Gibraltar Support

Start a new topic

VistaDB 5.2.1 EF Lambda with datetime

i am using ef6 with sqlserver.. all is ok, when i try switch to vistaDB, all is ok too. big part of application run smooth. but when run into query part with lambda expression with date, things fall apart.


when check the log, the detail where error happened is at => 'WHERE (([Extent1].[RecDate] >= convert(DateTime, '2016-07-20 00:00:00.000', 121))'

the generator try to convert using => ',121' style, which is not supported in VistaDB according to it's documentation.


if manually execute the query without ',121' the query is run ok.


is this bug in 'VistaDB.5.Entity.6.NET40.dll' when generating query...?? or there is some setting need to be done...??


Thanks, those hints helped us to find the logic in our EF providers generating the string literal in the SQL code, and the fix is then trivial to have it generate the string value with full precision.


The EF issue should be fixed with 5.2.2.

it is a DateTime pass as Object in lambda.
here's the code :

 

 var param = Expression.Parameter(typeof(TransaksiBeli), "s");

      Expression eTgl1 = Expression.GreaterThanOrEqual(
                  Expression.PropertyOrField(param, "Tanggal"),
                  Expression.Constant(cari1.DateTime));
      Expression eTgl2 = Expression.LessThanOrEqual(
                  Expression.PropertyOrField(param, "Tanggal"),
                  Expression.Constant(cari2.DateTime));
      var Hasil = Expression.AndAlso(eTgl1, eTgl2);

var lambdaLoh = Expression.Lambda<Func<TransaksiBeli, bool>>(Hasil, param);
      BindingList<TransaksiBeli> oList = new BindingList<TransaksiBeli>(aRep.Ambil(lambdaLoh));

 

and the data layer is just a simple ef call

 

 public List<TransaksiBeli> Ambil(System.Linq.Expressions.Expression<Func<TransaksiBeli, bool>> exp)
    {
      M1DbConteks _dbConteks = new M1DbConteks();
      return _dbConteks.TransaksiBeli.AsNoTracking().Where(exp).ToList();
    }

 

 when i debug the datalayer 'public List<TransaksiBeli>  Ambil(System.Linq.Expressions.Expression<Func<TransaksiBeli, bool>> exp)' it is correctly show the DateTime not a String



the resulting query generated is

 

SELECT
[Extent1].[ID]
AS[ID], 
[Extent1].[PihakID]
AS[PihakID], 
[Extent1].[SalesID]
AS[SalesID], 
[Extent1].[Tanggal]
AS[Tanggal], 
[Extent1].[PihakNama]
AS[PihakNama], 
[Extent1].[JenisTransaksi]
AS[JenisTransaksi], 
[Extent1].[CaraPembayaran]
AS[CaraPembayaran], 
[Extent1].[SubTotal]
AS[SubTotal], 
[Extent1].[Diskon_Tampilan]
AS[Diskon_Tampilan], 
[Extent1].[Diskon_Nilai]
AS[Diskon_Nilai], 
[Extent1].[Diskon_AsliNilai]
AS[Diskon_AsliNilai], 
[Extent1].[Diskon_AsliPersen]
AS[Diskon_AsliPersen], 
[Extent1].[PPN_Tampilan]
AS[PPN_Tampilan], 
[Extent1].[PPN_Nilai]
AS[PPN_Nilai], 
[Extent1].[PPN_AsliNilai]
AS[PPN_AsliNilai], 
[Extent1].[PPN_AsliPersen]
AS[PPN_AsliPersen], 
[Extent1].[Total]
AS[Total], 
[Extent1].[LamaKredit]
AS[LamaKredit], 
[Extent1].[Keterangan]
AS[Keterangan], 
[Extent1].[Pengali]
AS[Pengali], 
[Extent1].[Pembayaran]
AS[Pembayaran], 
[Extent1].[SudahBayar]
AS[SudahBayar], 
[Extent1].[NoRef]
AS[NoRef], 
[Extent1].[Nama]
AS[Nama], 
[Extent1].[Suspen]
AS[Suspen]
FROM[TransaksiBeli] AS[Extent1]
WHERE(([Extent1].[Tanggal] >= convert(DateTime2, '2016-07-23 00:00:00.000', 121)) AND([Extent1].[Tanggal] <= convert(DateTime2, '2016-08-22 09:08:58.275', 121))) AND(1 = [Extent1].[JenisTransaksi])

 

 

the table structure is

 

CREATE TABLE[TransaksiBeli] (
	[ID] NVarChar(25) NOT NULL,
	[PihakID] NVarChar(25) NOT NULL,
	[SalesID] NVarChar(25) NOT NULL,
	[Tanggal] DateTime2 NOT NULL,
	[JenisTransaksi]
Int NOT NULL,
	[CaraPembayaran]
Int NOT NULL,
	[SubTotal]
Decimal NOT NULL,
	[Diskon_Tampilan] NVarChar(50) NULL,
	[Diskon_Nilai]
Decimal NOT NULL,
	[Diskon_AsliNilai]
Decimal NOT NULL,
	[Diskon_AsliPersen]
Bit NOT NULL,
	[PPN_Tampilan] NVarChar(50) NULL,
	[PPN_Nilai]
Decimal NOT NULL,
	[PPN_AsliNilai]
Decimal NOT NULL,
	[PPN_AsliPersen]
Bit NOT NULL,
	[Total]
Decimal NOT NULL,
	[LamaKredit]
Int NOT NULL,
	[Keterangan] NVarChar(200) NULL,
	[Pengali]
Int NOT NULL,
	[Pembayaran]
Decimal NOT NULL,
	[SudahBayar]
Int NOT NULL,
	[NoRef] NVarChar(MAX) NULL,
	[Nama] NVarChar(70) NULL,
	[Suspen]
Bit NOT NULL,
	[PihakNama] NVarChar(MAX) NULL
);
ALTER TABLE[TransaksiBeli] ADD CONSTRAINT[PK_dbo_TransaksiBeli] PRIMARY KEY([ID]);

 

the error is happened too with empty table.


 

in the lambda expression code, it is a DateTime pass as object, not a string
here's the code
 
 var param = Expression.Parameter(typeof(TransaksiBeli), "s");
      Expression eTgl1 = Expression.GreaterThanOrEqual(
                  Expression.PropertyOrField(param, "Tanggal"),
                  Expression.Constant(cari1.DateTime));
      Expression eTgl2 = Expression.LessThanOrEqual(
                  Expression.PropertyOrField(param, "Tanggal"),
                  Expression.Constant(cari2.DateTime));
      var Hasil = Expression.AndAlso(eTgl1, eTgl2);

var lambdaLoh = Expression.Lambda<Func<TransaksiBeli, bool>>(Hasil, param);
      BindingList<TransaksiBeli> oList = new BindingList<TransaksiBeli>(aRep.Ambil(lambdaLoh));

 

and the call to data layer is simple ef

 

  public List<TransaksiBeli> Ambil(System.Linq.Expressions.Expression<Func<TransaksiBeli, bool>> exp)
    {
      M1DbConteks _dbConteks = new M1DbConteks();
      return _dbConteks.TransaksiBeli.AsNoTracking().Where(exp).ToList();
    }

 

 when i debug on data layer 'Ambil' method (code above).. it is show that the value is right and it is DateTime not String



Are you specifying the date value literal in your code as a string or is it a .NET DateTime value being converted (from a variable... or from a defined const)?

 thanks rob

i made a temporary workaround by using ef6 interceptor on IDbCommandInterceptor.
here's the code

 

public void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
   {
     command.CommandText = command.CommandText.Replace("', 121)", "')");
   }
public void NonQueryExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
   {
     command.CommandText = command.CommandText.Replace("', 121)", "')");
   }
public void ScalarExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
   {
     command.CommandText = command.CommandText.Replace("', 121)", "')");
   }

 

now it works fine.


for the note, when using datetime with linq is run ok.. it happened only when using lambda expression query.
and because i am using ef, i guess there's some bug in 'VistaDB.5.Entity.6.NET40.dll' when generating query to execute.

i will try changing datatype to datetime2. i hope this can solve the problem and i don't need the interceptor again.


The documentation may be out of date.  I checked the code in the Convert function implementation and we do seem to support format code 121.


I was able to reproduce it in Data Builder and test a theory.  The problem appears to start with the fact that VistaDB's DateTime is based on .NET's DateTime and natively supports 7 digits of fractional seconds unlike SQL Server's DateTime which only supports 3.  Conversion uses .NET's formatting and parsing for DateTime using explicit format strings based on the formats in MSDN documentation for SQL Server (which are mostly described for conversion into strings, not the parsing behavior converting from strings), but this may be too strict for parsing back from a string.  SQL Server appears to be more lax.


As a workaround, if you can add the additional four 0's to the fractional seconds it should parse okay.  If the string is being generated for you (or converted from a data value?) then you might try changing your SQL-declared DateTime types to DateTime2 (which matches .NET's DateTime) so it's more likely to generate the 7 fractional digits.


We'll have to look into how to tweak the conversion to handle absent low-end portions.

Login to post a comment