Difference between IQueryable, ICollection, IList & IDictionary interface

Question

I am trying to understand difference between IQueryable, ICollection, IList & IDictionary interface which is more faster for basic operations like iterating, Indexing, Querying and more.

which class like Collection, List, Dictionary etc would be good to initiating with these interfaces and when should we be using these class. Basic advantages of using these classes over others.

I tried reading other posts with similar questions but nothing answered my full questions. Thanks for the help.

Solution

I noticed few issues in @gunny229's answer. I've mentioned those issues in comment area of his post. Later on, I thought to write a more detailed post to connect some missing dots.

Disclaimer: I don't intend to cater OP's question in entirety but I want to point out the difference between IQueryable and IEnumerable when using LINQ to SQL.

I created following structure in DB (DDL script): CREATE TABLE [dbo].[Employee]([PersonId] [int] NOT NULL PRIMARY KEY,[Salary] [int] NOT NULL)

Here is the record insertion script (DML script):

INSERT INTO [EfTest].[dbo].[Employee] ([PersonId],[Salary])VALUES(1, 20)
INSERT INTO [EfTest].[dbo].[Employee] ([PersonId],[Salary])VALUES(2, 30)
INSERT INTO [EfTest].[dbo].[Employee] ([PersonId],[Salary])VALUES(3, 40)
INSERT INTO [EfTest].[dbo].[Employee] ([PersonId],[Salary])VALUES(4, 50)
INSERT INTO [EfTest].[dbo].[Employee] ([PersonId],[Salary])VALUES(5, 60)
GO

Now my goal was to get top 2 records from Employee table in database. So, I added an ADO.NET Entity Data Model item into my console application pointing to Employee table in my database and started writing LINQ queries.

Code for IQueryable route:

using (var efContext = new EfTestEntities())
{
    IQueryable<int> employees = from e in efContext.Employees  select e.Salary;
    employees = employees.Take(2);

    foreach (var item in employees)
    {
        Console.WriteLine(item);
    }
}

When I started to run this program, I had also started a session of SQL Query profiler on my SQL Server instance and here is the summary of execution:

1. Total number of queries fired: 1
2. Query text: SELECT TOP (2) [c].[Salary] AS [Salary] FROM [dbo].[Employee] AS [c]

It is just that IQueryable is smart enough to apply the Top (2) clause on database server side itself so it brings only 2 out of 5 records over the wire. Any further in-memory filtering is not required at all on client computer side.

Code for IEnumerable route:

using (var efContext = new EfTestEntities())
{
    IEnumerable<int> employees = from e in efContext.Employees  select e.Salary;
    employees = employees.Take(2);

    foreach (var item in employees)
    {
        Console.WriteLine(item);
    }
}

Summary of execution in this case:

1. Total number of queries fired: 1
2. Query text captured in SQL profiler: SELECT [Extent1].[Salary] AS [Salary] FROM [dbo].[Employee] AS [Extent1]

Now the thing is that IEnumerable brought all the 5 records present in Salary table and then performed an in-memory filteration on the client computer to get top 2 records. So more data (3 additional records in this case) got transferred over the wire unnecessarily.

Comments

[Ε Г И І И О]

Thanks, but I don't think @gunny229 's answer is anyway misleading. Yes he used the word 'query' but that's not in the context of a database. It's just another computation to get the top two. At least that's what I figured when reading it. Cheers.