WCF RIA Services solution, stored procedures for data retrieval, Entity Framework, LINQ-TO-SQL classes
I have a very basic scenario which I'm trying to accomplish with WCF RIA Services, and I'm running into a complete wall which I hope someone can help with. Judging from the number of posts I've seen regarding stored procedures, it doesn't seem like there is a concrete solution, but here goes.
I'm trying to build a basic WCF RS solution which will function as my data layer. The database exposes the data via stored procedures. So in my simple example, here are the steps I'm following:
1) In VS2010, create a 'WCF RIA Services Class Library' project
2) In the resulting .WEB project, I add a new item, 'ADO.NET Entity Model' (Generate From Database, and when it comes time to choose database objects, I only choose a stored procedure which returns data, no tables selected here).
3) This generates the blank Entity Data Model, so through trial and error I discovered that I need to right-click in the designer, select 'Add --> Function Import...' and go through the process which allows me to select my stored procedure from the drop-down, click 'Get Column Information', and then 'Create New Complex Type', which creates the complex type in the Model Browser, with the correct column names and so forth from the stored procedure.
At this point, the frustrating part comes in because it doesn't seem possible to generate Entities from the results of this process and the resulting Complex Type. When I now go to create a Domain Service Class, there are no available Data Context objects which can be selected, which would then I'm assuming generate some code for method calls and return resultsets and so forth.
So, I am at a complete loss as to how this is supposed to work. I can't possibly be the first person that has tried to do this.
Is my approach wrong, i.e., is the recommended approach no longer stored procedures for data access? In my case, my stored procedures join a couple tables to produce the results, so I can't just generate Entities based on tables alone, because how would I join them to produce the results that the sp does?
How are others handling this situation? I've also tried using LINQ-to-SQL classes, and I hit the same wall, it seems difficult to somehow generate entities which can be used in code.
I am at a complete loss, are there any suggestions as to how to create a simple WCF RIA Services solution, which uses either the Entity Framework or LINQ-TO-SQL classes which can then be linked to Entities? Or am I heading down the wrong path? Surely with all of the stored procedures in use in the real world there would be support for this sort of thing?
I have read many many blogs but they all seem to deal with simplistic examples using EF objects which are generated based on tables only and populating a datagrid, and the ones which deal with sp's seem to be showing how to use the Insert/Update/Delete mapping features to sp's, not just some simple read methods using stored procedures which return data which can then be exposed to the Silverlight client as a collection of the Complex Type.
Does anyone have any suggestions? This has been an exercise in frustration. Thanks in advance.
I agree with steelerfan. I have many "read" stored procedures that return complex types from my database. As I can tell, I have no way to port my application over to a silverlight front-end using RIA Services and my existing set of stored procedures. Can anyone help me out with how I go about recreating / rewriting the data layer? Do I have to scrap RIA Services?
Thank You Colin and Sladapter, like always
We can all rest and be happy, becasue the latest version of Ria now supports Complex Types