How can I use Entity Framework to load the results of a stored procedure?
I have a challenge. We have a database that was originally intended for use with VB6, and most of the functionality is in stored procedures. I cannot make changes to the stored procedures as the old application still needs to run for a while and I don't have my own copy of the database that I can change even for a little while.
So, is it possible to execute a stored procedure from EF and use it best to write the results to an array / collection of POCOs?
I tried the first database approach and imported, but EF says the stored procedure doesn't return any columns and therefore cannot create a complex type. I found there are ways to change the stored procedure to make this work, but I cannot change the database I am using.
Another problem is that the column names in the results are things like Last Modified Date, in other words, with spaces. How will EF try to match them? Will it become DataLastChanged or perhaps Data_last_changed? Is there a way to tag my POCOs with attributes so that I can tell how they are displayed?
What I was hoping for was something like
var resuls = efContext.ExecuteStoredProcedure<MyPOCOType>("spName",param1, param2, ...);
And EF is best suited for results to type. Is there such a thing? By the way, we are using EF4, but I believe 5 are available to us.
source to share
I think I hacked part of the problem myself. The following snippet does what I need.
using (DbContext context = new DbContext("DBConnectionStringNameFromAppConfig"))
{
SqlParameter[] parameters =
{
new SqlParameter("@OwnerID", DBNull.Value),
new SqlParameter("@ExternalColorID", colorOwner.ExternalColorID),
new SqlParameter("@ProductionSiteID", DBNull.Value),
new SqlParameter("@PanelstatusNr", DBNull.Value),
new SqlParameter("@DateLastChecked", DBNull.Value),
new SqlParameter("@rowcount", DBNull.Value),
};
var colors = context.Database.SqlQuery<Models.ColorSelectEvaluation>("[dbo].[sp_Color_Select_Evaluation] @OwnerID, @ExternalColorID, @ProductionSiteID, @PanelstatusNr, @DateLastChecked, @rowcount", parameters).ToList();
}
Confusing it is all the same naming of columns. They mostly work, but EF does not map the NeedsEvaluation totals column to the NeedsEvaluation property on my object.
source to share
For column names that do not match. Another Q&A on stackoverflow deals with this beautifully. Why is my DbModelBuilder config being ignored when matching Entity from DbSet <T> .SqlQuery?
To summarize, MS thinks that would be great, but they don't support name matching this way. The only solution is to change the stored procedure and this is not an option for me as it will break legacy applications still using it.
source to share