I am making a .NET Core application and I am using EF Core. My application has a controller, and by dependency injection, I access my MariaDB database in it.

However there's one thing that's still troubling my mind. Here's the code and the questions are below

public ActionResult<Dictionary<Car, List<Colour>>> GetCars(DbContext db)
    {

        var carsAndAllowedColours = db.CarsAndAllowedColours.AsNoTracking();

        var carsAndAllowedColoursDict = new Dictionary<Car, List<Colour>>();

        foreach (var element in carsAndAllowedColours)
        {
            var car = element.Car;
            var colour = element.Colour;
            var isColourAlreadyAKey = carsAndAllowedColoursDict.TryGetValue(car, out var coloursList);
            if (isColourAlreadyAKey && coloursList != null)
                coloursList.Add(colour);
            else
                carsAndAllowedColoursDict.Add(car, [colour]);
        }
        return carsAndAllowedColoursDict;
    }
}

Here are my questions:

1. Since my "carsAndAllowedColours" is an IEnumerable, will my application make a query for EACH element in my foreach loop?
2. At what time exactly is my the connection to the database open? Is it in the first time the query is executed? For instance, in the foreach loop?
3. When is the connection closed?

3.a. I tried to implement the following "watchers" to observe all the status of my connection, but all the watchers were always closed. It didn't make any sense to me...

public ActionResult<Dictionary<Car, List<Colour>>> GetCars()
    {

        var xpto = db.Database.GetDbConnection();

        var watcher0 = xpto.State;
        var carsAndAllowedColours = db.CarsAndAllowedColours.AsNoTracking();

        var carsAndAllowedColoursDict = new Dictionary<Car, List<Colour>>();

        var watcher1 = xpto.State;

        foreach (var element in carsAndAllowedColours)
        {
            var watcher2 = xpto.State;

            var car = element.Car;
            var colour = element.Colour;
            var isColourAlreadyAKey = carsAndAllowedColoursDict.TryGetValue(car, out var coloursList);
            if (isColourAlreadyAKey && coloursList != null)
                coloursList.Add(colour);
            else
                carsAndAllowedColoursDict.Add(car, [colour]);
        }
        var watcher3 = xpto.State;
        return carsAndAllowedColoursDict;
    }
}

3.b. On the other hand, I logged into my MariaDB container and accessed its terminal and tehre was a connection with a "Command" "Sleep" that began just after the first time the query was executed(in the foreach loop) and didn't end.

4. Is tehre any case instead of iterating over the IEnumerable I should convert to a List(.ToList())?

5. Is db.Database.GetDbConnection(); the right method to get the object related to the connection? Os does it create another NEW connection object?

Thank you and I hope you have a lovely day!

I am making a .NET Core application and I am using EF Core. My application has a controller, and by dependency injection, I access my MariaDB database in it.

However there's one thing that's still troubling my mind. Here's the code and the questions are below

public ActionResult<Dictionary<Car, List<Colour>>> GetCars(DbContext db)
    {

        var carsAndAllowedColours = db.CarsAndAllowedColours.AsNoTracking();

        var carsAndAllowedColoursDict = new Dictionary<Car, List<Colour>>();

        foreach (var element in carsAndAllowedColours)
        {
            var car = element.Car;
            var colour = element.Colour;
            var isColourAlreadyAKey = carsAndAllowedColoursDict.TryGetValue(car, out var coloursList);
            if (isColourAlreadyAKey && coloursList != null)
                coloursList.Add(colour);
            else
                carsAndAllowedColoursDict.Add(car, [colour]);
        }
        return carsAndAllowedColoursDict;
    }
}

Here are my questions:

1. Since my "carsAndAllowedColours" is an IEnumerable, will my application make a query for EACH element in my foreach loop?
2. At what time exactly is my the connection to the database open? Is it in the first time the query is executed? For instance, in the foreach loop?
3. When is the connection closed?

3.a. I tried to implement the following "watchers" to observe all the status of my connection, but all the watchers were always closed. It didn't make any sense to me...

public ActionResult<Dictionary<Car, List<Colour>>> GetCars()
    {

        var xpto = db.Database.GetDbConnection();

        var watcher0 = xpto.State;
        var carsAndAllowedColours = db.CarsAndAllowedColours.AsNoTracking();

        var carsAndAllowedColoursDict = new Dictionary<Car, List<Colour>>();

        var watcher1 = xpto.State;

        foreach (var element in carsAndAllowedColours)
        {
            var watcher2 = xpto.State;

            var car = element.Car;
            var colour = element.Colour;
            var isColourAlreadyAKey = carsAndAllowedColoursDict.TryGetValue(car, out var coloursList);
            if (isColourAlreadyAKey && coloursList != null)
                coloursList.Add(colour);
            else
                carsAndAllowedColoursDict.Add(car, [colour]);
        }
        var watcher3 = xpto.State;
        return carsAndAllowedColoursDict;
    }
}

3.b. On the other hand, I logged into my MariaDB container and accessed its terminal and tehre was a connection with a "Command" "Sleep" that began just after the first time the query was executed(in the foreach loop) and didn't end.

4. Is tehre any case instead of iterating over the IEnumerable I should convert to a List(.ToList())?

5. Is db.Database.GetDbConnection(); the right method to get the object related to the connection? Os does it create another NEW connection object?

Thank you and I hope you have a lovely day!

• c#
• database
• entity-framework-core
• mariadb

• carAndAllowedColours hasn't been declared, only carsAndAllowedColours has. And car is defined twice. carsAndRespectiveColoursDict and coloursList aren't defined either. So none of this compiles. – Charlieface Commented Mar 19 at 20:46
• I'd suggest you read up on GroupBy. – mjwills Commented Mar 20 at 3:19

3 Answers 3

1. var carsAndAllowedColours = db.CarsAndAllowedColours.AsNoTracking(); The AsNoTracking method returns an IQueryable hence foreach (var car in carsAndAllowedColours) will be executed in a single database query (which if there are hundreds or thousands in CarsAndAllowedColurs table in the database is strongly unrecommended) this is very much the same as calling carsAndAllowedColours.ToList() but with one caveat/performance hit mentioned in point 4.

2. The dbContext opens the database connection exactly before submitting the query and closes it after loading the data considering no connection pooling/reuse is enabled. During the lifetime of the dbContext multiple connections could be opened and closed. But normally Ado net driver provides Connection pooling to reduce the number of connections that need to opened by keeping a fixed set of connections alive since opening a new connection is a resource expensive and slow operation. After loading the data the dbContext caches the loaded entities in the Local property of the dbSet hence if you query some entity that is already loaded the dbContext returns in from the local cache instead of submitting a new query at least by default

3. Read the above answer

4. Calling .ToList() on an IQueryble collection (dbSet) generally executes the query, loads the data from the database and returns a List of loaded from the database and freshly constructed entity objects. This List implements the IEnumerable interface hence it is basically the same thing with one caveat. If you just do foreach (var car in carsAndAllowedColours) instead of foreach (var car in carsAndAllowedColours.ToList()) the connection will be kept alive until you finish your enumeration loop despite the fact that the data will be laded from the database in a single database query and if you are doing some slow and expensive operation in the body of your foreach loop this could lead to a performance issues. More on the topic in this answer. Personally I recommend always calling .ToList() before iterating over an IQueryable collection like this

   foreach (var car in carsAndAllowedColours.ToList())

5. I personally use dbContext.Database.Connection property to get the current connection

I know this wasn't exactly your question, but you can handle a lot of your query on the database, basically, you are just grouping cars by colour, a perfect database statement:

 return db.CarsAndAllowedColours
          .AsNoTracking()
          .GroupBy(cnc => cnc.Car)
          .ToDictionary(
              grp => grp.Key, // Key is the item you grouped by, in this case of type Car
              grp => grp.Select(cnc => cnc.Color).ToList());

Answers

1. You are using a controller which makes an HTTP connection from client to server. HTTP closes connection after each Request/Response

2. The Controller has a Frontend and a Backend. The Frontend is a Client/Server HTTP connection. The backend is a connection from Server to Database. The connection to the database may or may not stay open. This connection is a Connection Pool.

3. The Frontend connection closes after each HTTP Request/Response. The database connection closes when application ends.

4. IEnumerable is more efficient than converting to a list. The List requires more pricessing

5. I do not know the under the hood code for GetDbConnection(). It looks like db is the properties for the database connection and will return the active connection to the database.