You cannot do cross joins in SQL Azure but there is a way around that....
Posted
by SeanBarlow
on Geeks with Blogs
See other posts from Geeks with Blogs
or by SeanBarlow
Published on Sat, 19 Nov 2011 04:32:41 GMT
Indexed on
2011/11/20
17:56 UTC
Read the original article
Hit count: 147
Filed under:
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.
© Geeks with Blogs or respective owner
