Prevent user from submitting a timesheet record twice in MVC C# and using Entity Framework

Question

I have a timesheet system written in ASP.NET MVC in C# with Entity Framework 6 and I want to prevent users from submitting overlapping / clashing timesheets for the same job.

The SQL Server timesheet table has a number of columns, these are the most relevant:

╔═══════════════╦══════════╗
║ Column Name   ║ DataType ║
╠═══════════════╬══════════╣
║ UserId        ║ int      ║
║ JobId         ║ int      ║
║ StartDateTime ║ datetime ║
║ EndDateTime   ║ datetime ║
╚═══════════════╩══════════╝

and here's a sample of data:

╔══════╦════════╦═══════╦══════════════════╦══════════════════╗
║  Id  ║ UserId ║ JobId ║  StartDateTime   ║   EndDateTime    ║
╠══════╬════════╬═══════╬══════════════════╬══════════════════╣
║ 4893 ║    245 ║   119 ║ 26/08/2014 17:00 ║ 27/08/2014 08:00 ║
╚══════╩════════╩═══════╩══════════════════╩══════════════════╝

What would be the most efficient way to prevent a clash of date/time if someone tries to submit an overlapping timesheet for the same JobId?

Thanks for any pointers on this, as I want to keep the system pretty fast - which it currently is but the timesheet table will eventually have many rows to traverse through.

This type of validation needs to go to the database.The best place to do that is in the context class - a bit like checking for duplicates. But instead of an index, you will need a check constraint - http://stackoverflow.com/a/18736484/150342 — Colin, Nov 28 '14 at 12:02

score 1 · Answer 1 · answered Nov 28 '14 at 05:17

Here is the SQL you need to see if there are any overlaps. I'll leave the conversion to EF as an exercise for the reader.

SELECT Count(*) 
FROM TimeEntry 
WHERE UserId = @UserId AND JobId = @JobId AND 
( 
    (StartDateTime < @StartDateTime AND @StartDateTime < EndDateTime)
OR
    (StartDateTime < @EndDateTime  AND @EndDateTime < EndDateTime)
OR
    (@StartDateTime <= StartDateTime AND EndDateTime <= @EndDateTime )
)

score 1 · Accepted Answer · 2014-11-29T01:07:26.717

1

In your controller, assuming the POST method is

public ActionResult Edit(List<TimeRecord> timeRecords);
{
  // Sort by start time and group by job number
  var jobRecords = timeRecords.OrderBy(r => r.StartDateTime).GroupBy(r => r.JobID);
  // enumerate each group
  foreach (var group in jobRecords)
  {
    // enumerate each record in the group
    foreach (var record in group)
    {
      if (group.Any(r => r != record && r.StartDateTime >= record.StartDateTime && r.StartDateTime < record.EndDateTime))
      {
        // we have overlapping records?
      }
    }
  }
}

edited Nov 29 '14 at 01:07

answered Nov 28 '14 at 07:43

1

@beebul. Forgot to exclude the current record in the `.Any()` statement (now updated). Did a few tests with 100 dummy records. Takes between 2 and 3 millisecond to complete if all valid (i.e. insignificant) – Nov 29 '14 at 01:13
Sorry I can't get this to work, it throws an error even when the timesheet doesn't overlap. – beebul Nov 29 '14 at 10:17
Works fine for me (did you check my edit). What is the error? – Nov 29 '14 at 10:19
Sorry, not an error, just that any entry gets flagged as an overlap even when it isn't.. – beebul Nov 29 '14 at 20:21
I don't see how that's possible. You said that is valid to have overlapping times in different jobs - and this code allows that (e.g. `JobID=1, Start=8.00AM, End=9.00AM`, `JobID=1, Start=10.00AM, End=11.00AM`, `JobID=2, Start=8.00AM, End=9.00AM` is valid) – Nov 30 '14 at 00:03

Prevent user from submitting a timesheet record twice in MVC C# and using Entity Framework

2 Answers2