• Execute SQL Query Directly Using Entity Framework
  • Entity Framework - IEnumerator vs IQueryable
  • Entity Framework - Code First v/s Database First
  • What are lambda expressions?
  • What are various implementations of LINQ?
  • When to use IEnumerable vs IQueryable?
  • What is Task Parallel Library?
  • PLinq is supported for what type of Linq implementation?
  • What are Quantifiers in Linq?
  • When to use .First and when to use .FirstOrDefault with LINQ?
  • What operators/ methods can be used for immediate execution?
  • What is deferred execution in Linq?
  • What are the examples of remote execution vs local execution?
  • What kind of exception is thrown in task parallel calls?
  • What is data Parallelism vs Task Parallelism?
  • Between Task.Factory.StartNew and new Task(...).Start ?
  • Event-based Asynchronous Pattern vs Task-based Asynchronous Pattern?
  • What is recommended pattern for asynchronous operations?
  • Can you programmatically cancel a task?

Execute SQL Query Directly Using Entity Framework

Execute SQL query from Entity Framework:

//code sample
int employeeID = 1000;
var sqlQuery = String.Format(@"SELECT TOP 1 EmployeeName FROM Employee
            WHERE EmployeeID = '{0}'    ", employeeID);
var result = dbContext.Database.SqlQuery<string>(sqlQuery);
 


Entity Framework - IEnumerator vs IQueryable

IEnumerator vs IQueryable

 

  IEnumerable IQueryable
  Its Interface supports a simple iteration over a non-generic collection.

Provides functionality for evaluating queries against a specific data source wherein the type of the data is not specified.

IQueryable, which adds two key methods: CreateQuery<T> and Execute<T>. Both of these methods accept an Expression, which is basically a new set of classes that allow you to express things like method calls, operations, lambda functions, etc, as a tree of expressions.

By using IQueryable you can look inside of the LINQ query, and do things with the expression tree, perhaps so your operation now uses the index. The user now gets the benefit of the index, without even needing to know about it!

Best suitable for:
  • In-memory traversal
  • Querying data from in-memory collections like List, Array, etc.
  • Local queries
  • for LINQ to Object and LINQ to XML queries. As long as you have something that implements IEnumerable<T>, you can use LINQ.
  • To query data from out-memory
  • For remote data source - a database or web service (or remote queries)
  • Paging
  • IQueryable is suitable for LINQ to SQL queries, it has query provider (e.g., a LINQ to SQL provider)
Traversal: It is a forward only collection.
  • Can traverse forward and backward in the collection as well as between collection.
  • Query expression representing the IQueryable<T> object as a runtime-traversable expression that can be understood by the given query provider
Returns:

A list that is the actual querying took place and you get the results.

ToList is issued to force running the query and returning these enumerable results...

A "queryable" that is a query you could still be enriched before really sending it to the server.
When to use: Use IEnumerable/ToList if you want to return a list that has been retrieved from the Database. Use IQueryable if you want to return a base query that could be further enhanced before running it server side (by enumerating its items).
Namespace: System.Collection System.Linq
Derives from: No base interface Derives from IEnumerable
Processing Items: IEnumerable can move forward only over a collection, it can’t move backward and between the items. IQueryable can move forward, backward and between the items.
Moving Items: Only supports moving to next item. Can move items in the collection.
Searching: It will process all records one by one in the forward direction, will be done. Perform a faster search.
Filtering: IEnumerable filters record on client side. IQueryable executes select query on the server side with all filters.
Indexing: Indexing is provided by default. Support indexing by default.
How it works: While query data from the database, IEnumerable execute a select query on the server side, load data in memory on the client side and then filter data. While query data from the database, IQueryable execute a select query on the server side and filter data on the server itself.
Performance: Slower that IQueryable Faster that IEnumerable
When to use: When querying data from in-memory collections like List, Array, etc When querying data from out-memory (like remote database, service) collections.
Deferred Execution: IEnumerable supports deferred execution. IQueryable supports deferred execution
Custom Query: IEnumerable doesn’t supports custom query. IQueryable supports custom query using CreateQuery and Execute methods.
Lazy Loading: IEnumerable doesn’t support lazy loading. Hence not suitable for paging like scenarios. IQueryable support lazy loading. Hence it is suitable for paging like scenarios.
Extension method: Extension methods support by IEnumerable takes functional objects. Extension methods support by IQueryable takes expression objects means expression tree.
Usage :
MyDataBaseContext dbC = new MyDataBaseContext ();
IEnumerable list = dbC.Employees.Where(p => p.Name.StartsWith("T"));
list = list.Take(10);

 


MyDataContext dc = new MyDataContext ();
IQueryable list = dc.Employees.Where(p => p.Name.StartsWith("S"));
list = list.Take(10);
SQL Query:
SELECT [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee] AS [t0]
WHERE [t0].[EmpName] LIKE @p0
//@p0 represents 'T%'

SELECT TOP 10 [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee] AS [t0]
WHERE [t0].[EmpName] LIKE @p0

 

  Whatever IEnumerable can do, IQueryable can also do. Whatever IEnumerable can do, IQueryable can also do.

 

 

 

  •  

 

  •  

 

  •  

 

  •  

 

  •  

 

  •  

 

  •  

 

  •  

 

  •  

 

  •  

 

 

  •  

 

 

  •  

 


Entity Framework - Code First v/s Database First

 

Code First Approach

In code first approach we will first create entity classes with properties defined in it. Entity framework will create the database and tables based on the entity classes defined. So database is generated from the code. When the dot net code is run database will get created.

 

Advantages

  1. You can create  the database and tables from your business objects.
  2. You can specify which related collections are to be eager loaded, or not be serialized at all.
  3. Database version control.
  4. Good for small applications.

Disadvantages

  1. You have to write everything related to database in the visual studio code.
  2. For stored procedures you have to map stored procedure using Fluent API and write Stored Procedure inside the code.
  3. If you want to change anything in the database tables you to make changes in the entity classes in the code file and run the update-database from the  package manager console.
  4. Not preferred for Data intensive applications.  

Database First Approach

In this approach Database and tables are created first. Then you create entity Data Model using the created database.

 

Advantages

  1. Simple to create the data model
  2.  Graphical  user interface.
  3.   Mapping and creation of keys and relationships are easy as you need not have to write any code .
  4.  Preferred for data intense and large applications

Disadvantages

  1. Using an existing database to generate a .edmx model file and the associated code models results in a giant pile of auto generated code.
  2. When you need to add any functionality to generated model you have to extend the model class generated.