Using WDDCEDEEIWEB201to find associated timestamps from database

Question

I have to following LINQ query, where I look for some different timestamps in an DB:

var issues = 
    from i in ReadOnlyContext.Issues
    where i.TruckID == truckID && i.OutOfOrderStart < startDate && 
        i.OutOfOrderEnd > endDate ||
        i.TruckID == truckID && i.OutOfOrderStart > startDate && 
        i.OutOfOrderEnd < endDate ||
        i.TruckID == truckID && i.OutOfOrderStart < startDate && 
        i.OutOfOrderStart < endDate ||
        i.TruckID == truckID && i.OutOfOrderStart > startDate && 
        i.OutOfOrderEnd > endDate
    select i;

My problem is that I would like to filter the query, to only return entries, where OutOfOrderStart and OutOfOurderEnd is from the same row. How can I accomplish this?

Answer 1

All of those comparisons are comparisons on the same row. Your statement is saying to find each row, i, such that the TruckID of the ith row is equal to truckID (note that you don't need to repeat this test in each subcondition) and whose start/end dates match on one of the subconditions. If you're not getting the data you expect, then I would guess that your conditions don't match your requirements.

Using the distributive law, you could rewrite it as:

var issues = 
    from i in ReadOnlyContext.Issues
    where i.TruckID == truckID &&
        (i.OutOfOrderStart < startDate && i.OutOfOrderEnd > endDate
        || i.OutOfOrderStart > startDate && i.OutOfOrderEnd < endDate
        || i.OutOfOrderStart < startDate && i.OutOfOrderStart < endDate
        || i.OutOfOrderStart > startDate && i.OutOfOrderEnd > endDate)
    select i;

Answer 2

Hi and thanks for the answer :)

I didn't check if the OutOfOrderEnd or Start was included too.. But I can all be much more simpler:

var issues = 
    from i in ReadOnlyContext.Issues
    let startInside = (startDate < i.OutOfOrderStart && i.OutOfOrderStart < endDate)
    let endInside = (startDate < i.OutOfOrderEnd && i.OutOfOrderStart < endDate)
    let allOutside = (startDate > i.OutOfOrderEnd && endDate < i.OutOfOrderEnd)
    where i.TruckID == truckID && (startInside || endInside || allOutside)
select i;