Exception - "This MySqlConnection is already in use" when using MYSql server. However the same Linq query works fine with MS SQL Server

Question

I am developing a Quality Assurance Application using the .NET Core framework (5.0). Initially, I have used MS SQL Server DB and it was working fine. However, now due to requirement change, I have now moved to MYSQL Server.

I have successfully integrated the database using the code-first approach (Entity framework). Also, I am using the NuGet package Pomelo.EntityFrameworkCore.MySql and everything works just fine except the below-mentioned Linq query where I am getting an exception - This MySqlConnection is already in use.

public List<ErrorMessage> GetErrorMessage(int productId)
{
    List<ErrorMessage> localList = new List<ErrorMessage>();
    var product = _db.Products.Where(a => a.ProductId == productId).FirstOrDefault();
    if (product != null)
    {

        var products = _db.Products
            .Where(q => q.ProductName.ToLower() == product.ProductName.ToLower()).Select(e => e.ProductId);
        foreach (var item in products)
        {
            var errormessage = _db.ErrorMessageTracks.Where(q => q.ProductId == item).Select(e => e.ErrorMessage);
            foreach (var msg in errormessage)
            {
                if (!localList.Select(q => q.ErrorMessageId).Contains(msg.ErrorMessageId))
                {

                    localList.Add(msg);
                }
            }
        }

    }
    return localList;
}

I have referred to other similar questions but I couldn't resolve the issue as I am not sure how to close the SQL connection as I am using Linq Query and not the actual SQL Join. Any help would be appreciated!

Answer 1

You can add ToList() at the end of the linq query,it should solve the problem:

var products = _db.Products.Where(q => q.ProductName.ToLower() == product.ProductName.ToLower())
                           .Select(e => e.ProductId).ToList();