Goal: To ensure that a field in a table is unique.

I want to ensure that the UserName in the UserAccount table is unique. There can not be duplicates as this will be how the user logs in.

1. Example of the code as used within a MVC Edit method

        private UserAccountCollection c_UserAccount = new UserAccountCollection();

                // CHECK FOR Unique User Name
                int numRecords = (int)c_UserAccount.GetScalar(UserAccountFieldIndex.UserAccountId, null, AggregateFunction.Count,
                  (UserAccountFields.UserName == e_UserAccount.UserName));

                // If this one has changed then reduce the count by 1.
                if (e_UserAccount.Fields["UserName"].DbValue.ToString() == e_UserAccount.UserName)
                    numRecords--;

                if (numRecords != 0)
                {
                    this.ModelState.AddModelError("UserName", "That User Name already exists.");
                    return this.View("edit", e_UserAccount);
                }

2A. Example within a Validate class

        public override void ValidateEntityBeforeSave(IEntityCore involvedEntity)
        {
            
            UserAccountCollection c_UserAccount = new UserAccountCollection();

            // variable to collect errors
            StringBuilder sbExceptionMessage = new StringBuilder();

            // order to validate. Cast depends upon the entity you are validating.
            UserAccountEntity toValidate = (UserAccountEntity)involvedEntity;

            #region Check validation rules.

            // CHECK FOR Unique User Name
            int numRecords = (int)c_UserAccount.GetScalar(UserAccountFieldIndex.UserAccountId, null, AggregateFunction.Count,
              (UserAccountFields.UserName == toValidate.UserName));

            // If this one has changed then reduce the count by 1.
            if (toValidate.Fields["UserName"].DbValue.ToString() == toValidate.UserName)
                numRecords--;

            if (numRecords != 0)
            {
                sbExceptionMessage.Append(USERNAME_UNIQUE_ERROR_MESSAGE + DELIMITER);
            }


            //-- add more validations --
            //...
            #endregion

            // get the errors collected
            string strExceptionMessage = sbExceptionMessage.ToString();

            // Do exist any break rule in this entity?
            if (strExceptionMessage != string.Empty)
            {
                // set error info so we can access that outside
                toValidate.SetEntityError(strExceptionMessage);

                // throws an exception with all the breaking rules info
                throw new ORMEntityValidationException(strExceptionMessage, toValidate);
            }
        }

2B. Example handling the Exception within the MVC

            try
            {
                .
                .
                .
                // Check Validity of the Form Data
                if (e_UserAccount.IsValid())
                {   
                    e_UserAccount.Save();
                    return RedirectToAction("Index");
                }
            }
            catch (ORMEntityValidationException ex)
            {
                this.ModelState.AddModelError("Exception", ex.Message);
                return this.View("edit", e_UserAccount);
            }

Nice approach.

Although I always leave the uniqueness validation for database. i.e. I use a Unique key, and let the databse scream out with an exception if the inserted row has a duplicate value, then I notify the user with such error. That's always gonna be one database call.

In your case, it's 2 database calls, when for checking for the duplicate and another one for inserting.