Sometimes when using the Entity Framework we decide to encapsulate a complicated piece of SQL into a stored procedure and call it directly from your code.
But how to you extract the result of the Stored Proc if it returns a combination of columns from different tables?
Consider the following Stored Proc which returns a list clients recently worked on.
Notice that columns returned span 2 tables (Joined) so you can’t just make the SP return a List of Clients..
select top(@number)c.Client_Name as ClientName,c.ClientID asClientID,Max(p.EndTimeStamp) asLastTimeWorked
fromClients c joinTimeLog p
on
c.ClientID =p.ClientCode
Group Byc.Client_Name,c.ClientID
Order byMAX(p.EndTimeStamp)desc
So now you add the Stored Procedure in your Entity Diagram, but what will be its return type?
- If you double click the StoredProc in the Model.Store to get the following screen
- If your SP returns a combination of columns click on the “Get Column Information” button to get the column names returned by the SP
- Click on “Create new Complex Type” to create a new class for this group of columns
Now you can use the RecentClients_Results(Rename if you like) as a normal entity class in your code!
Hope this helps!
Until next time!
Cennest
Now you can use the RecentClients_Results(Rename if you like) as a normal entity class in your code!
Hope this helps!
Until next time!
Cennest