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
Join 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 Join");
var productList1 = db.Categories.Join(
db.Products,
category => category.Id,
product => product.CategoryId,
(category, product) => new
{
productId = product.Id,
productName = product.Name,
price = product.Price,
quantity = product.Quantity,
categoryId = category.Id,
categoryName = category.Name
})
.ToList();
foreach (var product in productList1)
{
Console.WriteLine("Product Id: " + product.productId);
Console.WriteLine("Product Name: " + product.productName);
Console.WriteLine("Price: " + product.price);
Console.WriteLine("Quantity: " + product.quantity);
Console.WriteLine("Category Id: " + product.categoryId);
Console.WriteLine("Category Name: " + product.categoryName);
Console.WriteLine("==========================");
}
Console.WriteLine("\nConditions in Join");
var productList2 = db.Categories.Join(
db.Products,
category => category.Id,
product => product.CategoryId,
(category, product) => new
{
productId = product.Id,
productName = product.Name,
price = product.Price,
quantity = product.Quantity,
categoryId = category.Id,
categoryName = category.Name
})
.Where(p => p.price > 24)
.ToList();
foreach (var product in productList2)
{
Console.WriteLine("Product Id: " + product.productId);
Console.WriteLine("Product Name: " + product.productName);
Console.WriteLine("Price: " + product.price);
Console.WriteLine("Quantity: " + product.quantity);
Console.WriteLine("Category Id: " + product.categoryId);
Console.WriteLine("Category Name: " + product.categoryName);
Console.WriteLine("==========================");
}
Console.ReadLine();
}
}
}
Output
Use Join
Product Id: 1
Product Name: Mobile 1
Price: 10.0000
Quantity: 2
Category Id: 1
Category Name: Mobile
==========================
Product Id: 2
Product Name: Mobile 2
Price: 24.0000
Quantity: 4
Category Id: 1
Category Name: Mobile
==========================
Product Id: 3
Product Name: Mobile 3
Price: 26.0000
Quantity: 9
Category Id: 1
Category Name: Mobile
==========================
Product Id: 4
Product Name: Laptop 1
Price: 15.0000
Quantity: 7
Category Id: 2
Category Name: Laptop
==========================
Product Id: 5
Product Name: Laptop 2
Price: 21.0000
Quantity: 16
Category Id: 2
Category Name: Laptop
==========================
Product Id: 6
Product Name: Tivi 1
Price: 18.0000
Quantity: 11
Category Id: 3
Category Name: Tivi
==========================
Product Id: 7
Product Name: Tivi 2
Price: 25.0000
Quantity: 17
Category Id: 3
Category Name: Tivi
==========================
Conditions in Join
Product Id: 3
Product Name: Mobile 3
Price: 26.0000
Quantity: 9
Category Id: 1
Category Name: Mobile
==========================
Product Id: 7
Product Name: Tivi 2
Price: 25.0000
Quantity: 17
Category Id: 3
Category Name: Tivi
==========================