Expression Tree for .NET DateTime <> Oracle Timestamp like search

Question

I need to know whether I can get a clue or not. I am trying to build an Expression Tree that is evaluated via Entity Framework 6 (LINQ to Entities) via ODP.NET managed driver (current Version) to the appropriate Oracle statement in order to perform filtering. This works perfectly for string <> nvarchar2 datatypes. The output for a Contains search is something like:

Select * 
[...]
FROM "MYSCHEMA"."MYTABLE" "Extent1"
WHERE ("Extent1"."ANVARCHAR2COLUM" LIKE '%givenStrValueToFind%') 

Now I want to get the same result for a DateTime <> Timestamp(6) since this line of sql statement is valid for an oracle query and returns all Dates which contains '08':

select * from "MYSCHEMA"."MYTABLE" where ATIMESTAMP6COLUMN like '%08%';

Since I am new to Expression Trees, I first need to know (after googling alot and tried this an that), whether this is possible before I dig deeper into. And if so, how could this be accomplished best? Since there is no Contains Method defined for DateTime and DateTime? Maybe providing Extension methods? And I dont want to execude queries directly against database.

Any hints would be nice to 'unhook' the given database datatype perhaps... Thx.

Answer 1

For EF 6 you can use :

ctx.MYTABLE.Where(x=>x.ATIMESTAMP6COLUMN.ToString().Contains("08")).ToList();

which translates to

SELECT 
[Extent1].[some property name] AS [some property name], 
[Extent1].[ATIMESTAMP6COLUMN] AS [ATIMESTAMP6COLUMN], 
.
.
.
FROM [dbo].[MYTABLE] AS [Extent1]
WHERE  CAST( [Extent1].[ATIMESTAMP6COLUMN] AS nvarchar(max)) LIKE N'%08%'

As far as what I know all versions of EF does not support string functions but EF 6.x supports this kind of functions(The EF that I tested with is EF 6.1.3, the test is done with sql server localdb as DBMS).

Answer 2

I don't know if I understood. Anyway, you don't need to build an expression tree to avoid DB load in memory. LINQ build an expression tree from an expression, EF translates the expression tree to a SQL statement (using the EF Provider) then the query is run on the DB.

In your case the LINQ Query should be something like this

myContext.MYTABLE.Where(e => e.ATIMESTAMP6COLUMN.Contains("08").ToList();

EDIT

Conversions:

You can use .ToString(). It should be a canonical function (function available on every EF Provider).

Other DateTime related functions:

You could have a look to other canonical functions (there are some about dates that allow to retrieve date parts) and also to non canonical functions (functions implemented only in EF Provider for oracle).

References:

Canonical functions https://msdn.microsoft.com/en-us/library/vstudio/bb738626(v=vs.100).aspx

Actually I can't find non canonical functions for Oracle.
If you need it the best thing is that you ask another question.