How to consume a complex object from sproc using WCF / OData data services?

Question

Using WCF data services (and the latest Entity Framework), I want to return data from a stored procedure. The returned sproc fields do not correspond 1: 1 to any object in my db, so I create a new complex type for it in the edmx model (instead of attaching an existing object):

• Right click * .edmx model / Add / Function Import
• Select sproc (returns three fields) - GetData li>
• Click Get Column Information
• Add function import name: GetData li>
• Click "Create New Complex Type - GetData_Result"

In the service, I define:

    [WebGet]
    public List<GetData_Result> GetDataSproc()
    {
        PrimaryDBContext context = new PrimaryDBContext();
        return context.GetData().ToList();
    }

System.Data.Services System.Data.Services.Client - Install-Package EntityFramework -Pre, 4.0, 5.x.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Services.Client;
using ConsoleApplication1.PrimaryDBService;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataServiceContext context = new DataServiceContext(new Uri("http://localhost:50100/PrimaryDataService1.svc/"));
            IEnumerable<GetData_Result> result = context.Execute<GetData_Result>(new Uri("http://localhost:50100/PrimaryDataService1.svc/GetDataSproc"));
            foreach (GetData_Result w in result)
            {
                Console.WriteLine(w.ID + "\t" + w.WHO_TYPE_NAME + "\t" + w.CREATED_DATE);
            }

            Console.Read();
        }
    }
}

UriKind.Relative - , .

URL-, , , .

:

  <system.diagnostics>
    <sources>
      <source name="System.ServiceModel" switchValue="Information, ActivityTracing" propagateActivity="true">
        <listeners>
          <add name="traceListener" type="System.Diagnostics.XmlWriterTraceListener" initializeData="c:\temp\WebWCFDataService.svclog" />
        </listeners>
      </source>
    </sources>
  </system.diagnostics>

... Microsoft Service Trace Viewer, :

.

<E2ETraceEvent xmlns="http://schemas.microsoft.com/2004/06/E2ETraceEvent">
<System xmlns="http://schemas.microsoft.com/2004/06/windows/eventlog/system">
<EventID>524312</EventID>
<Type>3</Type>
<SubType Name="Warning">0</SubType>
<Level>4</Level>
<TimeCreated SystemTime="2012-04-03T14:50:11.8355955Z" />
<Source Name="System.ServiceModel" />
<Correlation ActivityID="{66f1a241-2613-43dd-be0c-341149e37d30}" />
<Execution ProcessName="WebDev.WebServer40" ProcessID="5176" ThreadID="10" />
<Channel />
<Computer>MyComputer</Computer>
</System>
<ApplicationData>
<TraceData>
<DataItem>
<TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Warning">
<TraceIdentifier>http://msdn.microsoft.com/en-US/library/System.ServiceModel.EvaluationContextNotFound.aspx</TraceIdentifier>
<Description>Configuration evaluation context not found.</Description>
<AppDomain>fd28c9cc-1-129779382115645955</AppDomain>
</TraceRecord>
</DataItem>
</TraceData>
</ApplicationData>
</E2ETraceEvent>

, , ?

- -

Microsoft WCF Data Services October 2011 CTP, DataServiceProtocolVersion.V3, Microsoft.Data.Services.Client(v4.99.2.0). foreach:

. 'ConsoleApplication1.WcfDataServiceOctCTP1.GetDataSproc_Result' , . , , , .

, - , .

Answer 1

Recap: DAL DLE ( ), . WCF . , , ORM Dapper WCF.

*.edmx POCO sproc.

BaseRepository MiscDataRepository:

namespace WcfDataService.Repositories
{
    public abstract class BaseRepository
    {
        protected static void SetIdentity<T>(IDbConnection connection, Action<T> setId)
        {
            dynamic identity = connection.Query("SELECT @@IDENTITY AS Id").Single();
            T newId = (T)identity.Id;
            setId(newId);
        }

        protected static IDbConnection OpenConnection()
        {
            IDbConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["PrimaryDBConnectionString"].ConnectionString);
            connection.Open();
            return connection;
        }
    }
}

namespace WcfDataService.Repositories
{
    public class MiscDataRepository : BaseRepository
    {
        public IEnumerable<GetData_Result> SelectAllData()
        {
            using (IDbConnection connection = OpenConnection())
            {
                var theData = connection.Query<GetData_Result>("sprocs_GetData",  
                     commandType: CommandType.StoredProcedure);

                return theData;
            }
        }
    }
}

:

namespace WcfDataService
{
    public class Service1 : IService1
    {
        private MiscDataRepository miscDataRepository;

        public Service1()
            : this(new MiscDataRepository())
        {
        }

        public Service1(MiscDataRepository miscDataRepository)
        {
            this.miscDataRepository = miscDataRepository;
        }

        public IEnumerable<GetData_Result> GetData()
        {
            return miscDataRepository.SelectAllData();
        }
    }
}

... :

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Service1Client client = new Service1Client();
            IEnumerable<GetData_Result> result = client.GetData();
            foreach (GetData_Result d in result)
            {
                Console.WriteLine(d.ID + "\t" + d.WHO_TYPE_NAME + "\t" + d.CREATED_DATE);
            }
            Console.Read();
        }
    }
}

PetaPOCO, , Dapper - :

namespace PetaPocoWcfDataService
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
    public class Service1 : IService1
    {
        public IEnumerable<GetData_Result> GetData()
        {
            var databaseContext = new PetaPoco.Database("PrimaryDBContext");  // using PetaPOCO for data access
            databaseContext.EnableAutoSelect = false;                               // use the sproc to create the select statement

            return databaseContext.Query<GetData_Result>("exec sproc_GetData");
        }
    }
}

, PetaPOCO, Dapper .

EDMX - , .

, ProfileDetailsByID_Result sq_mobile_profile_get_by_id sproc.

public ProfileDetailsByID_Result GetAllProfileDetailsByID(int profileID)
{
    using (IDbConnection connection = OpenConnection("DatabaseConnectionString"))
    {
        try
        {
            var profile = connection.Query<ProfileDetailsByID_Result>("sq_mobile_profile_get_by_id",
                new { profileid = profileID },
                commandType: CommandType.StoredProcedure).FirstOrDefault();

            return profile;
        }
        catch (Exception ex)
        {
            ErrorLogging.Instance.Fatal(ex);        // use singleton for logging
            return null;
        }
    }
}

, Dapper EDMX . , , , Microsoft - OData.

--- ---

, Microsoft, :

, , Odata . , , .

* . , Xml Linq .

. , , . , .

,

.