Geeks With Blogs
Sean Barlow
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.
Posted on Friday, November 18, 2011 10:32 PM | Back to top

Comments on this post: You cannot do cross joins in SQL Azure but there is a way around that....

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © SeanBarlow | Powered by: | Join free