Sort in Lambda Expressions in Entity Framework Core

Create Database

Create a database with the name is LearnEntityFrameworkCore. This database have 2 tables: Category table and Product table. Category table and Product table have a One to Many. One category can have many products and One product belongs to one and only one category.

/* Table structure for table 'category' */

CREATE TABLE Category(
	Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Name varchar(50) NULL
)

/* Dumping data for table `category` */

GO
INSERT Category (Name) VALUES ('Mobile')
INSERT Category (Name) VALUES ('Laptop')
INSERT Category (Name) VALUES ('Tivi')

/* Table structure for table `product` */

GO
CREATE TABLE Product (
	Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Name varchar(50) NULL,
	Price money NULL,
	Quantity int NULL,
	CreationDate date NULL,
	Status bit NULL,
	CategoryId int NULL,
	FOREIGN KEY(CategoryId) REFERENCES Category(Id)
)

/* Dumping data for table `product` */

GO
INSERT Product (Name, Price, Quantity, CreationDate, Status, CategoryId) VALUES ('Mobile 1', 10.0000, 2, '2017-12-20', 1, 1)
INSERT Product (Name, Price, Quantity, CreationDate, Status, CategoryId) VALUES ('Mobile 2', 24.0000, 4, '2017-12-21', 0, 1)
INSERT Product (Name, Price, Quantity, CreationDate, Status, CategoryId) VALUES ('Mobile 3', 26.0000, 9, '2017-11-14', 1, 1)
INSERT Product (Name, Price, Quantity, CreationDate, Status, CategoryId) VALUES ('Laptop 1', 15.0000, 7, '2011-06-10', 1, 2)
INSERT Product (Name, Price, Quantity, CreationDate, Status, CategoryId) VALUES ('Laptop 2', 21.0000, 16, '2011-09-19', 0, 2)
INSERT Product (Name, Price, Quantity, CreationDate, Status, CategoryId) VALUES ('Tivi 1', 18.0000, 11, '2016-11-20', 1, 3)
INSERT Product (Name, Price, Quantity, CreationDate, Status, CategoryId) VALUES ('Tivi 2', 25.0000, 17, '2016-12-05', 0, 3)

Database Diagram

Structure of Category Table

Data of Category Table

Structure of Product Table

Data of Product Table

Create Console App(.NET Core)

On the Visual Studio, create a Console App(.NET Core) project

Enter Project Information:

• Name: LearnEntityFrameworkCoreWithRealApps

Click Ok button to finish create Console App(.NET Core) project

Add Libraries

Use NuGet add Libraries need for Entity Framework Core as below:

• Microsoft.EntityFrameworkCore
• Microsoft.EntityFrameworkCore.Tools
• Microsoft.EntityFrameworkCore.SqlServer
• Microsoft.EntityFrameworkCore.SqlServer.Design
• Microsoft.EntityFrameworkCore.Proxies
• Microsoft.Extensions.Configuration.JSON

Open Manage NuGet Packages

Add Microsoft.EntityFrameworkCore Library

Add Microsoft.EntityFrameworkCore.Tools Library

Add Microsoft.EntityFrameworkCore.SqlServer Library

Add Microsoft.EntityFrameworkCore.SqlServer.Design Library

Add Microsoft.EntityFrameworkCore.Proxies Library

Add Microsoft.Extensions.Configuration.JSON Library

Project After Adding Libraries

Create Entities

Create new folder named Models. In this folder, create new classes as below:

Category Entity

In Models folder, create new class named Category.cs as below:

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;

namespace LearnEntityFrameworkCoreWithRealApps.Models
{
    [Table("Category")]
    public partial class Category
    {
        public Category()
        {
            Products = new HashSet<Product>();
        }

        public int Id { get; set; }
        public string Name { get; set; }

        public virtual ICollection<Product> Products { get; set; }
    }
}

Product Entity

In Models folder, create new class named Product.cs as below:

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

namespace LearnEntityFrameworkCoreWithRealApps.Models
{
    [Table("Product")]
    public partial class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
        public int Quantity { get; set; }
        public DateTime CreationDate { get; set; }
        public bool Status { get; set; }
        public int CategoryId { get; set; }

        public virtual Category Category { get; set; }
    }
}

Create AppSettings File

Create new JSON file named appsettings.json. In appsettings.json file and new configurations as below:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=.;Database=LearnEntityFrameworkCore;user id=sa;password=123456"
  }
}

Create DbContext

In Models folder, create new class named LearnEntityFrameworkCoreDB.cs as below:

using System.IO;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

namespace LearnEntityFrameworkCoreWithRealApps.Models
{
    public partial class LearnEntityFrameworkCoreDB : DbContext
    {
        public virtual DbSet<Category> Categories { get; set; }
        public virtual DbSet<Product> Products { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                var builder = new ConfigurationBuilder()
                                    .SetBasePath(Directory.GetCurrentDirectory())
                                    .AddJsonFile("appsettings.json");
                var configuration = builder.Build();
                optionsBuilder.UseLazyLoadingProxies()
                              .UseSqlServer(configuration["ConnectionStrings:DefaultConnection"]);
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Product>(entity =>
            {
                entity.HasOne(d => d.Category)
                    .WithMany(p => p.Products)
                    .HasForeignKey(d => d.CategoryId)
                    .HasConstraintName("FK_Product_Category");
            });
        }
    }
}

Structure of Project

Sort in Lambda Expressions

using System;
using System.Linq;
using LearnEntityFrameworkCoreWithRealApps.Models;

namespace LearnEntityFrameworkCoreWithRealApps
{
    class Program
    {
        static void Main(string[] args)
        {
            var db = new LearnEntityFrameworkCoreDB();

            Console.WriteLine("Sort by Price Ascending");
            var productList1 = db.Products.OrderBy(p => p.Price).ToList();
            foreach (var product in productList1)
            {
                Console.WriteLine("Id: " + product.Id);
                Console.WriteLine("Name: " + product.Name);
                Console.WriteLine("Price: " + product.Price);
                Console.WriteLine("Quantity: " + product.Quantity);
                Console.WriteLine("Status: " + product.Status);
                Console.WriteLine("Creation Date: " + product.CreationDate.ToString("MM/dd/yyyy"));
                Console.WriteLine("Category Id: " + product.Category.Id);
                Console.WriteLine("Category Name: " + product.Category.Name);
                Console.WriteLine("==========================");
            }

            Console.WriteLine("\nSort by Price Descending");
            var productList2 = db.Products.OrderByDescending(p => p.Price).ToList();
            foreach (var product in productList2)
            {
                Console.WriteLine("Id: " + product.Id);
                Console.WriteLine("Name: " + product.Name);
                Console.WriteLine("Price: " + product.Price);
                Console.WriteLine("Quantity: " + product.Quantity);
                Console.WriteLine("Status: " + product.Status);
                Console.WriteLine("Creation Date: " + product.CreationDate.ToString("MM/dd/yyyy"));
                Console.WriteLine("Category Id: " + product.Category.Id);
                Console.WriteLine("Category Name: " + product.Category.Name);
                Console.WriteLine("==========================");
            }

            Console.WriteLine("\nConditions With Sort by");
            var productList3 = db.Products.Where(p => p.Status == true).OrderByDescending(p => p.Price).ToList();
            foreach (var product in productList3)
            {
                Console.WriteLine("Id: " + product.Id);
                Console.WriteLine("Name: " + product.Name);
                Console.WriteLine("Price: " + product.Price);
                Console.WriteLine("Quantity: " + product.Quantity);
                Console.WriteLine("Status: " + product.Status);
                Console.WriteLine("Creation Date: " + product.CreationDate.ToString("MM/dd/yyyy"));
                Console.WriteLine("Category Id: " + product.Category.Id);
                Console.WriteLine("Category Name: " + product.Category.Name);
                Console.WriteLine("==========================");
            }

            Console.ReadLine();
        }
    }
}

Output

Sort by Price Ascending
Id: 1
Name: Mobile 1
Price: 10.0000
Quantity: 2
Status: True
Creation Date: 12/20/2017
Category Id: 1
Category Name: Mobile
==========================
Id: 8
Name: Tivi 3
Price: 12.0000
Quantity: 3
Status: True
Creation Date: 12/24/2017
Category Id: 3
Category Name: Tivi
==========================
Id: 4
Name: Laptop 1
Price: 15.0000
Quantity: 7
Status: True
Creation Date: 06/10/2011
Category Id: 2
Category Name: Laptop
==========================
Id: 5
Name: Laptop 2
Price: 21.0000
Quantity: 16
Status: False
Creation Date: 09/19/2011
Category Id: 2
Category Name: Laptop
==========================
Id: 2
Name: Mobile 2
Price: 24.0000
Quantity: 4
Status: False
Creation Date: 12/21/2017
Category Id: 1
Category Name: Mobile
==========================
Id: 7
Name: Tivi 2
Price: 25.0000
Quantity: 17
Status: False
Creation Date: 12/05/2016
Category Id: 3
Category Name: Tivi
==========================
Id: 3
Name: Mobile 3
Price: 26.0000
Quantity: 9
Status: True
Creation Date: 11/14/2017
Category Id: 1
Category Name: Mobile
==========================

Sort by Price Descending
Id: 3
Name: Mobile 3
Price: 26.0000
Quantity: 9
Status: True
Creation Date: 11/14/2017
Category Id: 1
Category Name: Mobile
==========================
Id: 7
Name: Tivi 2
Price: 25.0000
Quantity: 17
Status: False
Creation Date: 12/05/2016
Category Id: 3
Category Name: Tivi
==========================
Id: 2
Name: Mobile 2
Price: 24.0000
Quantity: 4
Status: False
Creation Date: 12/21/2017
Category Id: 1
Category Name: Mobile
==========================
Id: 5
Name: Laptop 2
Price: 21.0000
Quantity: 16
Status: False
Creation Date: 09/19/2011
Category Id: 2
Category Name: Laptop
==========================
Id: 4
Name: Laptop 1
Price: 15.0000
Quantity: 7
Status: True
Creation Date: 06/10/2011
Category Id: 2
Category Name: Laptop
==========================
Id: 8
Name: Tivi 3
Price: 12.0000
Quantity: 3
Status: True
Creation Date: 12/24/2017
Category Id: 3
Category Name: Tivi
==========================
Id: 1
Name: Mobile 1
Price: 10.0000
Quantity: 2
Status: True
Creation Date: 12/20/2017
Category Id: 1
Category Name: Mobile
==========================

Conditions With Sort by
Id: 3
Name: Mobile 3
Price: 26.0000
Quantity: 9
Status: True
Creation Date: 11/14/2017
Category Id: 1
Category Name: Mobile
==========================
Id: 4
Name: Laptop 1
Price: 15.0000
Quantity: 7
Status: True
Creation Date: 06/10/2011
Category Id: 2
Category Name: Laptop
==========================
Id: 8
Name: Tivi 3
Price: 12.0000
Quantity: 3
Status: True
Creation Date: 12/24/2017
Category Id: 3
Category Name: Tivi
==========================
Id: 1
Name: Mobile 1
Price: 10.0000
Quantity: 2
Status: True
Creation Date: 12/20/2017
Category Id: 1
Category Name: Mobile
==========================