C# When trying to find index in sql databse i get error: There is already an open DataReader associated with this Command which must be closed first

Question

I already tried error 'there is already an open datareader associated with this command which must be closed first' but I still get same error

and made my connection string:

<add name="ToSrv" 
     connectionString="Data Source=********;Initial Catalog=F184DABH2Gr14;User ID=********;Password=*******;MultipleActiveResultSets=True;App=EntityFramework;" 
     providerName="System.Data.SqlClient"  />

Here is the code where im trying to find a person by email:

public Person GetPersonByEmail(string EmailParam)
{

    Email NewEmail = _emailRepository.First(x => x.UniqueEmail.ToString() == EmailParam);
    if (NewEmail == null)
    {
        Person FalsePerson = null;
        return FalsePerson;
    }

    Person TempPerson = _personRepository.First(x => x.Email.UniqueEmail == EmailParam);
    int ID = TempPerson.PersonID;

    Telephone newtlf = _TelephoneRepository.First(x => x.PersonRefId == ID);
    Email newEmail = _emailRepository.First(x => x.UniqueEmail == EmailParam);
    Adress newPrimaryAdress = _AltAdressRepository.First(x => x.person.PersonID == TempPerson.PersonID).AlternativeAdress;

    Person PersonToReturn = new Person(newtlf, 
                                       newPrimaryAdress,
                                       TempPerson.GivenName,
                                       TempPerson.FamilyName,
                                       TempPerson.MiddleName,
                                       TempPerson.Type, 
                                       newEmail);

    foreach (var VARIABLE in _AltAdressRepository.Find(x => x.person.PersonID == ID))
    {
        VARIABLE.AlternativeAdress = _AdressRepository.First(x => x.adressID == VARIABLE.altAdrID);

        PersonToReturn.altAdresser.Add(VARIABLE);
    }
    return PersonToReturn;
}

.

Please post the code that relates to your database connections, presumably this lives inside your repositories? Also where exactly is the exception thrown? Help us help you. — Chris Pickford, Apr 27 '18 at 12:12
Where is the code that creates _emailRepository? It's probably nothing to do with the code you have posted, and everything to do with how your DataReader/SqlConnection is created. — Neil, Apr 27 '18 at 12:12
The error has nothing to do with the code you have posted. The error is in your repository or wherever you are opening the reader. — CodingYoshi, Apr 27 '18 at 12:14
There is probably already an open datareader associated with this command which must be closed first. — SIGSTACKFAULT, Apr 27 '18 at 12:14
`_emailRepository.First` should be `FirstOrDefault`, `First` will never return `null` — Felix D., Apr 27 '18 at 12:16
Related: https://stackoverflow.com/questions/6140633/c-sharp-entity-framework-there-is-already-an-open-datareader-associated-with-th, https://stackoverflow.com/questions/4867602/entity-framework-there-is-already-an-open-datareader-associated-with-this-comma — Felix D., Apr 27 '18 at 12:21
@LasseVågsætherKarlsen If an item is not found in the collection wouldn't there be an `InvalidOperationException` "Sequence contains no matching element" ? — Felix D., Apr 27 '18 at 12:32
I know that, but if the first element is null, then that will be returned. I was just pointing out that "First will never return null" isn't entirely correct. I believe you are correct in that the code should use FirstAndDefault instead though. — Lasse V. Karlsen, Apr 27 '18 at 12:47
@LasseVågsætherKarlsen u mean by just using `First()` - I was skipping the parameters - was directly related to the OPs code. Now i get where the confusion comes from :P — Felix D., Apr 27 '18 at 12:48

score 1 · Accepted Answer · edited Apr 27 '18 at 12:52

1

You need to make it a list in unit of work as so:

List<AltAdresse> alt = _AltAdressRepository.Find(x => x.person.PersonID == ID).ToList();
foreach (var VARIABLE in alt)
{
    VARIABLE.AlternativeAdress = _AdressRepository.SingleOrDefault(x => x.adressID == VARIABLE.altAdrID);
    PersonToReturn.altAdresser.Add(VARIABLE);
}

This is because IEnumarable holds the database connection open

edited Apr 27 '18 at 12:52

Felix D.

4,811
8
38
72

answered Apr 27 '18 at 12:50

Nicholas Ladefoged

186
1
12

score 0 · Answer 2 · answered Apr 27 '18 at 12:14

Assuming your application is a web server, you must create a new SqlConnection for EVERY request that is made from the front end.

This is normally handled by your DI (something like Services.AddScoped or similar). If you have AddSingleton<> then it will need changing.

C# When trying to find index in sql databse i get error: There is already an open DataReader associated with this Command which must be closed first

2 Answers2