locked
How to implement WCF Data service to work with heterogeneous data source ? RRS feed

  • Question

  • Hi all, 

    I have a project on WCF. My plan is providing a shared database (SQL Db) to various clients throught OData.

    The problem is this shared database can be changed dynamically at runtime: the users can add new tables, with different schema .... In this case, the data source might be very heterogeneous (in term of tables' schemas).

    So, can we implement a UNIFIED WCF DS for a shared pool of data source with heterogeneous schema ?

    ps: I also read this serie: http://blogs.msdn.com/b/alexj/archive/2010/01/04/creating-a-data-service-provider-part-1-intro.aspx but the problem is I'm new to wcf and I really need the code example of this article. I know that this example code was available at http://www.odata.org/developers/odata-sdk but now it does not.

    I would grateful if someone who still have it would shared this example code for me. (OData Provider Toolkit code)

    Thank you in advance.

    Sunday, March 18, 2012 3:43 AM

Answers

  • No, it doesn't mean you have to fetch the data before hand. All it means is that I don't know of a publicly available implementation of IQueryable which can generade SQL based on metadata which is not defined in terms of CLR classes, but rather some different data structure. I know it's possible, since I know of several projects which do this internally. Unfortunately such implementations are inherently tied to the actual project, since they use the project specific metadata as well as make assumptions about the underlying SQL database layout.

    There are some public sources for this, for example http://iqtoolkit.codeplex.com/, but I didn't play with it myself to be able to tell if it is easy to reause in your case.

    Thanks,


    Vitek Karas [MSFT]

    Thursday, April 12, 2012 7:54 AM
    Moderator

All replies

  • Hi,

    The download links on odata.org have been fixed, you should be able to download the samples now.

    Thanks,


    Vitek Karas [MSFT]

    Sunday, March 18, 2012 2:58 PM
    Moderator
  • I've downloaded the code. Thank you for responding so quickly on the weekend.

    But, I wonder if there was a more convenient approach for this matter ?

    Monday, March 19, 2012 2:12 AM
  • Try the following toolkit -

    http://wcfdstoolkit.codeplex.com/

    This allows you a layer between OData and Your data source and provides projects to select all, select single etc etc...

    so just implement the methods and within the methods you can connect to you datasource in a way that is upported for that data source.

    Go through the toolkit and you should be able to project any data source as an OData feed using this.

    regards
    Lohith
    @kashyapa


    regards kashyapa

    Thursday, March 29, 2012 5:46 PM
  • Thank you. I will give it a try.
    Friday, March 30, 2012 2:32 AM
  • Try the following toolkit -

    http://wcfdstoolkit.codeplex.com/

    This allows you a layer between OData and Your data source and provides projects to select all, select single etc etc...

    so just implement the methods and within the methods you can connect to you datasource in a way that is upported for that data source.

    Go through the toolkit and you should be able to project any data source as an OData feed using this.

    regards
    Lohith
    @kashyapa


    regards kashyapa

    I've read this article.

    But its functions are not work for my situation:

    In my case, user can add new table, and this new table might not follow a "pre defined" data schema. So, I dont have the CRL class (entity) for all the table which I want to expose with WCF DS. Then traditional data context with CRL class (like the toolkit) cannot work here.

    I'm trying the un-typed custom data service provider approach. I hope it can work.

    Friday, March 30, 2012 8:33 AM
  • I think WCF Data Service also uses this approach to render projection but that comes on a cost , it treat all properties as string type and as result some queries and custom implementation do not work. Thought to share , have a look on this CurrentDataSource.ObjectMaterialized event problem 
    Wednesday, April 4, 2012 8:18 PM
  • Note that projections don't cause all properties to be treated as strings. All they do is to not instantiate the original type of the entity instance (the type used to represent the entity), instead they instantiate helper class called ProjectedWrapper which stores only the projected properties.

    For un-typed provider this might be even easier to implement if the entity is represented as a generic enough storage. In which case the projected wrapper doesn't differ that much from the generic storage used for no-projection queries.

    Thanks,


    Vitek Karas [MSFT]

    Friday, April 6, 2012 2:33 AM
    Moderator
  • I implemented a *dump* version of Un-typed Data Provider for SQL Database based on this article:http://msdn.microsoft.com/en-us/data/gg191846

    My approach is:

    _ Generate a dynamic metadata for the service on every request:

    • Information for all the resourceSet is storaged in a separate database.
    • Querying and generating the service's metadata when user request this info. (through CreateMetadata() method

    _ Support querying Un-Typed Data (read-only scenarior):

    • Inside the GetQueryRootForResourceSet: I change the GetResourceSetEntities to return a IList<MyResourceType> in order to create a approriate IQueryable for the ResourceSet
    • First, I query the SQL database looking for a table which match the ResourceSet name in the user wcf ds's query.
    • Then, I store the SQL results inside a IList<MyResourceType> and return the result.

    _ Everythings work fine:

    • When I add new table (resourceSet) to the SQL database, the metadata now can be updated dynamically with the information for that new resourceSet.
    • I now can query a SQL database with dynamic table and dynamic schema on each table without having CRL or entity classes.

    _ But, there are few concerns:

    •  In my first solution, user can see "ALL resourceSets", include one that not belong to or allowed for them to see.
    •  I have to create an IList<MyResourceType> instance and store it in the memory. It maybe cause memory overflow in real-world scenarior with thoursand of queries can be made.

    _ So, I have a 2 questions:

    1. Can we pass the user information (ie: User's indentity) into the GetService() method?  If yes, we can generate the metadata dynamically based on user's identity.s
    2. Is the WCF data service call the methods GetQueryRootForResourceSet() on every queries which consumer send to the services ? Or it just call the first time and maintain this data in a period of time ?
    3. If the answer of the previous question is yes. So, Do we have an event or somehow know WHEN does WCF DS sucessfully return query' results for consumer and we can safely "dispose" the QueryRoot (IQueryable instance) ? If we can know when, we will free the IList<MyResourceType> after each request, it may be have trace off in performance but can avoid overflow the memory.
    Wednesday, April 11, 2012 3:27 AM
  • Hi,

    Passing context to the various provider methods is currently usually done 'out-of-band'. For example when your service is hosted on ASP.NET (the typical one) there are static instances you can access to get to the request, the current user and so on. These should work inside the providers as well. Note though that you should only use these during the initial "request processing" phase (creation of the providers and creation of the query). Later on during the "response writing" phase some of the security context is lost (WCF internal design and so on), so the providers in use should cache the information they need in them.

    The GetQueryRootForResourceSet is called once for each request (actually for certain types of requests it may be called more than once). The thing is that your current implementation is not the typical way to implement this method. The method should return IQueryable which is just a description of the query root, no real data itself. Only later on when the entire query is constructed through the IQueryable and when the IQueryable is executed (GetEnumerator() is called on it), should you go and fetch the actual data. By that time the query will have a detailed description of exactly which data is needed to fulfill the request. The way you seem to implement it now is to load all of the data regardless of the request into memory. That will only work for small data sets and will stress the service for resources.

    The lifetime management of resource should be done via two ways:

    - Data source lifetime management. The instance returned from DataService.CreateDataSource is disposed after the request is processed. So just implement the IDisposable on it. Also each enumerator will be disposed, so the enumerator returned from IQueryable.GetEnumerator() (which is the thing doing the actual data processing) can implement IDisposable and get notified when it's not needed anymore.

    - Processing pipeline events (DataService.ProcessingPipeline), you can register handlers to these and get called before and after each request and so on.

    Thanks


    Vitek Karas [MSFT]

    Wednesday, April 11, 2012 1:15 PM
    Moderator
  • Hi, thank you so much for the detail answer.

    I wonder is there a way to get IQueryable for a SQL'datatable without fetching all of its data ?

    Wednesday, April 11, 2012 1:26 PM
  • The static way (generated classes for the tables) is LINQ to SQL, or EF.

    I'm not aware of dynamic provider like that though. That's the tricky part in implementing the custom provider - getting the IQueryable implemented.

    Thanks,


    Vitek Karas [MSFT]

    Wednesday, April 11, 2012 5:04 PM
    Moderator
  • Does it mean if we want to generate IQueryable in such a dynamic way, we must have all the data before-hand ?

    Like you said, we shouldn't go and fetch the atual data before the GetEnumerator of QueryExecute really runs. But inorder to have the IQueryable for a DynamicData Source to build expression and run it, we must "know" or fetch that data. 

    So, may we consider "double fetch data problem" is the inherently disadvantage of a Un-typed custom data service provider ?

    Wednesday, April 11, 2012 5:21 PM
  • No, it doesn't mean you have to fetch the data before hand. All it means is that I don't know of a publicly available implementation of IQueryable which can generade SQL based on metadata which is not defined in terms of CLR classes, but rather some different data structure. I know it's possible, since I know of several projects which do this internally. Unfortunately such implementations are inherently tied to the actual project, since they use the project specific metadata as well as make assumptions about the underlying SQL database layout.

    There are some public sources for this, for example http://iqtoolkit.codeplex.com/, but I didn't play with it myself to be able to tell if it is easy to reause in your case.

    Thanks,


    Vitek Karas [MSFT]

    Thursday, April 12, 2012 7:54 AM
    Moderator
  • I will examine the toolkit you provided. 

    Thank you so much for all the answers.


    Thursday, April 12, 2012 8:13 AM