The back end of my application runs on NetCore as a Web API. The front end can be considered unrelated.
我的应用程序的后端在NetCore上作为Web API运行。前端可以被认为是无关的。
Most of my application runs on Entity framework. However I've recently had to implement some custom filtering into one of our DB calls. This Db call is called up to 3 times at once when a page on the web UI is loaded.
我的大多数应用程序都在Entity框架上运行。但是我最近不得不在我们的一个数据库调用中实现一些自定义过滤。当加载Web UI上的页面时，此Db调用最多被调用3次。
I'm having issues with the following code, which is throwing the following error: System.InvalidOperationException: 'The connection was not closed. The connection's current state is open.'
我遇到以下代码的问题，这是抛出以下错误：System.InvalidOperationException：'连接未关闭。连接的当前状态是打开的。
The code:
代码：
foreach (var filter in dbFilters)
{
var cacheKey = $"matching_filters_id:{matchingConfigId}_filter:{filter.FilterFunctionName}";
var ids = new List<int>();
if (!_cache.TryGetValue(cacheKey, out ids))
{
ids = new List<int>();
using (var conn = _dbContext.Database.GetDbConnection())
{
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandText =
SqlHelper.BuildGetMatchingFilterIdsForIntersect(filter.FilterFunctionName);
using (var result = command.ExecuteReader())
{
while (!result.IsClosed && result.Read())
{
ids.Add((int) result["ItemId"]);
}
}
}
conn.Close();
}
var cacheEntryOptions = new MemoryCacheEntryOptions()
.SetSlidingExpiration(TimeSpan.FromSeconds(30));
_cache.Set(cacheKey, ids, cacheEntryOptions);
}
// Joins filtered list against each filter to make the list smaller.
firstFilter = firstFilter.Join(ids, o => o.SourceKey, id => id, (o, id) => o).ToArray();
}
What i'm attempting to do, is filter the list firstFilter which is a POCO containing a few properties. I'm attempting to filter this by joining it to one or more list of filtered ItemIds being returned from multiple different SQL queries. Not the most elegant solution but its what i have to work with.
我正在尝试做的是过滤列表firstFilter，这是一个包含一些属性的POCO。我试图通过将它连接到从多个不同的SQL查询返回的一个或多个过滤的ItemId列表来过滤它。不是最优雅的解决方案，但它是我必须使用的。
What i understand to be happening is that as the client is calling this method more then once, the methods are executing concurrently, causing the DB connection to be opened twice at once, causing the connection to fail and the error to be thrown.
我理解的是，当客户端多次调用此方法时，方法会同时执行，导致DB连接一次打开两次，导致连接失败并抛出错误。
I've attempted to stop this from happening by removing the async keyword form my method: public JsonResult GetMatches(int matchingConfigId...... ) but without success. I've also tried to intorduce session into the application, which I've read might cause calls to the same method from one client 'session' to happen in sequence rather then concurrently. But this has not seemed to have any effect.
我试图通过从我的方法中删除async关键字来阻止这种情况发生：public JsonResult GetMatches（int matchingConfigId ......）但没有成功。我还尝试将会话引入应用程序，我读过这可能会导致从一个客户端“会话”调用相同的方法，而不是同时发生。但这似乎没有任何影响。
Of course i might be barking up the entirely wrong tree.. but I've been bashing my head against said tree for most of the day now and I've reached my limit. Any help would be greatly appreciated
当然，我可能正在咆哮完全错误的树......但是我现在大部分时间一直在反对这棵树，我已经达到了我的极限。任何帮助将不胜感激
1 个解决方案
#1
0
So it turns out that the variable dbFilters, which came from entity framework, had not been resolved with a ToList() or ToListAsync() yet, so the connection to the DB for that call was still open, causing the subsequent calls in the above piece of code to fail.
事实证明，来自实体框架的变量dbFilters尚未使用ToList（）或ToListAsync（）解析，因此该调用的DB连接仍然打开，导致上面的后续调用一段代码失败。
#1
0
So it turns out that the variable dbFilters, which came from entity framework, had not been resolved with a ToList() or ToListAsync() yet, so the connection to the DB for that call was still open, causing the subsequent calls in the above piece of code to fail.
事实证明，来自实体框架的变量dbFilters尚未使用ToList（）或ToListAsync（）解析，因此该调用的DB连接仍然打开，导致上面的后续调用一段代码失败。