Transcript SDC Code Camp 2008 Slide Deck (Entity

SQL Server Compact Edition 3.5
and the Entity Framework
Rob Sanders and Matt Lynch
About Us
• Matt is an independent contractor who
specializes in SharePoint, Entity Framework
and C# and the .Net Framework.
• http://matt-lynch.spaces.live.com
• Rob is a consultant for Readify in Brisbane and
spends his time between SQL Server, Team
Foundation Server and BizTalk.
• http://internationalized.spaces.live.com
Presentation Agenda
•
•
•
•
•
Introduction to the ADO Entity Framework
Overview of SQL Server Compact Edition 3.5
Entity Framework Fundamentals
Followed by.. Advanced Entity Framework
Finally.. Entity Framework and SQL Compact
Edition – Together!
Origins of this Presentation
Rob> Hey mate, I’m working with LINQ to SQL
and SQL Server Compact Edition
Matt> Cool, hey did you know Compact
Edition supports the Entity Framework?
Rob> Hmmmm
Rob> We’ve got to do a presentation on it!
Matt> Absolutely!
Microsoft’s Data Access Strategy
“ The Entity Data Platform is a multi-release vision,
with future versions of reporting tools,
replication, data definition, security, etc. all being
built around a common Entity Data Model. “
– Mike Pizzo,
Architect, Data Programmability
April, 2007
Source: http://blogs.msdn.com/data/archive/2007/04/28/microsoft-s-data-access-strategy.aspx
Introducing the ADO.Net Entity
Framework(EF)
• The goal of EF is to decrease the amount of
code and maintenance required for dataoriented applications
• Entity Framework is central to Microsoft’s data
access strategy for the .Net Framework
• Shipped with .Net Framework 3.5 SP 1
• Underpinned by LINQ to Entities
The Entity Data Model
• The Entity Data Model is a Entity-Relationship data
model which supports advanced features such as:
–
–
–
–
–
Abstract classes
Property / Entity renaming
Inheritance
Complex Types
Stored procedure mapping for insert, update and
delete methods.
– Mapping a single entity to multiple tables
– Hiding join tables in collections
– Documentation which populate Xml comments
Entity SQL
• Write queries independently of the data store with a more
concise language
– Provides a common query language across providers by
extending the existing SQL language with constructs to work
with strong notions of type, inheritance, and relationships from
the Entity Data Model
• SQL
–
SELECT sp.FirstName, sp.LastName, sp.HireDate
FROM SalesPerson sp
INNER JOIN Employee e ON sp.SalesPersonID = e.EmployeeID
INNER JOIN Contact c ON e.EmployeeID = c.ContactID
WHERE e.SalariedFlag = 1 AND e.HireDate >= '2006-01-01'
• Entity SQL
–
SELECT sp.FirstName, sp.LastName, sp.HireDate
FROM AdventureWorks.AdventureWorksDB.SalesPeople AS sp
WHERE e.HireDate >= '2006-01-01‘
– Only used for select statements
Entity Framework Basics
Demos Happen Here!
• Entity Data Model
• Basic Queries
The SQL Server Family
The SQL Server 2008 Family of Products
• Enterprise
• Standard
• Workgroup
• Web
• Express
• Compact
Overview of SQL Compact Edition 3.5
•
•
•
•
•
•
•
•
Relational Database
Flat File based
Memory and CPU conscious design
In-Process
No installation
ODBC/ADO Supported
Runs on Windows Mobile and Desktop Platforms
Also supported by the Sync Framework (SQL
Server 2008)
What’s new in Compact Edition 3.5?
• Synchronization Options
– Sync Framework, Remote Data Access, Merge replication
• Extended T-SQL support
– CROSS APPLY
– CAST
– TOP
• Transaction Scope (On Desktops)
– Read Committed (default), Repeatable Read , Serializable
•
•
•
•
•
Identity Columns
Unicode support
Multi User, supports 256 connections – recommend < 100
LINQ to SQL and, of course,
Entity Framework!
Be Honest, What’s the Downside?
•
•
•
•
No support for Views
No support for Triggers
No support for Stored Procedures or UDFs
Does not support smalldatetime, xml, varchar(max),
nvarchar(max), char(c), varchar(n) datatypes
• You cannot integrate CLR assemblies
• No Full Text Search indexes
• No cross platform support
• No roles or privileges are supported
…but it’s nicer than an XML file.
Is Compact Edition
Worthy?
Asked at www.stackoverflow.com:
“Has anyone used (or considered using)
SQL Server Compact Edition 3.5?”
Votes of Confidence
"At work, we recently had to migrate a project that was designed
for SQL Server Express to SQLCE. Once we started digging into
it though, and saw what it DOES offer, we were convinced it
was the right move. It supports LINQ, querying a lot of data
[was] MUCH quicker than we thought it would”
- BFree
“I currently use SQL CE with NHibernate in a business application
where the SQL CE database gets synchronized with Sync
Framework via WCF to an SQL 2008 server. Works like a
charm! especially generating the database from Nhibernate mapping files [are] just awesome.“
- jkersch
More Demonstrations
Demo
• Create a Compact Edition Database
– With Visual Studio
– With C#
Advanced Entity Framework
• Demos:
– Concurrency
– Many to Many
– Table Per Type
• Grand Finale
Further Reading
SQL Compact Edition Homepage
http://www.microsoft.com/Sqlserver/2005/en/us/compact.aspx
Introduction to SQL Compact Edition
http://support.microsoft.com/kb/920700
Download SQL CE
http://www.microsoft.com/downloads/details.aspx?FamilyID=85e0c3ce-3fa1-453a-8ce9af6ca20946c3&displaylang=en
Introduction to the Entity Framework (Wildermuth)
http://www.theserverside.net/tt/articles/showarticle.tss?id=IntroducingEntityFramework
ADO.net Entity Framework Overview (MSDN)
http://msdn.microsoft.com/en-us/library/aa697427(VS.80).aspx
Fin
• Rob Sanders
• http://internationalized.spaces.live.com
• Twitter: AusRob
• [email protected]
• Matt Lynch
• http://matt-lynch.spaces.live.com
• [email protected]