IEnumerable and IQueriable in c#, know how to use them?

IEnumerable and IQueriable in c# are interfaces for .Net collections. IQueriable inherits from IEnumberable interface that means IQuery can do all the things IEnum does but IEnum can’t do everything that Iquery can do.

Lets look at a simple projects that returns list of customers from the database.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace IEnumerable
{  
    /// <summary>
    ///  Creating a customer entity and mapping it to the table that has exact same columns
    /// </summary>
        [Table("tblCustomer")]
        public class Customer
        {
            [Key]
            public int Id { get; set; }

            public string CustomerType { get; set; }
            public string FullName { get; set; }
            public string PhoneNumber { get; set; }
            public decimal BillAmount { get; set; }
            public DateTime BillDate { get; set; }
            public string Address { get; set; }

        }  
}


//// Create a CustomerDBContext to pull data
using Microsoft.Extensions.Logging;
using Microsoft.EntityFrameworkCore;

namespace IEnumerable
{
    // Customer  context is inheriting DBcontext     
    public class CustomerContext:DbContext
    {
        // logging this to see what is going on in the console
        private readonly ILoggerFactory loggerFactory
            = LoggerFactory.Create(conf => conf.AddConsole());
              

        private readonly string _connStr;
        public CustomerContext(string connStr)
        {
            _connStr = connStr;
            _connStr = connStr;
        }
        
        // Creating simple dbset of Customers 
        public DbSet<Customer> Customers { get; set; }

        //passing the connection String by overriding OnCofiguring To configure the connection in DBContext
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //initialise the logger and connection to the database. 
            optionsBuilder.UseLoggerFactory(loggerFactory);
            optionsBuilder.UseSqlServer(_connStr);
        }
    }
}

///Note make sure to install microsoft.extensions.logging.console from nuget
/// Main Program show working of how IEnumerable and Iqueriable work

using System;
using System.Collections.Generic;
using System.Linq;

namespace IEnumerable
{
    class Program
    {
        static void Main(string[] args)
        {

            // Now lets create the customers
            var context = new CustomerContext("Data Source=localhost;Initial Catalog=PetShopDB;Integrated Security=True");

            // lets see how IEnumerable works
            IEnumerable<Customer> customers = context.Customers.Where(cust => cust.Id > 5);

            /// Query executed for output of IEnumerable          
            /// select *  FROM[tblCustomer] AS[t]
            // WHERE[t].[Id] > 5
            // It returned all customers who has id more than 5


            // When we change to say take top 3 then
            var topCustomers = customers.Take(3);
            
            //Query executed for output of IEnumerable
            /// select *  FROM[tblCustomer] AS[t]
            // WHERE[t].[Id] > 5
            // It returned top 3 customers who has id more than 5
            // This means the query executed is same but it filtered data from onmemory collection.

            //    foreach (var Customer in topCustomers)
            //{
            //    Console.WriteLine($"Name = {Customer.FullName} { Customer.Id}" );
            //}

            /// Now lets test IQueriable 
            /// 
            IQueryable<Customer> qCusts = context.Customers
                .Where(e => e.Id > 5);
            //  FROM [tblCustomer] AS [t]
            //WHERE[t].[Id] > 5
            // showing all the rows that are greater than 5

            var topQCustomers = qCusts.Take(3);
            // SELECT TOP(@__p_0) ....
            // FROM [tblCustomer] AS [t]
            // WHERE[t].[Id] > 5
            // It returned top 3 customers who has id more than 5

            // So in this case the query itsef changed to make it top 3 

            foreach (var Customer in topQCustomers)
            {
                Console.WriteLine($"Name = {Customer.FullName} { Customer.Id}");

            }
        }
    }    

Note: uncomment the required section to test the output

This shows that IEnumerable actually holds the collection in memory and returns the filtered data from the memory whereas if we choose Iqueriable to perform a filter then it actually modifies the query itself and fetches it from the database.

When to use IEnumerable and IQueriable in c# ?

As a practice if you are dealing with in memory data collections then it is better to use IEnumerable

But if you are working with collections connected to databases then IQueriable is better choice because it will reduce the traffic by utilizing the power of SQL queries.