Query database and unsaved changes for validation with Entity Framework 6

entity-framework entity-framework-6 validation

Question

I have a data layer using Entity Framework 6 Database First. One of my entities represents a time span - it has a start date and an end date.

public class Range
{
    public Guid ID { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
}

I want to add validation that ensures Ranges never have overlapping dates. The application will be saving added, modified and deleted Ranges all at once. So I was playing with overriding ValidateEntity in my DbContext. Here is what I ended up with and then realized I still have a problem:

protected override DbEntityValidationResult ValidateEntity(DbEntityEntry entityEntry, IDictionary<object, object> items)
{
    var result = new DbEntityValidationResult(entityEntry, new List<DbValidationError>());
    if (entityEntry.Entity is Range && (entityEntry.State == EntityState.Added || entityEntry.State == EntityState.Modified))
    {
        Range range = entityEntry.Entity as Range;

        if (Ranges.Local.Any(p => range.EndDate >= p.StartDate && range.StartDate <= p.EndDate) ||
            Ranges.Any(p => range.EndDate >= p.StartDate && range.StartDate <= p.EndDate))
        {
            result.ValidationErrors.Add(
                    new System.Data.Entity.Validation.DbValidationError("EndDate", "Cannot overlap another range.")); //Could be StartDate's fault but we just need save to fail
        }
    }

    if (result.ValidationErrors.Count > 0)
    {
        return result;
    }
    else
    {
        return base.ValidateEntity(entityEntry, items);
    }
}

The problem is that Ranges.Local does not query the database, and Ranges, which queries the database, does not include the unsaved changes.

For example: If I have a Range starting 9/1 and ending 9/8 in the database, which I modified in memory to start 8/1 and end 8/8, and I have also added a new Range starting 9/6 ending 9/12, then Ranges.Any(p => blockOut.EndDate >= p.StartDate && blockOut.StartDate <= p.EndDate) will return true for the added Range because the currently saved Range overlaps. But it is actually valid because in memory, those dates have been changed and when it saves there will be no overlaps.

Is there any way to query the combination of Local and the database, i.e. only query the database for records that are not in Local?

EDIT:

Updated code that I think works in response to Steve Py's answer below:

//Check Local first because we may be able to invalidate without querying the DB
if (BlockOutPeriods.Local.Any(p => blockOut.ID != p.ID && blockOut.EndDate >= p.StartDate && blockOut.StartDate <= p.EndDate))
{
    result.ValidationErrors.Add(
            new System.Data.Entity.Validation.DbValidationError("EndDate",
            "Block out cannot overlap another block out."));
}
else 
{
    var editedIDs = BlockOutPeriods.Local.Select(p => p.ID);

    //!Contains avoids reading & mapping all records to Range model objects - better?
    if (BlockOutPeriods.Any(p => blockOut.EndDate >= p.StartDate && blockOut.StartDate <= p.EndDate && !editedIDs.Contains(p.ID)))
    {
        result.ValidationErrors.Add(
                new System.Data.Entity.Validation.DbValidationError("EndDate",
                "Block out cannot overlap another block out."));
    }
}
1
0
11/26/2019 2:29:16 AM

Accepted Answer

You could use a Union between the local state and data state, providing an IEqualityComparer to match the PKs so that local is used without duplicating from data set.

var result = Ranges.Local
    .Where(p => range.EndDate >= p.StartDate || range.StartDate <= p.EndDate)
    .Union(
        Ranges.Where(p => range.EndDate >= p.StartDate || range.StartDate <= p.EndDate),
        new RangeEqualityComparer())
    .Any(p => p.RangeId != range.RangeId && range.EndDate >= p.StartDate && range.StartDate <= p.EndDate);

This should:

  • pull any ranges from Local which potentially span the desired start/end date.
  • union with any ranges from DB which potentially span the desired date range.
  • check for the existence of any other record (<> my ID) that covers the range.
1
11/26/2019 12:07:42 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow