I want to create a WCF service that calls a stored procedure in SQL Server using the Entity Framework and returns the result set to a browser.
I imported the stored procedure using the function import in EF and I built a complex type.
It seems that a complex type from the EF cannot be serialized and returned restfully. The only way I have made this work is to create a concrete class and build it from the complex type returned from EF. This works but it means if I have 30 stored procedures I would need to create 30 concrete classes which is somewhat of a pain.
Is there a better way to do this?
WCF contract:
[ServiceContract]
public interface IService1
{
[OperationContract,WebGet,XmlSerializerFormat]
List<People> usp_GetPeople();
}
Concrete class would need to be created for every procedure:
public class People
{
public int person_id;
public string last_name;
public string first_name;
public string street_addr;
public string state_code;
public string postal_code;
public People(int person_id, string last_name, string first_name, string street_addr, string state_code, string postal_code)
{
this.person_id = person_id;
this.last_name = last_name;
this.street_addr = street_addr;
this.state_code = state_code;
this.postal_code = postal_code;
}
public People() { }
}
WCF service:
public class Service1 : IService1
{
/// <summary>
/// Call stored proc and return resultset.
/// </summary>
/// <returns>List of resultset as concrete class People.</returns>
public List<People> usp_GetPeople()
{
try
{
using (var db = new demoEntities())
{
var res = db.usp_GetPeople();
List<People> lst = new List<People>();
foreach (usp_GetPeople_Result r in res)
{
People p = new People(r.person_id, r.last_name, r.first_name, r.street_addr, r.state_code, r.postal_code);
lst.Add(p);
}
return lst;
}
}
catch (Exception e)
{
Utility.Log("Error in usp_GetPeople. " + e.ToString());
return null;
}
}
Answer is to return a List of the EF complex type.
public List<usp_GetPeople_Result> usp_GetPeople2()
{
using (var db = new demoEntities())
{
return db.usp_GetPeople().ToList();
}
}
This will not work:
public ObjectResult<usp_GetPeople_Result> usp_GetPeople3()
{
using (var db = new demoEntities())
{
return db.usp_GetPeople();
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With