Sunday, December 16, 2012 2:24 AM
I have an application that is WPF with SQL Express. Before that is was Windows Forms and Access. Performance was fine with Express and even with MS Access. Now I want to move the app to the web and host it on an Azure website. Unfortunately with Azure if you want to use a database like SQL12 localdb or SQL Express the hosting costs go up real quick. So I was thinking of using SQL CE. Got started but now work has grounded to a halt.
The application has very complex views. Lots of aggregates, Common Table Expressions, views calling views and sub selects. Of course none of those are supported in CE. For example the view below uses a CTE which is based on a view which itself does summation:
CREATE VIEW [dbo].[vwNuclideLimitDelta] AS WITH s AS (SELECT LicsCode, Nuclide, SUM(Activity) AS total_uCi FROM dbo.vwSourceNuclides GROUP BY LicsCode, Nuclide)
SELECT nl.LicsCode, nl.Nuclide, nl.N_Limit, s_2.total_uCi, nl.N_Limit - s_2.total_uCi AS Nuc_Delta FROM dbo.NuclideLimitsSpecificNuclide AS nl
INNER JOIN s AS s_2 ON nl.LicsCode = s_2.LicsCode AND nl.Nuclide = s_2.Nuclide UNION ALL SELECT s_1.LicsCode, s_1.Nuclide, nlALL.N_Limit, s_1.total_uCi, nlALL.N_Limit - s_1.total_uCi AS Nuc_Delta FROM dbo.NuclideLimitsForAllNuclides AS nlALL
INNER JOIN s AS s_1 ON nlALL.LicsCode = s_1.LicsCode WHERE (NOT EXISTS (SELECT 1 AS found FROM dbo.NuclideLimitsSpecificNuclide AS n WHERE (LicsCode = nlALL.LicsCode) AND (Nuclide = s_1.Nuclide)))
So I thought about going back to MS Access, however that is not supported by the Entity Framework. I kind of shudder at the thought of breaking all the views into plain selects and using a .ToList() in C# and then trying to join them using Linq (letting all the joins be done in c#).
Any suggestions on how I can host a Website on Azure and still use a DB that doesn't require higher hosting costs, work around the view issues, or use some other db (e.g. vistaDB, ClearDB-MySQL or something else not running on a service thread).
- Edited by chuck02323 Sunday, December 16, 2012 2:40 AM
Monday, December 17, 2012 7:09 AMModerator
This may be helpful: How To Host Your Site and Content On Azure Quickly and Easily: http://blogs.msdn.com/b/brandonwerner/archive/2010/02/26/how-to-host-your-site-and-content-on-azure-quickly-and-easily.aspx.
TechNet Community Support