locked
Is it Possible to Query Multiple Databases with WCF Data Services?

    Question

  • I have data being inserted into multiple databases with the same schema. The multiple databases exist for performance reasons.

    I want to create a WCF service that a client can use to query the databases. However from the client's point of view, there is only 1 database. By this I mean when a client performs a query, it should query all databases and return the combined results.

    I also need to provide the flexibility for the client to define its own queries. Therefore I am looking into WCF Data Services, which provides the very nice functionality for client specified queries.

    So far, it seems that a DataService can only make a query to a single database. I found no override that would allow me to dispatch queries to multiple databases.

    Does anyone know if it is possible for a WCF Data Service to query against multiple databases with the same schema?
    Wednesday, January 12, 2011 8:33 AM

Answers

  • Hi,

    It is not easy, but it's possible. You can implement a custom provider (Alex has a great series about that here: http://blogs.msdn.com/b/alexj/archive/2010/01/07/data-service-providers-getting-started.aspx) in which you can definitely take the query in the form of IQueryable (expression tree) and run it on multiple underlying providers and "combine" the results. It's a lot of work though and combining the results to get a correct behavior is also not easy (expecially if sorting is involved).

    Thanks,


    Vitek Karas [MSFT]
    • Marked as answer by Mas 2112 Wednesday, January 12, 2011 6:22 PM
    Wednesday, January 12, 2011 4:29 PM
    Moderator

All replies

  • Hi,

    It is not easy, but it's possible. You can implement a custom provider (Alex has a great series about that here: http://blogs.msdn.com/b/alexj/archive/2010/01/07/data-service-providers-getting-started.aspx) in which you can definitely take the query in the form of IQueryable (expression tree) and run it on multiple underlying providers and "combine" the results. It's a lot of work though and combining the results to get a correct behavior is also not easy (expecially if sorting is involved).

    Thanks,


    Vitek Karas [MSFT]
    • Marked as answer by Mas 2112 Wednesday, January 12, 2011 6:22 PM
    Wednesday, January 12, 2011 4:29 PM
    Moderator
  • It's a lot of work though and combining the results to get a correct behavior is also not easy (expecially if sorting is involved).
    I started thinking about sorting + paging after combining results from multiple databases, and it is far too complicated. I will look in another direction to solve my problem. Thank you.
    Wednesday, January 12, 2011 6:22 PM