LINQ with multiple SQL databases (data contexts) and transactions

[Robson Felix]

Hi guys,

I plan to start using LINQ to SQL real soon and would like to know if some
of you have had faced the following situations:

a) multiple data contexts (for example, multiple SQL servers or multiple
databases within the same
server)
- can i perform JOINs using these objects and perhaps a 3rd one, like a
class or entity? Including LEFT and INNER type of joins?
- aggregation?
- updates?
b) when using multiple data contexts, can I have a unique transaction
without using DTC? For instance, if I have a data context that is on server
A, database A1 and data context on server A, database B1, does it imply that
having different data connections, although in the same server, will
escalate it to DTC? How is it done? TransactionScope? DbTransaction?

Any thoughts are welcome.

Robson

 

[Marc Gravell]

These are things that should be pertty easy for you to test yourself,
but my *expectation* would be as below; I also wouldn't *expect*
ADO.NET Entity Framework to change much of this:

a) multiple data contexts (for example, multiple SQL servers or multiple
databases within the same
server)
    - can i perform JOINs using these objects and perhaps a 3rd one, like a
class or entity? Including LEFT and INNER type of joins?
- aggregation?

In terms of LINQ-to-SQL, no I wouldn't expect that to work. However,
if you had some (filtered) data locally (list/array etc) you should be
able to use LINQ-to-objects to join them locally. Either they are part
of the same model or they aren't ;-p

    - updates?

I would expect each data-context to take care of its own entities and
nothing more.

b) when using multiple data contexts, can I have a unique transaction
without using DTC? For instance, if I have a data context that is on server
A, database A1 and data context on server A, database B1, does it imply that
having different data connections, although in the same server, will
escalate it to DTC? How is it done? TransactionScope? DbTransaction?

I would anticipate that to do this you would have to use your own
TransactionScope to wrap the two apply steps. TransactionScope uses
DTC as soon as there are two connection strings (IIRC); so yes I would
expect it to use DTC.

Any thoughts are welcome.

My main thought: try it; let us know what you find.

Marc

 

[Frans Bouma [C# MVP]]

Hi guys,

I plan to start using LINQ to SQL real soon and would like to know if
some of you have had faced the following situations:

a) multiple data contexts (for example, multiple SQL servers or
multiple databases within the same server)
- can i perform JOINs using these objects and perhaps a 3rd one,
like a class or entity? Including LEFT and INNER type of joins?

It's about meta-data that's the problem: the meta-data of the mappings
is stored inside one context. So if you have 2 or 3, each representing
a different database, with a different schema, no, you're not going to
be able to merge them at runtime.

You need an o/r mapper who is able to map entities to tables/views in
multiple schemas/catalogs. If the schema is the same, within different
catalogs (e.g. you have 1 catalog per client of yours), some o/r
mappers allow at runtime overwriting of hte mapping information so you
can use 1 set of meta-data of the mappings and target multiple
catalogs/schemas. Ours is one of these (see signature).

We'll have FULL Linq support ready at the end of the month (in beta)

- aggregation?
- updates?

see above.

b) when using multiple data contexts, can I have a unique transaction
without using DTC? For instance, if I have a data context that is on
server A, database A1 and data context on server A, database B1, does
it imply that having different data connections, although in the same
server, will escalate it to DTC? How is it done? TransactionScope?
DbTransaction?

In Linq to Sql it's not possible to share transactions/objects among
datacontexts.

Our linq support will allow that though. It's tricky however, the
execution of a query is deferred, so it's not said when the query is
executed. This makes it particularly error prone: say you start a
transaction, and inside that transaction you want to fetch data from a
db: as the query isn't executed right away (that is: if it returns a
set. If it returns a single value it IS executed right away in Linq...
don't get me started on that stupid design flaw in Linq) but the
transaction holding object is INSIDE the query! So if you pass the
query object around, you'll violate the ACID principle of a
transaction: it can be the query object is stored somewhere (developers
do weird things sometimes) and the transaction is kept alive with it.

So these kind of features should be used with care. It's however a bit
odd that MS didn't offer these kind of 'advanced' features for people
who need them. Oh well...

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------