How to consume a complex object from a sproc using WCF Data Services / OData?

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 match 1:1 any entity in my db, so I create a new complex type for it in the edmx model (rather than attaching an existing entity):

1. Right-click the *.edmx model / Add / Function Import
2. Select the sproc (returns three fields) - GetData
3. Click Get Column Information
4. Add the Function Import Name: GetData
5. 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();
    }

I created a quick console app to test, and added a reference to System.Data.Services and System.Data.Services.Client - this after running Install-Package EntityFramework -Pre, but the versions on the libraries are 4.0 and not 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();
        }
    }
}

I didn't use the UriKind.Relative or anything else to complicate this.

When I navigate in the browser to the URL, I see data, but when I consume it in my console app, I get nothing at all.

Adding tracing to the mix:

  <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>

... and opening using the Microsoft Service Trace Viewer, I see two idential warnings:

Configuration evaluation context not found.

<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>

So why am I able to see data from the browser, but not when consumed in my app?

-- UPDATE --

I downloaded the Microsoft WCF Data Services October 2011 CTP which exposed DataServiceProtocolVersion.V3, created a new host and client and referenced Microsoft.Data.Services.Client (v4.99.2.0). Now getting the following error on the client when trying iterate in the foreach loop:

There is a type mismatch between the client and the service. Type 'ConsoleApplication1.WcfDataServiceOctCTP1.GetDataSproc_Result' is an entity type, but the type in the response payload does not represent an entity type. Please ensure that types defined on the client match the data model of the service, or update the service reference on the client.

I tried the same thing by referencing the actual entity - works fine, so same issue.

Answer 1

Recap: I want to create a high-performing WCF service DAL (data access layer) that returns strongly-typed stored procedures. I initially used a "WCF Data Services" project to accomplish this. It seems as though it has its limitations, and after reviewing performance metrics of different ORM's, I ended up using Dapper for the data access inside a basic WCF Service.

I first created the *.edmx model and created the POCO for my sproc.

Next, I created a base BaseRepository and 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;
            }
        }
    }
}

The service class:

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();
        }
    }
}

... and then created a simple console application to display the data:

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();
        }
    }
}

I also accomplished this using PetaPOCO, which took much less time to setup than Dapper - a few lines of code:

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");
        }
    }
}

I like how quick and simple it was to setup PetaPOCO, but using the repository pattern with Dapper will scale much better for an enterprise project.

It was also quite simple to create complex objects directly from the EDMX - for any stored procedure, then consume them.

For example, I created complex type return type called ProfileDetailsByID_Result based on the 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;
        }
    }
}

So using Dapper along with some EDMX entities seems to be a nice quick way to get things going. I may be mistaken, but I'm not sure why Microsoft didn't think this all the way through - no support for complex types with OData.

--- UPDATE ---

So I finally got a response from Microsoft, when I raised the issue over a month ago:

We have done research on this and we have found that the Odata client library doesn’t support complex types. Therefore, I regret to inform you that there is not much that we can do to solve it.

*Optional: In order to obtain a solution for this issue, you have to use a Xml to Linq kind of approach to get the complex types.

Thank you very much for your understanding in this matter. Please let me know if you have any questions. If we can be of any further assistance, please let us know.

Best regards,

Seems odd.