Linq query for find the 2nd Highest salary..

var employee = Employees
    .OrderByDescending(e => e.Salary)
    .Skip(1)
    .First();

If inside your table contain duplicate records means multiple employees may have equal salary and you wish to return an IEnumerable of all the employees with the second-highest salary you could do:

var employees = Employees
    .GroupBy(e => e.Salary)
    .OrderByDescending(f => f.Key)
    .Skip(1)
    .First();

CRUD Operations using LINQ Entities

LINQ In-memory Commits and Physical Commits

Entity objects form the base of LINQ technologies. So when any data is submitted to database, it goes through the LINQ objects. Database operations are done through ‘DataContext’ class. As said previously, entities form the base of LINQ, so all the data is sent to these entities first and then it's routed to the actual physical database. Due to this nature of working database commits is a two step process. The first step is in-memory and final step is physical commits.
In order to do in-memory operation ‘DataContext’ has provided ‘DeleteOnSubmit’ and ‘InsertOnSubmit’ methods. When we call these methods from the ‘DataContext’ class, they add and update data in the entity objects memory. Please note these methods do not change / add new data in the actual database.

Once we are done with the in-memory operations and we want to send all the updates to the database, we need to call ‘SubmitChanges()’ method. This method finally commits data into the physical database.

So let’s consider a customer table (customerid, customercodeand customername) and see how we can do the in-memory and physical commit operations.
Step 1: Create the Entity Customer Class

So as a first step, we create the entity of customerclass as shown in the below code snippet.

[Table(Name = "Customer")]
public class clsCustomerEntity
{
private int _CustomerId;
private string _CustomerCode;
private string _CustomerName;

[Column(DbType = "nvarchar(50)")]
public string CustomerCode
{
set
{
_CustomerCode = value;
}
get
{
return _CustomerCode;
}
}
[Column(DbType = "nvarchar(50)")]
public string CustomerName
{
set
{
_CustomerName = value;
}
get
{
return _CustomerName;
}
}
[Column(DbType = "int", IsPrimaryKey = true,IsDbGenerated=true)]
public int CustomerId
{
set
{
_CustomerId = value;
}
get
{
return _CustomerId;
}
}
}

Step 2: Create using LINQ
Create Data Context

So the first thing is to create a ‘datacontext’ object using the connection string.

DataContext objContext = new DataContext(strConnectionString);

Set the Data for Insert

Once you create the connection using the ‘DataContext’ object, the next step is to create the customer entity object and set the data to the object properties.
e

clsCustomerEntity objCustomerData = new clsCustomerEntity();
objCustomerData.CustomerCode = txtCustomerCode.Text;
objCustomerData.CustomerName = txtCustomerName.Text;

Do an In-memory Update

We then do an in-memory update in entity objects itself using ‘InsertOnSubmit’ method.

objContext.GetTable<clsCustomerEntity>().InsertOnSubmit(objCustomerData);

Do the Final Physical Commit

Finally we do a physical commit to the actual database. Please note until we call ‘SubmitChanges()’, data is not finally committed to the database.

objContext.SubmitChanges();

The Final Create LINQ Code

Below is the final LINQ code put together:

DataContext objContext = new DataContext(strConnectionString);
clsCustomerEntity objCustomerData = new clsCustomerEntity();
objCustomerData.CustomerCode = txtCustomerCode.Text;
objCustomerData.CustomerName = txtCustomerName.Text;
objContext.GetTable<clsCustomerEntity>().InsertOnSubmit(objCustomerData);
objContext.SubmitChanges();

Step 3: Update using LINQ

So let’s take the next database operation, i.e. update.
Create Data Context

As usual we first need to create a ‘datacontext’ object using the connection string as discussed in the create step:

DataContext objContext = new DataContext(strConnectionString);

Select the Customer LINQ Object Which we want to Update

Get the LINQ object using LINQ query which we want to update:
ode

var MyQuery = from objCustomer in objContext.GetTable<clsCustomerEntity>()
where objCustomer.CustomerId == Convert.ToInt16(txtCustomerId.Text)
select objCustomer;

Finally Set New Values and Update Data to Physical Database

Do the updates and call ‘SubmitChanges()’ to do the final update.

clsCustomerEntity objCustomerData =
    (clsCustomerEntity)MyQuery.First<clsCustomerEntity>();
objCustomerData.CustomerCode = txtCustomerCode.Text;
objCustomerData.CustomerName = txtCustomerName.Text;
objContext.SubmitChanges();

The Final Code of LINQ Update

Below is what the final LINQ update query looks like.

DataContext objContext = new DataContext(strConnectionString);
var MyQuery = from objCustomer in objContext.GetTable<clsCustomerEntity>()
where objCustomer.CustomerId == Convert.ToInt16(txtCustomerId.Text)
select objCustomer;
clsCustomerEntity objCustomerData =
    (clsCustomerEntity)MyQuery.First<clsCustomerEntity>();
objCustomerData.CustomerCode = txtCustomerCode.Text;
objCustomerData.CustomerName = txtCustomerName.Text;
objContext.SubmitChanges();

Step 4: Delete using LINQ

Let’s take the next database operation delete.

DeleteOnSubmit

We will not be going through the previous steps like creating data context and selecting LINQ object. Both of them are explained in the previous section. To delete the object from in-memory, we need to call ‘DeleteOnSubmit()’ and to delete from final database, we need use ‘SubmitChanges()’.
Hide   Copy Code

objContext.GetTable<clsCustomerEntity>().DeleteOnSubmit(objCustomerData);
objContext.SubmitChanges();

Step 5: Self Explanatory LINQ Select and Read

Now in the final step, selecting and reading the LINQ object by criteria. Below is the code snippet which shows how to fire the LINQ query and set the object value to the ASP.NET UI.
y Code

DataContext objContext = new DataContext(strConnectionString);

var MyQuery = from objCustomer in objContext.GetTable<clsCustomerEntity>()
where objCustomer.CustomerId == Convert.ToInt16(txtCustomerId.Text)
select objCustomer;

clsCustomerEntity objCustomerData =
    (clsCustomerEntity)MyQuery.First<clsCustomerEntity>();
txtCustomerCode.Text = objCustomerData.CustomerCode;
txtCustomerName.Text = objCustomerData.CustomerName;

Understanding Var and IEnumerable with LINQ

In this article I would like to share my opinion on Var and IEnumerable with LINQ. IEnumerable is an interface that can move forward only over a collection, it can’t move backward and between the items. Var is used to declare implicitly typed local variable means it tells the compiler to figure out the type of the variable at compilation time. A var variable must be initialized at the time of declaration. Both have its own importance to query data and data manipulation.

Var Type with LINQ

Since Var is anonymous types, hence use it whenever you don't know the type of output or it is anonymous. In LINQ, suppose you are joining two tables and retrieving data from both the tables then the result will be an Anonymous type.

 var q =(from e in tblEmployee 
 join d in tblDept on e.DeptID equals d.DeptID 
 select new 
 { 
 e.EmpID,
 e.FirstName,
 d.DeptName,
 }); 

In above query, result is coming from both the tables so use Var type.

 var q =(from e in tblEmployee where e.City=="Delhi" select new { 
 e.EmpID,
 FullName=e.FirstName+" "+e.LastName, 
 e.Salary 
}); 

In above query, result is coming only from single table but we are combining the employee's FirstName and LastName to new type FullName that is annonymous type so use Var type. Hence use Var type when you want to make a "custom" type on the fly.
More over Var acts like as IQueryable since it execute select query on server side with all filters. Refer below examples for explanation.

IEnumerable Example

 MyDataContext dc = new MyDataContext ();
IEnumerable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));
list = list.Take<Employee>(10); 

Generated SQL statements of above query will be :

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

Notice that in this query "top 10" is missing since IEnumerable filters records on client side

Var Example

 MyDataContext dc = new MyDataContext ();
var list = dc.Employees.Where(p => p.Name.StartsWith("S"));
list = list.Take<Employee>(10); 

Generated SQL statements of above query will be :

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

Notice that in this query "top 10" is exist since var is a IQueryable type that executes query in SQL server with all filters.

IEnumerable Type with LINQ

IEnumerable is a forward only collection and is useful when we already know the type of query result. In below query the result will be a list of employee that can be mapped (type cast) to employee table.

 IEnumerable<tblEmployee> lst =(from e in tblEmployee 
where e.City=="Delhi" 
select e); 

Note

1. In LINQ query, use Var type when you want to make a "custom" type on the fly.
2. In LINQ query, use IEnumerable when you already know the type of query result.
3. In LINQ query, Var is also good for remote collection since it behaves like IQuerable.
4. IEnumerable is good for in-memory collection.

IEnumerable vs. ICollection vs. IQueryable vs. IList

Collections are used quite often in applications and C# have different types of collection. Here are the subtle differences between collection types and choose appropriate type based on your needs.
IEnumerable: Provides Enumerator for accessing collection

• Used where you want to store a collection of objects which will be accessed only for read-only purpose.
• You need to iterate through the collection, means to access an element at position 5 you first need to access 0-4 objects.
• Cannot modify the list i.e. add, remove object operations not allowed.

ICollection:

• List can be modified and iterated i.e. read, add, delete, edit operation allowed.
• Operations like Sort are not allowed

IQueryable:

• a special type because it allows deferred query execution i.e. if you define any query over IQueryable collection then it won’t execute till the time GetEnumerator() is called.
• It is particularly used for Linq queries and in Entity Framework.
• All other collection types brings data from the database to the client side and then apply filter or do operation on that data. However, IQueryable filters data at database level i.e. filtered data is received from database.
• Specific example, In Entity Framework based application, you use IQueryable collection for seed data and if you call collection.SaveChanges() then database update command is not sent directly in fact when you will first try to access the data (or first time you call GetEnumerator on DBContext) then database update commands will be sent by entity framework. Good example of deferred query execution.

IList:

• Used where you need to iterate (read), modify and sort, order a collection
• Random element access allowed i.e. you can directly access an element at index 5 instead of first iterating through 0-4 elements.

These are the major differences between collection types and to explore more please visit following mentioned MSDN documentation.

IEnumerable VS IList

In LINQ to query data from collections, we use IEnumerable and IList for data manipulation. IEnumerable is inherited by IList, hence it has all the features of it and except this, it has its own features. IList has below advantage over IEnumerable. 

IList

1. IList exists in System.Collections Namespace.
2. IList is used to access an element in a specific position/index in a list.
3. Like IEnumerable, IList is also best to query data from in-memory collections like List, Array etc.
4. IList is useful when you want to Add or remove items from the list.
5. IList can find out the no of elements in the collection without iterating the collection.
6. IList supports deferred execution.
7. IList doesn't support further filtering.

IEnumerable

1. IEnumerable exists in System.Collections Namespace.
2. IEnumerable can move forward only over a collection, it can’t move backward and between the items.
3. IEnumerable is best to query data from in-memory collections like List, Array etc.
4. IEnumerable doesn't support add or remove items from the list.
5. Using IEnumerable we can find out the no of elements in the collection after iterating the collection.
6. IEnumerable supports deferred execution.
7. IEnumerable supports further filtering.

IEnumerable vs IQueryable

Both these interfaces are for .NET collections, 

The first important point to remember is “IQueryable” interface inherits from “IEnumerable”, so whatever “IEnumerable” can do, “IQueryable” can also do.

There are many differences but let us discuss about the one big difference which makes the biggest difference. “IQueryable” interface is useful when your collection is loaded using LINQ or Entity framework and you want to apply filter on the collection.
Consider the below simple code which uses “IEnumerable” with entity framework. It’s using a “where” filter to get records whose “EmpId” is “2”.

IEnumerable<Employee> emp = ent.Employees;

IEnumerable<Employee> temp = emp.Where(x => x.Empid == 2).ToList<Employee>();

This where filter is executed on the client side where the “IEnumerable” code is. 

In other words, 

all the data is fetched from the

 database and then at the client it scans

 and gets the record with “EmpId” is “2”.

But now see the below code we have changed “IEnumerable” to “IQueryable”.

IQueryable<Employee> emp = ent.Employees;

IEnumerable<Employee> temp = emp.Where(x => x.Empid == 2).ToList<Employee>();

In this case, the filter is applied on the database using the “SQL” 
query.  So the client sends a request and on the server side, a select query is fired on

 the database

 and only necessary data is returned.

So the difference between “IQueryable” and “IEnumerable” is about where the filter logic is executed. One executes on the client side and the other executes on the database.
So if you are working with only in-memory data collection “IEnumerable” is a good choice but if you want to query data collection which is connected with database, “IQueryable” is a better choice as it reduces network traffic and uses the power of SQL language.
Below is a nice FB video which demonstrates this blog in a more visual and practical manner.

IEnumerable VS IQueryable

IEnumerable

1. IEnumerable exists in System.Collections Namespace.
2. IEnumerable can move forward only over a collection, it can’t move backward and between the items.
3. IEnumerable is best to query data from in-memory collections like List, Array etc.
4. While query data from database, IEnumerable execute select query on server side, load data in-memory on client side and then filter data.
5. IEnumerable is suitable for LINQ to Object and LINQ to XML queries.
6. IEnumerable supports deferred execution.
7. IEnumerable doesn’t supports custom query.
8. IEnumerable doesn’t support lazy loading. Hence not suitable for paging like scenarios.
9. Extension methods supports by IEnumerable takes functional objects.

IEnumerable Example

 MyDataContext dc = new MyDataContext ();
IEnumerable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));
list = list.Take<Employee>(10); 

Generated SQL statements of above query will be :

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

Notice that in this query "top 10" is missing since IEnumerable filters records on client side

IQueryable

1. IQueryable exists in System.Linq Namespace.
2. IQueryable can move forward only over a collection, it can’t move backward and between the items.
3. IQueryable is best to query data from out-memory (like remote database, service) collections.
4. While query data from database, IQueryable execute select query on server side with all filters.
5. IQueryable is suitable for LINQ to SQL queries.
6. IQueryable supports deferred execution.
7. IQueryable supports custom query using CreateQuery and Execute methods.
8. IQueryable support lazy loading. Hence it is suitable for paging like scenarios.
9. Extension methods supports by IQueryable takes expression objects means expression tree.

IQueryable Example

 MyDataContext dc = new MyDataContext ();
IQueryable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));
list = list.Take<Employee>(10); 

Generated SQL statements of above query will be :

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

Notice that in this query "top 10" is exist since IQueryable executes query in SQL server with all filters.

How to merge result IQueryable together?

If I get two result IQueryable from different linq Query and I want to merge them together and return one as result, how to to this? For example, if:

int[] i1 = new int[] { 1, 2, 3 };
int[] i2 = new int[] { 3, 4 };
//returns 5 values
var i3 = i1.AsQueryable().Concat(i2.AsQueryable());
//returns 4 values
var i4 = i1.AsQueryable().Union(i2.AsQueryable());

Union will only give you the DISTINCT values, Concat will give you the UNION ALL.