public static void StaffRecordOnRole()
{
using (DStoreContext context = new DStoreContext())
{
var query = from stafff in context.Set <Staff>() join roles in context.Set <Role>() on stafff.RoleId equals roles.RoleId
join address in context.Set <Address>() on stafff.AddressId equals address.AddressId
where roles.RoleName == "Sales"
select new
{
stafff.FirstName,
stafff.LastName,
stafff.PhoneNumber,
stafff.Salary,
address.AddressLine1,
address.AddressLine2,
address.City,
address.State,
address.Pincode,
roles.RoleName,
roles.Description,
stafff.Gender
};
foreach (var staff in query)
{
Console.WriteLine($"{staff.FirstName},{staff.LastName},{staff.Salary},{staff.PhoneNumber},{staff.Gender},{staff.AddressLine1}{staff.AddressLine2}" +
$"{staff.City}{staff.State}{staff.Pincode}");
}
}
}
public static void DataRole()
{
using (DStoreContext context = new DStoreContext())
{
context.Roles.AddRange(
new Data.Role {
RoleName = "Sales", Description = "sales executive"
},
new Data.Role {
RoleName = "Manager", Description = "Brach manager"
},
new Data.Role {
RoleName = "Help Desk", Description = "For help 24 X 7"
},
new Data.Role {
RoleName = "Security", Description = "Gaurds"
},
new Data.Role {
RoleName = "Reception", Description = "Billing Counter"
},
new Data.Role {
RoleName = "Senior Manager", Description = "Zone manager"
}
);
context.SaveChanges();
Console.WriteLine("data Added Successfully");
}
}
public static void ProductOutOfStock()
{
using (DStoreContext context = new DStoreContext())
{
var query = from product in context.Set <Product>() where product.InStock == false
select(new { product.ProductId });
var count = query.Count();
Console.WriteLine($"No fo product out of stock {count}");
}
}
public static void ProductWithinCategory()
{
using (DStoreContext context = new DStoreContext())
{
var query = from category in context.Set <Category>() join prodcategory in context.Set <ProductCategory>() on
category.CategoryId equals prodcategory.CategoryId join product in context.Set <Product>() on
prodcategory.ProductId equals product.ProductId
group product by category.CategoryId into refGroup
select(new { count = refGroup.Count <Product>(), CategoryId = refGroup.Key });
foreach (var item in query)
{
Console.WriteLine($"{item.CategoryId} {item.count}");
}
}
}
public static void ProductNameCategoryStockPrice()
{
using (DStoreContext context = new DStoreContext())
{
var query = from product in context.Set <Product>() join prodcategory in context.Set <ProductCategory>() on
product.ProductId equals prodcategory.ProductId join category in context.Set <Category>() on
prodcategory.CategoryId equals category.CategoryId
where product.ProductName == "Nestlry" || product.Manufacturer == "ncert" || product.InStock == true || category.CategoryName == "Dairy"
select(new { product, category });
foreach (var items in query)
{
Console.WriteLine($"{items.product.ProductName},{items.product.InStock},{items.product.ExpiryDate},{items.category.CategoryName},{items.product.ProductCode}");
}
}
}
public static void DataStaffs()
{
DataAddressesmore();
using (DStoreContext context = new DStoreContext())
{
context.Staffs.AddRange(
new Data.Staff {
FirstName = "Alex", LastName = "kumar", AddressId = 3, Gender = "M", PhoneNumber = "9876543210", Salary = 10000, RoleId = 1
},
new Data.Staff {
FirstName = "Natasha", LastName = "Carry", AddressId = 4, Gender = "F", PhoneNumber = "9876543211", Salary = 10000, RoleId = 1
},
new Data.Staff {
FirstName = "Emma", LastName = "Watson", AddressId = 1, Gender = "F", PhoneNumber = "9876543212", Salary = 10000, RoleId = 1
},
new Data.Staff {
FirstName = "Parker", LastName = "Ben", AddressId = 6, Gender = "M", PhoneNumber = "9876543213", Salary = 10000, RoleId = 1
},
new Data.Staff {
FirstName = "Abhimanyu", LastName = "Rajpoot", AddressId = 2, Gender = "M", PhoneNumber = "9876543214", Salary = 28000, RoleId = 2
},
new Data.Staff {
FirstName = "Gauri", LastName = "Kumari", AddressId = 7, Gender = "F", PhoneNumber = "9876543215", Salary = 28000, RoleId = 2
},
new Data.Staff {
FirstName = "Ram", LastName = "Prasad", AddressId = 2, Gender = "M", PhoneNumber = "9876543216", Salary = 9000, RoleId = 3
},
new Data.Staff {
FirstName = "Rehan", LastName = "Sultan", AddressId = 5, Gender = "M", PhoneNumber = "9876543217", Salary = 7500, RoleId = 4
},
new Data.Staff {
FirstName = "Hema", LastName = "Venkatesh", AddressId = 8, Gender = "F", PhoneNumber = "9876543218", Salary = 7500, RoleId = 4
},
new Data.Staff {
FirstName = "Pralay", LastName = "Hota", AddressId = 1, Gender = "M", PhoneNumber = "9876543219", Salary = 12500, RoleId = 5
},
new Data.Staff {
FirstName = "Divya", LastName = "Singhanaya", AddressId = 1, Gender = "F", PhoneNumber = "9876543220", Salary = 12500, RoleId = 5
},
new Data.Staff {
FirstName = "Abhishesh", LastName = "Chaturvedi", AddressId = 1, Gender = "M", PhoneNumber = "9876543221", Salary = 39500, RoleId = 6
}
);
context.SaveChanges();
Console.WriteLine("data Added Successfully");
}
}
public static void DataSupplierProductOrder()
{
using (DStoreContext context = new DStoreContext())
{
context.SupplierProductOrders.AddRange(
new Data.SupplierProductOrder {
ProductId = 1, ProductOrderId = 6, SupplierId = 3
},
new Data.SupplierProductOrder {
ProductId = 2, ProductOrderId = 7, SupplierId = 2
},
new Data.SupplierProductOrder {
ProductId = 3, ProductOrderId = 5, SupplierId = 1
},
new Data.SupplierProductOrder {
ProductId = 4, ProductOrderId = 8, SupplierId = 3
},
new Data.SupplierProductOrder {
ProductId = 5, ProductOrderId = 4, SupplierId = 4
},
new Data.SupplierProductOrder {
ProductId = 2, ProductOrderId = 9, SupplierId = 4
},
new Data.SupplierProductOrder {
ProductId = 6, ProductOrderId = 3, SupplierId = 1
},
new Data.SupplierProductOrder {
ProductId = 7, ProductOrderId = 12, SupplierId = 2
},
new Data.SupplierProductOrder {
ProductId = 8, ProductOrderId = 2, SupplierId = 3
},
new Data.SupplierProductOrder {
ProductId = 4, ProductOrderId = 1, SupplierId = 2
},
new Data.SupplierProductOrder {
ProductId = 3, ProductOrderId = 10, SupplierId = 1
},
new Data.SupplierProductOrder {
ProductId = 1, ProductOrderId = 11, SupplierId = 4
}
);
context.SaveChanges();
Console.WriteLine("data Added Successfully");
}
}
public static void ProductAndSupplierWithRecentDatesOfSupply()
{
using (DStoreContext context = new DStoreContext())
{
var query = from product in context.Set <Product>() join posupplier in context.Set <SupplierProductOrder>() on
product.ProductId equals posupplier.ProductId join supplier in context.Set <Supplier>() on
posupplier.SupplierId equals supplier.SupplierId join po in context.Set <ProductOrder>() on
posupplier.ProductOrderId equals po.ProductOrderId join inventory in context.Set <Inventory>() on
product.ProductId equals inventory.ProductId
where po.OrderTime.Date <= new DateTime(2021, 04, 01) || inventory.ProductQuantity >= 2
select(new { product, po, supplier, inventory });
foreach (var item in query)
{
Console.WriteLine($"{item.product.ProductName}-->{item.supplier.SupplierName}-->{item.po.OrderTime}-->{item.inventory.ProductQuantity}");
}
}
}
public static void DataProductOrder()
{
using (DStoreContext context = new DStoreContext())
{
context.productOrders.AddRange(
new Data.ProductOrder {
OrderTime = new DateTime(2021, 01, 11), Qunatity = 10
},
new Data.ProductOrder {
OrderTime = new DateTime(2021, 05, 15), Qunatity = 1
},
new Data.ProductOrder {
OrderTime = new DateTime(2021, 03, 17), Qunatity = 3
},
new Data.ProductOrder {
OrderTime = new DateTime(2020, 01, 11), Qunatity = 2
},
new Data.ProductOrder {
OrderTime = new DateTime(2021, 05, 11), Qunatity = 9
},
new Data.ProductOrder {
OrderTime = new DateTime(2021, 05, 12), Qunatity = 6
},
new Data.ProductOrder {
OrderTime = new DateTime(2021, 05, 13), Qunatity = 4
},
new Data.ProductOrder {
OrderTime = new DateTime(2020, 11, 17), Qunatity = 2
},
new Data.ProductOrder {
OrderTime = new DateTime(2021, 05, 14), Qunatity = 7
},
new Data.ProductOrder {
OrderTime = new DateTime(2021, 02, 15), Qunatity = 20
},
new Data.ProductOrder {
OrderTime = new DateTime(2021, 03, 12), Qunatity = 3
},
new Data.ProductOrder {
OrderTime = new DateTime(2020, 10, 30), Qunatity = 1
}
);
context.SaveChanges();
Console.WriteLine("data Added Successfully");
}
}
public static void StaffRecordPhoneOrName()
{
using (DStoreContext context = new DStoreContext())
{
var query = from stafff in context.Set <Staff>()
join roles in context.Set <Role>() on stafff.RoleId equals roles.RoleId
join address in context.Set <Address>() on stafff.AddressId equals address.AddressId
where stafff.PhoneNumber == "9876543210" || stafff.FirstName == "Divya"
select new { stafff, address, roles };
foreach (var staff in query)
{
Console.WriteLine($"{staff.stafff.FirstName},{staff.stafff.LastName},{staff.stafff.Salary},{staff.stafff.PhoneNumber},{staff.stafff.Gender}," +
$"{staff.address.AddressLine1}{staff.address.AddressLine2}" +
$"{staff.address.City}{staff.address.State}{staff.address.Pincode},{staff.roles.RoleName}");
}
}
}
public static void SupplierDetails()
{
using (DStoreContext context = new DStoreContext())
{
var query = from supplier in context.Set <Supplier>() join supplireprodorder in context.Set <SupplierProductOrder>() on
supplier.SupplierId equals supplireprodorder.SupplierId join prodt in context.Set <Product>() on
supplireprodorder.ProductId equals prodt.ProductId join address in context.Set <Address>() on
supplier.AddressId equals address.AddressId join prodorder in context.Set <ProductOrder>() on
supplireprodorder.ProductOrderId equals prodorder.ProductOrderId
select(new { supplier, prodorder, prodt, address });
foreach (var item in query)
{
Console.WriteLine($"supplier : {item.supplier.SupplierName} supplied product : {item.prodt.ProductName} date of supply : {item.prodorder.OrderTime}" +
$"supplier address : { item.address.AddressLine1}{item.address.AddressLine2} {item.address.City},{item.address.State}({item.address.Pincode})");
}
}
}
public static void DataCategory()
{
using (DStoreContext context = new DStoreContext())
{
context.Categories.Add(new Data.Category {
CategoryName = "Chocolate"
});
context.Categories.Add(new Data.Category {
CategoryName = "Book"
});
context.Categories.Add(new Data.Category {
CategoryName = "Dairy"
});
context.Categories.Add(new Data.Category {
CategoryName = "Cosmetic"
});
context.SaveChanges();
Console.WriteLine("data Added Successfully");
}
}
public static void DataAddressesmore()
{
using (DStoreContext context = new DStoreContext())
{
context.Addresses.AddRange(new Data.Address {
AddressLine1 = "jankipuram", AddressLine2 = "near green city", City = "Lucknow", State = "uttar pradesh", Pincode = "209801"
},
new Data.Address {
AddressLine1 = "imambada", AddressLine2 = "ganges apartment", City = "Gwalior", State = "madhya pradesh", Pincode = "667542"
},
new Data.Address {
AddressLine1 = "cp", AddressLine2 = "flat no 112 ,amarpali", City = "Delhi", State = "delhi", Pincode = "567882"
},
new Data.Address {
AddressLine1 = "thaden", AddressLine2 = "g block", City = "Mumbai", State = "maharashtra", Pincode = "456711"
});
context.SaveChanges();
Console.WriteLine("data Added Successfully");
}
}
public static void DataAddress()
{
using (DStoreContext context = new DStoreContext())
{
context.Addresses.AddRange(new Data.Address {
AddressLine1 = "govind nagar", AddressLine2 = "123/7 a block", City = "kanpur", State = "uttar pradesh", Pincode = "208014"
},
new Data.Address {
AddressLine1 = "geetapuram", AddressLine2 = "A1 vaishali apartment", City = "jabalpur", State = "madhya paradesh", Pincode = "230914"
},
new Data.Address {
AddressLine1 = "sanjayghandhi puram", AddressLine2 = "near hotel pinacle", City = "balco", State = "chattisghar", Pincode = "340013"
},
new Data.Address {
AddressLine1 = "121/3", AddressLine2 = "F block", City = "patna", State = "bihar", Pincode = "554612"
});
context.SaveChanges();
Console.WriteLine("data Added Successfully");
}
}
public static void DataSupplier()
{
using (DStoreContext context = new DStoreContext())
{
context.Suppliers.AddRange(new Data.Supplier {
SupplierName = "Peter", SupplierAge = 30, AddressId = 1, Gender = "M", PhoneNumber = "7875767972"
},
new Data.Supplier {
SupplierName = "Kylie", SupplierAge = 20, AddressId = 2, Gender = "F", PhoneNumber = "7874447972"
},
new Data.Supplier {
SupplierName = "Pushpa", SupplierAge = 25, AddressId = 3, Gender = "F", PhoneNumber = "7875777772"
},
new Data.Supplier {
SupplierName = "Udit", SupplierAge = 27, AddressId = 4, Gender = "M", PhoneNumber = "7875761111"
}
);
context.SaveChanges();
Console.WriteLine("data Added Successfully");
}
}
public StaffController(DStoreContext context, IMapper mapper, LinkGenerator linkGenerator)
{
_linkGen = linkGenerator;
_context = context;
mapperStaff = mapper;
}
public StaffRoleController(DStoreContext context, IMapper mapper)
{
_context = context;
MapperStaffRole = mapper;
}
public ProductController(DStoreContext context, IMapper mapper)
{
_context = context;
MapperProduct = mapper;
}
static void Main(string[] args)
{
//Console.WriteLine("Hello World!");
EFQuery.StaffDetail();
using (DStoreContext context = new DStoreContext())
{
context.Categories.AddRange(
new Data.Category {
CategoryName = "Groceries"
},
new Data.Category {
CategoryName = "Dairy"
},
new Data.Category {
CategoryName = "Food"
},
new Data.Category {
CategoryName = "Appliances"
}
);
context.SaveChanges();
Console.WriteLine("data Added Successfully");
}
using (DStoreContext context = new DStoreContext())
{
context.Staffs.AddRange(
new Data.Staff {
FirstName = "Vikas", LastName = "kumar", AddressId = 3, Gender = "M", PhoneNumber = "1234567890", Salary = 10000, RoleId = 1
},
new Data.Staff {
FirstName = "Nishu", LastName = "singh", AddressId = 4, Gender = "M", PhoneNumber = "0987654321", Salary = 15000, RoleId = 2
},
new Data.Staff {
FirstName = "Vik", LastName = "kumar", AddressId = 1, Gender = "M", PhoneNumber = "1234567890", Salary = 10000, RoleId = 3
},
new Data.Staff {
FirstName = "Nik", LastName = "singh", AddressId = 6, Gender = "M", PhoneNumber = "0987654321", Salary = 15000, RoleId = 4
}
);
context.SaveChanges();
Console.WriteLine("data Added Successfully");
}
using (DStoreContext context = new DStoreContext())
{
context.Addresses.AddRange(
new Data.Address {
AddressLine1 = "E-125", AddressLine2 = "nand gram", City = "ghaziabad", Pincode = "201003"
},
new Data.Address {
AddressLine1 = "E-124", AddressLine2 = "raj nagar", City = "delhi", Pincode = "201001"
},
new Data.Address {
AddressLine1 = "E-125", AddressLine2 = "nand gram", City = "noida", Pincode = "201004"
},
new Data.Address {
AddressLine1 = "E-124", AddressLine2 = "raj nagar", City = "delhi", Pincode = "201002"
}
);
context.SaveChanges();
Console.WriteLine("data Added Successfully");
}
using (DStoreContext context = new DStoreContext())
{
context.Roles.AddRange(
new Data.Role {
RoleName = "Manager", Description = "Brach manager"
},
new Data.Role {
RoleName = "Billing", Description = "Counter Billing perpose"
},
new Data.Role {
RoleName = "Security", Description = "For Security perpose"
},
new Data.Role {
RoleName = "Helper", Description = "For helping Customer"
}
);
context.SaveChanges();
Console.WriteLine("data Added Successfully");
}
using (DStoreContext context = new DStoreContext())
{
context.ProductOrderDetails.AddRange(
new Data.ProductOrderDetail {
OrderTime = new DateTime(2021, 01, 13), Qunatity = 10
},
new Data.ProductOrderDetail {
OrderTime = new DateTime(2020, 02, 13), Qunatity = 15
},
new Data.ProductOrderDetail {
OrderTime = new DateTime(2019, 03, 13), Qunatity = 20
},
new Data.ProductOrderDetail {
OrderTime = new DateTime(2018, 04, 33), Qunatity = 30
}
);
context.SaveChanges();
Console.WriteLine("data Added Successfully");
}
using (DStoreContext context = new DStoreContext())
{
context.Suppliers.AddRange(
new Data.Supplier {
SupplierName = "Varun", AddressId = 3, PhoneNumber = "1122334455"
},
new Data.Supplier {
SupplierName = "Deepak", AddressId = 2, PhoneNumber = "6677889900"
},
new Data.Supplier {
SupplierName = "Rajnish", AddressId = 3, PhoneNumber = "1234512345"
},
new Data.Supplier {
SupplierName = "David", AddressId = 1, PhoneNumber = "1234567890"
}
);
context.SaveChanges();
Console.WriteLine("data Added Successfully");
}
using (DStoreContext context = new DStoreContext())
{
context.SupplierProductOrderDetails.AddRange(
new Data.SupplierProductOrderDetail {
ProductId = 1, ProductOrderId = 2, SupplierId = 4
},
new Data.SupplierProductOrderDetail {
ProductId = 2, ProductOrderId = 3, SupplierId = 2
},
new Data.SupplierProductOrderDetail {
ProductId = 3, ProductOrderId = 1, SupplierId = 1
},
new Data.SupplierProductOrderDetail {
ProductId = 4, ProductOrderId = 4, SupplierId = 3
}
);
context.SaveChanges();
Console.WriteLine("data Added Successfully");
}
}
