Entity Framework Core format exception on query

Question

I have the following query

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;";

When I execute the following method trying to execute the SQL statement

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

I get an exception saying

Input string was not in correct format.

The backend is a PostgreSQL database (version 10). But I can't figure out where exactly I'm going wrong.

The full stacktrace:

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

I suggest you to try this query at db side first, i think your query has some problems — Furkan Öztürk
– Furkan Öztürk, Commented Dec 19, 2019 at 11:07
According to your acception for last answer, the problem has occured by your filter string. I am glad that you found your solution — Furkan Öztürk
– Furkan Öztürk, Commented Dec 20, 2019 at 22:15

Niek van der Maaden · Accepted Answer · 2019-12-19 12:01:23Z

1

Make sure you escape your query brackets:

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

To

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

answered Dec 19, 2019 at 12:01

Niek van der Maaden

4823 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Entity Framework Core format exception on query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related