Bulb Flash:-Returning Complex objects from Stored Procedures in Entity Framework!

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

image

  • If your SP returns a combination of columns click on the “Get Column Information” button to get the column names returned by the SP

image

  • Click on “Create new Complex Type” to create a new class for this group of columns

image

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

Advertisements

About Cennest
Cennest is the brainchild of a group of IT Professionals keen to try their hand in the world of IT Freelancing. After working in the corpora

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: