15 minlesson

Event History & Date Range Filtering

Event History & Date Range Filtering

With the POST endpoint in place, we need a way to retrieve the full tracking history for a parcel. This GET endpoint returns all events in chronological order and supports filtering by date range through query parameters.

The GET Endpoint

The event history endpoint follows the same nested resource pattern:

GET /api/parcels/{parcelId}/events

This returns all tracking events for the specified parcel, ordered by timestamp ascending (oldest first). Chronological order is natural for a timeline --- you read it from top to bottom, earliest to latest.

csharp
1[HttpGet]
2public async Task<ActionResult<IEnumerable<TrackingEventResponse>>> GetEventHistory(
3    Guid parcelId,
4    [FromQuery] DateTime? from,
5    [FromQuery] DateTime? to)
6{
7    // Implementation follows...
8}

The from and to parameters are bound from query string parameters using [FromQuery]. They are nullable because filtering is optional --- when omitted, the endpoint returns the full history.

Step 1: Verify the Parcel Exists

Same as the POST endpoint, we first check that the parcel exists:

csharp
1var parcelExists = await _context.Parcels
2    .AnyAsync(p => p.Id == parcelId);
3

4if (!parcelExists)
5{
6    return NotFound(new ProblemDetails
7    {
8        Title = "Parcel not found",
9        Detail = $"No parcel exists with ID {parcelId}",
10        Status = 404
11    });
12}

Notice a difference from the POST endpoint: here we use AnyAsync instead of FirstOrDefaultAsync. We do not need the parcel entity itself --- we only need to know if it exists. AnyAsync generates a more efficient SQL query (SELECT EXISTS(...) or SELECT TOP 1 1 ...).

Step 2: Build the Query with Optional Filters

We start with the base query and conditionally apply date filters:

csharp
1var query = _context.TrackingEvents
2    .Where(e => e.ParcelId == parcelId)
3    .AsQueryable();
4

5if (from.HasValue)
6{
7    query = query.Where(e => e.Timestamp >= from.Value);
8}
9

10if (to.HasValue)
11{
12    query = query.Where(e => e.Timestamp <= to.Value);
13}

This approach uses query composition --- building up the LINQ query incrementally before executing it. The key insight is that LINQ queries are not executed when they are defined. They are only executed when you enumerate the results (e.g., with ToListAsync).

Why Conditional Filtering Works

Each .Where() call adds a filter to the expression tree. EF Core combines all the filters into a single SQL WHERE clause:

sql
1-- No filters
2SELECT * FROM TrackingEvents WHERE ParcelId = 1 ORDER BY Timestamp
3

4-- With both filters
5SELECT * FROM TrackingEvents
6WHERE ParcelId = 1 AND Timestamp >= '2024-03-01' AND Timestamp <= '2024-03-31'
7ORDER BY Timestamp

This pattern avoids complex conditional SQL and keeps the C# code readable. You add filters only when the corresponding parameter has a value.

Step 3: Execute and Map

After building the query, we order by timestamp and project to the response DTO:

csharp
1var events = await query
2    .OrderBy(e => e.Timestamp)
3    .Select(e => new TrackingEventResponse
4    {
5        Id = e.Id,
6        ParcelId = e.ParcelId,
7        Timestamp = e.Timestamp,
8        EventType = e.EventType.ToString(),
9        Description = e.Description,
10        LocationCity = e.LocationCity,
11        LocationState = e.LocationState,
12        LocationCountry = e.LocationCountry,
13        DelayReason = e.DelayReason
14    })
15    .ToListAsync();
16

17return Ok(events);

Using Select to project directly to the response DTO has two benefits:

  1. Efficiency: EF Core only queries the columns it needs (though in this case we use all columns)
  2. Separation: The database entity shape does not leak into the API response

The EventType.ToString() in the Select is evaluated client-side after EF Core fetches the results. EF Core fetches the enum as an integer and the .ToString() converts it to the name.

The Complete GET Endpoint

csharp
1[HttpGet]
2public async Task<ActionResult<IEnumerable<TrackingEventResponse>>> GetEventHistory(
3    Guid parcelId,
4    [FromQuery] DateTime? from,
5    [FromQuery] DateTime? to)
6{
7    var parcelExists = await _context.Parcels
8        .AnyAsync(p => p.Id == parcelId);
9

10    if (!parcelExists)
11        return NotFound(new ProblemDetails
12        {
13            Title = "Parcel not found",
14            Detail = $"No parcel exists with ID {parcelId}",
15            Status = 404
16        });
17

18    var query = _context.TrackingEvents
19        .Where(e => e.ParcelId == parcelId)
20        .AsQueryable();
21

22    if (from.HasValue)
23        query = query.Where(e => e.Timestamp >= from.Value);
24

25    if (to.HasValue)
26        query = query.Where(e => e.Timestamp <= to.Value);
27

28    var events = await query
29        .OrderBy(e => e.Timestamp)
30        .Select(e => new TrackingEventResponse
31        {
32            Id = e.Id,
33            ParcelId = e.ParcelId,
34            Timestamp = e.Timestamp,
35            EventType = e.EventType.ToString(),
36            Description = e.Description,
37            LocationCity = e.LocationCity,
38            LocationState = e.LocationState,
39            LocationCountry = e.LocationCountry,
40            DelayReason = e.DelayReason
41        })
42        .ToListAsync();
43

44    return Ok(events);
45}

Query Parameter Formatting

Clients pass date filters as query string parameters:

GET /api/parcels/f0e1d2c3-b4a5-6789-0abc-def123456789/events?from=2024-03-01T00:00:00Z&to=2024-03-31T23:59:59Z

ASP.NET Core automatically parses ISO 8601 date strings into DateTime values. The [FromQuery] attribute tells the model binder to look in the query string for these parameters.

Common request variations:

1# Full history (no filters)
2GET /api/parcels/f0e1d2c3-b4a5-6789-0abc-def123456789/events
3

4# Events after a specific date
5GET /api/parcels/f0e1d2c3-b4a5-6789-0abc-def123456789/events?from=2024-03-15T00:00:00Z
6

7# Events before a specific date
8GET /api/parcels/f0e1d2c3-b4a5-6789-0abc-def123456789/events?to=2024-03-20T23:59:59Z
9

10# Events within a date range
11GET /api/parcels/f0e1d2c3-b4a5-6789-0abc-def123456789/events?from=2024-03-15T00:00:00Z&to=2024-03-20T23:59:59Z

All four variations are handled by the same endpoint. When from or to is null, the corresponding filter is simply not applied.

Validating Date Range Parameters

What if the client provides a from date that is after the to date? We should catch this and return a helpful error:

csharp
1if (from.HasValue && to.HasValue && from.Value > to.Value)
2{
3    return BadRequest(new ProblemDetails
4    {
5        Title = "Invalid date range",
6        Detail = "'from' date must be earlier than or equal to 'to' date.",
7        Status = 400
8    });
9}

Add this check before building the query. It prevents executing a query that would always return zero results, and gives the client a clear error message.

Testing with curl

Fetching the full history:

bash
1curl http://localhost:5000/api/parcels/f0e1d2c3-b4a5-6789-0abc-def123456789/events

Expected response:

json
1[
2  {
3    "id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
4    "parcelId": "f0e1d2c3-b4a5-6789-0abc-def123456789",
5    "timestamp": "2024-03-15T10:30:00Z",
6    "eventType": "PickedUp",
7    "description": "Package picked up from sender",
8    "locationCity": "Chicago",
9    "locationState": "IL",
10    "locationCountry": "US",
11    "delayReason": null
12  },
13  {
14    "id": "b2c3d4e5-f6a7-8901-bcde-f12345678901",
15    "parcelId": "f0e1d2c3-b4a5-6789-0abc-def123456789",
16    "timestamp": "2024-03-15T14:00:00Z",
17    "eventType": "Departed",
18    "description": "Left Chicago sorting facility",
19    "locationCity": "Chicago",
20    "locationState": "IL",
21    "locationCountry": "US",
22    "delayReason": null
23  },
24  {
25    "id": "c3d4e5f6-a7b8-9012-cdef-123456789012",
26    "parcelId": "f0e1d2c3-b4a5-6789-0abc-def123456789",
27    "timestamp": "2024-03-15T20:15:00Z",
28    "eventType": "Arrived",
29    "description": "Arrived at Indianapolis hub",
30    "locationCity": "Indianapolis",
31    "locationState": "IN",
32    "locationCountry": "US",
33    "delayReason": null
34  }
35]

Filtering by date range:

bash
1curl "http://localhost:5000/api/parcels/f0e1d2c3-b4a5-6789-0abc-def123456789/events?from=2024-03-15T12:00:00Z&to=2024-03-15T18:00:00Z"

This returns only the "Departed" event, since it is the only one within the 12:00-18:00 window.

Empty Results

When the parcel exists but has no events (or no events match the filter), the endpoint returns 200 OK with an empty array:

json
1[]

This is correct REST behavior. A 404 would mean the parcel does not exist, while an empty 200 means the parcel exists but has no matching events. The distinction matters for clients.

Performance Considerations

For parcels with many events, consider adding a database index on the Timestamp column:

csharp
1protected override void OnModelCreating(ModelBuilder modelBuilder)
2{
3    modelBuilder.Entity<TrackingEvent>()
4        .HasIndex(e => new { e.ParcelId, e.Timestamp });
5}

A composite index on (ParcelId, Timestamp) covers both the filtering and ordering operations. EF Core generates:

sql
1CREATE INDEX IX_TrackingEvents_ParcelId_Timestamp
2ON TrackingEvents (ParcelId, Timestamp);

This index benefits:

  • The POST endpoint's query for the latest event (WHERE ParcelId = X ORDER BY Timestamp DESC LIMIT 1)
  • The GET endpoint's filtered timeline query (WHERE ParcelId = X AND Timestamp >= Y ORDER BY Timestamp)

Complete Controller

Here is the full TrackingEventsController with both endpoints:

csharp
1[ApiController]
2[Route("api/parcels/{parcelId}/events")]
3public class TrackingEventsController : ControllerBase
4{
5    private readonly ParcelTrackingDbContext _context;
6

7    public TrackingEventsController(ParcelTrackingDbContext context)
8    {
9        _context = context;
10    }
11

12    [HttpPost]
13    public async Task<ActionResult<TrackingEventResponse>> Create(
14        Guid parcelId,
15        CreateTrackingEventRequest request)
16    {
17        var parcel = await _context.Parcels
18            .FirstOrDefaultAsync(p => p.Id == parcelId);
19

20        if (parcel is null)
21            return NotFound(new ProblemDetails
22            {
23                Title = "Parcel not found",
24                Detail = $"No parcel exists with ID {parcelId}",
25                Status = 404
26            });
27

28        var latestEvent = await _context.TrackingEvents
29            .Where(e => e.ParcelId == parcelId)
30            .OrderByDescending(e => e.Timestamp)
31            .FirstOrDefaultAsync();
32

33        if (latestEvent is not null && request.Timestamp < latestEvent.Timestamp)
34            return BadRequest(new ProblemDetails
35            {
36                Title = "Invalid event timestamp",
37                Detail = $"Event timestamp is earlier than the most recent event.",
38                Status = 400
39            });
40

41        var trackingEvent = new TrackingEvent
42        {
43            ParcelId = parcelId,
44            Timestamp = request.Timestamp,
45            EventType = request.EventType,
46            Description = request.Description,
47            LocationCity = request.LocationCity,
48            LocationState = request.LocationState,
49            LocationCountry = request.LocationCountry,
50            DelayReason = request.DelayReason
51        };
52

53        _context.TrackingEvents.Add(trackingEvent);
54

55        parcel.Status = request.EventType switch
56        {
57            EventType.PickedUp => ParcelStatus.PickedUp,
58            EventType.Departed => ParcelStatus.InTransit,
59            EventType.Arrived => ParcelStatus.InTransit,
60            EventType.InTransit => ParcelStatus.InTransit,
61            EventType.OutForDelivery => ParcelStatus.OutForDelivery,
62            EventType.DeliveryAttempt => ParcelStatus.OutForDelivery,
63            EventType.Delivered => ParcelStatus.Delivered,
64            EventType.Exception => ParcelStatus.Exception,
65            EventType.Returned => ParcelStatus.Returned,
66            _ => parcel.Status
67        };
68

69        await _context.SaveChangesAsync();
70

71        var response = new TrackingEventResponse
72        {
73            Id = trackingEvent.Id,
74            ParcelId = trackingEvent.ParcelId,
75            Timestamp = trackingEvent.Timestamp,
76            EventType = trackingEvent.EventType.ToString(),
77            Description = trackingEvent.Description,
78            LocationCity = trackingEvent.LocationCity,
79            LocationState = trackingEvent.LocationState,
80            LocationCountry = trackingEvent.LocationCountry,
81            DelayReason = trackingEvent.DelayReason
82        };
83

84        return CreatedAtAction(
85            nameof(GetEventHistory),
86            new { parcelId },
87            response);
88    }
89

90    [HttpGet]
91    public async Task<ActionResult<IEnumerable<TrackingEventResponse>>> GetEventHistory(
92        Guid parcelId,
93        [FromQuery] DateTime? from,
94        [FromQuery] DateTime? to)
95    {
96        var parcelExists = await _context.Parcels
97            .AnyAsync(p => p.Id == parcelId);
98

99        if (!parcelExists)
100            return NotFound(new ProblemDetails
101            {
102                Title = "Parcel not found",
103                Detail = $"No parcel exists with ID {parcelId}",
104                Status = 404
105            });
106

107        if (from.HasValue && to.HasValue && from.Value > to.Value)
108            return BadRequest(new ProblemDetails
109            {
110                Title = "Invalid date range",
111                Detail = "'from' date must be earlier than or equal to 'to' date.",
112                Status = 400
113            });
114

115        var query = _context.TrackingEvents
116            .Where(e => e.ParcelId == parcelId)
117            .AsQueryable();
118

119        if (from.HasValue)
120            query = query.Where(e => e.Timestamp >= from.Value);
121

122        if (to.HasValue)
123            query = query.Where(e => e.Timestamp <= to.Value);
124

125        var events = await query
126            .OrderBy(e => e.Timestamp)
127            .Select(e => new TrackingEventResponse
128            {
129                Id = e.Id,
130                ParcelId = e.ParcelId,
131                Timestamp = e.Timestamp,
132                EventType = e.EventType.ToString(),
133                Description = e.Description,
134                LocationCity = e.LocationCity,
135                LocationState = e.LocationState,
136                LocationCountry = e.LocationCountry,
137                DelayReason = e.DelayReason
138            })
139            .ToListAsync();
140

141        return Ok(events);
142    }
143}

Summary

In this section, you learned how to:

  • Build a GET endpoint that returns an ordered event timeline
  • Use nullable query parameters for optional date range filtering
  • Compose LINQ queries conditionally based on which parameters are present
  • Distinguish between AnyAsync (existence check) and FirstOrDefaultAsync (entity retrieval)
  • Return 200 OK with an empty array when no events match, rather than 404
  • Add a composite index for query performance on ParcelId and Timestamp

Together, the POST and GET endpoints form a complete tracking events API that enforces business rules, maintains chronological integrity, and supports flexible querying.

Event History & Date Range Filtering - Anko Academy