Getting an Error: InvalidOperationException

Question

I get the error: InvalidOperationException while using the below mentioned code:

using (MunimPlusContext context = new MunimPlusContext())
{
    var dbGroup = context.GroupSet
                         .Where(x => x.GroupName.ToLower() == groupName.ToLower())
                         .SingleOrDefault();

    if (dbGroup == null)
        return true;
    else
        return dbGroup.GroupId == group.GroupId;
}

The detail provided by the error is:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

What I have tried:

1. I used the below mentioned line of code to overcome this error:

   context.Database.Connection.Open();

2. I also used SQL Profiler which fires the query as follows, but I don't understand the generated SQL:

   exec sp_executesql N'SELECT TOP (2) [Extent1].[GroupId] AS [GroupId], [Extent1].[GroupName] AS [GroupName], [Extent1].[Alias] AS [Alias], [Extent1].[ParentId] AS [ParentId], [Extent1].[IsSystemGroup] AS [IsSystemGroup], [Extent1].[NatureOfGroupId] AS [NatureOfGroupId], [Extent1].[EffectId] AS [EffectId], [Extent1].[BankDetailsVisibility] AS [BankDetailsVisibility], [Extent1].[CreditLimitsVisibility] AS [CreditLimitsVisibility], [Extent1].[GeneralDetailsVisibility] AS [GeneralDetailsVisibility], [Extent1].[ContactDetailsVisibility] AS [ContactDetailsVisibility], [Extent1].[TaxInformationVisibility] AS [TaxInformationVisibility] FROM [dbo].[Group] AS [Extent1] WHERE ((LOWER([Extent1].[GroupName])) = (LOWER(@p__linq__0))) OR ((LOWER([Extent1].[GroupName]) IS NULL) AND (LOWER(@p__linq__0) IS NULL))',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'Primary' go

When I get this error:

When I am trying to check in the database for duplicates using FluentValidation as follows, I get this error:

RuleFor(obj => obj.GroupName).Must(UniqueName)
                             .WithMessage("Group with same name already exists. Please choose a different Group name");

Here is the method UniqueName which is the source of error:

private bool UniqueName(Group group, string groupName)
{
    using (MunimPlusContext context = new MunimPlusContext())
    {
        var dbGroup = context.GroupSet
                             .Where(x => x.GroupName.ToLower() == groupName.ToLower())
                             .SingleOrDefault();

        if (dbGroup == null)
            return true;
        else
            return dbGroup.GroupId == group.GroupId;
    }
}

Update:

Here is the full stack trace:

at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at MunimPlus.Entities.Group.GroupValidator.UniqueName(Group group, String groupName) in H:\Work\Trial\New\MunimPlus\MunimPlusSolution\MunimPlus.Entities\Group.cs:line 274 at FluentValidation.DefaultValidatorExtensions.<>c__DisplayClass42.b__3(T x, TProperty val, PropertyValidatorContext propertyValidatorContext) in c:\Projects\FluentValidation\src\FluentValidation\DefaultValidatorExtensions.cs:line 219 at FluentValidation.DefaultValidatorExtensions.<>c__DisplayClass72.<Must>b__6(Object instance, Object property, PropertyValidatorContext propertyValidatorContext) in c:\Projects\FluentValidation\src\FluentValidation\DefaultValidatorExtensions.cs:line 235 at FluentValidation.Validators.PredicateValidator.IsValid(PropertyValidatorContext context) in c:\Projects\FluentValidation\src\FluentValidation\Validators\PredicateValidator.cs:line 37 at FluentValidation.Validators.PropertyValidator.Validate(PropertyValidatorContext context) in c:\Projects\FluentValidation\src\FluentValidation\Validators\PropertyValidator.cs:line 71 at FluentValidation.Internal.PropertyRule.InvokePropertyValidator(ValidationContext context, IPropertyValidator validator, String propertyName) in c:\Projects\FluentValidation\src\FluentValidation\Internal\PropertyRule.cs:line 346 at FluentValidation.Internal.PropertyRule.<Validate>d__10.MoveNext() in c:\Projects\FluentValidation\src\FluentValidation\Internal\PropertyRule.cs:line 234 at System.Linq.Enumerable.<SelectManyIterator>d__142.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at FluentValidation.AbstractValidator1.Validate(ValidationContext1 context) in c:\Projects\FluentValidation\src\FluentValidation\AbstractValidator.cs:line 113 at FluentValidation.AbstractValidator1.Validate(T instance) in c:\Projects\FluentValidation\src\FluentValidation\AbstractValidator.cs:line 94 at FluentValidation.AbstractValidator1.FluentValidation.IValidator.Validate(Object instance) in c:\Projects\FluentValidation\src\FluentValidation\AbstractValidator.cs:line 55 at Core.Common.Core.EntityBase.Validate() in H:\Work\Trial\New\Core\Core.Common\Core\EntityBase.cs:line 206 at Core.Common.Core.EntityBase..ctor() in H:\Work\Trial\New\Core\Core.Common\Core\EntityBase.cs:line 25 at MunimPlus.Entities.Group..ctor() at lambda_method(Closure , Shaper ) at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.HandleEntityAppendOnly[TEntity](Func2 constructEntityDelegate, EntityKey entityKey, EntitySet entitySet)
at lambda_method(Closure , Shaper ) at System.Data.Entity.Core.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)

Update1:

If I don't use context.Database.Connection.Open(), then I get another error saying that:

Underlying Provider failed to Open.

But before this call to the database I have some other calls where its working fine.

If I remove this validation, then also my project works fine.

Still I would like to show my connection string here:

<connectionStrings>
  <add name="MunimPlus" 
       connectionString="data source=.\SQLEXPRESS;Initial Catalog=Max;Integrated Security=SSPI" 
       providerName="System.Data.SqlClient" />
</connectionStrings>

Update 2:

Got some hint on problem but not solution.

I have a base class called EntityBase. All of my Entities inherits from EntityBase. So my Group class will look something like:

public class Group : EntityBase
{

    Fields.....

    Properties....

    class GroupValidator : AbstractValidator<T>
    {
        public GroupValidator()
        {
            RuleFor(obj => obj.GroupName).NotEmpty().WithMessage("Group name cannot be empty.");
            RuleFor(obj => obj.GroupName).Must(UniqueName).WithMessage("Group with same name already exists. Please choose a different Group name");
            RuleFor(obj => obj.ParentId).NotNull().WithMessage("Please select the group under which this group will appear")
                                        .GreaterThan(0).WithMessage("Please select a valid/existing group name");
        }

        private bool UniqueName(Group group, string groupName)
        {
            if (groupName == null)
                groupName = "";

            using (MunimPlusContext context = new MunimPlusContext())
            {
                Group dbGroup = context.GroupSet.FirstOrDefault(x => x.GroupName.ToLower() == groupName.ToLower());

                if (dbGroup == null)
                    return true;
                else
                    return dbGroup.GroupId == group.GroupId;
            }
        }
    }

    protected override IValidator GetValidator()
    {
        return new GroupValidator();
    }

}

Look at the last method called GetValidator, it is an overridden version of virtual method defined in EntityBase class.

Now, a part of EntityBase class looks like:

public abstract class EntityBase
{

    public EntityBase()
    {
        _Validator = GetValidator();
        Validate();
    }

    protected IValidator _Validator = null;

    protected IEnumerable<ValidationFailure> _ValidationErrors = null;

    protected virtual IValidator GetValidator()
    {
        return null;
    }

    public IEnumerable<ValidationFailure> ValidationErrors
    {
        get { return _ValidationErrors; }
        set { }
    }

    public void Validate()
    {
        if (_Validator != null)
        {
            ValidationResult results = _Validator.Validate(this);
            _ValidationErrors = results.Errors;
        }
    }

    public virtual bool IsValid
    {
        get
        {
            if (_ValidationErrors != null && _ValidationErrors.Count() > 0)
                return false;
            else
                return true;
        }
    }
}   

Now inside CarValidator class's UniqueName method's line

Group dbGroup = context.GroupSet.FirstOrDefault(x => x.GroupName.ToLower() == groupName.ToLower());

for each group a new instance is created and validated due to the EntityBase class. So, cursor runs across using(MunimPlusContext context = new MunimPlusContext) but never closes the connection as it goes deeper and deeper to create new instances of group, as a result maximum connections in database is reached. Thus I get connection pooling problem.

When I increased Max Pool Size to 999, I got another error StackOverFlowException.

Demo Project reproducing the issue:

https://drive.google.com/file/d/0B5WyqSALui0bM252VXdveVVMMzQ/view?usp=sharing

Answer 1

So what you have now is an infinite recursion problem. So it starts out like this:

1. Make a new group which calls the constructor (which calls the base constructor in EntityBase).
2. The EntityBase constructor gets a validator and then calls its Validate method, which ultimately calls into UniqueName.
3. The line context.GroupSet.FirstOrDefault will create a new Group object. This starts you back at step one again.

This loop of making objects will never stop until you exhaust the stack, which causes the stack overflow exception you got (before you used to hit the connection pool limit before you got the stack overflow and the connection pool problem masked the real problem here). A couple of things you could try:

First, change the context.GroupSet.FirstOrDefault to use Any instead of FirstOrDefault. This way step 3 never creates an object and never sends you back to step 1. While this may solve the problem, it could easily pop back up if you need to do something similar somewhere else.

Second (and by far, preferable) is don't call Validate in your constructor. Typically you should try to validate an object before it is persisted to your database, not on creation (for example, validating a brand new, empty object doesn't do me much good when I'm going to fill it in later. I need to validate before I save to make sure I don't violate some constraint by putting certain combinations of data into the object.) In projects I have done we have done something like this:

public class EntityBase
{
    //...
    public void Save()
    {
        //Validate here and check for errors
        //If errors exist, throw an exception
        //If no errors, persist to database.
    }
}

Obviously you will have to figure out how you want to do this in your project. Maybe it is through a repository or service, but you are far better off validating before saving instead of on creation.

(Note: I did not try any of this in the code you shared as there is no way I am going to run code that I was handed on the internet.)