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

 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.

 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