CODEWITHSUNDEEP
c#sqlazurewindows-phone-8
gua
gua

Reputation: 19

How to make a query on multiple(joined) Tables using Azure Mobile Services with windows phone 8?

I have two tables, they are indexed in the azure database manager. So i have the foreign key in the second table. My tables are for example

OrderTable (OrderId,OrderDate,CustomerId) /CustomerId is my foreign key

Customer Table( CustomerId,CustomerName,....)

So i just want a query like this:

Select *
From OrderTable o1,CustomerTable c1
Where c1.CustomerId=o1.CustomerId

I used the microsoft sample TodoItems, and i already can make querys on one table like this:

    items = await todoTable
    .Where(todoItem => todoItem.Date >= DateTime.Now)
    .ToCollectionAsync();
    .ToListAsync();

So in my app i got the two table, is there any option to query the joined tables like the one above ?

Upvotes: 0

Views: 2126

Answers (2)

the Reverend
the Reverend

Reputation: 12549

Create a view like Rikalous pointed out. You can do this by clicking on Sql Databases on your windows azure portal. Select your server and then click on the "Manage URL" located on the Dashboard page at the bottom right.

Once you login, click on "New Query" and then just type in the sql code to create a view.

CREATE mySchema.myView AS
SELECT * FROM Table t1 INNER JOIN OtherTable t2 ON t1.a=t2.b

Once your view is created, go back to your Windows Azure Portal. Go to your Mobile Service and create a new table. Create the table with the name of your view, the system will detect the view and present it to you. You will see that no default columns are present nor any data will show up. But you will be able to query it as any other table, plus you can also modify its insert/update/read scripts.

*Important: double check that your view is created on the correct schema. Also double check after adding the table on Mobile Services that no table was created on the server.

Upvotes: 1

Rikalous
Rikalous

Reputation: 4564

You can perform joins in LINQ, but in your situation, it's probably easier to create a view that does the join and then select from that using LINQ.

Also, you should avoid using the older join syntax as you have - it will stop being supported at some point - and use the INNER JOIN clause, i.e.

    SELECT * FROM OrderTable o1 INNER JOIN CustomerTable c1 
    ON c1.CustomerId = o1.CustomerId

Upvotes: 2

Related Questions