>>>>From a contrarian POV, on top of the benefits of only having to chuck a coiuple of DLLs into the deployment:
>>>>
>>>>You can use the Compact edition with the ADO.NET support for EF and Linq (native providers not OLDEB)
>>>>You can use SSMS to create/manage CE databases.
>>>>IIRC replicating to a main SQL server is supported?
>>>>
>>>>Admitted downsides:
>>>>4GB database limit
>>>>No Stored Procs.
>>>>No nested transactions ?
>>>
>>>No views, no triggers
>>
>>True - I should have mentioned Views, sorry.
>>Out of curiosity I just cobbled a quick CE database together and created a .Net EF connection. Worked perfectly ( including associations and cascades, even offered to import the DB into the project which would make deployment even more of a no-brainer). If using EF/Linqthen the lack of views seems to become somewhat irrelevant?
>
>I read somewhere recently CE works well with EF
>
>Yes, views may be more irrelevant then
>
>>
>>Triggers? Only Mark knows whether that lack would be a problem. It it were I'd be mildly surprised.
>
>If you need cascading deletes / some control that you cannot insert a child record without corresponding parent / control that the parent cannot be deleted if there are still child records -- wouldn't you need triggers ?

Sure there are cases when only triggers will do (ran into one myself recently with self-referential tables) . But if you know you haven't got them available then you design the database accordingly.

I don't think I've used MySql but I've used SQLLite a couple of times without problems. To me the advantage of CE is the ability to use native SQL providers in .NET, it's ability to play in the same box as other MS SQL versions and the non-existent install issues. As always there is a trade-off between the small footprint and reduction in features....