locked
validate duplication in EF RRS feed

  • Question

  • Hi All.

    My WPF form adds record to SQL database using SP with multiple parameters. In this project I'm using EF Code First with existing database. All fields in the form are required. The user must enter fields such as:
        1. EmpNo
        2. First Name
        3. Last Name
        4. Email

    I would like to validate for duplication EmpNo field on LostFucus event if same value exist in database. How it to do if is it possible?

    Thanks.


    Monday, April 20, 2020 5:16 PM

All replies

  • Hello,

    Using lost focus can be done as shown here, read all the replies as there are some things to watch out for.

    https://stackoverflow.com/questions/103575/wpf-validation-not-firing-on-first-lostfocus-of-the-textbox


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, April 20, 2020 5:30 PM
  • Hi Karen Payne. Thanks for reply.

    The link that you suggested show validation for empty field. And I would like to validate EmpId field if user will enter existing value. I would like to validate duplication on LostFocus event.

    Thanks.

    Monday, April 20, 2020 5:50 PM
  • Hi Karen Payne. Thanks for reply.

    The link that you suggested show validation for empty field. And I would like to validate EmpId field if user will enter existing value. I would like to validate duplication on LostFocus event.

    Thanks.

    You can't get a duplicate Emp-id if using EF correctly,  and EF is assigning the Emp-Id to an Employee record when EF inserts a new Employee record into the database.

    Monday, April 20, 2020 7:27 PM
  • Hi Karen Payne. Thanks for reply.

    The link that you suggested show validation for empty field. And I would like to validate EmpId field if user will enter existing value. I would like to validate duplication on LostFocus event.

    Thanks.

    Yes I know, you need to adapt to what is need. I ignored the part of an id being assignable as I figured you were handling this yourself somehow but not having an auto-incrementing primary key you will have issues inserting data so hopefully EmpId is not a unique identifier.

    Also without a primary key you lose the ability of the Change tracker to know about changes see working with properties.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, April 20, 2020 7:42 PM
  • Early when I developed project using dataset I created SP like this

    CREATE PROCEDURE sp_ValidateEmpNo
    (
        @EmpNo nvarchar(100)
    )
    as
    SELECT EmpNo
    FROM Employees
    WHERE EmpNo = @EmpNo


    And then use it in TableAdapter. Now I develop C# application with Entity Framework code first. How I can achieve this functionality to avoid duplicate for EmpNo. I would like to inform user after data entered and field lost focus to EmpNo field. If entered value is duplicate field must be validated and textbox become red frame. If not duplicate so nothing exception. Or you can show me better way to not duplicate using Entity Framework code first?

    Thanks.





    • Edited by zleug Tuesday, April 21, 2020 12:58 AM
    Tuesday, April 21, 2020 12:11 AM
  • Use a primary key e.g.

    If you need A-Z in the key then create a composite column/multicolumn primary key (do some research on this)


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, April 21, 2020 12:56 AM
  • Early when I developed project using dataset I created SP like this

    CREATE PROCEDURE sp_ValidateEmpNo
    (
        @EmpNo nvarchar(100)
    )
    as
    SELECT EmpNo
    FROM Employees
    WHERE EmpNo = @EmpNo


    And then use it in TableAdapter. Now I develop C# application with Entity Framework code first. How I can achieve this functionality to avoid duplicate for EmpNo. I would like to inform user after data entered and field lost focus to EmpNo field. If entered value is duplicate field must be validated and textbox become red frame. If not duplicate so nothing exception. Or you can show me better way to not duplicate using Entity Framework code first?

    Thanks.





    All this leads back to you not understanding how to effectively develop a program that uses a database. No user should be able to enter a key for a record to be used with a  database no user. The only part of the information you should be gathering from a user is the Firstname, Lastname and Email address. You let the database engine assign the numeric primary-key ID for the record when it inserts the record. This is how things are done normally when working with a database, even if you're using a stored procedure and a dataset with datatable in working with a database.

    You should have picked up on this when you started using EF that it too has the ability to assign the numeric primary-key to a record inserted  into a database table by using the Identity primary-key assigned to a record by the MS SQL Server database engine. 

    All enterprise level databases like MS SQL Server have this feature. And your database table should be configured to use it. EF can use the feature too when it inserts a record.

    https://www.w3schools.com/sql/sql_autoincrement.asp

    https://www.entityframeworktutorial.net/code-first/foreignkey-dataannotations-attribute-in-code-first.aspx

    Let me ask you this. What is the purpose of using an ORM like EF with you using a stored procedure that is doing simple CRUD operations (create, read, update and delete) record operations with a database? Why are you even using an ORM like EF when you are using stored procedures and defeating the purpose of using any ORM? Stored Procedures have a place with using an ORM like EF. But for simple CRUD operations and you're using EF and a stored procedure? Comon man.....

    https://www.c-sharpcorner.com/article/crud-operation-using-entity-framework-in-windows-form-application/

    Tuesday, April 21, 2020 1:34 AM
  • I want to validate duplicate value in EmpNo field. I will try describe script.

    The form has EmpNo field. When somebody enter value to the field value must be compare to same field in the database table. If entered value duplicate the form field should be validate error by change color of field border to red display message under field. At that case application not allow user to save duplicate value. If entered value not duplicate the new record can be saved.

    Thanks.


    • Edited by zleug Tuesday, April 21, 2020 1:49 AM
    Tuesday, April 21, 2020 1:47 AM
  • Hi DA924x. Thanks for reply.

    Now I don't use SP. Now I just would like to figure out how to validate EmpNo field in the form in case if entered value duplicate in lost focus event. If value not duplicate allow user to save record.

    Thanks.


    • Edited by zleug Tuesday, April 21, 2020 2:17 AM
    Tuesday, April 21, 2020 2:07 AM
  • Hi DA924x. Thanks for reply.

    Now I don't use SP. Now I just would like to figure out how to validate EmpNo field in the form in case if entered value duplicate in lost focus event. If value not duplicate allow user to save record.

    Thanks.


    This is not an optimal approach. You shouldn't have to validate any key pertaining  to a record that is being inserted into a table. The EmpNo ,  aka the EmpId,  should be an Int primary-key column for the table that is an Identity column that is an auto-incremented number and tracked by that database engine for a given table. You can't have a duplicate key if the database table is configured correctly and tracked by the database engine.

    What's the purpose of you having the user enter an Employee Number at the screen?

    Tuesday, April 21, 2020 2:43 AM
  • I very appreciate that try to help to solve my problem but I cannot guess why you are saying EmpNo is key. For instance the Employee table has such data:
    EmpId     EmpNo    FirstName    LastName
    1               1245      John            Smith
    2               5421      Bill               White
    3               2154      Mary             Lee

    User ran application and try to create new record and entered into field EmpNo 5421 then press tab to jump to next field. In that case I would like to validate EmpNo field as duplicate. So, if that is possible. What code need to be to catch duplicate and validate field?

    Thanks.

    Tuesday, April 21, 2020 3:29 AM
  • I very appreciate that try to help to solve my problem but I cannot guess why you are saying EmpNo is key. For instance the Employee table has such data:
    EmpId     EmpNo    FirstName    LastName
    1               1245      John            Smith
    2               5421      Bill               White
    3               2154      Mary             Lee

    User ran application and try to create new record and entered into field EmpNo 5421 then press tab to jump to next field. In that case I would like to validate EmpNo field as duplicate. So, if that is possible. What code need to be to catch duplicate and validate field?

    Thanks.

    IMO, you're not implementing SoC where the Save() process in a class was called by the Save button on the form and that class and its Save() method would take the EmployNO that's in the EF Entity and do a duplication check and not allow the save process to continue.    

    https://en.wikipedia.org/wiki/Separation_of_concerns

    but you can put this code in the tbxEmplyNo textbox LeaveFocus() event.

    var emp = (context.Employee.Where(a => a.EmplyNO == tbxEmplno.Text)).SingleOrDefault(); if (!emp == null) { //put out message empno is duplicated. // don't leave the tbxEmplyNo tbxEmploy.Setfocus();

    }

    //if emp is a null, then no record was found that had empno

    and you can move to the next field.


    https://www.c-sharpcorner.com/blogs/singleordefault-vs-firstordefault-in-linq-query1


    • Edited by DA924x Tuesday, April 21, 2020 4:11 AM
    Tuesday, April 21, 2020 4:09 AM
  • Hi DA924x.

    I tried your code. When I used exactly like you suggested it gives error on !emp with message: "Operator ! cannot be applied to operand of type Employee."

    Then I modify IF statement:

                if (emp == null)
                {
                    MessageBox.Show("No Duplicate");
                }
                else
                {
                    MessageBox.Show("Duplicated");
                    EmpNoTextBox.Focus();
                }

    It catch duplication but now message in the loop and I cannot leave from Duplicated message. How to fix the problem?

    Thanks.

    Tuesday, April 21, 2020 2:33 PM
  • Just use

    if (DbSet.Any(x=>x.EmpNo==empNo)){

      // Value already exists - show the error to the user

    }


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, April 21, 2020 3:22 PM
  • Use .Any method instead of what you're shown as it will be more optimized code.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, April 21, 2020 3:26 PM
  • Hi Naomi. Thanks for reply.

    What is DbSet.Any in my case? 

    Thanks.

    Tuesday, April 21, 2020 3:31 PM
  • Does this work for you

    context.Employee.Any(x=>x.EmpNo==empNo)

    ?

    where empNo is a variable you pass?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, April 21, 2020 3:51 PM
  • It should be this. Sorry, I don't know why I typed it the other way.  

    if (emp != null)

    it should work then.

    You can always use the return; statement to leave a method early.  

     if (emp != null)
        {
          MessageBox.Show("Duplicated");
          EmpNoTextBox.Focus();
        }
               

    But to me, this is a cheesy-way of implementing business rules in having such rules in a control's event. Business rules belong in the business logic layer, a Save() method in the BLL that a Save button's click event calls in the presentation layer..

    Are you thigh coupling everything in the Windows form project?

    https://www.c-sharpcorner.com/UploadFile/yusufkaratoprak/difference-between-loose-coupling-and-tight-coupling/

    It's back to SoC.

    https://en.wikipedia.org/wiki/Separation_of_concerns

    <copied>

    Layered designs in information systems are another embodiment of separation of concerns (e.g., presentation layer, business logic layer, data access layer, persistence layer).

    <end>

    https://docs.microsoft.com/en-us/previous-versions/msp-n-p/ee658117(v=pandp.10)

    It's probably too late for you to try and implement a layered styled in your  current solution, but it is something to think about in future solutions you may work on, and not tightly couple everything in the Windows form project.

    Also consider this and tight coupling of code.

    https://ardalis.com/new-is-glue

    https://www.weeklydevtips.com/episodes/005

    http://buzzedcode.com/loose-coupling-with-interface-in-csharp/

    https://www.codeproject.com/Articles/13831/Dependency-Injection-for-Loose-Coupling

    You can change the below tutorial and make it work with EF being used in the DAL and using the DTO pattern replacing the dataset, datatable and value object it is using along with implementing somethings I have shown you.

    https://www.codeproject.com/Articles/36847/Three-Layer-Architecture-in-C-NET-2

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    HTH

     

    Tuesday, April 21, 2020 4:12 PM
  • Hi DA924x.

    Still have the problem with loop when duplicated. How to fix it?

    Thanks.

    Tuesday, April 21, 2020 4:28 PM
  • Can you post your current code? Are you trying to call it from LostFocus event? If yes, then this is a mistake. Did you review the link Karen sent you?

    E.g. if you try to call something from LostFocus such as Message Box, then you're going into infinitive loop as it will be causing another LostFocus event to fire. You need to figure out a way to only show that messagebox once.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Tuesday, April 21, 2020 4:35 PM
    Tuesday, April 21, 2020 4:31 PM
  • Hi Naomi

    That is my code

            private void EmpNoTextBox_LostFocus(object sender, RoutedEventArgs e)
            {
                var context = new Model1();
                var emp = (context.Employees.Where(a => a.EmpNo == EmpNoTextBox.Text)).SingleOrDefault();
    
                if (emp != null)
                {
                    MessageBox.Show("Duplicated");
                    Keyboard.Focus(EmpNoTextBox);
    
    That line I took from Karen link. It does not return focus 
    //((Control)sender).GetBindingExpression(TextBox.TextProperty).UpdateSource();
                }
                else
                {
                    MessageBox.Show("No Duplicate");
                }
            }
    


    Thanks.
    Tuesday, April 21, 2020 4:51 PM
  • Hi Naomi

    That is my code

            private void EmpNoTextBox_LostFocus(object sender, RoutedEventArgs e)
            {
                var context = new Model1();
                var emp = (context.Employees.Where(a => a.EmpNo == EmpNoTextBox.Text)).SingleOrDefault();
    
                if (emp != null)
                {
                    MessageBox.Show("Duplicated");
                    Keyboard.Focus(EmpNoTextBox);
    
    That line I took from Karen link. It does not return focus 
    //((Control)sender).GetBindingExpression(TextBox.TextProperty).UpdateSource();
                }
                else
                {
                    MessageBox.Show("No Duplicate");
                }
            }


    Thanks.

    To me, you need to put some more logic in the event  like if textbox.text == "" then return; and not do the dup check. Maybe, some other code in the event to prevent you from being trapped in the loop and only do the code if you have a valid EmpNo like a number check and code of that nature.

    That's why I don't like what you're trying to do. It too cheesy. Maybe you set  a Boolean flag in the event-code and check the flag after you leave the event and then show the message box. Maybe you only check the flag in the Save button-click-event. Maybe you only do the dup code check in the Save button click-event, stop the save and show a message if it's a duplicate. 

    • Proposed as answer by Naomi N Tuesday, April 21, 2020 6:50 PM
    Tuesday, April 21, 2020 5:47 PM
  • What are the other methods for TextBox control? Basically, you should not be doing it this way. You should be doing something like this

    private void EmpNoTextBox_LostFocus(object sender, RoutedEventArgs e)
            {
                var context = new Model1();
                bool isDuplicate = (context.Employees.Any(a => a.EmpNo == EmpNoTextBox.Text))
    
    // We need to use this flag from some other event, may be from the Save button
    Alternatively, we can display some validation control on the form
    
                   --?? will return false; stay in that control
    
                }
            }

    I don't really use Windows Forms Controls, but I know from experience with other languages that you should not call any type of Message box from the LostFocus event because it may get you into infinitive loop.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, April 21, 2020 6:56 PM
  • context.Employees.Any will cause the query to check every item wasting time instead of short-circuiting out of the query on a FirstOrDefualt
    Tuesday, April 21, 2020 7:16 PM
  • No, you're incorrect. ANY corresponds to EXISTS in SQL Server and this is preferred method if you only need to check for the duplicates.

    In other words, both codes (yours and ANY translated to SQL Server code):

    select top (1) * from Employees where EmpNo = @EmpNo;

    and then you're checking for this being null

    Alternatively, ANY translates into

    if exists (select 1 from Employees where EmpNo = @EmpNo)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles




    • Edited by Naomi N Tuesday, April 21, 2020 7:50 PM
    Tuesday, April 21, 2020 7:45 PM
  • No, you're incorrect. ANY corresponds to EXISTS in SQL Server and this is preferred method if you only need to check for the duplicates.

    In other words, both codes (yours and ANY translated to SQL Server code):

    select top (1) * from Employees where EmpNo = @EmpNo;

    and then you're checking for this being null

    Alternatively, ANY translates into

    if exists (select 1 from Employees where EmpNo = @EmpNo)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles




    The Any() only checks for it to exist, and First or Single retunes an object if it exist or null if it doesn't exist if using the default.

    Tuesday, April 21, 2020 8:40 PM
  • Yes, I know that. Do you explain this to OP?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, April 21, 2020 8:42 PM
  • I tried to by pass LostFocus event by creating ValidationRule class. Here my code in C#

        class ValidateDuplicateEmpNo : ValidationRule
        {
            private string _ObjectType;
            private string _EmpNo;
            private Xceed.Wpf.Toolkit.WatermarkTextBox EmpNoTextBox;
    
            public string ObjectType
            {
                get
                {
                    return _ObjectType;
                }
                set
                {
                    _ObjectType = value;
                }
            }
    
            private string EmpNo
            {
                get
                {
                    return _EmpNo;
                }
                set
                {
                    _EmpNo = value;
                }
            }
    
            public override ValidationResult Validate(object value, CultureInfo cultureInfo)
            {
                if (ObjectType == "Duplicate")
                {
                    var context = new Model1();
    
                    bool IsDuplicate = (context.Employees.Any(a => a.EmpNo == EmpNoTextBox.Text));
    
                    if (IsDuplicate == true)
                    {
                        return new ValidationResult(false, "The value " + EmpNoTextBox.Text + "is duplicated");
                    }
                }
                return new ValidationResult(true, null);
            }
        }
    and XAML
    <xctk:WatermarkTextBox x:Name="EmpNoTextBox" Watermark="Emp No" VerticalContentAlignment="Center" FontFamily="Tahoma" FontSize="14" Grid.Column="1" TabIndex="0" Height="30" 
          CharacterCasing="Upper" Validation.ErrorTemplate="{StaticResource validationTemplate}" Style="{StaticResource InputControlErrors}" > 
          <TextBox.Text>
              <Binding Path="EmpNo" UpdateSourceTrigger="PropertyChanged">
                  <Binding.ValidationRules>
                       <local:RequiredFields  ObjectType="String"/>
                       <local:ValidateDuplicateLan ObjectType="Duplicate" />
                  </Binding.ValidationRules>
              </Binding>
           </TextBox.Text>
    </xctk:WatermarkTextBox>

    But when I type first character in EmpNo field I got error message:
    System.Reflection.TargetException: 'Non-static method requires a target.'

    in line

    bool IsDuplicate = (context.Employees.Any(a => a.EmpNo == EmpNoTextBox.Text));

    How to fix the problem?

    Thanks.




    • Edited by zleug Tuesday, April 21, 2020 9:14 PM
    Tuesday, April 21, 2020 8:58 PM
  • How to fix the problem?

    All this code I would simply put the dup-code check (all of the code the DB code too) in the Save button click-event. It either passes the test in the Save button's  click-event or you stop the save of any DB records and send out a message forcing the user to fix the problem. And then the user presses the Save button again. You have a Save button on the from, right? Or you have something on the form the user has to press in order to save the record, right? 

    Maybe, it would be of help to you to now post to the WPF forum. :)

    https://docs.microsoft.com/answers/topics/wpf.html

    • Edited by DA924x Tuesday, April 21, 2020 9:51 PM
    Tuesday, April 21, 2020 9:49 PM
  • Hi zleug,
    I viewed your description and replies, it is mostly related to WPF. So it is recommended to ask the questions in Microsoft Q&A forum and you can get more professional answer.
    Thank you for your understanding.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 22, 2020 5:34 AM