Entity Framework ExecuteStoreQuery with anonymous type

Question

I'm trying to create a generic method to get data from SQL Server by one stored procedure, but it doesn't return any data:

public static IEnumerable<T> Select<T>(string SQL)  
{
  string spName = "exec spGetData @SQL";
  var parametros = new object[]{
                                new SqlParameter("@SQL", SQL)
                              };
  IEnumerable<T> result;
  using (DBContext db = new DBContext())
  {
    result = from a in db.ObjectContext.ExecuteStoreQuery<T>(spName, parametros).ToList() select a;
  }
  return result;
}

Someone could help me?

Answer 1

EF handles stored procedures and creates entity result sets from the function mapping of the stored proc result select to be consumed off of the EF context.

Steps

1. In the designer choose 'Update Model From Database`.
2. When you get to the page which allows one to add tables/views/stored procs select the stored proc of interest.
3. Once the wizard is finished EF will contain the stored proc in the Model Browser which can be displayed by right clicking the EF design surface and selecting Model Browser.
4. In the Model Browser look for the stored proc in the Model Store -> Stored Procedures / Functions folder. Verify that the mapping is good by looking at the Model-> Function Imports and that the result entity can be found in the Complex Types folder.

From there you can call your stored proc off of an EF context and it returns a list of the result set to the code.

---

I give a more thorough explanation on my blog article and provide hints and work arounds to some problems with complex entity failures : Entity Framework Stored Procedure Instructions