Could you please describe the problem you've encountered with the Convert.ToString() method in more details? E.g., please specify
1) the type of data being retrieved;
2) the arguments of the method;
3) the output of this method.
I have two places where I need to convert DateTime or TimeStamp fields to strings:
Issue A)
Some select statements where I need a string representation of a date, truncating either the date part or the time part. ie, using Linq to generate a resulting SQL like the following (SomeTimeStamp is of type TimeStamp):
Code: Select all
SELECT TO_CHAR(T.SomeTimeStamp, 'HH24:MI:SS.FF') AS Time
FROM SomeTable T
But when using Convert.ToString(value, IFormatProvider) in the Linq query expression:
Code: Select all
var formatProvider = CultureInfo.InvariantCulture.DateTimeFormat
var query = from item in context.SomeTable
select new {Time = Convert.ToString(item.SomeTimeStamp, formatProvider)}
That generates:
Code: Select all
SELECT TO_CHAR(T.SomeTimeStamp, ' 'fmMMfm/fmDDfm/YYYY HH24:MI:SS') AS Time
FROM SomeTable T
Notice three issues there:
- It uses a format string that includes both the date and the time part
- The time part does not include milliseconds
- I don't care about it for now, it notice the 'fm' string that is sorrounding the month and day characters (fmMMfm and fmDDfm instead of MM and DD).
So how do I call Convert.ToString(...) to make it generate the TO_CHAR(...) call with my own custom format, as in the first example?
I could do somehing like that by creating a new culture and modifying some private properties by reflection (a reaaaally nasty thing to do...):
Code: Select all
var formatProvider = new CultureInfo("en-US", true).DateTimeFormat;
formatProvider.LongTimePattern = "HH:mm:ss.fff";
formatProvider.ShortDatePattern= "";
formatProvider.GetType().GetField("generalLongTimePattern", BindingFlags.NonPublic | BindingFlags.Instance).SetValue(formatProvider, formatProvider.LongTimePattern);
var query = from item in context.SomeTable
select new
{
HOUR_PART = Convert.ToString(item.SomeTimeStamp, formatProvider),
};
With that I got the following (somewhat similar to what i needed):
Code: Select all
SELECT TO_CHAR(t1.TRADE_TIME, ' HH24:MI:SS.FF3') AS C1
FROM DEVSMRT2.REG_TRADE_VIEW t1
But also notice the extra space at the left of "HH24". It seems obvious that linqConnect uses the two properties I hacked in order to create the format string, and adds the space to separate whatever the date format string is from the time format....
So how do I make linqConnect to generate what I want? The same is also valid for any oracle TO_*(....) function.
I know, I know, if I'm gonna use a date, number, timestamp, etc, in the select statement, I probably can modify whatever legacy code in the app I'm refactoring, so it uses .Net types, and work around the formating some other way... But that is not the case of the following issue, where I need the formatted field value as part of a WHERE statement:
Issue B
There's a query that needs to filter rows from a Sales table within an interval of dates that includes several days , but only for certain time span during each day. The field I'm filtering by is a timestamp field.
So, the resuling query should be something like: retrieve all sales done from 03/01/2011 to 03/15/2011 that took place between 10:00AM and 11:00AM.
So the SQL generated by linqConnect should be something like:
Code: Select all
SELECT * FROM Sales S
WHERE S.Date BETWEEN '2011-03-01' AND '2011-03-15'
AND TO_CHAR(S.Date, 'HH24:MI:SS.FF') BETWEEN '10:00:00.000' AND '11:00:00.000'
As far as I know linqConnect cannot generate the BETWEEN operator, but it does not matter, 'cause it does generate the equivalent using '>=' and '<=' operators.
The problem is generating the TO_CHAR(...) call, like in Issue 1.
You should be able to work with the data retrieved as with common .NET objects. For example, you can use the methods of the Convert class and the DateTime structure, as well as the String.Format method. In particular, the LINQ query you've specified should return time portions of dates converted to strings (provided that 'SomeDateField' is of the DateTime type). Please tell us if you are encountering some other behaviour.
If I use String.Format(...) static method, or String.ToString("hh:mm:ss.fff") instance method applied to the field within the linq query expression, like:
Code: Select all
var query = from item in dataContext.SomeTable
select new {
FormattedDate = item.SomeTimeStampField.ToString("HH:mm:ss")
}
well, linqConnect seems to ignore the above use of ToString() (the same for String.Format), generating a SQL string that simply retrieves the field as it is, with no TO_CHAR call:
Code: Select all
SELECT t.SomeTimeStampField
FROM SomeTable t
I used a SELECT statement in this example, but it's the same for WHERE statements.
Maybe the conversion is made later, in memory, as you seem to imply in the following quoted paragraph:
However, it is not guaranteed that this conversion is performed at the server side. For example, the above query is translated to something like "select SomeDateField from SomeTable", and the results are converted to strings at the client side.
...but for the case of WHERE statements, even if being possible, I cannot perform the filter in memory: we're dealing with literally millions of records, so the SQL statement submitted to the DB must be complete (i.e., include the TO_CHAR(...) call).
Is what I need clear enough?
Thanks in advance
