Asp.Net MVC LINQ query to check if agreement already exists between 2 dates

I have an Asp.Net MVC5 project which I need some help with. The Logic is that a Customer needs to have an agreement. This is fine because I can create the first agreement. The problem I am having is that if another agreement needs to be created for the customer it can't be within the same period as the existing one. Initially I am thinking of placing something into the Agreement controller in the Create method to do the check. What I have currently is

Controller:

 // GET: Agreements/Create
    public ActionResult Create(int? Id, string Name)
    {

        if (Id == null)
        {
            return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
        }

        Agreement model = new Agreement();
       // model.FullName = Name;
        model.CustomerId = Id;

        ViewBag.CustomerId = new SelectList(db.Customers, "CustomerId", "Name");
        ViewBag.SupplierId = new SelectList(db.Suppliers, "SupplierId", "Name");

        return View(model);
    }

    // POST: Agreements/Create
    // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
    // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Create([Bind(Include = "AgreementId,CustomerId,CustomerHasAgreement,SupplierId,Start,End,AgreementPlanId,AgreementType")] Agreement agreement)
    {

        if (ModelState.IsValid)
        {
            var bExist = db.Agreements.Any(s => (s.Start <= agreement.End && s.End >= agreement.Start));    //or your check logic
            if (!bExist)
            { 
                db.Agreements.Add(agreement);
                db.SaveChanges();
                return RedirectToAction("Details", "Agreements", new { id = agreement.AgreementId });
            }
            else 
                ModelState.AddModelError("Error", "Sorry something happened unexpectedly !!!");
                return RedirectToAction("Index");

        }

        ViewBag.CustomerId = new SelectList(db.Customers, "CustomerId", "Name", agreement.CustomerId);
        ViewBag.SupplierId = new SelectList(db.Suppliers, "SupplierId", "Name", agreement.SupplierId);
        return View(agreement);
    }

Model:

    public class Agreement
{
    public int AgreementId { get; set; }
    public int? CustomerId { get; set; }

    [HiddenInput(DisplayValue = false)]
    public bool CustomerHasAgreement { get; set; }
    public int SupplierId { get; set; }
    [DisplayFormat(DataFormatString = "{0:dd/MM/yyyy}")]
    public DateTime? Start { get; set; }
    [DisplayFormat(DataFormatString = "{0:dd/MM/yyyy}")]
    public DateTime? End { get; set; }
    public int? AgreementPlanId { get; set; }
    public virtual Customer Customer { get; set; }
    public virtual Supplier Supplier { get; set; }
    public virtual Plan Plans { get; set; }
    public int weeks { get; set; }

    public AgreementType AgreementType { get; set; }

    public string FullName { get; set; }

 }

public enum AgreementType
{
    Low = 1,
    Moderate = 2,
    High = 3,
    Emergency = 4,
    Temporary = 5,
    [Display(Name = "Long Term")]
    Long_Term = 6
}    
}

I have tried to use a LINQ statement to check to see if the new agreement is greater than the end date of the old agreement.

 var bExist = db.Agreements.Any(s => (s.Start <= agreement.End && s.End >= agreement.Start));    //or your check logic

This only seems to either skip over the if statement and not insert the record

Please can someone help me please. I have tried going through the Microsoft tutorial http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/advanced-entity-framework-scenarios-for-an-mvc-web-application but seem to constantly get stuck when trying to implement it into my solution.

Many thanks

Mark

Answers


@markabarmi Do an OR operator. You're looking for 1 or 2 of 2 possible issues. Doesn't need to have both to trigger bExists to be true. Also limit that result set to your specific customer in question.

var bExist = db.Agreements.Any(s => (s.CustomerId == agreement.CustomerId) && ((s.Start <= agreement.End) || (s.End >= agreement.Start)));

Need Your Help

android emulator on eclipse won't open

android ubuntu emulation

I've gotten it to work before but now the android emulator on eclipse refuses to open. No new window, nothing. Most of the time the console says nothing when I tell it to launch, other times it say...

Pivoting help needed

sql sql-server sql-server-2008 pivot pivot-without-aggregate

Please help me with this. I am totally stuck. I have coders block or something.