Saturday, 5 July 2014

LINQ ,ENTITY FRAMEWORK,LINQ TO ENTITIES,LINQ TO SQL DIFFERENCE AND SIMILARTIES


Assumptions:

1..NET2.0/4.0 and vs 2005/2008/2010/above is installed on system. 
 Code Download: Click Here to download entire solution
2. Knowledge .Net2.0,3.5 Basics with C#and basic SQL Server
Note : I am using Microsoft vs2013 Express for web application development.You can use visual studio any version starting from 2008

INTRODUCTION

 Actually, I thought of writing this post regarding LINQ ,ENTITY FRAMEWORK..because most of the posts and queries I have seen are regarding these technologies and seems most of the developers are confused with this part of .NET Technology. But trust me LINQ is such a strong language extension which has given great power to .NET languages to query data within application only. LINQ defines a .NET application programming interface (API) and set of extensions to the Visual Basic and C# languages that enables querying diverse data types with a single syntax that ’ s similar to Structured Query Language (SQL) Which we use for database access. 

 LET US UNDERSTAND LINQ FIRST

 What is LINQ?

Language Integrated Query (LINQ) is a set of extensions to the .NET Framework that encompass language-integrated query, set, and transform operations. It extends C# and Visual Basic with native language syntax for queries from object, XML, and databases, and provides class libraries to take advantage of these capabilities. Let us see an example first. Suppose there is a list of integers as follows:

var list = new List<int>() { 1, 2, 3, 4, 5, 6, 100 };

To find all the even numbers in this list, you might write some code as follows:
var firstList= new List<int>();
foreach (var num in list)
{
if (num % 2 == 0)
list1.Add(num);
}
Now with LINQ, you can select all of the even numbers from this list and assign the query result to a variable in just one-sentence expression like this:
var secondList= from number in list
where number % 2 == 0
select number;
In this example, secondList and firstList are equivalent. list2 contains the same numbers as list1 does. As you can see, you don't write a foreach loop. Instead, you write a SQL statement.
But what do from, where, and select mean here? Where are they defined? How and when can they be used? Let us start the exploration now.
Language Integrated Query, or LINQ, is a flexible ,structured simple , SQL-like query language designed to give the programmer consistent syntax to query any data set, whether database, XML, or just plain objects. We can also take it  as It is just developed  for comfort a.
EXAMPLE:
We are going to Query an Object Collection
Scenario/Problem: You want to query a collection for all objects meeting some criteria.
This and the following examples use some data objects to demonstrate how LINQ works.
//class declared and definition Book Antiqua";">
public class Book
{
public string Title { get; set; }
public int AuthorId { get; set; }
public int PublishYear { get; set; }
public Book(string title, int authorId, int year)
{
this.Title = title;
this.AuthorId = authorId;
this.PublishYear = year;
}
public override string ToString()
{
return string.Format(“{0} - {1}”, Title, PublishYear);
}
class Author
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public Author (int id, string firstName, string lastName)
{
this.Id = id;
this.FirstName = firstName;
this.LastName = lastName;
}
}
//...
List<Book> books = new List<Book>{
new Book(“Jack”, 1, 1941),
new Book(“Rack”,1, 1942),
new Book(“Back”,1, 1943),
 };

List authors = new List<Author>
{
new Author(1, “Shabir”, “Hakim”)
};
BASIC LINQ QUERY SHOWN BELOW  RETRIEVES ALL BOOK INFORMATION
 
var allBooks = from book in books select book;
foreach (Book book in allBooks)
{
Console.WriteLine(book.ToString());
}
The var keyword is often used with LINQ for reasons you’ll see later. In this case,
allBooks is a collection of Book objects. The output is as follows:
Jack - 1841
Rack - 1842
Back - 1853  
Before We Understand Entity Framework,we should know why Entity Framework and Why Not Entity Framework?
Entity Framework is an evolution of ADO.NET and is a data access framework comes with .NET 4.0 , Bridges the gap between database and objects(POCO) and sits on top of ADO.NET

When Not to use Framework?

 Code can only access the database through stored procedures. EF does have limited support for SP.
Frequently insert operations  Bulk is not supported yet by EF
Assume we want to work in a method of loading ALL data into memory is that recommended using Entity framework? What problems should we expect?
Loading all entities will require many queries and a lot of time.
Latencies because of the EF change tracking and large collection handling.
EF contexts are not thread-safe, you should not use one context for the entire service.
If you do intend to do that, use EF for loading entities, but not for managing them (detach the entities from the content).
do not use the context as a cache object (for distributed scenarios)
it is not thread safe, having overheads and doesn’t follow the idea of separation of concern. it is much better to use a designated cache API like AppFabric Cache.

Why Entity Framework ?

As LINQ to Entities is part of the Entity Framework, let's now learn what that is.
ADO .NET Entity Framework (EF) is an addition to the Microsoft ADO.NET family. It enables developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for the data-oriented applications. The Entity Framework applications provide the following benefits:
  • Applications can work in terms of a more application-centric conceptual model including types with inheritance, complex members, and relationships
  • Applications are freed from hard coded dependencies on a particular data engine or storage schema   Mappings between the conceptual model and the storage-specific schema can change without changing the application code  Developers can work with a consistent application object model that can be mapped to various storage schema s, possibly implemented in different database management systems
  • Multiple conceptual models can be mapped to a single storage schema   The Language Integrated Query (LINQ) support provides compile-time syntax validation for queries against a conceptual model
 With Entity Framework, developers work with a conceptual data model, an Entity Data Model (EDM), instead of the underlying databases. 
The conceptual data model schema is expressed in the Conceptual Schema Definition Language (CSDL), 
the actual storage model is expressed in the Storage Schema Definition Language (SSDL),
 and the mapping in between is expressed in the Mapping Schema Language (MSL). 
A new data-access provider, EntityClient, is created for this new framework, but under the hood, the ADO.NET data providers are still being used for communicating with the databases.

OVERHEAD OF USING EF VS ADO.NET

  • The overhead of taking a ado.net data reader an transforming the results to objects, and the overhead of compiling SQL queries from LINQ.
    You will have the same overhead and even more, if you try to build your own ORM framework. Better don't do it
  • When you have abstraction layer you get overhead. The overhead depends on what they do with Entity Framework.
  • in general you should consider to get away from Inheritance because it does have a greater overhead.

 LINQ to Entities

Now let's have a look at what LINQ to Entities is.
LINQ to Entities provides the LINQ support that enables developers to write queries against an Entity Framework conceptual model using Visual Basic or Visual C#. Queries against the Entity Framework are represented by command-tree queries, which execute against the object context. LINQ to Entities converts the LINQ queries to the command-tree queries, executes the queries against Entity Framework, and returns objects that can be used by both Entity Framework and LINQ.
LINQ to Entities allows developers to create flexible, strongly-typed queries against the EDM by using the LINQ expressions and standard LINQ query operators. To a certain degree, LINQ to Entities is similar to LINQ to SQL, but LINQ to Entities is a true ORM  Article from Microsoft and it supports more features than LINQ to SQL, such as multiple-table inheritance. LINQ to Entities also supports many other mainstream RDBMS databases such as Oracle, DB2, and MySQL, in addition to Microsoft SQL Server.

 LINQ TO SQL

Before LINQ to Entities, Microsoft released another ORM  Product, LINQ to SQL. Both LINQ to SQL and LINQ to Entities can be used in the data access layer to interact with databases, but they are quite different. In this section, we will learn what LINQ to SQL is, and in the next section we will compare these two technologies.
In short, LINQ to SQL is a component of the .NET framework that provides a runtime infrastructure for managing relational data as objects.
In LINQ to SQL, the data model of a relational database is mapped to an object model expressed in the programming language of the developer. 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 the results back to objects that you can work with in your own programming language.
Unlike LINQ to Entities, with LINQ to SQL developers don't need to create an extra data model between their applications and the underlying database. Under the hood of LINQ to SQL, the ADO.NET SqlClient adapters are used to communicate with the actual SQL Server databases.
The following diagram shows the use of LINQ to SQL in a .NET application: 



Comparing LINQ to SQL with LINQ to Entities 
 Now we know what LINQ to Entities is and what LINQ to SQL is. Next in this section, let's compare these two technologies.
As described earlier, LINQ to Entities applications work against a conceptual data model (EDM). All mappings between the languages and the databases go through the new EntityClient mapping provider. The application no longer connects directly to a database or sees any database-specific constructs. The entire application operates in terms of the higher level EDM. 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 EDM such as inheritance, relationships, complex types, and so on. On the other hand, for developers who do not require mapping to a conceptual model, LINQ to SQL enables developers to experience the LINQ programming model directly over the existing database schema. LINQ to SQL allows developers to generate the .NET classes that represent data. Rather than mapping to a conceptual data model, these generated classes are mapped 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 was previously described for in-memory collections, entities, or the dataset, as well as for other data sources such as XML. Compared to LINQ to Entities, LINQ to SQL has some limitations, mainly because of its direct mapping against the physical relational storage schema. For example, you cannot map two different database entities into one single C# or VB object and if the underlying database schema changes, this might require significant client application changes.

To summarize, if you want to work against a conceptual data model, use LINQ to Entities. If you want to have a direct mapping to the database from your programming languages, use LINQ to SQL. The following table lists some of the features supported 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


Interestingly, some people say LINQ to SQL was an intermediate solution. The fact is that LINQ to SQL was created by the C# team instead of the ADO.NET team. It was of great importance for the C# team to release an O/RM mapper together with their new LINQ technology. Without a LINQ to databases implementation, the C# team would have had a hard time evangelizing LINQ.
Some Best readings about LINQ: LINQ INTERESTING READINGS




WHAT IS LINQ PROVIDER

A LINQ provider is software that implements the IQueryProvider and IQueryable interfaces for a particular data store. In other words, it allows you to write LINQ queries against that data store. For example, LINQ to Active Directory ;which allows us to write Ling Queries against ACTIVE DIRECTORY or LINQ to XML provider allows you to write LINQ  queries against XML documents
 Here you can see series of posts for building Providers provider.
Complete list of posts in the Building an IQueryable Provider series 

Index

Core Microsoft LINQ Sites

Official Microsoft Flavors of LINQ

LINQ Providers

LINQ and Other Languages

Blogs and More LINQ Links

Third Party LINQ Books and Documents