Write a linq query to get second highest number?

var query = (from e in Employees
            group e by e.Salary into t
            orderby t.Key descending 
        
            select new
            {
             t
             
            }).Skip(1).First();
            
            query.Dump();
            
            
            

Write a linq query for given sql query

select Name,Address from Employee e
left join Department d on e.EmpId =d.EmpId
where e.Status =1

----------------------------------------------------
create table Employee
(
  EmpId int,
  Name varchar(100),
  Address varchar(100),
  Status bit
)



insert into Employee(EmpId,Name,Address,Status) values (1,'Ashish','chandigarh',1), (2,'Pankaj','Ghaziabad',1), (3,'Rahil','Delhi',1)


create table Department
(
  ID int,
  EmpId int,
  DeptName varchar(100)

)


insert into Department(ID,EmpId,DeptName) values (1,1,'IT'), (2,2,'Bank'), (5,5,'CA')



var query = (from e in Employees
            join d in Departments on e.EmpId equals d.EmpId into tmp
from t in tmp.DefaultIfEmpty()
where   e.Status == true
select new
{
 Name = e.Name,
 Address = e.Address

}).ToList();


query.Dump();

Difference Between IEnumerable, ICollection and IList Interface in C#

Introduction

In this article, we will understand:

• What is IEnumerable interface and when to use it?
• What is ICollection interface and when to use it?
• What is IList Interface and when to use it?

Description

IEnumerable, ICollection and IList are interfaces in the .NET Framework used the most often. IEnumerable is the base of the ICollection and IList interfaces (and many others). All these interfaces provide various functionalities and are useful in various cases.

IEnumerable Interface

There are two different interfaces defined in the .NET base class library.There is a non-generic IEnumerableinterface and there is a generic type-safe IEnumerable<T> interface.

• IEnumerable Interface exists in System.Collections Namespace.
• IEnumerable interface is used when we want to iterate among our classes using a foreach loop.
• IEnumerable can move forward only over a collection, it can’t move backward and between the items.
• IEnumerable is best to query data from in-memory collections like List, Array, etc.
• IEnumerable is used for LINQ to Object and LINQ to XML queries.
• IEnumerable supports deferred execution.

IEnumerable

IEnumerable interface contains only a single method definition i.e., GetEnumerator() and the GetEnumerator method must return an instance of an object of a class which implements the IEnumeratorinterface

Hide   Copy Code

public interface IEnumerable
{
IEnumerator GetEnumerator();
}

The IEnumerator interface implements two methods, MoveNext() and Reset() and it also has one property called Current that returns the current element in the list.

• Initially, the enumerator is positioned before the first element in the collection. You must call the MoveNext method to advance the enumerator to the first element of the collection before reading the value of Current; otherwise, Current is undefined.
• Current returns the same object until either MoveNext or Reset is called.
• MoveNext sets Current to the next element.
• The Reset method is provided for COM interoperability.
• If MoveNext passes the end of the collection, the enumerator is positioned after the last element in the collection and MoveNext returns false. When the enumerator is at this position, subsequent calls to MoveNext also return false. If the last call to MoveNext returned false, calling Current throws an exception.
• To set Current to the first element of the collection again, you can call Reset
• An enumerator remains valid as long as the collection remains unchanged.
• If changes are made to the collection, such as adding, modifying, or deleting elements, the enumerator is irrecoverably invalidated and the next call to MoveNext or Reset throws an InvalidOperationException.
• If the collection is modified between MoveNext and Current, Current returns the element that it is set to, even if the enumerator is already invalidated.

IEnumerable<T>

IEnumerable<T> is a generic type-safe interface and is located in the System.Collections.Genericnamespace.
IEnumerable<T> inherits from IEnumerable Interface.
IEnumerable<T> defines a single method GetEnumerator which returns an instance of an object that implements the IEnumerator<T> interface. 

Hide   Copy Code

public interface IEnumerable<out T> : IEnumerable
{
IEnumerator GetEnumerator();
}

ICollection Interface

There are two different interfaces defined in the .NET base class library. There is a non-generic ICollection interface and there is a generic type-safe ICollection interface.

• The ICollection Interface is inherited from the IEnumerable interface which means that any class that implements the ICollection Interface can also be enumerated using a foreach loop.
• In the IEnumerable Interface, we don’t know how many elements there are in the collection whereas the ICollection interface gives us this extra property for getting the count of items in the collection.

ICollection

Defines size, enumerators, and synchronization methods for all nongeneric collections.

Hide   Copy Code

public interface ICollection : IEnumerable
{
int count { get; }
bool IsSynchronized { get; }
Object SyncRoot { get; }
void CopyTo(Array array,int index);
}

The ICollection interface contains the following properties:

• Count Property
• IsSynchronized Property
• SyncRoot Property
• CopyTo Method

The Count property is used for maintaining the count of elements in the list.

The IsSynchronized and SyncRoot properties help to make the collection thread-safe.

The CopyTo method copies the entire collection into an array.

The generic version of this interface provides Add and Remove methods also.

ICollection<T>

This is the generic version of ICollection Interface.

Defines methods to manipulate generic collections.

Hide   Copy Code

public interface ICollection : IEnumerable, IEnumerable
{
int count { get; }
bool IsReadOnly { get; }
void Add(T item);
void Clear();
bool Contains(T item);
void CopyTo(T[] array, it arrayIndex);
bool Remove(T item);
}

IList Interface

• IList exists in System.Collections Namespace.
• IList Interface implements both IEnumerable and ICollection Interface.
• IList is used to access an element in a specific position/index in a list.
• Like IEnumerable, IList is also best to query data from in-memory collections like List, Array, etc.
• IList is useful when you want to add or remove items from the list.
• IList can find out the number of elements in the collection without iterating the collection.
• IList supports deferred execution.
• IList doesn’t support further filtering.

The IList also has generic and non generic version, i.e., IList and IList.

IList

• IList implements ICollection and IEnumerable.
• IList provides method definitions for adding and removing elements and to clear the collection.
• IList provides methods for handling the positioning of the elements within the collection.
• IList provides an object indexer to allow the user to access the collection with square brackets.

Hide   Copy Code

public interface IList : ICollection,IEnumerable
{
bool IsFixedSize { get; }
bool IsReadOnly { get; }
Object this[int index] { get;set; }
int Add(Object value);
int IndexOf(Object value);
void Insert(intindex,object value);
void Remove(Object value);
void RemoveAt(int index);
}

The IList interface contains the following:

1. IsFixedSize Property
2. IsReadOnly Property
3. Indexer
4. Add Method
5. Clear Method
6. Contains Method
7. Indexof Method
8. Insert Method
9. Remove Method
10. RemoveAt Method

The IList interface has one indexer by which we can access any element by its position and can insert an element and remove an element at any position.

IList<T>

IList<T> is a generic type-safe interface and is located in the System.Collections.Generic namespace.
IList<T> inherits from IEnumerable and ICollection Interface.

Hide   Copy Code

public interface IList : ICollection, IEnumerable, IEnumerable
{
T this[int index] { get; set; }
int IndexOf(T item);
void Insert(int index, T item);
void RemoveAt(int index);
}

When to Use?

Now since we know all interfaces, the next question we have is when we have to use which interface?

The important point is to use the interface that our application needs us to use.

Interface	Best Practices
IEnumerable, IEnumerable<T>	The only thing you want is to iterate over the elements in a collection.
	You only need read-only access to that collection.
ICollection, ICollection<T>	You want to modify the collection or you care about its size.
IList, IList<T>	You want to modify the collection and you care about the ordering and / or positioning of the elements in the collection.
List, List<T>	As per DIP, you should depend on abstractions instead of implementations, you should never have a member of your own implementations with the concrete type List/List.

Summary

In this article, we have discussed IEnumerable, ICollection and IList Interfaces and best practices.