Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
What to do CE or MS Access or Entity Framework

Unanswered What to do CE or MS Access or Entity Framework

  • Sunday, December 16, 2012 2:24 AM
     
      Has Code
    Sign In to Vote
    0

    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).

    Thanks,


    • Edited by chuck02323 Sunday, December 16, 2012 2:40 AM
    •  
     

All Replies