How to expose a stored procedure via WCF data services and consume it in WPF client application?

Question

I have a simple stored proc which returns an int32 (select max(ID) from customer) - how can I expose that via EF4+WCF data services and use it from a WPF client?

There is lot of info about mapping a SP as a function in EF4 but the next step of exposing it in WCF dataservice and using in a client seems to be elusive. A 'How to...." for this will be very greatly appreciated.

 

Thanks

 

 

Answer 1

Hi,

You can create a service operation in your WCF data service which will expose the FunctionImport. Here are two posts I wrote about service operations and how to use them:
http://blogs.microsoft.co.il/blogs/gilf/archive/2008/11/14/service-operations-adding-business-logic-to-a-data-service.aspx
http://blogs.microsoft.co.il/blogs/gilf/archive/2008/11/14/consuming-data-services-service-operations.aspx

Here is a post that explain how to create a service operation on top of a LINQ to SQL model: http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/12/16/ado-net-data-services-part-2-using-service-operations-with-webget.aspx 
The implementation can be adjust also to EF.

I hope it will help you.

---

Gil Fink

Answer 2

Gil - thanks for the info. My situation is slightly different. I do not have an 'entity' being retured from my Stored proc - just a single int32. So my SP is quite simple - 'Select max(customerID) from Customer'

In EF4.0 I map the  SP to a function called maxID and expose the variable in my customer.svc.cs file like this

[WebGet]

 

 

public IQueryable<int?> maxID()

return this.CurrentDataSource.maxContactID().AsQueryable();

and hitting the corresponding URL (

http://localhost:2125/CustomerDataService.svc/maxID") works - brings back this XML -

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>

- <maxID xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices">

  <element p2:type="Edm.Int32" xmlns:p2="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">12</element>

  </maxID>

See that '12' there?

But when I do this in my code I get an error

IQueryable<int?> _MaxIDs = DS.CreateQuery<int?>("maxID");

MessageBox.Show(_MaxIDs.ToList()[0].ToString());

The second line errors out with "The XML element contains mixed content."

Any ideas what I am doing woring?

Thanks.

 

Answer 3

Hi RobinRaul,

If you map the sp to a function, a new method is added to the automatically generated context class, something like:

public ObjectResult<Nullable<global::System.Int32>> maxID()
{
    return base.ExecuteFunction<Nullable<global::System.Int32>>("maxID");
}

You can call the method directly in your code using an instantiated context, for example:

TestEntities context = new TestEntities();
ObjectResult<int?> res = context.maxID();
Console .WriteLine(res.First().Value);    

Best regards,

Alex Liang

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 4

Yes - that is true - I can do it if I direclty work with EF. But if I am exposing my EDMX via a WCF data service then how do I do it? The 'maxID' is directly not avialable in the context in WPF client using the data aservice. So based on all the other research it looks like I have to manually expose the maxID function by adding a [webget] function to my svc file which I did (see my previous reply) but I am having trouble getting it to work.

Do you have a quick sample of a SP exposed through WCF dataservice and consumed in a WPF client?

 

Thanks for your help.

 

 

Answer 5

Hi RobinRaul,

Please refer to this :

http://dotnet.dzone.com/news/exposing-stored-procedure-wcf

Best regards,

Alex Liang

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 6

Alex -

But the example in the link is still returning an "entity". In my case the SP is returing a simple integer. And I am not able to get it to work via WCF service. In this case (see my previous reply) I have exposed the SP as a mapped function called "maxID" and I see that  that it is working because when I go the URL

http://localhost:2125/CustomerDataService.svc/maxID

I get this reponse (correct value 12 is coming back)

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>

- <maxID xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices">

  <element p2:type="Edm.Int32" xmlns:p2="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">12</element>

  </maxID>

BUT in my code when I do this

string

 

 

DataserviceURL2 = http://localhost:2125/CustomerDataService.svc/maxID;

 

 

IEnumerable<int?> _MaxIDs = DS.Execute<int?>(new Uri(DataserviceURL2));

 

 

 

MessageBox.Show(_MaxIDs.First().Value.ToString());

I get this error on last line

"Error processing response stream. The XML element contains mixed content."

So looks like the XML being returned is not correct.

Any ideas?

Answer 7

Hi RobinRaul,

I am moving this thread to ADO.NET Data Services forum for better support. Thanks.

Best regards,

Alex Liang

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 8

Hi Robin,

Were you able to find an answer for your issue?

Thanks,

Cathy Miller

Microsoft Online Community Support

Answer 9

Hi Cathy,

I have the similar problem, I don´t find how to get it to work, could you help me?

Answer 10

Hi DavidFdz,

While WCF Data Services can expose store procedure via EF function call using WebGet attribute, there is no easy way to consume the data on the client end without using your own method to de-serialize the data. 

MVC 4 WebAPI addresses the issue by giving the ability to send any IQueryable / IEnumerable thru REST.

---

Haroon