Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Better way to call a stored procedure using Entity Framework and return restfully from WCF?

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;
    }
}
like image 737
Superdog Avatar asked Nov 13 '22 19:11

Superdog


1 Answers

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();
        }
    }
like image 60
Superdog Avatar answered Nov 15 '22 08:11

Superdog