LINQ

Ref : http://www.codeproject.com/Articles/215712/LINQ-to-SQL-Basic-Concepts-and-Features

1.What is ORM?

ORM stands for Object-Relational Mapping. 

Sometimes it is called O/RM, or O/R mapping. 

It is a programming technique that contains a set of classes that map relational database entities to objects in a specific programming language.

ORM, each database is represented by an ORM context object in the specific programming language, and database entities such as tables are represented by classes, with relationships between these classes.

The ORM is responsible for the mappings and the connections between these classes and the database. 

The application only needs to deal with these classes, instead of with the physical database. 

The application does not need to worry about 

• how to connect to the database,
•  how to construct the SQL statements, 
• how to use the proper locking mechanism to ensure concurrency,
•  how to handle distributed transactions. 

These databases-related activities are handled by the ORM.

2.What is ODBC?

It was developed to unify all of the communication protocols for various RDBMSs. 

ODBC was designed to be independent of programming languages, database systems, and Operating Systems. 

So with ODBC, an application could communicate with different RDBMSs by using the same code, simply by replacing the underlying ODBC drivers.


3. Ways of accessing a Database from an Application

There are some other mechanisms to access a database from an application, such as JDBC and ADO.NET. However, to keep the diagram simple, they have not been shown here.

4. LINQ to SQL

4.1 What is LINQ to SQL

Component of the .NET Framework version 3.5

Data model of a relational database is mapped to an object model expressed in the programming language .

4.2 How it Works?

When the application runs,
                             LINQ to SQL translates the ->
                                             language-integrated queries in the object model->
                                                                 into SQL and sends them to the database for execution.
When the database returns the results, 
                             LINQ to SQL translates them 
                                             back to objects that you can work with in your own programming language.

4.3 It Supports

1. Transactions, 
2. Views,
3. Stored Procedures,
4. User-defined functions
5. Provide easy way to integrate data validation and business logic rules into your data model.
6. Supports single table inheritance in the object model.

4.4 What are the existing .NET ORM Products

Open Source products 

• NHibernate, 
• NPersist

Commercial products 

• LLBLGen 
• WilsonORMapper

LINQ to SQL is one of Microsoft's new ORM products to compete with many existing .NET ORM products.

4.5 LINQ to SQL - Advantages over other ORM products

It is designed and built specifically for .NET and SQL Server

It has many advantages over other ORM products

• Can use LINQ features 
• It fully supports SQL Server Stored Procedures. 
• All the relationships (foreign keys) for all tables, and the fields of each table just become properties of its corresponding object.
• Intellisense popup when you type in an entity (table) name
• All of the fields and the query results are strongly typed,(get a compiling error instead of a runtime error)

4.6 LINQ to SQL -What is Happening inside. 

In LINQ to SQL, ADO.NET SqlClient adapters are used to communicate with real SQL Server databases.

The usage of LINQ to SQL in a .NET application:

5. Comparing LINQ to SQL  with LINQ to Objects

Lets look at the relationships between LINQ to SQL and LINQ to Objects(use LINQ to query in-memory objects).


Some key Differences

• LINQ to SQL needs a Data Context object. (bridge between LINQ and the database)
• LINQ to Objects doesn’t need any intermediate LINQ provider or API.
• --------
• LINQ to SQL returns data of type IQueryable<T> (This is a derived type from IEnumerable<T> interface.)
• LINQ to Objects returns data of typeIEnumerable<T>
• --------
• LINQ to SQL is translated to SQL by way of Expression Trees.(allow them to be evaluated as a single unit and translated to the appropriate and optimal SQL statements)
• LINQ to Objects does not need to be translated.
• --------
• LINQ to SQL is translated to SQL calls and executed on the specified database.
• LINQ to Objects is executed in the local machine memory.

Some key Similarities 

• All use the same SQL like syntax and share the same groups of standard query operators
• From a language syntax point, working with a database is the same as working with in-memory objects.

6. LINQ to Entities 

6.1 Let’s first see what Entity Framework is

ADO.NET Entity Framework (EF) was first released with Visual Studio 2008 and .NET Framework 3.5 Service Pack 1. 

Many people view EF as just another ORM product from Microsoft, though by design it is supposed to be much more powerful than just an ORM tool.


6.2 What is inside Entity Framework 

With Entity Framework, developers work with a Conceptual Data Model(CDM), and Entity Data Model(EDM),instead of the underlying databases.

• Conceptual Data Model(CDM) is expressed in the Conceptual Schema Definition Language (CSDL).
• Data Base(Actual Storage Model) is expressed in the Storage Schema Definition Language (SSDL).
• Mapping in between CDM and Data Base is expressed in the Mapping Schema Language (MSL).

A new data-access provider(Object Services Model), EntityClient Data Provider, and ADO.NET data providers are still being used to communicate with the databases. 

The diagram below, which has been taken from the July 2008 issue of the MSDN Magazine, shows the architectures of the Entity Framework.

6.3 What we can see from the above Diagram

We can see that LINQ is one of the query languages that can be used to query against Entity Framework Entities.

6.4 LINQ to Entities Advantages

LINQ to Entities allows to create 
                                                flexible, 
                                                strongly typed queries 
                                                              against the Entity Data Model (EDM)
 by using LINQ expressions and the LINQ standard query operators. 

It is the same as what LINQ to SQL can do, though LINQ to Entities supports more features than LINQ to SQL,

•  like multiple-table inheritance, 
•  it supports many other mainstream RDBMS databases besides Microsoft SQL Server, like Oracle, DB2, and MySQL.

6.5 How it Works

• LINQ to Entities applications work against a conceptual data model (EDM). 
• All mappings between the languages and the databases go through the new Entity Client Mapping provider. 
• The application no longer connects directly to a database.
• This means that you can no longer use the native database query language; not only will the database not understand the EDM model, but also current database query languages do not have the constructs required to deal with the elements introduced by the EDM such as inheritance, relationships, complex-types, etc.

6.6. Comparing LINQ to SQL  with LINQ to Entities

Some key Advantages

LINQ to SQL enables developers to experience the LINQ programming model directly over an existing database schema.( allows developers to generate .NET classes that represent data. Rather than mapping to a conceptual data model) - These generated classes map directly to database tables, views, Stored Procedures, and user defined functions. 

Using LINQ to SQL, developers can write code directly against the storage schema using the same LINQ programming pattern as previously described for in-memory collections, Entities, or the DataSet, as well as other data sources such as XML.

Some key Differences

LINQ to SQL has some limitations, mainly because of its direct mapping against the physical relational storage schema. For example, you can’t map two different database entities into one single C# or VB object, and underlying database schema changes might require significant client application changes.

The table below lists some supported features by these two data access methodologies:

Features	LINQ to SQL	LINQ to Entities
Conceptual Data Model	No	Yes
Storage Schema	No	Yes
Mapping Schema	No	Yes
New Data Access Provider	No	Yes
Non-SQL Server Database Support	No	Yes
Direct Database Connection	Yes	No
Language Extensions Support	Yes	Yes
Stored Procedures	Yes	Yes
Single-table Inheritance	Yes	Yes
Multiple-table Inheritance	No	Yes
Single Entity from Multiple Tables	No	Yes
Lazy Loading Support	Yes	Yes

7.0 Deferred Execution

One important thing to remember when working with LINQ to SQL is the deferred execution of LINQ.

The standard query operators differ in the timing of their execution, depending on whether they return a singleton value or a sequence of values.

• Excute Immediately -  return a singleton value (for example, Average and Sum) execute immediately. 
• Not Excuted Immediately- Methods that return a sequence and return an enumerable object.

IEnumerable<T> Interface.

• Methods that operate on in-memory collections and extend this inteface will return enumerable object.
• When that  enumerable object is enumerated, the logic of the query operator is employed and the query results are returned.

IQueryable<T> Interface.

• Methods that extend this interface do not implement any querying behavior, but build an expression tree that represents the query to be performed. 
• The query processing is handled by the source IQueryable object.