Given the above database table, I am trying to write EF query where we show the latest record for each Id Id2 combo but failures will stay listed forever. Successes will only be included if it was in the last week.

The part I think I am struggling with is that If a failure exists for a given Id Id2 combo, but has a future success that failure shouldn't be returned.

Any suggestions on where to begin? The previous attempt tried to do a

.Where(x => x.state == 'Failed' || x.CreatedOn > DateTime.Now.AddDays(-7)

but a fail 10 days ago followed by a success 9 days ago was being listed. (This case should return nothing since the success was over a week ago)

Basically all fails except where a later success UNION with success last 2 days. Is this even possible? I've never spent this long on a linq between 2 tables.

CodePudding user response：

First a question: If the latest state is a failure, do you only want to report the failures since the last success? In other words for the sequence {Failure, Failure, Success, Failure, Failure} do you only want to report the last two Failures? I will assume, yes.

It seems to me that you first need to group by Id2 and calculate two things for each group:

1. What was the latest state.
2. What was the date of the latest success.

From that, you can select members of the group where either:

1. The LatestState = "Success" and Date = LatestSuccessDate, or
2. State = "Failed" and (LatestSuccessDate is null or Date > LatestSuccessDate)

Something like

Data
    .Where(r => r.CreatedOn >= DateTime.Now.AddDays(-7))
    .GroupBy(r => r.Id2)
    .SelectMany(grp => new {
        var latestState = grp
            .OrderByDescending(r => r.CreatedOn)
            .Select(r => r.State)
            .First(),
        var latestSuccessDate = grp
            .OrderByDescending(r => r.CreatedOn)
            .Where(r => r.State = 'Success')
            .Select(r => r.CreatedOn)
            .FirstOrDefault();
        return grp
            .Where(r =>
                (latestState == "Success" && r.CreatedOn == latestSuccessDate)
                || (r.State == "Failure" && (latestSuccessDate == null || r.CreatedOn > latestSuccessDate))
            );
    });

Disclaimer - The above is unchecked and might not be 100% syntactically correct, but conveys my ideas. There are also I'm sure room for optimizations, such as pre-ordering the members of each group.

I expect that the above will perform most of the work in memory. I am not sure if this can be coded in a form that will translate to a server side SQL query. Someone with more skills than I may offer a solution. The goal would be to generate something like:

SELECT D.*
FROM (
    -- Select latest state per group
) LS
LEFT JOIN (
    -- Select the latest success date per group
) LSD
    ON LSD.Id2 = LS.Id2
JOIN DATA D
    ON D.Id2 = LS.Id2
WHERE -- Other conditions 

CodePudding user response：

I am not sure this is translatable to an EF query, but thought I'd post it in case it is (or in case it could provide some inspiration). This is a Linq to Objects approach that should produce the desired output.

If your class is defined as follows:

public class Item
{
    public int Id1 { get; set; }
    public int Id2 { get; set; }
    public DateTime Date { get; set; }
    public State State { get; set; }
}

public enum State
{
    Undefined,
    Success,
    Failed
}

and your items are stored in an Item collection, you can:

1. group the items by Id1 and Id2
2. from each group, select the one item with the latest Date value (using .MaxBy())
   • ( .MaxBy( * ) could alternatively be replaced with .OrderByDescending( * ).First() )
3. filter the now distinct-by-Id1-and-Id2 item collection to keep only the items that either have State = Failed or are no less than a week old

For an item collection List<Item> items, it could be implemented as follows:

List<Item> result = items
    .GroupBy(item => ( item.Id1, item.Id2 ))
    .Select(gr => gr.MaxBy(item => item.Date))
    .Where(item =>
          item.State == State.Failed ||
          item.Date > DateTime.Now.AddDays(-7))
    .ToList();

Example fiddle here.