Transcript EF Code First with SQL CE and LocalDb

Steve Maier
@YsFred4, @ThoughtsPrayers, @YsApps, @AppRochester
http://strugglingthru.net/
[email protected]
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
SQL CECe vs LocalDb
LocalDb
 2.5 MB download size
 32 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







•
> 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
database