Cumulative sum of stock movements when dates and id is not in the same order

Question

I am calculating cumulative sum of stock movements according to shipping bill date

IQueryable<StokHareketCumulative> query =
        from stockMovement1 in ce.StockMovements
        from stockMovement2 in ce.StockMovements
        join stock in ce.Stocks on stockMovement1.StokId equals stock.Id
        where 
         stockMovement1.StockId == stockMovement2.StockId && 
         stockMovement1.ShippingBill.Date >= stockMovement2.ShippingBill.Date //(last_line)
        ...

However there may be more than one shipping date with the same value

In this case I am trying to add stock movement Id in comparison.

//last_line goes to:

(stockMovement1.ShippingBill.Date == stockMovement2.ShippingBill.Date ?   
stockMovement1.ShippingBill.Date >= stockMovement2.ShippingBill.Date && stockMovement1.Id >= stockMovement2.Id 
: stockMovement1.ShippingBill.Date >= stockMovement2.ShippingBill.Date)  

But I don't get right calculation.

How can I get right calculation?

Answer 1

Your problem is that your Date field does not have enough granularity to properly order on that field. what happens here is that, if you sort a field, by default it maintains the order before the sort when there are conflicts.

You're already trying to mitigate this with

(stockMovement1.ShippingBill.Date == stockMovement2.ShippingBill.Date ?   
stockMovement1.ShippingBill.Date >= stockMovement2.ShippingBill.Date && stockMovement1.Id >= stockMovement2.Id 
: stockMovement1.ShippingBill.Date >= stockMovement2.ShippingBill.Date)

I suggest another solution:

stockMovement1.ShippingBill.Date > stockMovement2.ShippingBill.Date || stockMovement1.Id > stockMovement2.Id 

This makes use of the C# OR operator function to return early if the solution is already known. If Date1 is later than Date2, it returns true immediately and does not evaluate further. If this is not the case (so false), it returns true if Id1 is greater than Id2, otherwise it returns false. I assume Id will be unique obviously.

In fact, if Id is unique and autonumbered with an incrementor, it's better to just always compare with Id:

stockMovement1.Id > stockMovement2.Id

Because an autoincremented Id has an implication that Items from day N always have a higher number than those of day N-1, which means that ordering by ID also orders them chronologically.