c# - 查询时的 Entity Framework Core 格式异常

Question

我有以下查询

private const String _RFS_TAG = "NEW_RFS_WLI";
private const String _RFS_QUERY_FILTER = "'{\"bsareadyforservicewli\" : {\"messageheader\" : {\"messagetype\" : \"" + _RFS_TAG + "\"}}}'";

private const String _LATEST_RFS_FOR_ENTRY_POINT_QUERY = "SELECT o.* FROM connectivity_order_entry_points as oep " +
                    "INNER JOIN connectivity_orders as o " +
                    "ON oep.connectivity_order_id = o.id " +
                    "INNER JOIN connectivity_order_updates as ou " +
                    "ON o.id = ou.connectivity_order_id " +
                    "WHERE oep.connectivity_entry_point_id = {0} AND " +
                    "ou.data @> " + _RFS_QUERY_FILTER + " " +
                    "ORDER BY ou.id DESC " +
                    "LIMIT 1;";

当我执行以下方法尝试执行 SQL 语句时

public async override Task<Order> GetActiveOrderForEntryPoint(int id)
{
    return await Context.Orders.FromSql(_LATEST_RFS_FOR_ENTRY_POINT_QUERY, id).FirstOrDefaultAsync();
}

我得到一个例外说

输入字符串的格式不正确。

后端是一个PostgreSQL数据库（版本 10）。但我无法弄清楚我到底哪里错了。

完整的堆栈跟踪：

fail: Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware[1]
      An unhandled exception has occurred while executing the request.
System.FormatException: Input string was not in a correct format.
   at System.Text.StringBuilder.FormatError()
   at System.Text.StringBuilder.AppendFormatHelper(IFormatProvider provider, String format, ParamsArray args)
   at System.String.FormatHelper(IFormatProvider provider, String format, ParamsArray args)
   at System.String.Format(String format, Object[] args)
   at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.GenerateFromSql(String sql, Expression arguments, IReadOnlyDictionary`2 parameters)
   at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.VisitFromSql(FromSqlExpression fromSqlExpression)
   at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.<GenerateList>b__59_0(Expression e)
   at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.GenerateList[T](IReadOnlyList`1 items, Action`1 generationAction, Action`1 joinAction, IReadOnlyList`1 typeMappings)
   at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.GenerateList(IReadOnlyList`1 items, Action`1 joinAction, IReadOnlyList`1 typeMappings)
   at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
   at Microsoft.EntityFrameworkCore.Query.Sql.DefaultQuerySqlGenerator.GenerateSql(IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.ShaperCommandContext.GetRelationalCommand(IReadOnlyDictionary`2 parameters)
   at 

标签： c#.net-coreentity-framework-core

Answer 1

确保您转义查询括号：

private const String _RFS_QUERY_FILTER = "'{\"bsareadyforservicewli\" : {\"messageheader\" : {\"messagetype\" : \"" + _RFS_TAG + "\"}}}'";

至

private const String _RFS_QUERY_FILTER = "'{{\"bsareadyforservicewli\" : {{\"messageheader\" : {{\"messagetype\" : \"" + _RFS_TAG + "\"}}}}}}'";