• 120 likes • 247 Views
EF Code First with SQL CE and LocalDb. Steve Maier @YsFred4, @ThoughtsPrayers, @YsApps, @AppRochester http://strugglingthru.net/ steve.maier@gmail.com. About me. Principal Software Engineer at InfiMed , Inc (Varian Medical Systems) WPF, SQL Server 2005/2008, WCF, MFC, COM
E N D
EF Code First with SQL CE and LocalDb Steve Maier @YsFred4, @ThoughtsPrayers, @YsApps, @AppRochester http://strugglingthru.net/ steve.maier@gmail.com
About me • Principal Software Engineer at InfiMed, Inc (Varian Medical Systems) • WPF, SQL Server 2005/2008, WCF, MFC, COM • Co-Founder of AppRochester • Mobile device user group in Rochester, NY • Speaker in Buffalo, Rochester, Syracuse, and Boston • Hold 12 MS certifications • I have “drank the Microsoft koolaid” and enjoy it • Worked with, WPF, Silverlight, SQL Server (co-author), ASP.NET, MVC, Linq to SQL, EF, WCF, driver development, DirectX, XNA, and many more things. • 11 apps in iOS store • 6 apps in Windows Phone store • 2 Android apps
Overview • About me • Entity Framework • Code First • SQL CE • Localdb
Entity Framework • ERM included in .NET 3.5 sp1 • Point EF to a database and have it generate classes and relationships for you • Previous products like CodeSmith, nHibernate, etc • Database First • EF 4.0 included with .NET 4.0 • fixed many bugs, added features like lazy loading (Linq to SQL) • EF 4.1 – online download, Package Manager • Adds Code First • EF 4.3.1 current version • EF 5.0 beta • Adding migration -> versioning of databases
Database First • Create the database using scripts or SSMS • Point EF to an existing database • Generated an edmx file with the model • Great for adding/converting existing databases to a new ERM easily • Update the model from the database • Demo
Code First • Takes the opposite approach • Start with the classes that you want to store • Generate the database • Changing the classes can regenerate the database • Migration in 4.3.1 and 5.0 is setup to handle updating the database better • Defaults to SQLExpress that is installed on the system • 4.3.1 will default to localdb if SQLExpress is not installed • Attributes or Fluent API • Demo
Code First Data Annotation (Attributes) • Key • Required • MaxLength / MinLength • NotMapped • ComplexType • ConcurrencyCheck • TimeStamp • Table / Column • DatabaseGenerated • InverseProperty / ForeignKey • ErrorMessage – not really an annotation
SQL CE • Single file, no server running – similar to Access • CE 3.5 and 4.0 are common versions • Can run on mobile devices (Windows CE 6, Windows Phone 7.5)
localdb • New feature of SQL 2012 • Being installed with VS 2011 instead of SQL Express • Same sqlserver.exe as SQLExpress • Not launched as a service but launched as a child process
SQL Ce vs LocalDb SQL CE LocalDb • 2.5 MB download size 18 MB expanded on disk • Single file • Privately installed, embedded, with the application • Non-admin installation option • Runs in-process with application • Database size support – 4G • Code free, document safe, file format • Number of concurrent connections - 256 • 32 MB download size > 160 MB on disk (TBD) • Multiple files • Database size support – 10G • Runs as process started by app • Procedural T-SQL - Select Case, If, features • LINQ to SQL • Distributed transactions • Native XML, XQuery/XPath • Stored procedures, views, triggers • Role-based security • Number of concurrent connections – unlimited but only local • Being bundled into VS 2011 • Transact-SQL - Common Query Features • ADO.NET Entity Framework 4.1
Code First DEMO • Converting a product with hardcoded values to a database driven model • Started developing with SQL CE for small local database • Simple table definitions