Corey Roth and Friends Blogs

Group site for developer blogs dealing with (usually) Ionic, .NET, SharePoint, Office 365, Mobile Development, and other Microsoft products, as well as some discussion of general programming related concepts.

Not Necessarily Dot Net

Linq to SQL with multiple databases

I think I've mentioned that I'm working on a project that dips its fingers into databases all over the place. Its main goal is to eliminate all the systems we have around that are also doing this.  If it has to be done at all (and, really, it does), it should only happen once.

One of the biggest issues I've run across while doing this is that Linq to SQL just does not play nice with multiple data contexts.

If you're working with 3 different databases, that's 3 different connections.  Linq to SQL will not let you do any joining etc.

You can't pull the results of one query into memory and then join against that (well, you can in some really isolated circumstances, but it's mostly a crap shoot).

The only Linq to SQL solution I've found so far is to run a query on the first database, do a foreach over it, and query the second database multiple times.  This, of course, is usually brutally slow.

I worked out a compromise with our DBA.  He added something he calls variously "data links" or "linked servers" or "server mappings" to the central database I need.  They now show up as read-only tables there.

Performance is still spotty, and I'm not sure it's even vaguely something that could be considered a best practice.  I suspect it's some sort of gaping security risk.  But apparently that's the way they've been doing business for years: "Need data from our database?  Here, let me set up the views we'll let you see in your database."

If I cared more about dba stuff, I'd look up what was actually done, give technical details and analysis, list pros and cons, etc.

But, if your organization's anything like the one where I work, where security's a combination of swiss cheese and flaming hoops, you're trying to work with multiple teams who all have their own opinions and philosophies (and no one's willing to concede that they're all pretty much equally valid), and you're supporting a wide range of technologies, sometimes just finding a solution that works is enough. 



No Comments

Leave a Comment

Powered by Community Server (Non-Commercial Edition), by Telligent Systems