c# - 在 EF 生成的查询中预期为“(”
问题描述
以下代码是在 MySQL Server 5.7 上运行的 SQL 查询:
SELECT
`t`.`c`,
`t`.`Id`,
`t0`.`Id`,
`t0`.`Bio`,
`t0`.`Image`,
`t0`.`Name`,
`t0`.`Follower_id`,
`t0`.`Followed_id`
FROM
(
SELECT
(
SELECT
COUNT(*)
FROM
`PROFILE_FOLLOW_PROFILE` AS `p`
WHERE
`u`.`Id` = `p`.`Followed_id`
) AS `c`,
`u`.`Id`
FROM
`USER_PROFILES` AS `u`
WHERE
`u`.`Id` = 2
LIMIT
1
) AS `t`
LEFT JOIN LATERAL (
SELECT
`u0`.`Id`,
`u0`.`Bio`,
`u0`.`Image`,
`u0`.`Name`,
`p0`.`Follower_id`,
`p0`.`Followed_id`
FROM
`PROFILE_FOLLOW_PROFILE` AS `p0`
INNER JOIN `USER_PROFILES` AS `u0` ON `p0`.`Follower_id` = `u0`.`Id`
WHERE
`t`.`Id` = `p0`.`Followed_id`
ORDER BY
(
SELECT
1
)
LIMIT
10 OFFSET 0
) AS `t0` ON TRUE
ORDER BY
`t`.`Id`,
`t0`.`Follower_id`,
`t0`.`Followed_id`,
`t0`.`Id`
此查询不会运行,并将返回以下错误:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( SELECT `u0`.`Id`, `u0`.`Bio`, `u0`.`Image`, `u0`.`' at line 29
此语句由以下 EF 查询生成:
var selectedProfiles = _context.USER_PROFILES
.Where(x => x.Id == id)
.Select(x => new PagedListResult<ProfileForPostDto>
{
Total = x.Followers.Count,
Data = (x.Followers
.Select(f => new ProfileForPostDto
{
Id = f.Follower.Id,
Bio = f.Follower.Bio,
Image = f.Follower.Image,
Name = f.Follower.Name
})
.Skip((pageNum - 1) * pageSize)
.Take(pageSize)
.ToList()),
Page = pageNum,
PageSize = pageSize
}).FirstOrDefault();
我一直在检查 sql 查询的语法,根据我自己和 SQL 验证器,它似乎是正确的。
程序上的日志指向语句的同一部分:
2020-03-02 20:06:44.239 +01:00 [Error] Failed executing DbCommand ("9"ms) [Parameters=["@__id_0='?' (DbType = Int32), @__pageSize_3='?' (DbType = Int32), @__p_2='?' (DbType = Int32)"], CommandType='Text', CommandTimeout='30']"
""SELECT `t`.`Id`, `t1`.`Id`, `t1`.`Bio`, `t1`.`Image`, `t1`.`Name`, `t1`.`Type`, `t1`.`User`, `t1`.`Follower_id`, `t1`.`Followed_id`
FROM (
SELECT `u`.`Id`
FROM `USER_PROFILES` AS `u`
WHERE `u`.`Id` = @__id_0
LIMIT 1
) AS `t`
LEFT JOIN LATERAL (
SELECT `u0`.`Id`, `u0`.`Bio`, `u0`.`Image`, `u0`.`Name`, `u0`.`Type`, `u0`.`User`, `t`.`Follower_id`, `t`.`Followed_id`
FROM (
SELECT `p`.`Follower_id`, `p`.`Followed_id`
FROM `PROFILE_FOLLOW_PROFILE` AS `p`
WHERE `t`.`Id` = `p`.`Followed_id`
ORDER BY (SELECT 1)
LIMIT @__pageSize_3 OFFSET @__p_2
) AS `t0`
INNER JOIN `USER_PROFILES` AS `u0` ON `t`.`Follower_id` = `u0`.`Id`
) AS `t1` ON TRUE
ORDER BY `t`.`Id`, `t1`.`Follower_id`, `t1`.`Followed_id`, `t1`.`Id`"
2020-03-02 20:06:44.308 +01:00 [Error] An exception occurred while iterating over the results of a query for context type '"Cosprea.EntityFrameworkCore.Models.CospreaAPIContext"'."
""MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(
SELECT `u0`.`Id`, `u0`.`Bio`, `u0`.`Image`, `u0`.`Name`, `u0`.`Type`, `u0' at line 8
我不知道出了什么问题,在它说的地方添加另一个“(”没有任何意义。
任何想法?
解决方案
MySQL 不支持横向连接
推荐阅读
- java - Liferay生成的数据库的名称是什么?
- c# - 为什么飞行相机永远不会在地形上的航路点之间飞行?
- module - 如何跨子例程处理模块中的 Fortran 全局可分配变量
- python - String concatenation in while loop not working
- python - Python : pysvn.Client.info2() - when using this command, the list doesn't appear in alphabetical format
- r - Calculate Ratio of multiple pairs of columns
- javascript - 将 .focus 设置为倒数第二个输入,而不是第一个或最后一个
- java - Vertx 抛出“响应已被写入”IllegalStateException
- python - 使用 if 语句循环遍历列表,只有列表的最后一个位置运行 if 语句
- python-3.x - 如何将图形添加到编码足球比赛