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
Like Clause in Lambda Expressions
using System;
using System.Collections.Generic;
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("Start with mob string");
var productList1 = db.Products.Where(p => p.Name.StartsWith("mob")).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("\nEnds with top 1 string");
var productList2 = db.Products.Where(p => p.Name.EndsWith("top 1")).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("\nContains bi string");
var productList3 = db.Products.Where(p => p.Name.Contains("bi")).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
Start with mob string
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
==========================
Id: 3
Name: Mobile 3
Price: 26.0000
Quantity: 9
Status: True
Creation Date: 11/14/2017
Category Id: 1
Category Name: Mobile
==========================
Ends with top 1 string
Id: 4
Name: Laptop 1
Price: 15.0000
Quantity: 7
Status: True
Creation Date: 06/10/2011
Category Id: 2
Category Name: Laptop
==========================
Contains bi string
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
==========================
Id: 3
Name: Mobile 3
Price: 26.0000
Quantity: 9
Status: True
Creation Date: 11/14/2017
Category Id: 1
Category Name: Mobile
==========================