//12
public bool UpdateCategory(int id, string name, string description)
{
using (var db = new NordWindContext())
{
var update = new Category
{
Id = id,
Name = name,
Description = description
};
try
{
db.Categories.Update(update);
db.SaveChanges();
Console.WriteLine("The category has been updated");
return(true);
}
catch (Exception e)
{
Console.WriteLine("The category cannot be updated");
return(false);
}
}
}
//8
//Works
public List <Product> GetProductByCategory(int id)
{
using (var db = new NordWindContext())
{
var query =
(from c in db.Categories
join pr in db.Products
on c.Id equals pr.CategoryID
join od in db.OrderDetails
on pr.Id equals od.ProductId
where pr.CategoryID == id
select new Product
{
Name = pr.Name,
Category = new Category
{
Name = c.Name
}
}).Distinct();
Console.WriteLine(query.Last().Name);
return(query.ToList());
}
}
//10
public List <Category> GetCategories()
{
using (var db = new NordWindContext())
{
var query =
from c in db.Categories
select new Category
{
Id = c.Id,
Name = c.Name,
Description = c.Description
};
return(query.ToList());
}
}
//1
//Almost Works
public Order GetOrder(int id)
{
using (var db = new NordWindContext())
{
var query =
(from o in db.Orders
join od in db.OrderDetails
on o.Id equals od.OrderId
join pr in db.Products
on od.ProductId equals pr.Id
join c in db.Categories
on pr.CategoryID equals c.Id
where o.Id == id
select new Order
{
Id = o.Id,
Date = o.Date,
// Required = o.Required,
// Shipped = o.Shipped,
Freight = o.Freight,
ShipName = o.ShipName,
ShipCity = o.ShipCity,
OrderDetails = new List <OrderDetails>
{
new OrderDetails {
UnitPrice = od.UnitPrice,
Quantity = od.Quantity,
Discount = od.Discount,
Product = new Product
{
Name = pr.Name,
Category = new Category
{
Name = c.Name
}
}
}
}
}).ToList();
for (int i = 0; i < query.Count(); i++)
{
Console.WriteLine(query[i].OrderDetails.First().Product.Category.Name);
}
return(query.First());
}
}
//3
//Works
public List <Order> GetOrders()
{
using (var db = new NordWindContext())
{
var query =
from o in db.Orders
select new Order {
Id = o.Id,
Date = o.Date,
ShipName = o.ShipName,
ShipCity = o.ShipCity
};
return(query.ToList());
}
}
//10
public List <object> GetCategories()
{
using (var db = new NordWindContext())
{
var query =
from c in db.Categories
select new { c.Id, c.Name, c.Description };
foreach (var item in query)
{
Console.WriteLine(item);
}
return(query.ToList <Object>());
}
}
//2
public List <Order> GetOrderFromShippingName(string shipName)
{
using (var db = new NordWindContext())
{
var ship_name_order_q =
from o in db.Orders
where o.ShipName.Equals(shipName)
select new Order
{
Id = o.Id,
Date = o.Date,
ShipName = o.ShipName,
ShipCity = o.ShipCity
};
return(ship_name_order_q.ToList());
}
}
//3
public List <Order> GetOrders()
{
using (var db = new NordWindContext())
{
var q = //here I became too lazy to actually call it anything other than q... sry
from o in db.Orders
select new Order
{
Id = o.Id,
Date = o.Date,
ShipName = o.ShipName,
ShipCity = o.ShipCity
};
return(q.ToList());
}
}
//11
public Category CreateCategory(string name, string description)
{
using (var db = new NordWindContext())
{
var category = new Category
{
Name = name,
Description = description
};
db.Categories.Add(category);
db.SaveChanges();
return(category);
}
}
//11
public Category CreateCategory(string name, string description)
{
using (var db = new NordWindContext())
{
var category = new Category
{
Name = name,
Description = description
};
db.Categories.Add(category);
db.SaveChanges();
Console.WriteLine("The category has been added");
return(category);
}
}
static void Main(string[] args)
{
DataService d = new DataService();
/* This is all the test calls of my functions, just ignore them, or use them as you make yours*/
var order = d.GetOrder(10248);
//var shippingOrder = d.getOrderFromShippingName("The Big Cheese");
//var orders = d.GetOrders();
//var orders = d.GetOrderDetailsByOrderId(10248);
//var orders = d.GetOrderDetailsByProductId(11);
//var products = d.GetProduct(1);
//var products = d.GetProductByName("ant");
//var products = d.GetProductByCategory(1);
//var categorys = d.GetCategory(-1);
//var categories = d.getCategories();
//d.CreateCategory("Test", "Nope");
//d.deleteCategory(13);
//d.updateCategory(12, "Nope", "Nope");
using (var db = new NordWindContext())
{
var category = db.Categories.FirstOrDefault(x => x.Id == 11);
if (category != null)
{
category.Name = "2017 Testing";
}
db.SaveChanges();
}
}
//7
public List <Product> GetProductByName(string sub)
{
using (var db = new NordWindContext())
{
var query =
(from p in db.Products
join c in db.Categories
on p.CategoryID equals c.Id
where p.Name.Contains(sub)
select new Product
{
Name = p.Name,
Category = new Category
{
Name = c.Name
}
}).OrderBy(x => x.Name).ToList();
return(query.ToList <Product>());
}
}
//4
//Works
public List <OrderDetails> GetOrderDetailsByOrderId(int id)
{
using (var db = new NordWindContext())
{
var query =
(from od in db.OrderDetails
join pr in db.Products
on od.ProductId equals pr.Id
where od.OrderId == id
select new OrderDetails {
Product = new Product
{
Name = pr.Name
},
UnitPrice = od.UnitPrice,
Quantity = od.Quantity
});
return(query.ToList());
}
}
//6
public Product GetProduct(int id)
{
using (var db = new NordWindContext())
{
var query =
from pr in db.Products
join c in db.Categories
on pr.CategoryID equals c.Id
where pr.Id == id
select new Product
{
Name = pr.Name,
UnitPrice = pr.UnitPrice,
Category = new Category
{
Name = c.Name
}
};
return(query.FirstOrDefault());
}
}
//13
public bool DeleteCategory(int id)
{
using (var db = new NordWindContext())
{
var delete = new Category
{
Id = id
};
try
{
db.Categories.Remove(delete);
db.SaveChanges();
Console.WriteLine("Sucess!");
return(true);
}
catch (Exception e)
{
Console.WriteLine("Can't delete");
return(false);
}
}
}
//13
public bool DeleteCategory(int id)
{
using (var db = new NordWindContext())
{
var delete = new Category
{
Id = id
};
try
{
db.Categories.Remove(delete);
db.SaveChanges();
Console.WriteLine("The category has been deleted");
return(true);
}
catch (Exception e)
{
Console.WriteLine("The category cannot be deleted");
return(false);
}
}
}
//5
//Works
public List <OrderDetails> GetOrderDetailsByProductId(int id)
{
using (var db = new NordWindContext())
{
var query =
(from od in db.OrderDetails
join o in db.Orders
on od.OrderId equals o.Id
where od.ProductId == id
select new OrderDetails {
Order = new Order
{
Date = o.Date
},
UnitPrice = od.UnitPrice,
Quantity = od.Quantity
});
Console.WriteLine(query.First().UnitPrice);
return(query.ToList());
}
}
//9
public Category GetCategory(int id)
{
using (var db = new NordWindContext())
{
var query =
(from c in db.Categories
where c.Id == id
select new Category
{
Id = c.Id,
Name = c.Name,
Description = c.Description
});
if (query.FirstOrDefault() != null)
{
Console.WriteLine(query.First());
return(query.First());
}
Console.WriteLine("null");
return(null);
}
}
