locked
when i try to insert data check if data exist or not RRS feed

  • Question

  • User1772251356 posted

    Hello 

    I am beginner,

    when I try to insert a record into the database if data exist the how I will show into view the data already exist in MVC with entity framework

     

    public ActionResult Create(Employee emp)
    {
    Employee Employee = new Employee() {
    Eid = emp.Eid,
    Ename = emp.Ename,
    Salary = emp.Salary,
    City = emp.City,
    JoinDate = emp.JoinDate,
    IsActive= emp.IsActive
    };
    if (ModelState.IsValid)
    {
    db.Employees.Add(Employee);
    db.SaveChanges();
    return RedirectToAction("Index");
    }
    else
    {
    return View();
    }
    }

    Wednesday, June 26, 2019 2:31 PM

Answers

  • User1520731567 posted

    Hi santosh1991,

    If you do not want to change your design,you could add If clause in your httppost Create action,

    But what do you use to judge whether this record exists?

    In addition to the primary key, all other fields should be match,

    or is there a unique field in the table?

    About If clause,you could add Any() to check if this record exist or not,please refer to the below:

    public ActionResult Create(Employee emp)
    {

    ....
    if (ModelState.IsValid) { if (db.Employees.ToList().Any(o => o.Ename== emp.Ename && o.Salary== emp.Salary...))//if true,the record already exists { ... } else { db.Employees.Add(Employee); db.SaveChanges(); return RedirectToAction("Index"); } }
    ...
    }

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 27, 2019 7:00 AM

All replies

  • User-821857111 posted

    Catch the exception that happens when you try to insert a duplicate and add an entry to ModelState:

    if (ModelState.IsValid)
    {
        try{

            db.Employees.Add(Employee);
            db.SaveChanges();
            return RedirectToAction("Index");
        }

        catch(Exception ex)
        {
            ModelState.AddFormError("Record already exists");
            return View();
        }
    }

    You probably want to catch the correct exception type. I can;t remember what it is off-hand.

    By the way, it looks like your code sets the value of the Primary Key. Is that the case? If so, why? 

    Wednesday, June 26, 2019 2:42 PM
  • User1772251356 posted

    I didn't set the type as primary key when I insert into record it allows duplicate values.

    when i try  this ModelState.AddFormError("Record already exists"); 

    i got error 

    .

    Wednesday, June 26, 2019 2:55 PM
  • User-821857111 posted

    santosh1991

    when i try  this ModelState.AddFormError("Record already exists"); 

    i got error 

    Sorry - try ModelState.AddModelError("Custom", "Record already exists");

    Then add a validation summary helper to your view:

    @Html.ValidationSummary(false)

    Wednesday, June 26, 2019 3:13 PM
  • User1772251356 posted

    ModelStateDictonary doesn't contain a definition for "AddFormError".

    when i used try catch,  it allows also duplicate value into the database. didn't get any error message.

    Wednesday, June 26, 2019 3:24 PM
  • User1120430333 posted

    If you are  doing things correctly, then you should never be trying to set the ID of the object you are trying to insert into a database table using EF. 

    An ID = 0 tells EF that it is going to insert a object, which is set to 0 when you created to the object. So, you don't ever address the ID property of the object.

    An ID > 0 tells EF that it is suppose to update an existing object by its ID in the database table.

    https://www.youtube.com/watch?v=hg3H_pAzoPI

    Fundamentally, you're doing something wrong.

    Wednesday, June 26, 2019 3:25 PM
  • User-821857111 posted

    santosh1991

    ModelStateDictonary doesn't contain a definition for "AddFormError".

    Correct. I edited my previous response accordingly.

    santosh1991

    when i used try catch,  it allows also duplicate value into the database. didn't get any error message.

    That is poor design. Really, you should design the database to prevent duplicates by adding unique constraints (https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-unique-constraints?view=sql-server-2017). If you can't do that, you can either use a stored proc to insert the data, using IF EXISTS to detect a duplicate (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql?view=sql-server-2017), or execute a query using EF to fetch any duplicates before inserting, and if there are any matches, present an error message to the user.

    Wednesday, June 26, 2019 3:59 PM
  • User1520731567 posted

    Hi santosh1991,

    If you do not want to change your design,you could add If clause in your httppost Create action,

    But what do you use to judge whether this record exists?

    In addition to the primary key, all other fields should be match,

    or is there a unique field in the table?

    About If clause,you could add Any() to check if this record exist or not,please refer to the below:

    public ActionResult Create(Employee emp)
    {

    ....
    if (ModelState.IsValid) { if (db.Employees.ToList().Any(o => o.Ename== emp.Ename && o.Salary== emp.Salary...))//if true,the record already exists { ... } else { db.Employees.Add(Employee); db.SaveChanges(); return RedirectToAction("Index"); } }
    ...
    }

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 27, 2019 7:00 AM
  • User-821857111 posted

    if (db.Employees.ToList().Any(...

    No, don't do that. ToList() will force evaluation of the query which will result in the equivalent of "select * from Employees" being executed against the database. If you are writing a system for a government agency, there could literally be millions of records in the database. Leave ToList() out of the LINQ query, and it should translate to SQL that makes use of IF EXISTS and only one SQL command being executed against the db.

    Thursday, June 27, 2019 10:54 AM
  • User1772251356 posted

    Yes, right my DB design is very poor, in real time we must follow design DB in proper ways. thank you for the suggestion I am really happy with your support.

    once again thank you. 

    Friday, June 28, 2019 10:55 AM