With this query I able to select result as JSON.

string strQuery = "declare @json varchar(max) = (select field1 from table1 FOR JSON AUTO)print @json";

How could to use this result on .Net and what type of parameter should use instead of Newtonsoft.Json.Linq.JObject

using (TestEntities objEntities = new TestEntities())
        {
            Newtonsoft.Json.Linq.JObject strJson = objEntities.Database.SqlQuery<Newtonsoft.Json.Linq.JObject>(strQuery).FirstOrDefault();
            Response.Write(strJson.ToString());
        }

CodePudding user response：

You should read this as a string, then deserialize it afterwards if you need to. But you are in any case ToString-ing it, so there is no point deserializing it at all. Just keep it as a string.

Furthermore, PRINT has a maximum of 8000 bytes (4000 Unicode characters), and is intended for informational messages, it is not designed for data transfer. Use SELECT instead.

You should also pass JSON as nvarchar(max)

using (TestEntities objEntities = new TestEntities())
{
    const string strQuery = @"
declare @json nvarchar(max) = (
    select field1
    from table1
    FOR JSON AUTO
);
select @json;
";
    var result = objEntities.Database.SqlQuery<string>(strQuery).FirstOrDefault();
    Response.Write(result);
    // alternatively
    var strJson = Newtonsoft.Json.Linq.JObject.Parse(result);
}

Don't be tempted to just do a bare SELECT with FOR JSON, as it has issues when the result is over 2000 characters. You must put it in a variable first.