> Can I, how can I SELECT, join tables from 2 different databases?
> So, a business object using two DatabaseKeys...won't work. Any suggestions?
Lots of suggestions - you might not like them though <g>
This is known as a "heterogeneous join" and if you do an Internet search on that phrase you'll get lots of helpful pages, mixed in with lots and lots of unhelpful pages.
#0 - you're not going to get an MM.NET business object that can "just do it"
Some database engines, for example the Borland BDE and the one built into Crystal Reports, can do heterogeneous joins internally. Not necessarily in the most efficient way (Crystal in particular gave us real headaches a few years ago). As far as I know, ADO.NET does not have a native ability for this. LINQ might work but I'm not an expert.
So ways you can do it ... I'm not going to go into detail because that would take too long, and I don't necessarily know it. Google is your friend <g>
#1 [Easy] If both databases are in the same SQL Server instance, learn to write queries using three-part syntax, for example:
select .... from DB1.dbo.table join DB2.dbo.othertable on ....
#2 [Still fairly easy] If your main database is SQL Server, investigate using a "linked server", for example we've linked a FoxPro database into a SQL server this way. Then you can do most things. Linked servers in MSSQL use a four part syntax (servername.database.owner.table) to refer to tables.
#3 [Hard] If a linked server won't help, for example if you are combining MS Access with MySQL, then you probably have to analyse the query yourself, decide how to split the query across the two databases, and combine the two partial result sets by hand
For example, if you want to select Customer and Billing Address from one database, and Invoice from the other, perhaps your two queries will be:
Database A: select cust.cust_id, cust.cust_name, addr.address
from cust join addr on cust.cust_id = addr.cust_id and addr.type='Bill'
Database B: select * from invoice
Bring both into ADO.NET datatables with FillDataSet() or the equivalent, create a temporary index on cust_id or whatever as required, join the two datatables (can you do that across datasets?), or scan one and look up in the other.
LINQ may also help here, even if it doesn't do the whole join, because you should be able to formulate a LINQ query against the datatables or business objects
eg (Pseudo code)
DataTable dtA = bizobjA.FillDataSet( "select from cust join addr...." );
DataTable dtB = bizobjB.FillDataSet( "select from invoices..." );
query = from a in dtA
join b in dtB on a.cust_id equals b.externalCustomerId
....
#4 [Lucky] Discover something I haven't!
Hope this helps (#1 might be all you wanted to know!)
Walter