I have two tables (Ansatt, Stilling) that are connected together through a separate table through Foreign keys (Ansatt_Stilling). The new table allows connecting 1 Ansatt with One Stilling. The Stilling table has Start and En dates. The goal is that an Ansatt entity cannot have more than 1 Stilling entity simultaneously. So no overlapping. When the user inputs data in the Ansatt_Stilling table, I try to do so. The controller checks if there is an entry in that table where the Stilling has an overlapping start and end dates.

The Ansatt Model

public class Ansatt
{
    [Key]
    public int Id { get; set; }
    [Required]
    public string Navn { get; set; }

    //Relationships
    public virtual ICollection<Ansatt_Stilling> Ansatt_Stillinger { get; set; }
    public virtual ICollection<Ansatt_Oppgave> Ansatt_Oppgaver { get; set; }

}

The Stilling Model

public class Stilling
{
    [Key]
    public int Id { get; set; }
    [Required]
    public string Stilling_Navn { get; set; }
    [Required]
    public DateTime Start_Dato { get; set; }
    [Required]
    public DateTime Slutt_Dato { get; set; }

    // Relationships
    public virtual ICollection<Ansatt_Stilling> Ansatt_Stillinger { get; set; }

The Ansatt_Stilling Model

public class Ansatt_Stilling
{
    public int ansatt_id { get; set; }
    public int stilling_id { get; set; }

    [ForeignKey("ansatt_id")]
    public virtual Ansatt Ansatt { get; set; }
    [ForeignKey("stilling_id")]
    public virtual Stilling Stilling { get; set; }
}

The Ansatt_Stilling Controller

[HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<IActionResult> Create([Bind("ansatt_id,stilling_id")] Ansatt_Stilling ansatt_Stilling)
    {
        if (!ModelState.IsValid)
        {
            
            if (_context.Ansatt_Stillinger.Any(o => o.stilling_id == ansatt_Stilling.stilling_id) && _context.Ansatt_Stillinger.Any(o => o.ansatt_id == ansatt_Stilling.ansatt_id))
            {

                //ViewBag.Duplikat = "Ansatt allerede har en stilling i den perioden";
                ViewBag.Duplikat = "Allerede Registrert";
                return View();
            }
            else
            {
                bool isOverlapping = _context.Ansatt_Stillinger.Any(v => v.Stilling.Start_Dato == ansatt_Stilling.Stilling.Start_Dato);
                ViewBag.Duplikat = " Start dato";
                _context.Add(ansatt_Stilling);
                await _context.SaveChangesAsync();
                return RedirectToAction(nameof(Index));
            }
        }
        ViewData["ansatt_id"] = new SelectList(_context.Ansatter, "Id", "Navn", ansatt_Stilling.ansatt_id);
        ViewData["stilling_id"] = new SelectList(_context.Stillinger, "Id", "Stilling_Navn", ansatt_Stilling.stilling_id);
        return View(ansatt_Stilling);
    }

What I want to do is something along the lines of

if (_context.Ansatt_Stillinger.Any(o => o.Stilling.Start_Dato== ansatt_Stilling.Stilling.Start_Dato))

Is there a way to have this type of access?

CodePudding user response：

Try this one, I hope it will help you to make better one;

public static bool isConflictWithAny(int ansatt_id, int stilling_id)
{
    var targetStilling = _context.Stilling.FirstOrDefult(x => x.Id == stilling_id);
    if (targetStilling == null) 
        return false;

    DateTime S = targetStilling.Start_Date;
    DateTime E = targetStilling.End_Date;

        
    return _context.Ansatt_Stillinger
           .Any(a=>
                a.ansatt_id == ansatt_id &&        // same ansatt
                a.stilling_id != stilling_id &&    // other stillings
              (
                // x < S  <  E  < z    insider
                (a.Stilling.StartDate <= S && E <= a.Stilling.EndDate) ||

                // S < x  <  E  < z    concurrent at last
                ( a.Stilling.StartDate <= E && a.Stilling.EndDate >= E) ||

                // x < S  <  z  < E    concurrent at first
                ( a.Stilling.StartDate <= S && a.Stilling.EndDate <= E) ||

                // S < x  <  z < E     outsider
                ( S <= a.Stilling.StartDate && a.Stilling.EndDate <= E)
              )
            );
}