locked
Untyped DataSets in WCF Data Services RRS feed

  • Question

  • I'm trying to return a data from a DataTable from a WCF Data Services Web Service. The problem I have is that I do not know the type until Runtime.

    I've followed the approach outlined here: http://blogs.msdn.com/b/vitek/archive/2010/11/09/adding-multi-value-properties-to-untyped-providers.aspx

    My initial approach was something like:

    protected override DSPContext CreateDataSource()
        {
            DSPContext context = new DSPContext();
    
            ResourceSet viewsSet, viewRowsSet, viewRowValuesSet;
            this.Metadata.TryResolveResourceSet("Views", out viewsSet);
            this.Metadata.TryResolveResourceSet("ViewRows", out viewRowsSet);
            this.Metadata.TryResolveResourceSet("ViewRowValues", out viewRowValuesSet);
    
            IList<DSPResource> views = context.GetResourceSetEntities(viewsSet.Name);
            IList<DSPResource> viewRows = context.GetResourceSetEntities(viewRowsSet.Name);
            IList<DSPResource> viewRowValues = context.GetResourceSetEntities(viewRowValuesSet.Name);
    
            int viewID = 71;
    
            DataTable dtData = GetDataForView(viewID);
    
            var viewObj = new DSPResource(viewsSet.ResourceType);
            viewObj.SetValue("ViewID", viewID);
            viewObj.SetValue("ViewRows", new List<DSPResource>());
            views.Add(viewObj);
    
            int idx = 0;
            foreach (DataRow dr in dtData.Rows)
            {
                var viewRowObj = new DSPResource(viewRowsSet.ResourceType);
                viewRowObj.SetValue("RowID", idx);
                viewRowObj.SetValue("ViewRowValues", new List<DSPResource>());
                //viewRows.Add(viewRowObj);
    
                int innerIDX = 0;
                foreach (object colVal in dr.ItemArray)
                {
                    var viewRowValue = new DSPResource(viewRowValuesSet.ResourceType);
                    viewRowValue.SetValue("FieldName", dtData.Columns[innerIDX].ColumnName);
                    viewRowValue.SetValue("FieldValue", colVal.ToString());
    
                    ((List<DSPResource>)viewRowObj.GetValue("ViewRowValues")).Add(viewRowValue);
    
                    innerIDX++;
                }
    
    
                ((List<DSPResource>)viewObj.GetValue("ViewRows")).Add(viewRowObj);
                idx++;                
            }
            return context;
        }
        protected override DSPMetadata CreateDSPMetadata()
        {
            DSPMetadata metadata = new DSPMetadata("MyWCFService", "MyWCF");
    
            ResourceType view = metadata.AddEntityType("View");
            metadata.AddKeyProperty(view, "ViewID", typeof(int));
    
            ResourceType viewRow = metadata.AddEntityType("ViewRow");
            metadata.AddKeyProperty(viewRow, "RowID", typeof(int));
    
            ResourceType viewRowValue = metadata.AddEntityType("ViewRowValue");
            metadata.AddKeyProperty(viewRowValue, "FieldName", typeof(string));
            metadata.AddPrimitiveProperty(viewRowValue, "FieldValue", typeof(string));          
    
            ResourceSet views = metadata.AddResourceSet("Views", view);
            ResourceSet viewRows = metadata.AddResourceSet("ViewRows", viewRow);
            ResourceSet viewRowValues = metadata.AddResourceSet("ViewRowValues", viewRowValue);
    
            metadata.AddResourceReferenceProperty(viewRow, "View", views);
            metadata.AddResourceSetReferenceProperty(view, "ViewRows", viewRows);
    
            metadata.AddResourceReferenceProperty(viewRowValue, "ViewRow", viewRows);
            metadata.AddResourceSetReferenceProperty(viewRow, "ViewRowValues", viewRowValues);
    
            return metadata;
        }

    When I try the URL "http://localhost/Test.svc/Views()/ViewRows" I get a list of Rows, each row has an ID and a collection of field values in that Row. All the webservice returns is the List of RowIDs. In order to get the collection for each row I need to call the following URL "http://localhost/Test.svc/Views()/ViewRows" for each row.

    I want to be able to return the data all in one go instead of making multiple server calls.

    That started my thinking, that I actually need to be able to construct the Metadata differently for each call the the WS (depending on the ID passed).  E.g. ID 1 will have a totally different structure that ID 2.  Imagine a scenario where you provide a user to enter SQL statements (anything they want). Then they call a WCF Data Service API to execute the statement and return whatever data the query returned in a tabular format.

    Can anybody assist me as to how to do this?

    Thursday, June 28, 2012 3:14 PM

Answers

  • In that case I would probably setup the service manually, not using WCF Data Services but just ODataLib alone. That will allow you to write the payload for each request by hand with no necessity for declaring the model up-front. You can still use model, but you can construct the model right before writing the response.

    ODataLib is Microsoft.Data.OData.dll which can be found on NuGet.

    Thanks,


    Vitek Karas [MSFT]

    • Marked as answer by keithnolan Saturday, June 30, 2012 11:42 AM
    Friday, June 29, 2012 1:39 PM
    Moderator

All replies

  • I've been thinking about this  more.  What I really want to achieve is be able to dynamically change the metadata for different ID's.

    E.g. For a request to http://localhost/Test.svc/Views(1) I may want to return a structure with Columns Name, Address, Phone No,

    But for a request http://localhost/Test.svc/Views(2) I may want to return a structure with Columns Company Name, Location, Fax No.

    I'm sure there has to be a way to achieve this. It's like I need to be able to read the ID value from the query string and react to it.

    I'd really appreciate anybodies ideas on this.

    Friday, June 29, 2012 7:27 AM
  • Hi,

    I would not encourage designing the service in that way. The problem is that clients don't expect OData services with such behavior. OData service is usually somewhat statically typed (the $metadata endpoint is the type information). Some client use this information to generate code, show UI and such (for example the WCF DS .NET client generates classes based on the $metadata).

    The WCF DS custom provider can in theory change the model on per-request bases, but for client consumption doing this in a substantial way is not recommended.

    I can see two ways to design your service:

    1) Use open properties (a lot). Define the entity returned from the Views entity set as having some kind of ID which would be the primary key (you have that already anyway) and mark it as Open. Then it would have no other declared properties. Then the server can return any properties on per-instance bases (no changes to the metadata is required). On the other hand some client will have a bit of trouble consuming these. For example the .NET client doesn't really support this directly, but there are workaround. The other downside is that you can't have open navigation properties. So if your view would contain a navigation property, such property would have to be statically declared and thus present on each instance.

    2) Use type inheritance. Somehow you would know all the possible shapes of the results and you would define entity types for every single one and make them inherit from a base type. Then the View set would be of the base type and each instance would be of a specific more derived type. This allows navigation properties and client support and so on. The downside is the necessity to know the types.

    Thanks,


    Vitek Karas [MSFT]

    Friday, June 29, 2012 8:35 AM
    Moderator
  • Hi Vitek,

    Thanks for taking the time to reply. I don't think either approach will work.  The big problem is that I have no knowledge of the types at all.

    Imagine an application where a user can save objects which execute SQL statements, each statement would have an ID then the WS is called to return the data from the SQL statement.

    e.g. ID 1 - "Select name, address, phone from customer" so http://localhost/Test.svc/Views(1) would return Name, Address, Phone columns

    ID 2 - Select business name, contact no, fax from supplier so http://localhost/Test.svc/Views(1) would return name, contact no, fax columns

    The user can configure this to come from any datasource (RDBMS, NoSQL, Cloud, RestAPI etc) so I have absolutely no knowledge of the underlying data source.

    The end consumer for this information is MS PowerPivot, hence the reason for trying to return the data as an Atom feed.


    • Edited by keithnolan Friday, June 29, 2012 9:34 AM
    Friday, June 29, 2012 9:33 AM
  • In that case I would probably setup the service manually, not using WCF Data Services but just ODataLib alone. That will allow you to write the payload for each request by hand with no necessity for declaring the model up-front. You can still use model, but you can construct the model right before writing the response.

    ODataLib is Microsoft.Data.OData.dll which can be found on NuGet.

    Thanks,


    Vitek Karas [MSFT]

    • Marked as answer by keithnolan Saturday, June 30, 2012 11:42 AM
    Friday, June 29, 2012 1:39 PM
    Moderator