EF DateTime formatting example

context.cs

public IQueryable<HistoryItemContract> FetchNoLinqKit(DateTime? createdAfter, DateTime? createdBefore)
        {
            var data = HistorySet
                .Join(HistoryLinkSet, (x) => x.HISTORYID, (hl) => hl.HISTORYID, (x, hl) => new { x, hl })
                .Join(ContactSet, (x) => x.hl.CONTACTID, (c) => c.ID, (x, c) => new { x, c });

            if (createdAfter.HasValue)
            {
                // WARN; We assume the value is never NULL!
                data = data.Where(x => x.x.x.CREATEDATE >= createdAfter.Value);
            }

            if (createdBefore.HasValue)
            {
                // WARN; We assume the value is never NULL!
                data = data.Where(x => x.x.x.CREATEDATE <= createdBefore.Value);
            }

            return data.Select(x => new
            {
                History = x.x.x,
                Contact = x.c,
            })
            .Select(x => new HistoryItemContract()
            {
                    // INFO; Expression splicing
                    type = HistoryType.INVALID, // historyTypeConverter.Invoke(x.Type.HISTORYTYPEGUID),
                    created = x.History.CREATEDATE,
                    regarding = x.History.REGARDING,
                    // resourcePath = x.Attachment.FILEPATH,
                    contact = x.Contact.ID,
            });
        }

controller.cs

[Route("{contactId}")]
        [HttpGet]
        public async Task<List<HistoryItemContract>> GetHistoryItemsFor(
            Guid contactId,
            [FromUri]DateTime? createdAfter = default,
            [FromUri]DateTime? createdBefore = default,
            [FromUri]IEnumerable<HistoryType> type = default,
            [FromUri]int? limit = default,
            CancellationToken token = default)
        {
            _logger.Debug("{Event}", nameof(GetHistoryItemsFor));

            var responseLimit = (limit.HasValue == false || limit.Value < 0) ? _configuration.ResponseCountLimit : limit.Value;
            var whitelistedTypeSet = type.Distinct().ToList();

            using (var ctxt = new HistoryContext())
            {
                ctxt.Database.Log = d => Debug.WriteLine(d);

                var data = ctxt .FetchNoLinqKit(createdAfter, createdBefore) /* DBG */ // .FetchAllHistoryItems()
                    // WARN; We assume the contact id data is never NULL!
                    .Where(x => x.contact.Value == contactId);

                var result = await data
                    .OrderByDescending(x => x.created)
                    // NOTE; Prevent DOS'ing ourselves.
                    .Take(responseLimit)
                    .ToListAsync(token);

                return result;
            }
        }

trace.log

SELECT TOP (50) 
    [Project1].[C1] AS [C1], 
    [Project1].[C2] AS [C2], 
    [Project1].[REGARDING] AS [REGARDING], 
    [Project1].[CONTACTID] AS [CONTACTID]
    FROM ( SELECT 
        [Extent1].[REGARDING] AS [REGARDING], 
        [Extent3].[CONTACTID] AS [CONTACTID], 
        0 AS [C1], 
         CAST( [Extent1].[CREATEDATE] AS datetime2) AS [C2]
        FROM   [dbo].[TBL_HISTORY] AS [Extent1]
        INNER JOIN [dbo].[TBL_CONTACT_HISTORY] AS [Extent2] ON [Extent1].[HISTORYID] = [Extent2].[HISTORYID]
        INNER JOIN [dbo].[TBL_CONTACT] AS [Extent3] ON [Extent2].[CONTACTID] = [Extent3].[CONTACTID]
        WHERE ([Extent1].[CREATEDATE] >= @p__linq__0) AND ([Extent3].[CONTACTID] = @p__linq__1)
    )  AS [Project1]
    ORDER BY [Project1].[C2] DESC

-- p__linq__0: '01/05/2019 00:00:00' (Type = DateTime2)

-- p__linq__1: '296786e6-eb8f-4fad-aa52-c5aa207c7964' (Type = Guid, IsNullable = false)

-- Executing asynchronously at 07/10/2019 15:07:26 +02:00

-- Completed in 26 ms with result: SqlDataReader