LINQ 2 SQL

1
LINQ 2 SQL

• 
  By, Shahzad Sarwar

2
What you should know, before starting

• Anonymous function
• Anonymous type
• Anonymous Methods
• Lamda expression
• Collection Initializers
• Object Initializers
• Extension methods
• Type Inference
• Automatic properties
• Partial Methods
• Generic
• Projection (Later)
• Expression Tree (Later)

3

• What is the problem?
• Class!Data
• 1. strongly-typed queries , what ?
• 2. strongly-typed results
• Benefits
• 1. IntelliSense
• 2. Compile-time error checking.

4

• What is Linq to SQL?
• 1.OR Mapper API
• 2.Syntax to query ( LINQ to objects )
• 3.Tools
• What r OR Mappers(OR/M)?
• Not ORM?
• What is Object Role Modeling (ORM)?
• Associated language FORML (Formal Object-Role
  Modeling Language)
• Supported by Visio and Microsoft Architect
• References http//www.orm.net/

5
Current Solutions in market

• .NET Persistence
• BBADataObjects
• DataObjects.NET
• Data Tier Modeler for .NET
• DotNorm
• Eldorado.NET
• Enterprise Core Objects (ECO)
• Entity Broker
• eXpress Persistent Objects for .NET
• FastObjects.NET
• JC Persistent Framework
• LLBLGen Pro
• ModelWorks
• Nhibernate
• Nolics.NET

6
Current Solutions in market

• Norm
• Norpheme
• ObjectBroker
• ObjectSpaces
• ObjectSpark
• Objectz.NET
• OJB.NET
• OPF.Net (Object Persistent Framework)
• ORM.NET
• Pragmatier Data Tier Builder
• RapTier
• Sisyphus Persistence Framework
• TierDeveloper
• Bob.NET
• ObjectPersistor.NET
• Genome

7
A view of LINQ

• LINQ Consist of
• Total 6 LINQ implementations
• 3 general LINQ technologies
• 2 related to relational databases (OR/M)
• 1 Future Technologies
• LINQ to Objects
• Over objects , system.linq
• LINQ to Dataset
• over dataset ( LINQ to objects
  system.data.dataextension )
• LINQ to SQL- DLinq
• Direct sql server ( system.data.linq )

8

• LINQ to entities
• entities framework ( a part of the ADO.NET Entity
  Framework which allows LINQ query capabilities)
• The Entity Data Model (EDM) is a conceptual data
  System.data.entity
• LINQ to XML xlinq
• The low-level XmlReader/XmlWriter API in .NET
  today. System.Xml.Linq
• PLINQ
• Query execution engine accepts any
  LINQ-to-Objects or LINQ-to-XML query and
  automatically utilizes multiple processors or
  cores for execution when they are available.

9
Conceptual View (LINQ)
10
Conceptual View (LINQ to Dataset)
11
Conceptual View (LINQ 2 entities)
12
Conceptual View ( PLINQ)
13
References

• LINQ to Dataset
• http//blogs.msdn.com/adonet/archive/2007/01/26/qu
  erying-datasets-introduction-to-linq-to-dataset.as
  px
• http//msdn.microsoft.com/en-us/vbasic/bb688086.as
  px
• http//msdn.microsoft.com/en-us/library/bb386977.a
  spx
• http//www.danielmoth.com/Blog/2007/07/linq-to-dat
  aset.html
• http//channel9.msdn.com/posts/DanielMoth/LINQ-to-
  DataSet/
• LINQ to entities
• http//msdn.microsoft.com/en-us/library/bb386964.a
  spx
• http//dotnetaddict.dotnetdevelopersjournal.com/ad
  oef_vs_linqsql.htm
• http//blogs.msdn.com/kaevans/archive/2008/01/29/l
  inq-to-sql-vs-linq-to-entities-smackdown.aspx
• http//msdn.microsoft.com/en-us/library/cc161164.a
  spx

14

• What is difference LINQ to SQ L and LINQ to
  Entities?
• 11 vs many to many , complex mapping
• sql only , any database .... so run in database
  specfic pattern.
• http//msdn.microsoft.com/en-us/library/cc161164.a
  spx
• Sample Application on the fly
• Sample Application
• Sample Add , delete , edit
• Example
• What next ??????????????????????????? It is over

15
DataContext

• Connection like class (Mixture of a lot of
  objects)
• Caching n change tracking
• Example
• Log
• ExecuteCommand
• GetTable
• ExecuteQuery
• Refresh
• ObjectTrackingEnabled
• Transaction
• Cases
• Nested Query
• Cache Query / Multiple context
• ToList()
• What is Projection?

16
Context Management

• Four Approaches
• 1. Create a new Context for each atomic operation
  (Application level management)
• Problem pass data objects or the data
  context itself around to other components in your
  application.
• E.g. if you use business objects there's no real
  clean way to get a context passed between
  business objects.
• 2.Create a global DataContext and handle all
  operations against this single DataContext object
• Problem
• In other words if you have multiple simulataneous
  data operations that might not be directly
  linked/atomic
• Not possible to do simple update.....?
• and Options such ObjectTrackingEnabled or
  DeferredLoadingEnabled can't be set easily
• Works for desktop , not for Web because of
  threading ........

17
Context Management

• 3.Create a thread specific DataContext
• Request specific DataContext that is tied to the
  active thread,Web request to the active ASP.NET
  HttpContext.
• Example
• Factory Pattern
• 4. Create a per business object DataContext

18
Architecture Guide Lines

• References
• http//mtaulty.com/CommunityServer/blogs/mike_taul
  tys_blog/archive/2007/05/10/9322.aspx
• http//jonkruger.com/blog/2008/02/10/linq-to-sql-i
  n-disconnectedn-tier-scenarios-saving-an-object/
• http//andrewtokeley.net/archive/2008/07/06/mockin
  g-linq-to-sql-datacontext.aspx
• http//devlicio.us/blogs/derik_whittaker/archive/2
  008/05/21/using-linq-s-datacontext-to-re-create-yo
  ur-database-for-testing.aspx
• http//www.west-wind.com/weblog/posts/246222.aspx
• http//blogs.msdn.com/mirceat/archive/2008/03/13/l
  inq-framework-design-guidelines.aspx
• http//msdn.microsoft.com/en-us/library/bb882661.a
  spx

19
Code Generation

• 2 Options
• VS Studio OR Designer
• SQLMetal
• Code Understanding
• Explore
• XML Mapping file
• Generated classes
• dataConext ,Database, Table,
  relationships
• Example

20
Query pipeline

• LINQ 2 Objects IEnumerable
• LINQ 2 SQL IQueryable
• public interface IQueryable IEnumerable
• Type ElementType get
• Expression Expression get
• IQueryProvider Provider get
• What is Expression Tree?
• a method of translating executable code into
  data.
• Add-ons
• Query Visualizer
• Expression Tree Visualizer
• Example
• Out of processor , Grammar , sql processor
• Various Stages ( Out of scope )
• 1-Many mapping b/q LINQ grammer to db sql
• References
• http//blogs.msdn.com/charlie/archive/2008/01/31/e
  xpression-tree-basics.aspx

21
Joins n Lazy Loading

• Types of Joins
• Inner Join
• Left Outer Join
• Right Outer Join
• Cross Join
• Example
• LINQ implementation of Joins
• Deferred Loading of objects
• DataLoadOptions/DeferredLoadingEnabled property
• AssociateWith Property

22
LINQDataSource

• Like sqlDataSource
• Example 1 (Wizard View , declarative View )
• Overriding events
• Selecting event
• Example 2

23
Using Store Procedure

• Add Store procedures to Model
• Example
• Add function to Models
• Example
• Difference b/w Store Procedure n function
• for computations , for Business logic
• Functions can be called inside select/update/delet
  e stmt but not the procedures.

24
LINQ2SQL Customization

• By Store Procedure
• Example
• Object validation (Partial classes)
• Example
• Customize entity class ( 1 to m mapping )
• Example

25
Inheritance

• In OR Mapping, 3 Type
• Example

26
Table per Class Hierarchy

• Example

27
Table per Subclass
28
Table per Concrete Class
29
LINQ Dev. Tools

• Visual LINQ Query Builder
• through a friendly interface
• http//code.msdn.microsoft.com/vlinq
• Demo
• LINQPad
• http//www.linqpad.net/
• Development tool
• Demo

30
Performance

• Benchmark 1
• References
• http//blogs.msdn.com/ricom/archive/2007/06/22/dli
  nq-linq-to-sql-performance-part-1.aspx
• http//blogs.msdn.com/ricom/archive/2007/06/22/dli
  nq-linq-to-sql-performance-part-1.aspx

31
Performance

• Adventure Db , Store procedure
• So, comparing to the raw ADO.NET - DAAB is 8
  slower and LINQ is 28 slower.Comparing to DAAB
  - LINQ is 18 slower.
• Reference
• http//alexpinsker.blogspot.com/2007/07/benchmarki
  ng-linq-vs.html

32
Performance

• But consider
• Productivity
• SQL being first class language in VS
• Caching of DB.
• Change tracking optimization
• So Over a Great Milestone in Development process

33
What is Left..

• Design Pattern for LINQ to SQL
• XLINQ
• LINQ to Dataset
• LINQ to entities
• PLINQ
• n.
• ..
• ..
• ..
• So Wait for Next presentation on LINQ

34
(No Transcript)