How to correlate multiple databases into one unified oData Feed? RRS feed

  • Question

  • How do I create an oData feed that from multiple databases. Perhaps the question is, "Can I create an Entity Data Model that includes tables from multiple databases, on multiple servers?"

    My goal is to have a single .svc feeding all of my data, regardless of underlying database, in one feed, employing friendly names, that I can build BISM views of in Sharepoint.


    -------------- Bill Ross
    • Moved by Adi Unnithan Sunday, October 9, 2011 12:12 AM This question can find the best answer in the WCF Data Services forum. (From:SQL Server Developer Tools - Codename “Juneau”)
    Friday, October 7, 2011 10:15 PM


All replies

  • Thank you for your response. Let me clarify the scenario...

    I want to do something like a "select * from server1\instance1.dbo.Table1 inner join server2\instance2.dbo.Table2 on ..." and am planning to use the an Entity Model to create my oData feed. How would I implement this? Would I create one .edmx from each server, one feed.svc from each server and then have to do the join when I consume? Do I merge at the .csdl via the .sdl? Or do I do it in the Entity Model creation step?

    Or would it just be smarter to put the data all in the same database?!


    -------------- Bill Ross
    Monday, October 10, 2011 2:40 PM
  • Okay, I'll pursue this, then, via Entity Framework.


    -------------- Bill Ross
    Monday, October 10, 2011 3:39 PM
  • If you're hitting issues with that approach, another thing to try is to use the EntitySet resolver; this allows you to do the "one feed from each server and do the join":


    Windows Live uses this approach to combine different collections, e.g. from contacts and photos, into a single OData feed:




    Adi Unnithan | Software Design Engineer | SQL Server Developer Tools | Please mark the post as answered if it sufficiently answers your question
    Monday, October 10, 2011 11:36 PM
  • Adi, that is, to my mind, the elegant solution I was looking for. Thanks!
    -------------- Bill Ross
    Wednesday, October 12, 2011 2:32 PM