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
Sum in Lambda Expressions
using System;
using System.Linq;
using LearnEntityFrameworkCoreWithRealApps.Models;
namespace LearnEntityFrameworkCoreWithRealApps
{
class Program
{
static void Main(string[] args)
{
var db = new LearnEntityFrameworkCoreDB();
var sum1 = db.Products.Sum(p => p.Quantity);
Console.WriteLine("Sum Quantities: " + sum1);
var sum2 = db.Products.Where(p => p.Status == true).Sum(p => p.Quantity);
Console.WriteLine("Conditions in Sum Quantities: " + sum2);
var sum3 = db.Products.Sum(p => p.Price * p.Quantity);
Console.WriteLine("Total: " + sum3);
Console.ReadLine();
}
}
}
Output
Sum Quantities: 58
Conditions in Sum Quantities: 21
Total: 1252.0000