LINQ 2 SQL

beansproutscompleteΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 9 μήνες)

80 εμφανίσεις

LINQ 2 SQL


By, Shahzad Sarwar

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)


What is the problem?



Class!=Data


1. strongly
-
typed queries , what ?


2. strongly
-
typed results


Benefits


1. IntelliSense


2. Compile
-
time error checking.



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/

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


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


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 )


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.


Conceptual View
(LINQ)

Conceptual View
(LINQ to Dataset)

Conceptual View
(LINQ 2 entities)

Conceptual View
( PLINQ)

References

[LINQ to Dataset]


http://blogs.msdn.com/adonet/archive/2007/01/26/querying
-
datasets
-
introduction
-
to
-
linq
-
to
-
dataset.aspx


http://msdn.microsoft.com/en
-
us/vbasic/bb688086.aspx


http://msdn.microsoft.com/en
-
us/library/bb386977.aspx


http://www.danielmoth.com/Blog/2007/07/linq
-
to
-
dataset.html


http://channel9.msdn.com/posts/DanielMoth/LINQ
-
to
-
DataSet/

[LINQ to entities]


http://msdn.microsoft.com/en
-
us/library/bb386964.aspx


http://dotnetaddict.dotnetdevelopersjournal.com/adoef_vs_linqsql.htm


http://blogs.msdn.com/kaevans/archive/2008/01/29/linq
-
to
-
sql
-
vs
-
linq
-
to
-
entities
-
smackdown.aspx


http://msdn.microsoft.com/en
-
us/library/cc161164.aspx

What is difference LINQ to SQ L and LINQ
to Entities?


1:1 vs many to many , complex mapping


sql only , any database .... so run in database specfic pattern.

http://msdn.microsoft.com/en
-
us/library/cc161164.aspx



Sample Application on the fly


Sample Application:


Sample Add , delete , edit


Example



What next ??????????????????????????? It is over…

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?


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 ........

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


Architecture Guide Lines


References:


http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/arc
hive/2007/05/10/9322.aspx


http://jonkruger.com/blog/2008/02/10/linq
-
to
-
sql
-
in
-
disconnectedn
-
tier
-
scenarios
-
saving
-
an
-
object/


http://andrewtokeley.net/archive/2008/07/06/mocking
-
linq
-
to
-
sql
-
datacontext.aspx


http://devlicio.us/blogs/derik_whittaker/archive/2008/05/21/using
-
linq
-
s
-
datacontext
-
to
-
re
-
create
-
your
-
database
-
for
-
testing.aspx


http://www.west
-
wind.com/weblog/posts/246222.aspx


http://blogs.msdn.com/mirceat/archive/2008/03/13/linq
-
framework
-
design
-
guidelines.aspx


http://msdn.microsoft.com/en
-
us/library/bb882661.aspx


Code Generation


2 Options


VS Studio OR Designer


SQLMetal


Code Understanding


Explore



XML Mapping file



Generated classes


dataConext ,Database, Table, relationships

Example


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/expression
-
tree
-
basics.aspx


http://blogs.msdn.com/charlie/archive/2008/02/13/linq
-
farm
-
seed
-
using
-
the
-
expression
-
tree
-
visualizer.aspx



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


LINQDataSource


Like sqlDataSource


Example 1
(Wizard View , declarative View )


Overriding events


Selecting event


Example 2

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/delete stmt
but not the procedures.

LINQ2SQL Customization


By Store Procedure


Example


Object validation (Partial classes)


Example


Customize entity class ( 1 to m mapping )


Example


Inheritance


In OR Mapping, 3 Type


Example


Table per Class Hierarchy

[Example]

Table per Subclass


Table per Concrete Class

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]


Performance


Benchmark 1




References


http://blogs.msdn.com/ricom/archive/2007/06/22/dlinq
-
linq
-
to
-
sql
-
performance
-
part
-
1.aspx


http://blogs.msdn.com/ricom/archive/2007/06/22/dlinq
-
linq
-
to
-
sql
-
performance
-
part
-
1.aspx

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/benchmarking
-
linq
-
vs.html

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

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 …………………