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. We'll extend the service interface to support querying events with optional date range filtering.

Extending the Service Interface

First, we add a method for retrieving event history with optional filters:

csharp
1public interface ITrackingEventService
2{
3    Task<TrackingEventDto> CreateAsync(Guid parcelId, CreateTrackingEventRequest request);
4    Task<List<TrackingEventDto>> GetByParcelIdAsync(
5        Guid parcelId,
6        DateTime? from = null,
7        DateTime? to = null);
8}

The parameters use default values (null) to make filtering optional. When omitted, the method returns the full history.

Implementing GetByParcelIdAsync

The service method handles event retrieval with optional filtering.

Step 1: Verify the Parcel Exists

csharp
1public async Task<List<TrackingEventDto>> GetByParcelIdAsync(
2    Guid parcelId,
3    DateTime? from = null,
4    DateTime? to = null)
5{
6    var parcelExists = await _context.Parcels
7        .AnyAsync(p => p.Id == parcelId);
8

9    if (!parcelExists)
10        throw new KeyNotFoundException($"Parcel with ID {parcelId} not found.");
11

12    // Query building follows...
13}

Notice 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: Validate Date Range

Before building the query, validate that the date range makes sense:

csharp
1if (from.HasValue && to.HasValue && from.Value > to.Value)
2    throw new ArgumentException("'from' date must be earlier than or equal to 'to' date.");

This prevents executing a query that would always return zero results.

Step 3: 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    query = query.Where(e => e.Timestamp >= from.Value);
7

8if (to.HasValue)
9    query = query.Where(e => e.Timestamp <= to.Value);

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 4: Execute and Map

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

csharp
1return await query
2    .OrderBy(e => e.Timestamp)
3    .Select(e => new TrackingEventDto
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();

Using Select to project directly to the 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 Service Method

Putting all the steps together:

csharp
1public async Task<List<TrackingEventDto>> GetByParcelIdAsync(
2    Guid parcelId,
3    DateTime? from = null,
4    DateTime? to = null)
5{
6    var parcelExists = await _context.Parcels
7        .AnyAsync(p => p.Id == parcelId);
8

9    if (!parcelExists)
10        throw new KeyNotFoundException($"Parcel with ID {parcelId} not found.");
11

12    if (from.HasValue && to.HasValue && from.Value > to.Value)
13        throw new ArgumentException("'from' date must be earlier than or equal to 'to' date.");
14

15    var query = _context.TrackingEvents
16        .Where(e => e.ParcelId == parcelId)
17        .AsQueryable();
18

19    if (from.HasValue)
20        query = query.Where(e => e.Timestamp >= from.Value);
21

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

25    return await query
26        .OrderBy(e => e.Timestamp)
27        .Select(e => new TrackingEventDto
28        {
29            Id = e.Id,
30            ParcelId = e.ParcelId,
31            Timestamp = e.Timestamp,
32            EventType = e.EventType.ToString(),
33            Description = e.Description,
34            LocationCity = e.LocationCity,
35            LocationState = e.LocationState,
36            LocationCountry = e.LocationCountry,
37            DelayReason = e.DelayReason
38        })
39        .ToListAsync();
40}

The Controller GET Endpoint

The controller delegates to the service and handles HTTP concerns:

csharp
1[HttpGet]
2public async Task<ActionResult<IEnumerable<TrackingEventDto>>> GetEventHistory(
3    Guid parcelId,
4    [FromQuery] DateTime? from,
5    [FromQuery] DateTime? to)
6{
7    try
8    {
9        var events = await _trackingEventService.GetByParcelIdAsync(parcelId, from, to);
10        return Ok(events);
11    }
12    catch (KeyNotFoundException ex)
13    {
14        return NotFound(new ProblemDetails
15        {
16            Title = "Parcel not found",
17            Detail = ex.Message,
18            Status = 404
19        });
20    }
21    catch (ArgumentException ex)
22    {
23        return BadRequest(new ProblemDetails
24        {
25            Title = "Invalid date range",
26            Detail = ex.Message,
27            Status = 400
28        });
29    }
30}

The controller translates service exceptions into appropriate HTTP responses while remaining thin.

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 service method. When from or to is null, the corresponding filter is simply not applied.

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 Service and Controller

Here is the full TrackingEventService with both methods:

csharp
1public class TrackingEventService : ITrackingEventService
2{
3    private readonly ParcelTrackingDbContext _context;
4

5    public TrackingEventService(ParcelTrackingDbContext context)
6    {
7        _context = context;
8    }
9

10    public async Task<TrackingEventDto> CreateAsync(Guid parcelId, CreateTrackingEventRequest request)
11    {
12        var parcel = await _context.Parcels
13            .FirstOrDefaultAsync(p => p.Id == parcelId);
14

15        if (parcel is null)
16            throw new KeyNotFoundException($"Parcel with ID {parcelId} not found.");
17

18        var latestEvent = await _context.TrackingEvents
19            .Where(e => e.ParcelId == parcelId)
20            .OrderByDescending(e => e.Timestamp)
21            .FirstOrDefaultAsync();
22

23        if (latestEvent is not null && request.Timestamp < latestEvent.Timestamp)
24            throw new InvalidOperationException(
25                $"Event timestamp {request.Timestamp:O} is earlier than the most recent event at {latestEvent.Timestamp:O}. " +
26                "Events must be in chronological order.");
27

28        var trackingEvent = new TrackingEvent
29        {
30            ParcelId = parcelId,
31            Timestamp = request.Timestamp,
32            EventType = request.EventType,
33            Description = request.Description,
34            LocationCity = request.LocationCity,
35            LocationState = request.LocationState,
36            LocationCountry = request.LocationCountry,
37            DelayReason = request.DelayReason
38        };
39

40        _context.TrackingEvents.Add(trackingEvent);
41

42        parcel.Status = request.EventType switch
43        {
44            EventType.PickedUp => ParcelStatus.PickedUp,
45            EventType.Departed => ParcelStatus.InTransit,
46            EventType.Arrived => ParcelStatus.InTransit,
47            EventType.InTransit => ParcelStatus.InTransit,
48            EventType.OutForDelivery => ParcelStatus.OutForDelivery,
49            EventType.DeliveryAttempt => ParcelStatus.OutForDelivery,
50            EventType.Delivered => ParcelStatus.Delivered,
51            EventType.Exception => ParcelStatus.Exception,
52            EventType.Returned => ParcelStatus.Returned,
53            _ => parcel.Status
54        };
55

56        await _context.SaveChangesAsync();
57

58        return new TrackingEventDto
59        {
60            Id = trackingEvent.Id,
61            ParcelId = trackingEvent.ParcelId,
62            Timestamp = trackingEvent.Timestamp,
63            EventType = trackingEvent.EventType.ToString(),
64            Description = trackingEvent.Description,
65            LocationCity = trackingEvent.LocationCity,
66            LocationState = trackingEvent.LocationState,
67            LocationCountry = trackingEvent.LocationCountry,
68            DelayReason = trackingEvent.DelayReason
69        };
70    }
71

72    public async Task<List<TrackingEventDto>> GetByParcelIdAsync(
73        Guid parcelId,
74        DateTime? from = null,
75        DateTime? to = null)
76    {
77        var parcelExists = await _context.Parcels
78            .AnyAsync(p => p.Id == parcelId);
79

80        if (!parcelExists)
81            throw new KeyNotFoundException($"Parcel with ID {parcelId} not found.");
82

83        if (from.HasValue && to.HasValue && from.Value > to.Value)
84            throw new ArgumentException("'from' date must be earlier than or equal to 'to' date.");
85

86        var query = _context.TrackingEvents
87            .Where(e => e.ParcelId == parcelId)
88            .AsQueryable();
89

90        if (from.HasValue)
91            query = query.Where(e => e.Timestamp >= from.Value);
92

93        if (to.HasValue)
94            query = query.Where(e => e.Timestamp <= to.Value);
95

96        return await query
97            .OrderBy(e => e.Timestamp)
98            .Select(e => new TrackingEventDto
99            {
100                Id = e.Id,
101                ParcelId = e.ParcelId,
102                Timestamp = e.Timestamp,
103                EventType = e.EventType.ToString(),
104                Description = e.Description,
105                LocationCity = e.LocationCity,
106                LocationState = e.LocationState,
107                LocationCountry = e.LocationCountry,
108                DelayReason = e.DelayReason
109            })
110            .ToListAsync();
111    }
112}

And the complete controller:

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

7    public TrackingEventsController(ITrackingEventService trackingEventService)
8    {
9        _trackingEventService = trackingEventService;
10    }
11

12    [HttpPost]
13    public async Task<ActionResult<TrackingEventDto>> Create(
14        Guid parcelId,
15        CreateTrackingEventRequest request)
16    {
17        try
18        {
19            var result = await _trackingEventService.CreateAsync(parcelId, request);
20            return CreatedAtAction(nameof(GetEventHistory), new { parcelId }, result);
21        }
22        catch (KeyNotFoundException ex)
23        {
24            return NotFound(new ProblemDetails
25            {
26                Title = "Parcel not found",
27                Detail = ex.Message,
28                Status = 404
29            });
30        }
31        catch (InvalidOperationException ex)
32        {
33            return BadRequest(new ProblemDetails
34            {
35                Title = "Invalid event timestamp",
36                Detail = ex.Message,
37                Status = 400
38            });
39        }
40    }
41

42    [HttpGet]
43    public async Task<ActionResult<IEnumerable<TrackingEventDto>>> GetEventHistory(
44        Guid parcelId,
45        [FromQuery] DateTime? from,
46        [FromQuery] DateTime? to)
47    {
48        try
49        {
50            var events = await _trackingEventService.GetByParcelIdAsync(parcelId, from, to);
51            return Ok(events);
52        }
53        catch (KeyNotFoundException ex)
54        {
55            return NotFound(new ProblemDetails
56            {
57                Title = "Parcel not found",
58                Detail = ex.Message,
59                Status = 404
60            });
61        }
62        catch (ArgumentException ex)
63        {
64            return BadRequest(new ProblemDetails
65            {
66                Title = "Invalid date range",
67                Detail = ex.Message,
68                Status = 400
69            });
70        }
71    }
72}

Summary

In this section, you learned how to:

  • Extend the service interface to support querying with optional parameters
  • Build a service method that returns an ordered event timeline with date filtering
  • Use nullable parameters with default values for optional filtering
  • Compose LINQ queries conditionally based on which parameters are present
  • Validate date range parameters in the service layer before executing queries
  • Distinguish between AnyAsync (existence check) and FirstOrDefaultAsync (entity retrieval)
  • Translate service exceptions (KeyNotFoundException, ArgumentException) to appropriate HTTP responses in the controller
  • Return 200 OK with an empty list when no events match, rather than 404
  • Add a composite index for query performance on ParcelId and Timestamp

Together, the service layer and thin controllers form a complete tracking events API that enforces business rules, maintains chronological integrity, and supports flexible querying with proper separation of concerns.