1

I've been trying to call a stored procedure through Entity Framework and WCF Data Services (OData). It returns an entity not a complex type. Following walkthroughs found all over the web (like this one), I came up with this code inside my service:

[WebGet]
public IQueryable<Entity> GetEntitiesByParameterId(int parameterId)
{
     return CurrentDataSource.GetEntitiesByParameterId(parameterId).AsQueryable();
}

Calling the proc this way: ~WcfService.svc/GetEntitiesByParameterId?parameterId=1 executes the stored procedure and returns entities that should be returned. No problem there.

Everything works well until I try to use $select OData option ie. ~WcfService.svc/GetEntitiesByParameterId?parameterId=1&$select=name. Upon debugging, the method above runs without any error but it returns an Operation could destabilize the runtime error upon reaching the client. After so much research, apparently it is a very general error pointing to a lot of different causes. I haven't found one that really matches my particular problem. Closest are this and this but none of the solutions worked on my end.

Also, from the second article above:

This is a known limitation of WCF DS. ...

Second is that some of the queries won't work correctly because LINQ to EF needs little different LINQ expressions than LINQ to Objects in some cases. Which is the problem you're seeing.

It has been posted on 2012. If it its true, are there still no updates on this? And is there any other workaround to get the $select working on the stored proc call?

TL;DR:

Works:

~WcfService.svc/GetEntitiesByParameterId?parameterId=1
~WcfService.svc/GetEntitiesByParameterId?parameterId=1&$top=1
~WcfService.svc/GetEntitiesByParameterId?parameterId=1&$skip-5
~WcfService.svc/GetEntitiesByParameterId?parameterId=1&$filter={filter query}
~WcfService.svc/GetEntitiesByParameterId?parameterId=1&$expand=SomeNavigationProperty

Doesn't work:

~WcfService.svc/GetEntitiesByParameterId?parameterId=1&$select=name

Tech details:

EntityFramework 5, WCF Data Service 5.0, OData V3

*I've also tried upgrading to EF6 and WCF 5.6.2 and it still didn't work.

Any help would be appreciated. Thanks!

UPDATE: After a little more fumbling through this, I tried not going through the stored procedure and just return a manually constructed List<Entity> then returned it as queryable. Surprised to see that it still has the same error when $select is used. This may be a WCF Service Operation limitation and not particularly just for stored procedure calls. I went back to the documentation and it does show usage of other OData queries (top, expand & orderby) but nothing about $select.

This is merely an observation through my tests since I can't find much source for this particular problem. Any clarifications and other documentations are welcome.

Community
  • 1
  • 1
iamnobody
  • 646
  • 9
  • 23
  • 1
    I am afraid that $select is still not supported yet. But to work around this issue, you can implement another operation like GetEntitiesNameByParameterId to wrap GetEntitiesByParameterId and selecting name in to this operation. This action returnes the collection of name. – Layla Liu MSFT Dec 12 '14 at 12:49
  • @LaylaLiuMSFT Hi! Thanks for this. I would just like to clarify what you said: "$select is still not supported yet." Is this for stored procedure calls only or for WCF service operations in general? And would you know when it would be supported since there are already talks of abandoning further updates on WCF? – iamnobody Dec 15 '14 at 04:09
  • @LaylaLiuMSFT Updated my post for some further observations. Also, thanks for the workaround but I'd like the $select to be more dynamic and flexible ie. add/remove as many columns as needed like how OData does it. Again, thanks for taking the time. – iamnobody Dec 15 '14 at 06:38

1 Answers1

1

As per what Layla Liu MSFT commented, $select is still not supported though I couldn't find any definitive documentation about it.

From what I gather and observed, $select breaks the stored procedure call because it tries to alter the data shape already gotten from the database and attempts to return a dynamic entity instead. Something about the stored proc returning an ObjectResult might be messing it up. As to why the hardcoded List<Entity> doesn't work, I do not have any idea. Do not quote me on this however. As I have said, these are merely my observations.

Workaround: I found a simple and elegant workaround for it though. Since my stored procedures are only getting data from the database and does not alter data in any way (INSERT, UPDATE, DELETE), I tried using table-valued functions that returns a table equivalent to the entity on my EF. I've found that calling this function on the Service Operation method returns an IQueryable<Entity> which is basically what is needed. $select also works now and so does other OData query options.

Steps:

  1. Create a function on the database
  2. Update EDMX -> Add function
  3. Add new Function Import with Entity return type
  4. Create service operation in WCF Data Service that calls CurrentDataSource.<FunctionName>()
  5. Test in fiddler.

CODES

Database Function:

CREATE FUNCTION GetEntities(@parameter)
RETURN @entites TABLE(
    [Id] [int], 
    [Name] [nvarchar](100),
    ...
)
AS
BEGIN      
    INSERT INTO @entities
       SELECT [Id], [Name], ... FROM [EntityTable]

    RETURN      
END

WCF:

[WebGet]
public IQueryable<Entity> GetEntity(int parameter)
{
     return CurrentDataSource.GetEntity(parameter);
}

It doesn't really solve the stored procedure problem but I'm marking this as answer until someone can provide a better one as it does solve what I'm trying to do.

Hope this helps others too. :)

iamnobody
  • 646
  • 9
  • 23