A
aspdotnet
It is very much possible to perform cross database join using LINQ-to-SQL
Refer to the below link, good example of how to perform cross database join operation
http://aspdotnethacker.blogspot.com/2010/06/hello-folks-today-i-will-tell-you-how.html
Damiano Fusco wrote:
Multiple Database
11-Aug-09
You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.
Previous Posts In This Thread:
LINQ with multiple SQL databases (data contexts) and transactions
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 sam
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
Re: LINQ with multiple SQL databases (data contexts) and transactions
Robson Felix wrote
It's about meta-data that's the problem: the meta-data of the mapping
is stored inside one context. So if you have 2 or 3, each representin
a different database, with a different schema, no, you're not going t
be able to merge them at runtime
You need an o/r mapper who is able to map entities to tables/views i
multiple schemas/catalogs. If the schema is the same, within differen
catalogs (e.g. you have 1 catalog per client of yours), some o/
mappers allow at runtime overwriting of hte mapping information so yo
can use 1 set of meta-data of the mappings and target multipl
catalogs/schemas. Ours is one of these (see signature)
We'll have FULL Linq support ready at the end of the month (in beta
see above
In Linq to Sql it's not possible to share transactions/objects amon
datacontexts
Our linq support will allow that though. It's tricky however, th
execution of a query is deferred, so it's not said when the query i
executed. This makes it particularly error prone: say you start
transaction, and inside that transaction you want to fetch data from
db: as the query isn't executed right away (that is: if it returns
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 th
transaction holding object is INSIDE the query! So if you pass th
query object around, you'll violate the ACID principle of
transaction: it can be the query object is stored somewhere (developer
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 bi
odd that MS didn't offer these kind of 'advanced' features for peopl
who need them. Oh well... :
F
--
-----------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NE
LLBLGen Pro website: http://www.llblgen.co
My .NET blog: http://weblogs.asp.net/fboum
Microsoft MVP (C#)
------------------------------------------------------------------------
These are things that should be pertty easy for you to test yourself,but my
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
ike
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 b
able to use LINQ-to-objects to join them locally. Either they are part
of the same model or they aren't ;-p
I would expect each data-context to take care of its own entities and
nothing more.
r
at
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.
My main thought: try it; let us know what you find.
Marc
Multiple Database
You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.
Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Binding Beyond the Limitation of Name Scopes
http://www.eggheadcafe.com/tutorial...f-49faac8854c8/wpf-binding-beyond-the-li.aspx
Refer to the below link, good example of how to perform cross database join operation
http://aspdotnethacker.blogspot.com/2010/06/hello-folks-today-i-will-tell-you-how.html
Damiano Fusco wrote:
Multiple Database
11-Aug-09
You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.
Previous Posts In This Thread:
LINQ with multiple SQL databases (data contexts) and transactions
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 sam
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
Re: LINQ with multiple SQL databases (data contexts) and transactions
Robson Felix wrote
It's about meta-data that's the problem: the meta-data of the mapping
is stored inside one context. So if you have 2 or 3, each representin
a different database, with a different schema, no, you're not going t
be able to merge them at runtime
You need an o/r mapper who is able to map entities to tables/views i
multiple schemas/catalogs. If the schema is the same, within differen
catalogs (e.g. you have 1 catalog per client of yours), some o/
mappers allow at runtime overwriting of hte mapping information so yo
can use 1 set of meta-data of the mappings and target multipl
catalogs/schemas. Ours is one of these (see signature)
We'll have FULL Linq support ready at the end of the month (in beta
see above
In Linq to Sql it's not possible to share transactions/objects amon
datacontexts
Our linq support will allow that though. It's tricky however, th
execution of a query is deferred, so it's not said when the query i
executed. This makes it particularly error prone: say you start
transaction, and inside that transaction you want to fetch data from
db: as the query isn't executed right away (that is: if it returns
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 th
transaction holding object is INSIDE the query! So if you pass th
query object around, you'll violate the ACID principle of
transaction: it can be the query object is stored somewhere (developer
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 bi
odd that MS didn't offer these kind of 'advanced' features for peopl
who need them. Oh well... :
F
--
-----------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NE
LLBLGen Pro website: http://www.llblgen.co
My .NET blog: http://weblogs.asp.net/fboum
Microsoft MVP (C#)
------------------------------------------------------------------------
These are things that should be pertty easy for you to test yourself,but my
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
ike
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 b
able to use LINQ-to-objects to join them locally. Either they are part
of the same model or they aren't ;-p
I would expect each data-context to take care of its own entities and
nothing more.
r
at
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.
My main thought: try it; let us know what you find.
Marc
Multiple Database
You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.
Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Binding Beyond the Limitation of Name Scopes
http://www.eggheadcafe.com/tutorial...f-49faac8854c8/wpf-binding-beyond-the-li.aspx