Entity Framework vs. LINQ to SQL(edmx vs dbml)

Hi Friends,


This post is regarding the difference between LINQ to SQL vs. Entity Framework.
Both are introduced as latest technologies and at times a bit confusing when to use which. Entity Framework and LINQ to SQL have a lot in common but still different from each other in quite a few ways:

Entity Framework:
1. Enterprise Development
2. Works with Conceptual model of database
3. Works with all data sources
4. ".EDMX" is created while using Entity Framework

LINQ:
1. Rapid Application Development
2. Works with objects in database
3. Mainly woks with SQL Server
4. ".dbml" is created while using LINQ to SQL

Entity Framework is more targeted towards Enterprise Development where the schema is usually optimized for storage considerations like performance consistency and partitioning. Entity Framework is designed around exposing an application-oriented data model that is loosely coupled and may differ from the existing database schema. For example, you can map a single entity (class) to multiple or map multiple entities to the same table. Entity Framework has “.edmx” (ADO.NET Entity Model) file when added in the application.

LINQ to SQL mainly has features to support Rapid Application Development against SQL Server. LINQ to SQL allows you to have a strongly typed view of your existing database schema. You can build LINQ queries over tables and return results as strong typed objects. LINQ to SQL has “.dbml”(LINQ to SQL) file when added in the application. You can use LINQ to SQL by decorating the existing classes with the attributes.

Please review: Entity Framework Basics

Thanks,
Paras Sanghani

Entity Framework Best Practices

Dear Friends,


This post is regarding some of points to be considered while writing the LINQ Queries which may affect the performance alot.






For Beginners in Entity Framework, Please review: Entity Framework Basics

Kindly, find all the below points which can increase the performance:

Compiled queries
When you have an application that executes structurally similar queries many times in the Entity Framework, you can frequently increase performance by compiling the query one time and executing it several times with different parameters. For example, an application might have to retrieve all the customers in a particular city; the city is specified at runtime by the user in a form. LINQ to Entities supports using compiled queries for this purpose.
use Compiled queries to amortize the overhead inherent in SQL generation.
The CompiledQuery class provides compilation and caching of queries for reuse.
For Example:
// Compiled Query
public static Func> _statesByCountry =
CompiledQuery.Compile((Entities db, int CountryID) => db.States.Where(s => s.CountryID == CountryID));

//Using Compiled Query
public virtual IEnumerable GetStatesByCountryID(int countryID)
{
return _ statesByCountry (Context, countryID).ToList();
}

Select N+1
Select N + 1 is a data access anti-pattern where the database is accessed in a suboptimal way. Suppose that want to show all Sites Information from all schools. The native implementation would be something like:
var productQuery = from product in _ctx. Product
select Product;

foreach (Product product in productQuery)
{
         //lazy loading of comments list causes:
        // SELECT * FROM Sites where SchoolId = @SchoolId
        product.ProductDetails.Load();
       foreach (ProdutDetail productDetail in product.ProductDetails)
       {
       //print comment...
       }
}

In this example, we can see that we are loading a list of Product (the first select) and then traversing the object graph. However, we access the collection in a lazy fashion, causing Entity Framework to go to the database and bring the results back one row at a time. This is incredibly inefficient.
The solution for this example is simple. Force an eager load of the collection using the Include method to specify what pieces of the object model we want to include in the initial query.
// SELECT * FROM Product JOIN ProductDetail ..
var productQuery = (from product in _ctx.Product.Include("ProductDetail")
                               select product);

foreach (Product product in productQuery)
{
             // no lazy loading of comments list causes
            foreach (ProdutDetail productDetail in product.ProductDetails)
            {
             //print comment...
            }
}
In this case, we will get a join and only a single query to the database.
Note: this is the classical appearance of the problem. It can also surface in other scenarios, such as calling the database in a loop, or more complex object graph traversals. In those cases, it is generally much harder to see what is causing the issue.

Avoid too many joins
Queries with too many joins might be a performance problem.
Each join requires the database to perform additional work, and the complexity and cost of the query grows rapidly with each additional join. While relational database are optimized for handling joins, it is often more efficient to perform several separate queries instead of a single query with several joins in it.
For OLTP systems, you should consider simplifying your queries or simplifying the data model. While we do not recommend avoiding joins completely, we strongly discourage queries with large numbers of joins. Another issue to pay attention to is possible Cartesian products in queries contains joins, it is very easy to create such a thing and not notice it during development.

Avoid Too Many Database Calls Per Session
One of the most expensive operations that we can do in our applications is to make a remote call. Going beyond our own process is an extremely expensive operation. Going beyond the local machine is more expensive still.
Calling the database, whether to query or to write, is a remote call, and we want to reduce the number of remote calls as much as possible.
There are several reasons why this can be:

1. A large number of queries as a result of a Select N + 1
2. Calling the database in a loop
3. Updating (or inserting / deleting) a large number of entities
4. A large number of (different) queries that we execute to perform our task

For the first reason, you can see the suggestions for Select N + 1.
Calling the database in a loop is generally a bug, and should be avoided. Usually you can restructure the code in such a way that you are not required to call the database in that way. All of the above points must taken into consideration while writing the Complex LINQ Queries. 
 


Thanks,
Paras Sanghani

LINQ --- An interesting Technology...

Hello Friends,
Linq is very interesting Technology introduced in .Net Framework 3.5.
LINQ stands for Language Integrated Query.
Over View Of LINQ:

• LINQ provides query functionality directly in C# programming languages as we do in Sql Server
• LINQ queries are language constructs which are similar to any other defined code block, such as methods and class definations.
• We can run LINQ queries against any .NET Framework collection that implements IEnumerable, IEnumerable(T), or any collection that implements an interface that inherits from IEnumerable(T), blocks of XML and XML Documents, SQL Server databases, and ADO.NET datasets.

LINQ Providers:
There are mainly four types of LINQ Providers available:

1. LINQ to Objects
2. LINQ to XML
3. LINQ to SQL
4. LINQ to DataSets

Besides the above four, Other Providers is also supported.
LINQ Queries:
The LINQ query by itself defi nes only the shape and structure of the data returned by the
query, not the actual data. The data is available after executing the query. The process of
targeting a data source with a LINQ query and accessing the data has three stages:
1. The data source
2. Query creation
3. Query execution
The Data SourceThe data source is any .NET Framework collection that implements IEnumerable,
IEnumerable(T), or any collection that implements an interface that inherits from
IEnumerable(T), Blocks of XML and XML Documents, SQL Server databases, and ADO.NET
DataSets.
Query CreationQuery creation is the LINQ query that determines the data to retrieve from the data source.
In addition to the actual data to return, LINQ queries can also specify additional information,
such as sort order, and grouping of the returned data.
The following code shows a LINQ query that returns all buttons on a form:
var buttonsQuery =
from Control buttons in this.Controls
where (buttons is Button)
orderby buttons.Text
select buttons;
The variable that will contain the results of the query is called the range variable. In the
above example, the variable is named buttonsQuery.
Query ExecutionQuery execution is typically deferred until the query is iterated over with a foreach loop. In
the case of aggregate functions, such as Sum, Count, and Average, these queries execute
immediately and do not need to be iterated over.
To force immediate execution of a query, call the ToList or ToArray methods of the query’s
range variable, as shown in the following example.
var buttonsQuery =
from Control buttons in this.Controls
where (buttons is Button)
orderby buttons.Text
select buttons.ToList();
In the example LINQ query shown above, you force the query to execute immediately by
calling buttons.ToList().


Thanks,
Paras Sanghani