locked
Return Multiple Result Sets from Stored Procedures to Silverlight App RRS feed

  • Question

  • Some of my stored procedures return multiple datasets.  For example, one stored procedure returns a Company dataset and Address, Phone, and EMail datasets associated with the company.

    Is there a way to return these multiple result sets to my Silverlight application using OData?

    Thanks



    TomBean

    Monday, December 17, 2012 6:39 PM

Answers

  • As noted above $expand will only work with IQueryable (so the latest error is expected).

    The cast is also I guess expected. You can't cast List<T> to IQueryable<T>, but you can call .AsQueryable() which will return IQueryable<T> for that list. That should work.

    Thanks,


    Vitek Karas [MSFT]

    • Marked as answer by TomBean Monday, December 31, 2012 4:44 PM
    Sunday, December 30, 2012 7:26 PM
    Moderator

All replies

  • If they are all entities and they have navigation properties between them (so that a Company is an entity which has a navigation property to Address and so on), then you can return just a queryable collection of the Company entities and expand the others. Like so:

    /CompanyServiceOperation?$expand=Address,Phone,EMail

    Note though that the client will need to explicitly ask for the expansions, there's no (simple) way for the server to auto-expand.

    Thanks,


    Vitek Karas [MSFT]

    Monday, December 17, 2012 7:47 PM
    Moderator
  • Vitek,

    Unfortunately, they are all complex types.  I change Address, Phone and EMail to use the entities for their tables but the Company needs to remain a complex type.

    Does that mean I'll have to make 4 calls to the database to retrieve them?

    Thanks


    TomBean

    Monday, December 17, 2012 8:24 PM
  • You could create a new complex type which has all four and return that.

    Thanks,


    Vitek Karas [MSFT]

    Monday, December 17, 2012 8:41 PM
    Moderator
  • Vitek,

    There will only be one Company record, however, usually there will be multiple records for Address, Phone and EMail.

    Would the correct way to create the new complex type be to start with the Company complex type and add a collection of the Address, Phone and EMail records to the end of the Company complex type?

    For example:

    <ComplexType Name="CompanyData">
            <Property Type="Int32" Name="CompanyId" Nullable="false" />
            <Property Type="String" Name="CompanyName" Nullable="false" MaxLength="100" />
            <Property Type="CompanyModel.CompanyAddresses" Name="CompanyAddresses" Nullable="true" />
            <Property Type="CompanyModel.CompanyPhones" Name="CompanyPhones" Nullable="true" />
            <Property Type="CompanyModel.CompanyEMails" Name="CompanyEMails" Nullable="true" />
    </ComplexType>

    If so, will the CompanyAddresses, CompanyPhones and CompanyEMails be collections?

    After I call the stored procedure, I assume I have to transfer the returned data to each of the complex properties in code, correct?

    Thanks


    TomBean

    Monday, December 17, 2012 9:33 PM
  • Vitek,

    I found out the way I suggested of creating the new complex type is incorrect.  I added the Address, Phone and EMail complex types to my Company complex type and got the following error:

    "Nested ComplexType property 'Addresses' in the ReturnType 'ap_Company_Load_Result' of the function 'CompanyEntities.ap_Company_Load' is not supported, please consider flattening the nested ComplexType property."

    If I do as the error message suggests, I will only be able to return one Address, so, that won't work.

    Is it possible to add a collection of Addresses, etc.?

    Thanks


    TomBean

    Tuesday, December 18, 2012 12:09 AM
  • I don't know this error message - it might be coming from EF or something like that...

    Plain OData definitely supports nested complex types. I just tried with a model like this one:

        public class Company
        {
            public string Name { get; set; }
            public List<Address> Addresses { get; set; }
        }
    
        public class Address
        {
            public string City { get; set; }
        }

    And it works just fine.

    What is the exception type (And callstack)?

    Thanks,


    Vitek Karas [MSFT]

    Tuesday, December 18, 2012 3:18 PM
    Moderator
  • Vitek,

    Here's the InnerException:

    System.Data.EntityCommandCompilationException was caught
      HResult=-2146232005
      Message=An error occurred while preparing the command definition. See the inner exception for details.
      Source=System.Data.Entity
      StackTrace:
           at System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)
           at System.Data.EntityClient.EntityProviderServices.CreateCommandDefinition(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)
           at System.Data.EntityClient.EntityCommand.CreateCommandDefinition()
           at System.Data.EntityClient.EntityCommand.TryGetEntityCommandDefinitionFromQueryCache(EntityCommandDefinition& entityCommandDefinition)
           at System.Data.EntityClient.EntityCommand.GetCommandDefinition()
           at System.Data.Objects.ObjectContext.CreateFunctionObjectResult[TElement](EntityCommand entityCommand, ReadOnlyMetadataCollection`1 entitySets, EdmType[] edmTypes, MergeOption mergeOption)
           at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, MergeOption mergeOption, ObjectParameter[] parameters)
           at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, ObjectParameter[] parameters)
           at DatabaseWebApp.CompanyDataModel.CompanyEntities.ap_Company_Load(Nullable`1 CompanyId) in c:\IDC\DatabaseWebApp\CompanyDataModel\CompanyDataModel.Context.cs:line 88
           at DatabaseWebApp.CompanyDataService.ap_Company_Load(Int32 CompanyId) in c:\IDC\DatabaseWebApp\CompanyDataService.svc.cs:line 40
      InnerException: System.NotSupportedException
           HResult=-2146233067
           Message=Nested ComplexType property 'Addresses' in the ReturnType 'ap_Company_Load_Result' of the function 'CompanyEntities.ap_Company_Load' is not supported, please consider flattening the nested ComplexType property.
           Source=System.Data.Entity
           StackTrace:
                at System.Data.EntityClient.EntityCommandDefinition.ValidateEdmResultType(EdmType resultType, EdmFunction functionImport)
                at System.Data.EntityClient.EntityCommandDefinition.DetermineStoreResultType(MetadataWorkspace workspace, FunctionImportMappingNonComposable mapping, Int32 resultSetIndex, IColumnMapGenerator& columnMapGenerator)
                at System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree)
           InnerException:

    The call stack only has two entries:
         1)  DatabaseWebApp.dll!DatabaseWebApp.CompanyDataService.ap_Company_Load(int CompanyId = 100007)
         2)  [External Code]

    Here's the way I added the Address, Phone and EMail types:

    <Property Type="Int32" Name="CompanyId" Nullable="false" />
    <Property Type="String" Name="Name" Nullable="false" MaxLength="100" />
    <Property Type="CompanyModel.CompanyAddress" Name="Addresses" Nullable="false" />
    <Property Type="CompanyModel.CompanyPhone" Name="Phones" Nullable="false" />
    <Property Type="CompanyModel.CompanyEMail" Name="EMailAddresses" Nullable="false" />

    CompanyModel.CompanyAddress, CompanyModel.CompanyPhone and CompanyModel.EMail are defined in the .edmx file as ComplexTypes with all their member properties.

    Thanks


    TomBean

    Tuesday, December 18, 2012 3:43 PM
  • This is from EF... but I'm not very good with EF, so I don't know if there's a workaround or not.

    Post a question to their forums here: http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/threads

    Thanks,


    Vitek Karas [MSFT]

    Tuesday, December 18, 2012 4:06 PM
    Moderator
  • Vitek,

    I posted the question to the Entity Framework forum but they don't seem to know much about packaging the results to return them to Silverlight.

    Following the code you sent showing OData supports nested complex types, I created the following:

    public partial class CompanyLoaded : List<LoadedCompany>
    {
    }
    [DataServiceKey("CompanyId")]
    public partial class LoadedCompany
    {
    	public List<Company_Load_Result> CompanyInfo { get; set; }
    	public List<CompanyAddress> Addresses { get; set; }
    	public List<CompanyPhone> Phones { get; set; }
    	public List<CompanyEMail> EMails { get; set; }
    }

    I can populate the LoadedCompany object with all the data returned by the stored procedure and add it to the CompanyLoaded object but when I try to cast it to IEnumerable<CompanyLoaded> to return it to my SilverLight application, it throws an InvalidCastException.

    I thought Lists implemented IEnumerable, and therefore, the cast should be valid.

    What can I do to package the results to return them to Silverliight?

    Thanks


    TomBean

    Thursday, December 20, 2012 10:43 PM
  • I would not ask this exact question in EF forums - that would be misleading. I would just ask if it's possible to define a nested complex type (complex type which has another complex property).

    As for the above code, I don't know what's wrong. I must admit I don't even understand what you're trying to do. If you could share the code which actually creates the instance and returns it in the service operation...

    Thanks,


    Vitek Karas [MSFT]

    Friday, December 21, 2012 4:54 PM
    Moderator
  • Vitek,

    Here's the code that retrieves the data and populates the objects from the above classes:

    try
    {
    	CompanyLoaded companyLoaded = new CompanyLoaded();
    	LoadedCompany loadedCompany = new LoadedCompany();
    	ObjectContext objectContext = ((IObjectContextAdapter)companyEntities).ObjectContext;
    	// create a sql command to execute the proc
    	DbCommand dbCommand = companyEntities.Database.Connection.CreateCommand();
    	dbCommand.CommandText = "ap_Company_Load @CompanyId";
    	dbCommand.Parameters.Add(new SqlParameter("@CompanyId", companyId));
    	// execute the command
    	companyEntities.Database.Connection.Open();
    	DbDataReader dbDataReader = dbCommand.ExecuteReader();
    	// read the company from the first result set
    	loadedCompany.CompanyInfo = objectContext.Translate<ap_Company_Load_Result>(dbDataReader).ToList();
    	// move to the second result set to get addresses
    	dbDataReader.NextResult();
    	// read the addresses
    	loadedCompany.CompanyAddresses = objectContext.Translate<Company_Load_Address>(dbDataReader).ToList();
    	// move to the third result set to get phones
    	dbDataReader.NextResult();
    	// read the phones
    	loadedCompany.CompanyPhones = objectContext.Translate<Company_Load_Phone>(dbDataReader).ToList();
    	// move to the fourth result set to get eMails
    	dbDataReader.NextResult();
    	// read the eMails
    	loadedCompany.CompanyEMails = objectContext.Translate<Company_Load_EMail>(dbDataReader).ToList();
    	// add the company to list
    	companyLoaded.Add(loadedCompany);
    	return (IEnumerable<CompanyLoaded>)companyLoaded;
    }
    catch (Exception ex)
    {
    	throw new Exception("An error occurred during query execution.", ex);
    }
    finally
    {
    	// close the connection
    	companyEntities.Database.Connection.Close();
    }

    As I said in my last post, the data is all in the loadedCompany object in the companyLoaded object but when I cast it to IEnumerable, I get the InvalidCastException.

    Thanks


    TomBean



    • Edited by TomBean Friday, December 21, 2012 5:09 PM
    Friday, December 21, 2012 5:07 PM
  • Two questions:

    - Why do you have a class which derives from List<T>... in this case it makes little sense (since you're returning it as IEnumerable<T> anyway).

    - You're saying you get InvalidCastException, right... so it's not a compiler error. In that case this can be much deeper in the guts. Could you post the callstack of the exception?

    Thanks,


    Vitek Karas [MSFT]

    Friday, December 21, 2012 5:20 PM
    Moderator
  • Vitek,

    I was under the impresion that you could only return IEnumerable or IQueryable types to Silverlight from a WCF Data Service, so, I created the CompanyLoaded class to wrap the LoadedCompany class that's composed of 4 Lists.

    The messge I get in the Exception is:

    "Unable to cast object of type 'DatabaseWebApp.CompanyDataModel.CompanyLoaded' to type 'System.Collections.Generic.IEnumerable`1[DatabaseWebApp.CompanyDataModel.CompanyLoaded]'."

    and the stack trace is:

    "at DatabaseWebApp.CompanyDataService.ap_Company_Load(Int32 companyId) in c:\IDC\DatabaseWebApp\CompanyDataService.svc.cs:line 119"

    Thanks


    TomBean

    Friday, December 21, 2012 5:44 PM
  • That makes sense. The CompanyLoaded derives from List<LoadedCompany>, so it can be cast to IEnumerable<LoadedCompany>. But not to IEnumerable<CompanyLoaded>. (I'm surprised it actually compiled.)

    You can return a single instance as well, be it complex or entity. In fact that code path doesn't have the bug of not being able to read collection of complex values as a result. A single complex value can be read just fine by the client.

    Thanks,


    Vitek Karas [MSFT]

    Friday, December 21, 2012 6:00 PM
    Moderator
  • Vitek,

    I tried changing my return to: 

        return (IEnumerable<LoadedCompany>)companyLoaded;

    When I tried to update my service reference, I got the error below:

    The server encountered an error processing the request. The exception message is 'Unable to load metadata for return type 'System.Collections.Generic.IEnumerable`1[DatabaseWebApp.CompanyDataModel.LoadedCompany]' of method 'System.Collections.Generic.IEnumerable`1[DatabaseWebApp.CompanyDataModel.LoadedCompany] ap_Company_Load(Int32)'.'. See server logs for more details. The exception stack trace is:

    at System.Data.Services.Providers.BaseServiceProvider.AddServiceOperation(MethodInfo method, String protocolMethod) at System.Data.Services.Providers.BaseServiceProvider.AddOperationsFromType(Type type) at System.Data.Services.Providers.BaseServiceProvider.LoadMetadata() at System.Data.Services.DataService`1.CreateMetadataAndQueryProviders(IDataServiceMetadataProvider& metadataProviderInstance, IDataServiceQueryProvider& queryProviderInstance, BaseServiceProvider& builtInProvider, Object& dataSourceInstance) at System.Data.Services.DataService`1.CreateProvider() at System.Data.Services.DataService`1.HandleRequest() at System.Data.Services.DataService`1.ProcessRequestForMessage(Stream messageBody) at SyncInvokeProcessRequestForMessage(Object , Object[] , Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage41(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage3(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage2(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage1(MessageRpc& rpc) at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

    And when I tried just returning the companyLoaded object, I still couldn't update my service reference and got the following error:

    The server encountered an error processing the request. The exception message is 'Unable to load metadata for return type 'DatabaseWebApp.CompanyDataModel.CompanyLoaded' of method 'DatabaseWebApp.CompanyDataModel.CompanyLoaded ap_Company_Load(Int32)'.'. See server logs for more details. The exception stack trace is:

    at System.Data.Services.Providers.BaseServiceProvider.AddServiceOperation(MethodInfo method, String protocolMethod) at System.Data.Services.Providers.BaseServiceProvider.AddOperationsFromType(Type type) at System.Data.Services.Providers.BaseServiceProvider.LoadMetadata() at System.Data.Services.DataService`1.CreateMetadataAndQueryProviders(IDataServiceMetadataProvider& metadataProviderInstance, IDataServiceQueryProvider& queryProviderInstance, BaseServiceProvider& builtInProvider, Object& dataSourceInstance) at System.Data.Services.DataService`1.CreateProvider() at System.Data.Services.DataService`1.HandleRequest() at System.Data.Services.DataService`1.ProcessRequestForMessage(Stream messageBody) at SyncInvokeProcessRequestForMessage(Object , Object[] , Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage41(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage3(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage2(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage1(MessageRpc& rpc) at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

    Thanks


    TomBean



    • Edited by TomBean Friday, December 21, 2012 6:35 PM
    Friday, December 21, 2012 6:32 PM
  • I assume this is still using EF provider. In that case, just defining a type in C# won't do it. As noted several times before.

    In EF provider case, all types must be defined in the EF model. Otherwise they're not available to the OData service (this is currently by design). SO service operation can only return either a type from the EF model or a collection of such type. (Or a primitive type).

    Thanks,


    Vitek Karas [MSFT]

    Friday, December 21, 2012 6:39 PM
    Moderator
  • Vitek,

    In order to quit using the EF provider I have done the following:

    1)  Removed all references to the Function Import and the ComplexTypes from the .edmx file.
    2)  Created a new model similar to your OData code mapping nested complex types with this code:

    using System;
    using System.Collection.Generic;
    using System.Data.Entity;
    using System.Linq;

    namespace DatabaseWebApp.CompanyResultSets { public class CompanyResults : DbContext { public DbSet<LoadedCompany> LoadedCompanys { get; set; } } public class LoadedCompany { public int CompanyId { get; set; } public List<Company_Load> CompanyInfo { get; set; } public List<Company_Load_Address> CompanyAddresses { get; set; } public List<Company_Load_Phone> CompanyPhones { get; set; } public List<Company_Load_EMail> CompanyEMails { get; set; } } }

    Each of the classes used to create the Lists are defined like:

    namespace DatabaseWebApp.CompanyResultSets
    {
        using System;
        using System.ComponentModel.DataAnnotations;
        
        public partial class Company_Load
        {
            public int CompanyId { get; set; }
            public string Name { get; set; }
            ...
        }
    }

    The Address, Phone and EMail classes are defined similarly but instead of CompanyId, they have AddressId, PhoneId, and EMailId fields.

    When I opened my Web App in a browser and try to access my service, I got the following error:

    "The server encountered an error processing the request. The exception message is 'One or more validation errors were detected during model generation: \tSystem.Data.Entity.Edm.EdmEntityType: : EntityType 'LoadedCompany' has no key defined. Define the key for this EntityType. \tSystem.Data.Entity.Edm.EdmEntitySet: EntityType: EntitySet 'LoadedCompanys' is based on type 'LoadedCompany' that has no keys defined. '."

    In your sample code, you didn't have a [Key] attribute in your class definitions but I decided to add it to my classes to see if it solved the problem, so I annotated each of my Id fields with [Key].

    After I added [Key], I got the following error when I tried to access my service:

    "The server encountered an error processing the request. The exception message is 'The model backing the 'CompanyResults' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).'."

    Since this is the first definition of the context, I don't know what this means or what to do to solve it.

    Since your example and other OData examples don't have the [Key] attribute in the classes, I suspect I've done something incorrectly.  The only place I've seen the [Key] attribute used in is the Code First examples and I was trying to create an OData model.  None of my using statements have any references to OData and it seems like they should.

    What have I done wrong?

    Thanks


    TomBean

    Wednesday, December 26, 2012 4:37 PM
  • If you want to NOT use the EF provider, then your context class (the class you pass to DataService<T> as the T) must not derive from DbContext. Otherwise it's treated as EF (since it is EF) and used like that.

    For WCF DS to recognize a certain property as a key it either has to be called "ID", or "ClassNameID" or you must use the DataServiceKey attribute on the class. (Note that the casing is important). This algorithm is only used if the reflection provider is used. If the EF provider is used, it will use the EF semantics (which are different).

    Also note, that building a reflection based context over otherwise EF backed entity sets currently has some limitations. Certain types of queries might not work. Typically $expand and $select queries will run into problems. This is due to the incompatibility of the LINQ providers used by EF and Linq to Objects. WCF DS generates different LINQ expressions for the two providers and not all work with the other one. This is a known limitation. The only workaround is to write an expression visitor and wrap the IQueryable returned from your entity sets. Then in the visitor detect these "Wrong" patterns and fix them up for the provider being used. This is not trivial work.

    Thanks,


    Vitek Karas [MSFT]

    Thursday, December 27, 2012 9:57 AM
    Moderator
  • Vitek,

    I changed my CompanyResults class so it is no longer derived from DbContext and added the DataServiceKey attribute to each of my Id keys.

    I read the documentation of Reflection Providers and didn't see any information about building a context or about using a Reflection Provider on anything but an in-memory provider.

    How do I create the Reflection Provider context to allow me to create a SQLCommand to call my stored procedure?

    The only additional information mentioned is implementing a LINQ to SQL data source.  Is that what I need to do?

    Thanks


    TomBean

    Thursday, December 27, 2012 6:55 PM
  • Vitek,

    Since the CompanyResults class is not derived from DbContext, I'm getting the following error:

    "Unable to cast object of type 'DatabaseWebApp.CompanyResultSets.CompanyResults' to type 'System.Data.Entity.Infrastructure.IObjectContextAdapter'".

    What do I need to do?

    Thanks


    TomBean

    Thursday, December 27, 2012 8:21 PM
  • Vitek,

    I am getting the cast error because I'm actually casting my CompanyResults object to IObjectContextAdapter so I could get an ObjectContext to call the Translate method to load the data from each result set into the appropriate list.  Guess I'll have to find another way.

    Thanks


    TomBean

    Thursday, December 27, 2012 9:14 PM
  • Vitek,

    I got my data loaded into each of the lists, CompanyInfo, CompanyAddresses, CompanyPhones and CompanyEMails.

    I returned LoadedCompany back to my Silverlight app.  I was still using BeginExecute() and EndExecute() to get the result sets back.  The version of EndExecute() I am using is the one that expects an IEnumerable result and the LoadedCompany isn't IEnumerable.

    I'm trying to find information about how I can consume the OData results in my Silverlight app.

    Do you have any examples or documentation you would recommend?

    Thanks


    TomBean

    Friday, December 28, 2012 11:36 PM
  • You can verify the server returns what you expect using some kind of HTTP tracing tool (for example Fiddler).

    In the client app, even though the result is a singleton the API only returns IEnumerable. So if there's just one instance it will return an enumerable with one item in it.

    Thanks,


    Vitek Karas [MSFT]

    Saturday, December 29, 2012 9:04 AM
    Moderator
  • Vitek,

    I'm calling my service as follows:

    dataServiceContext.BeginExecute<LoadedCompany>(uri, OnLoadCompanyCompleted, dataServiceContext, ODataConstants.MethodGet, false);
    The method in the service is hit and I instantiate a LoadedCompany object, defined as:
    [DataServiceKey("CompanyId")]
    public partial class LoadedCompany
    {
    	public int CompanyId { get; set; }
    	public List<Company_Load_Result> CompanyInfo { get; set; }
    	public List<CompanyAddress> CompanyAddresses { get; set; }
    	public List<CompanyPhone> CompanyPhones { get; set; }
    	public List<CompanyEMail> CompanyEMails { get; set; }
    	public LoadedCompany(int companyId)
    	{
    		this.CompanyId = companyId;
    	}
    }
    In order to return an IEnumerable, I created another class CompanyLoaded, defined as:
    public class CompanyLoaded : List<LoadedCompany>
    {
    }
    I populated the LoadedCompany object with the data returned from the call to my stored procedure

    and added the LoadedCompany object to the CompanyLoaded list, cast it to IEnumerable<LoadedCompany> and returned it to the Silverlight app.

    In the OnLoadCompanyCompleted callback, I call:

    DataServiceContext context = (DataServiceContext)result.AsyncState;
    IEnumerable<LoadedCompany> companys = context.EndExecute<LoadedCompany>(result);

    The returned IEnumerable<LoadedCompany> list contains one LoadedCompany object and its CompanyId is set, however, all the lists, CompanyInfo, CompanyAddresses, CompanyPhones and CompanyEMails, have zero items in them even though they were populated when the service returned.

    Did the data get removed from the lists in the return or do need to do something else to retrieve the data?

    Thanks



    TomBean

    Sunday, December 30, 2012 1:36 AM
  • Is CompanyAddress (for example) an entity or a complex type? If it's an entity (it has DataServiceKey for example), then this is an expected behavior. Client needs to ask for expansion of navigation properties explicitly. Add $expand=CompanyAddresses to your query URL (and make the service operation return IQueryable, otherwise it won't work).

    If CompanyAddress is a complex type (it doesn't have a key), then this is not an expected behavior, the addresses should be returned with the result always.

    Thanks,


    Vitek Karas [MSFT]

    Sunday, December 30, 2012 10:01 AM
    Moderator
  • Vitek,

    Here's the definition for CompanyAddress:

    [DataServiceKey("AddressId")] public partial class CompanyAddress { public int AddressId { get; set; } public System.DateTime CreateDate { get; set; } public System.DateTime ChangeDate { get; set; } public Nullable<System.DateTime> DeleteDate { get; set; } public string User { get; set; } public int VendorId { get; set; } public string AddressType { get; set; } public string POBox { get; set; } public string AddressLine1 { get; set; } public string AddressLine2 { get; set; } public string City { get; set; } public string State { get; set; } public string PostalCode { get; set; } public string CountryCode { get; set; } public string ISO { get; set; } public bool Business { get; set; } public bool Remitance { get; set; } }

    The phone and eMail classes are similarly defined.

    I was returning IEnumerable.  When I tried to change to IQueryable, I got the following error:

    "Unable to cast object of type 'DatabaseWebApp.CompanyResultSets.CompanyLoaded' to type 'System.Linq.IQueryable`1[DatabaseWebApp.CompanyResultSets.LoadedCompany]'."

    Since the cast to IEnumerable works, why doesn't the cast to IQueryable?

    Thanks


    TomBean


    • Edited by TomBean Sunday, December 30, 2012 2:12 PM
    Sunday, December 30, 2012 2:11 PM
  • Vitek,

    I also tried changing the return to the LoadedCompany object and got this error:  "Unable to cast object of type 'DatabaseWebApp.CompanyResultSets.LoadedCompany' to type 'System.Linq.IQueryable`1[DatabaseWebApp.CompanyResultSets.LoadedCompany]'."

    Thanks


    TomBean

    Sunday, December 30, 2012 2:45 PM
  • Vitek,

    I changed back to returning an IEnumerable and added $expand=CompanyAddresses to the Uri so it is:

    http://localhost:49924/CompanyDataService.svc/ap_Company_Load?CompanyId=100007$expand=CompanyAddresses

    And got the following error in the OnLoadCompanyComplete callback.

    <?xml version="1.0" encoding="utf-8" standalone="yes"?><error xmlns="<message">http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"><code></code><message xml:lang="en-US">Bad Request - Error in query syntax.</message></error>

    Thanks


    TomBean

    Sunday, December 30, 2012 4:27 PM
  • As noted above $expand will only work with IQueryable (so the latest error is expected).

    The cast is also I guess expected. You can't cast List<T> to IQueryable<T>, but you can call .AsQueryable() which will return IQueryable<T> for that list. That should work.

    Thanks,


    Vitek Karas [MSFT]

    • Marked as answer by TomBean Monday, December 31, 2012 4:44 PM
    Sunday, December 30, 2012 7:26 PM
    Moderator
  • Vitek,

    I appended AsQueryable() to the return, i.e. "return companyLoaded.AsQueryable();", but still got the "Bad Request - Error in query syntax" error.

    I found another post of yours showing an example of using $expand and in it the parameter was at the end of the Uri, so,  I changed the Uri to:

    http://localhost:49924/CompanyDataService.svc/ap_Company_Load?$expand=CompanyInfo,CompanyAddresses,CompanyPhones,CompanyEMails&CompanyId=100007.

    That Uri worked and all my data was returned to my Silverlight app.

    Thanks


    TomBean

    Monday, December 31, 2012 4:44 PM
  • Sorry - I didn't notice this problem in your URL above. You're missing the ampersand &. Each query parameter must be separated by the &. In addition, the built-in parameters like $expand include the $ sign.

    So for example:

    /ServiceOperation?param1=1&$expand=Foo

    or

    /ServiceOperation?$expand=Foo&param1=1

    Both will work exactly the same (the order of the parameters in the query string is not significant).

    Thanks,


    Vitek Karas [MSFT]

    Tuesday, January 1, 2013 4:13 PM
    Moderator
  • Vitek,

    Missing one character is very undertandable especially considering how many questions you have answered.

    I really appreciate your help and your patience.

    It's too bad there isn't more extensive documentation and complete, simple examples on OData.

    I would be willing to send you the essential parts of my/your project if you think you could turn it into an example that might help you spend less time answering questions.

    Thanks


    TomBean

    Wednesday, January 2, 2013 1:18 AM