I’m implementing an order related project with C# code first method and the code goes like this.
There is a web system to make orders and there is other mechanism to received tracking status. (Order prepare, Order Dispatched, Order received to warehouse, delivered etc…)

Also orders can be made by phone calls and there are agents who prepare orders manually and quickly dispatch. In this scenario, it will add the order end of the day and tracking comes as expected.

Eg: Customer give a call and the store agent prepare it without adding information to the system. (But he is logging orders to a book, not to the system)
Then the courier service comes and scan tracking sticker (barcode sticker) and confirm the order is picked up. Same time it logged relevant info to our tacking table using courier service’s API. Likewise each and every point it has a tracking event. (if it comes to a warehouse, if it hand over to a linehaul, if it hand over to an air line shipping etc…)
At the end of the day there is computer operator and he enter all manual orders to the system.

public class Order //Here I mentioned only few relevant properties for making this question short.
{
 public int Id { get; set;}
 public Address CustomerAddress { get; set; }
 public string LabelNo { get; set;}

}

public class Tracking
{
 public int Id { get; set;}
 public int? OrderId { get; set; } //this is nullable since, it might received manual orders which enter to the system later.
 public Order Order{ get; set; }
 public string LabelNo { get; set;}
 public string Status { get; set;}
 public string Location { get; set;}
}

With a get API, I need to take last tracking object with the order.
Here are DTOs.

public class OrderDataDto
{
 public int Id { get; set;}
 public AddressDto CustomerAddress { get; set; }
 public string LabelNo { get; set;}
 public TrackingDto RecentTracking { get; set;}
}

public class TrackingDto
{
 public int Id { get; set;}
 public string Status { get; set;}
 public string Location { get; set;}
}

As you can see in this Dtos, there is a link between orderDto and tackingDto is one to one. (Actually this doesn’t work, I need to do simpler like this). But DB level entity has one to many relationship between Order and Tracking.

Here is the Get method.

public async Task<PagedResultDto<OrderDataDto>> GetOrders(InputDto input)
 {
     try
     {
         var query = await _orderManager.GetAllOrdersAsync();
         query = DtoFilter.FilterBy<Order, OrderDataDto>(query, input.Filter);//Filter means, it comes Odata from the query string.

         var totalCount = query.Count();

         List<Order> orders = query
         .OrderBy(input.Sorting)
         .Skip(input.SkipCount)
         .Take(input.MaxResultCount)
         .ToList();

         var orderDtoList = ObjectMapper.Map<List<Order>, List<OrderDataDto>>(orders);               
         return new PagedResultDto<OrderDataDto>(totalCount, orderDtoList);
     }
     catch (Exception ex)
     {
         throw new UserFriendlyException(ex.Message);
     }
 }

This is what I expect as a response at last,

{
    "result": {
        "totalcount": 22,
        "items": [
            {
                "id": "7894652",
                "customeraddress": {
                    "name": "john",
                    "phoneno": "0123456789",
                    "address": "45/B, Spenser building, Main street, Kalundali"
                },
                "labelno": "AE01254785",
                "recenttracking ": {
                    "id": 1548,
                    "status": "order dispatched",
                    "location": "Kalundali"
                }
            },
            {
               .....................
               .....................
               .....................
            }
        ]
    }
}

Also there is filter for query with OData.

Eg: If i need to get orders which having recent tracking status as “order dispatched”, then the query like below,

/api/orders/order/getorders?filter=recenttracking/status eq 'order dispatched'

The challenge is, DB level it has one to many relationship between Order and Tracking, while I need to get output as last tracking with OrderDto.

Any expert can advice me how to get a final output like above sample json with Odata filtration?