;)
Let’s face it. There are times when a stored procedure is going to create a more efficient query, and provide the flexibility you need to return attributes from multiple SQL tables in a non-traditional way.
In my case, I had a number of different log tables that I wanted to combine into a single result. My log tables contained different field attributes, and so I was not able to simply use the ObjectQuery<T>.Union method to combine my results. It was much easier to simply create a stored procedure that could translate the information into a nice generic result from the combined tables.
It was this result that I wanted to work with, not the individual tables. You can accomplish this in three easy steps:
1) Update your Model diagram (edmx) with the stored procedure you want to add. (Update Model from Database –> Add –> Stored Procedure)
2) Right-click on your diagram and choose Add –> Function Import. Enter the name you want to use as the Entity function to retrieve the stored procedure. Select the stored procedure you want to associate with this Entity Function. Press Get Column Information. Then select Create New Complex Type. Enter the name of the custom Complex Type Entity you want to associate with the stored procedure.
3) That’s it! Now you can use the function in a very elegant way and still have a strongly typed object to work with.
