linq to sql - Design for multi-tenant app using SOA, UoW, Repository, DataContext & multiple databases -

let me start apologizing length of post wanted provide detail possible increase chance of answer. in advance.

i’m adding new features existing application integrates data several databases. in short, allows clients and/or accountants access , update financial information locations. application has 3 tiers web client (i’m looking replace silverlight client soon), service layer running on app server , database tier.

when took ownership, application, despite having 3-tiers, simplistic. using ado.net , web service pass-through dynamic (string) sql queries on 1 of databases. first task “clean-up” existing code. there nothing object-oriented app , flat. i’ve begun refactoring application leverage linq-to-sql, implemented repository pattern make business logic testable (not mention more maintainable evolve data access technology ado.net l2s and, hopefully, on entity framework) , reworked code use features of .net.

for part, web app piece of cake every page simple query on table or view in database (via web service). on app server side, ended datacontext each database , repository each entity (or aggregate root using ddd terms). use constructor injection (castle windsor) set proper repository instance service class , likewise datacontext used repository.

this , except have add edit capability application requires logic spanning 4 databases , i’m @ loss right approach. critical flow , architecture of app consistent , maintainable junior-grade developers. (aside me, team vb6 devs think programming .net because using vs , bcl – know little patterns, practices, unit testing, mocking, etc, etc, etc) 

let me walk through needed make edit view work:

1. when page first loaded, generate list of “editable” fiscal periods.
   1. this list based on set of rules contained in sql server database (call “metadata” db). each row in table identifies fiscal period name along timeframe may edited.
   2. these rules used query table in db2 financial database maps period name , year actual dates. query return entries end date in past , close date (end date plus edit length above) after today.
2. the page default selected period recent. when period selected, fire request populate list of locations current user may edit during selected period.
   1. first have interrogate user’s security information “security” database , determine if in accountants role. if so, have query metadata database retrieve list of clients current user. if not, use clientid of current user.
   2. next query db2 financial database obtain list of locations, filtering based on selected period , list of clientids user may access. filter based on status return locations active during period.
3. as period, page default selected location first 1 in list. when location selected, make request actual financial data location during selected period.
   1. this request made “staging” database , pretty straight-forward.
4. all of repeats if user selects different period or location.

so, let’s take #2 , walk through how should implementing in service application using unit of work , repository patterns and… oh, 1 problem – there no linq-to-db2 , company won’t make jump ef yet left using ado.net when working database. don’t let hold i’d solution data access tech-agnostic possible because change minds day. in fact, shift paradigms , go nhibernate know @ point. i’m more concerned getting service façade datacontext , not how datacontext (or objectcontext) implemented.

can walk me through implementation of calling web service method current user’s credentials , selected fiscal period, performing steps outlined in #2 above , returning appropriate list of locations?

whew… lot take in. appreciate sorting out.