LINQ to SQL - Combine date and time columns into a single value

Question

I'm trying to translate this SQL statement to LINQ:

SELECT sessionid, userid, CAST(sessiondate AS DATETIME) + CAST(sessiontime AS DATETIME) AS sessiondatetime FROM sometable

where sessiondate is of type DATE and sessiontime is of type TIME.

I've tried the following:

var query = from session in table
            select new
            {
              session.Id,
              session.UserId,
              DateTime = session.Date + session.Time
            };

where table is the return value of a GetTable<Session>() call on a DataContext instance and the Session class maps sessionid to Id, userid to UserId, sessiondate to Date (DateTime), and sessiontime to Time (TimeSpan).

The LINQ gets translated to this rather lengthy SQL statement:

SELECT [t0].[sessionid] AS [Id], [t0].[userid] AS [UserId], CONVERT(DateTime,DATEADD(HOUR, DATEPART(HOUR, [t0].[sessiontime]), CONVERT(DateTime,DATEADD(MINUTE, DATEPART(MINUTE, [t0].[sessiontime]), CONVERT(DateTime,DATEADD(SECOND, DATEPART(SECOND, [t0].[sessiontime]), CONVERT(DateTime,DATEADD(MILLISECOND, DATEPART(MILLISECOND, [t0].[sessiontime]), [t0].[sessiondate])))))))) AS [DateTime] FROM [sometable] AS [t0]

Unfortunately, when attempting to execute that statement, it tells me that "The datepart millisecond is not supported by date function dateadd for data type date." I'm guessing it's unhappy about the DATEADD call with milliseconds. Is there a way to fix this?

Edit: note that both session.Date and session.Time are nullable.

Answer 1

That unfortunate data structure makes the code unfortunately ugly:

var query = from session in table
select new
{
    session.id,
    session.userid,
    combinedDate = new DateTime(
        session.Date.Year, session.Date.Month, session.Date.Day, 
        session.Time.Hour, session.Time.Minute, session.Time.Second, 
        session.Time.Millisecond)
};