Cross database joins in SQL azure

How to do cross-database joins in SQL azure?

To do cross-database joins, we need to process all of the databases by querying and then join the results together.
Scaling SQL Azure this way is called sharding – partitioning your data into different shards, each stored in a different database or even different servers.
Note: SQL Azure databases have a limit of 10GB.

Well the simple answer is you cant do it. It is not supported but there are ways around that. The solution is actually very simple and easy to implement. So here is what I did and how I did it.
I created two SQL Azure Databases. The first Database is called AccountDb and has a single table named Account, which has an ID, CompanyId and Name in it. The second database I called CompanyDb and it contains two tables. The first table I named Company and the second I named Address. The Company Table has an Id and Name column. The Address Table has an Id and CompanyId columns. Since we cannot do cross joins in Azure we have to have one of the models preloaded with data. I simply put the Accounts into a List of accounts and use that in my join.

var accounts = new AccountsModelContainer().Accounts.ToList();
var companies = new CompanyModelContainer().Companies;
var query = from account in accounts
join company in
(
from c in companies
select c
) on account.CompanyId equals company.Id
select new AccountView() {
AccountName = account.Name,
CompanyName = company.Name,
Addresses = company.Addresses
};
return query.ToList();

Combine your databases
If you have tables that are frequently used together, i.e. they are joined in queries or the rows are inserted in the same transactions, then it would be a good idea to move the similar tables into the same database. This of course eliminates the need to traverse databases. SQL Azure has very recently increased the maximum database size from 50GB to 150GB, which potentially makes this a more viable option than perhaps it once was.

Join your data in your application
Two separate queries could be run on the two separate databases and then these results could be joined within that application. Obvious downsides to this will be the potential for large DB I/O, large network transfer and large memory usage in your app. This is not something to consider if the amount of data that is likely to be returned is large (e.g. 1000+ rows) but it is fine if the data will be manageable.