Return an unknown object in an ApiController witih EF?

Question

Is it possible to return a result set from the database without knowing the schema in advanced?

I'm exposing the ability for the client to pass parameters to a stored procedure via API:

    [Route("TheRequest")]
    public object Get([FromUri] TheRequest request)

This will then return:

_repository.Database.SqlQuery<object>(request.ToSqlString());   //execute sql against a stored procedure, passing in the required parameters to it

When attempting to do this, I believe that the controller doesn't know how to serialize the object returned. This is what postman returns:

Is it possible to return a Json serialized object response from the database without knowing the object schema?

Answer 1

Is it possible to return a result set from the database without knowing the schema in advanced?

In regards to just EF, the answer is not really. EF was designed for you to know the schema in advance. So you can still use DAO off of the ContextDb.Database but there isn't much point to using EF you you do that.

Now if the question was, can I generically typed instance from EF then sure, no problem:

var result = DbContext.Set<T>().FirstOrDefault();

This code doesn't know what it's pulling at compile time.

Is it possible to return a Json serialized object response from the database without knowing the object schema?

Sorta, as I mentioned before, you can't use EF as it was intended, but you could certainly do something like

public ActionResult Index(string type)
{
  var entityType = Type.GetType(type);
  // reflection
  var methods = typeof(ContextDb).GetMethods("Set");

  // Not tested, but something like the following
  // Find the Generic Version
  var method = methods.Where(m => m.IsGenericMethod).FirstOrDefault();
  // Make the method info for the typed method
  var methodInfo = method.MakeGenericMethod(entityType);
  // Invoke method, cast as needed, get value
  var result = (methodInfo.Invoke(ContextDb) as DbSet).FirstOrDefault();

  return Json(result);
}