CODEWITHSUNDEEP
c#mysqlasp.net-coreentity-framework-corepomelo-entityframeworkcore-mysql
Simran Singh
Simran Singh

Reputation: 52

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

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!

Upvotes: 0

Views: 4189

Answers (2)

user3429301
user3429301

Reputation: 19

Adding Tolist() to get the products for the the outer for will execute the query immediately and allow the connection to be available for the inner for to get the items.

Upvotes: -1

Abdoul Aziz Cisse
Abdoul Aziz Cisse

Reputation: 54

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();

Upvotes: 0

Related Questions