retrieve json with entityframework core

Question

I have store procedures returning json, thanks to for json path.

How do you consume them with entity-framework-core?

The following doesn't work:

var foo = _db.Set<JObject>()
             .FromSql("dbo.Mine @customerid = {0}", _user.guid)
             .FirstOrDefault();

Because JObject type is not part of the model:

InvalidOperationException: Cannot create a DbSet for 'JObject' because this type is not included in the model for the context.

But how are we supposed to do that with entity-framework-core?

Have you tried with <Mine> instead of <JObject>

H. Herzl
– H. Herzl

2018-01-29 18:25:38 +00:00
Commented Jan 29, 2018 at 18:25 — H. Herzl
– H. Herzl, Commented Jan 29, 2018 at 18:25
@H.Herzl Mine is a store procedure. Which return json.

Max Favilli
– Max Favilli

2018-01-29 18:45:36 +00:00
Commented Jan 29, 2018 at 18:45 — Max Favilli
– Max Favilli, Commented Jan 29, 2018 at 18:45

Max Favilli · Accepted Answer · 2018-01-30 17:03:32Z

1

After searching on google for a while I understood is not supported yet. If you don't have a model in the context you can't retrieve data with entityframework, point: https://learn.microsoft.com/en-us/ef/core/querying/raw-sql and https://github.com/aspnet/EntityFrameworkCore/issues/1862

I resolved doing it the old way:

  var jsonResult = new System.Text.StringBuilder();
  /*"using" would be bad, we should leave the connection open*/
  var connection = _db.Database.GetDbConnection() as SqlConnection;
  {
    await connection.OpenAsync();

    using (SqlCommand cmd = new SqlCommand(
        "Mine",
        connection))
    {
      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.Add("@customerid", SqlDbType.NVarChar).Value = _user.guid;

      using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
      {
        if (!reader.HasRows)
        {
          jsonResult.Append("[]");
        }
        else
        {
          while (reader.Read())
          {
            jsonResult.Append(reader.GetValue(0).ToString());
          }
        }
      }
    }
  }
  var raw = JArray.Parse(jsonResult.ToString());

  var ret = raw.ToObject<List<SiteData>>();

I am in doubt if it's better to explicitly close the connection or not.

edited Jan 30, 2018 at 17:03

answered Jan 29, 2018 at 18:51

Max Favilli

6,5393 gold badges44 silver badges65 bronze badges

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

3 Comments

H. Herzl Over a year ago

using should close the connection after of dispose it

Max Favilli Over a year ago

But since the context is injected in core, and that is the context connection being used should we dispose it?

Max Favilli Over a year ago

@H.Herzl tested, we must leave the connection open for subsequent context queries, otherwise they will fail.

Collectives™ on Stack Overflow

retrieve json with entityframework core

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related