DateTime in Lambda Expressions in Entity Framework

Create Database

Create a database with the name is LearnEntityFramework. 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

ADO.NET Entity Data Model

Use the Entity Wizard to create an Entity Data Model From Database in Visual Studio.

Structure of Product

Date and Time in Lambda Expressions

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LearnEntityFramework
{
    class Program
    {
        static void Main(string[] args)
        {
            LearnEntityFrameworkDB db = new LearnEntityFrameworkDB();

            Console.WriteLine("Use Year, Month and Day in DateTime");
            var productList1 = db.Products.Where(p => p.CreationDate.Value.Year == 2016 && p.CreationDate.Value.Month == 11 && p.CreationDate.Value.Day == 20).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.Value.ToString("MM/dd/yyyy"));
                Console.WriteLine("Category Id: " + product.Category.Id);
                Console.WriteLine("Category Name: " + product.Category.Name);
                Console.WriteLine("==========================");
            }

            Console.WriteLine("\nCompare Date");
            var productList2 = db.Products.Where(p => p.CreationDate.Value >= new DateTime(2017, 12, 15) && p.CreationDate.Value <= new DateTime(2017, 12, 25)).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.Value.ToString("MM/dd/yyyy"));
                Console.WriteLine("Category Id: " + product.Category.Id);
                Console.WriteLine("Category Name: " + product.Category.Name);
                Console.WriteLine("==========================");
            }

            Console.WriteLine("\nSubstract Date");
            var passedDate = DateTime.Now.AddDays(-3);
            Console.WriteLine(passedDate.ToShortDateString());
            var productList3 = db.Products.Where(p => DbFunctions.TruncateTime(p.CreationDate.Value) == DbFunctions.TruncateTime(passedDate)).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.Value.ToString("MM/dd/yyyy"));
                Console.WriteLine("Category Id: " + product.Category.Id);
                Console.WriteLine("Category Name: " + product.Category.Name);
                Console.WriteLine("==========================");
            }

            Console.ReadLine();
        }
    }
}

Output

Use Year, Month and Day in DateTime
Id: 6
Name: Tivi 1
Price: 18.0000
Quantity: 11
Status: True
Creation Date: 11/20/2016
Category Id: 3
Category Name: Tivi
==========================

Compare Date
Id: 1
Name: Mobile 1
Price: 10.0000
Quantity: 2
Status: True
Creation Date: 12/20/2017
Category Id: 1
Category Name: Mobile
==========================
Id: 2
Name: Mobile 2
Price: 24.0000
Quantity: 4
Status: False
Creation Date: 12/21/2017
Category Id: 1
Category Name: Mobile
==========================

Substract Date
12/21/2017
Id: 2
Name: Mobile 2
Price: 24.0000
Quantity: 4
Status: False
Creation Date: 12/21/2017
Category Id: 1
Category Name: Mobile
==========================