You cannot do cross joins in SQL Azure but there is a way around that....

So I was asked today how to do cross joins in SQL Azure using Linq. 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();

So as long as you have your data loaded from one of the contexts you can still execute your queries and get the data back that you want.