I have two entities in a database which hold a DateTime in two different formats; SuperLogs.EndTime is a normal DateTime whilst SuperLogs.Logs holds the DateTime as separated numeric components of Year (int), Month (int), Day (int) and Time (double). What I want to do in my query is get the only SuperLogs entity which latest Log has a DateTime greater than the EndTime of superLogX.
I have a C# function (GetLogDateTime) to convert the DateTime on the Logs into an actual DateTime but when passed into the following query, "System.InvalidOperationException: Internal .NET Framework Data Provider error 1025." is thrown.
var superLogNewerThanSuperLogX = await MyDbContext.SuperLogs.SingleOrDefaultAsync
(
superLog => superLog.Logs.Max<Log, DateTime>(GetLogDateTime) > superLogX.EndTime
);
GetLogDateTime is implemented as follows:
private static DateTime GetLogDateTime(Log log)
{
var time = log.Time;
var hasTime = log.Time.HasValue;
var hours = hasTime ? Math.Floor(time.Value / 10000) : 0;
var minutes = hasTime ? Math.Floor(time.Value / 100) - hours * 100 : 0;
return new DateTime
(
log.Year,
log.Month,
log.Day,
(int)hours,
(int)minutes,
hasTime ? (int)(time.Value - (hours * 10000 + minutes * 100)) : 0,
hasTime ? (int)((time.Value - Math.Truncate(time.Value)) * 1000) : 0
);
}
My guess is part of the problem is that GetLogDateTime only converts into a Func<Log, DateTime> but needs to convert into an Expression<Func<Log, DateTime>> somehow. I'm not sure how to do this and if it's not possible I also have a Database-implemented Scalar Function which performs the same task as this method.
CREATE FUNCTION dbo.CreateDateTime
(
@year SMALLINT,
@month SMALLINT,
@day SMALLINT,
@time FLOAT
)
RETURNS DATETIME AS
BEGIN
DECLARE @paddedYear VARCHAR(4) = REPLACE(STR(@year, 4), SPACE(1), '0')
DECLARE @paddedMonth VARCHAR(2) = REPLACE(STR(@month, 2), SPACE(1), '0')
DECLARE @paddedDay VARCHAR(2) = REPLACE(STR(@day, 2), SPACE(1), '0')
DECLARE @hours FLOAT = ROUND(@time / 10000, 0, 1)
DECLARE @hoursAndMinutes FLOAT = ROUND(@time / 100, 0, 1)
DECLARE @hoursMinutesAndSeconds FLOAT = ROUND(@time, 0, 1)
--DATETIME only supports 3 decimal places on seconds so VARCHAR 5 is the max needed for the string 0.xxx
DECLARE @milliseconds VARCHAR(5) = CAST(@time - ROUND(@time, 0, 1) AS VARCHAR(5))
RETURN IIF
(
@year IS NULL
OR @month IS NULL
OR @day IS NULL,
NULL,
CAST
(
IIF
(
@time IS NULL,
@paddedYear
+ @paddedMonth
+ @paddedDay,
@paddedYear
+ @paddedMonth
+ @paddedDay
+ ' '
--In ROUND, the final argument of 1 forces ROUND to always round down (desired here).
+ REPLACE(STR(@hours, 2), SPACE(1), '0')
+ ':'
+ REPLACE(STR(@hoursAndMinutes - @hours * 100, 2), SPACE(1), '0')
+ ':'
+ REPLACE(STR(@hoursMinutesAndSeconds - @hoursAndMinutes * 100, 2), SPACE(1), '0')
+ IIF
(
@time <> @hoursMinutesAndSeconds,
'.'
+ SUBSTRING(@milliseconds, 3, LEN(@milliseconds) - 2),
''
)
)
AS DATETIME
)
)
END
However, this doesn't particularly help either as I can't seem to get Entity Framework to allow me to call it! At the moment the only viable option I can see is to try to move my entire query (much bigger than shown here) into the database as a Stored Procedure. Is there another way or is this what I'm going to be forced to do?
