Hi I have this strange situation. I am using .NET CORE and I have MicrosoftSQL and MySQL databases in development. When I make specific query with Entity Framework. MySQL shuts down if I am in development every time, same query is executed no problems on SQL or in MySQL but in CentOS server in production. I don't understand what is the problem with MySQL and why does it do that:
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (1,283ms) [Parameters=[@__myId_0='?' (Size = 255), @__p_1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT `a`.`CookRank`, `a`.`UserName`, `r3`.`DateOfCreation`, SUBSTRING(`r3`.`Description`, 0 + 1, 2000), `r3`.`Id`, `r3`.`MainPicture`, `r3`.`Name`, `c`.`Name`, `r3`.`CategoryId`, (
SELECT COUNT(*)
FROM `RecipeComments` AS `r`
WHERE `r3`.`Id` = `r`.`RecipeId`), `r3`.`Difficulty`, (
SELECT COUNT(*)
FROM `UserFavouriteRecepies` AS `u`
WHERE (`r3`.`Id` = `u`.`RecipeId`) AND NOT (`u`.`IsDeleted`)), (
SELECT COUNT(*)
FROM `RecipeIngredients` AS `r0`
WHERE (`r3`.`Id` = `r0`.`RecipeId`) AND NOT (`r0`.`IsDeleted`)), (
SELECT SUM(CAST(`r1`.`Score` AS signed))
FROM `RecipeVotes` AS `r1`
WHERE (`r3`.`Id` = `r1`.`RecipeId`) AND NOT (`r1`.`IsDeleted`)) / (
SELECT COUNT(*)
FROM `RecipeVotes` AS `r2`
WHERE (`r3`.`Id` = `r2`.`RecipeId`) AND NOT (`r2`.`IsDeleted`))
FROM `Recipes` AS `r3`
INNER JOIN `AspNetUsers` AS `a` ON `r3`.`AuthorId` = `a`.`Id`
INNER JOIN `Categories` AS `c` ON `r3`.`CategoryId` = `c`.`Id`
WHERE NOT (`r3`.`IsDeleted`) AND EXISTS (
SELECT 1
FROM `RecipeComments` AS `r4`
WHERE (`r3`.`Id` = `r4`.`RecipeId`) AND (NOT (`r4`.`IsDeleted`) AND (`r4`.`AuthorId` = @__myId_0)))
ORDER BY (
SELECT `r5`.`DateOfCreation`
FROM `RecipeComments` AS `r5`
WHERE (`r3`.`Id` = `r5`.`RecipeId`) AND (NOT (`r5`.`IsDeleted`) AND (`r5`.`AuthorId` = @__myId_0))
ORDER BY `r5`.`DateOfCreation` DESC
LIMIT 1) DESC
LIMIT @__p_1 OFFSET 0
fail: Microsoft.EntityFrameworkCore.Query[10100]
An exception occurred while iterating over the results of a query for context type 'Infrastructure.Data.ApplicationDbContext'.
MySql.Data.MySqlClient.MySqlException (0x80004005): Failed to read the result set.
---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host..
And that is how my Query looks like in C#:
["my-commented"] = (x) => x.Where(x => !x.IsDeleted && x.Comments.Any(rc => !rc.IsDeleted && rc.AuthorId == myId))
.OrderByDescending(x => x.Comments.Where(rc => !rc.IsDeleted && rc.AuthorId == myId).Select(rc => rc.DateOfCreation)
.OrderByDescending(d => d).First()),
It breaks in MySQL develompent mode every time, otherwise it works, if in Production or if I use MicrosoftSQL.
User contributions licensed under CC BY-SA 3.0