public CPersonDto FindItemByKeyFullLoad(int key)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
System.Data.Entity.Infrastructure.DbQuery <CPersonDto> query = context.CPeopleDto
.Include("Reminders")
.Include("PersonalTargets")
.Include("SpendingHistories")
.Include("SubCategories")
.Include("SubCategories.Payments")
.Include("LoginData")
.Include("Family.FamilyTargets")
.Include("Family.People.Reminders")
.Include("Family.People.PersonalTargets")
.Include("Family.People.SpendingHistories")
.Include("Family.People.SubCategories")
.Include("Family.People.SubCategories.Payments")
.Include("Family.People.LoginData");
CPersonDto person = query.FirstOrDefault(p => p.PersonID == key);
return(person);
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to find Person (id = {0}) in DB. Message: {1}", key, ex.Message);
return(null);
}
}
public IQueryable <Order> OrdersForProduct(int productID = 0)
{
System.Data.Entity.Infrastructure.DbQuery <Order> query = _contextProvider.Context.Orders;
query = query.Include("Customer").Include("OrderDetails");
return((productID == 0)
? query
: query.Where(o => o.OrderDetails.Any(od => od.ProductID == productID)));
}
public static void OutputQuery <T>(IQueryable <T> query)
{
System.Data.Entity.Infrastructure.DbQuery <T> dbQuery =
query as System.Data.Entity.Infrastructure.DbQuery <T>;
if (dbQuery != null)
{
System.Diagnostics.Debug.WriteLine(dbQuery.ToString());
}
}
public CFamilyDto FindByPersonIdFullLoad(int personId)
{
try
{
using (SpendingHelperDBEntities context = new SpendingHelperDBEntities())
{
System.Data.Entity.Infrastructure.DbQuery <CFamilyDto> q1 = context.CFamiliesDto
.Include("FamilyTargets")
.Include("People.Reminders")
.Include("People.PersonalTargets.Category")
.Include("People.SpendingHistories.Category")
.Include("People.SubCategories.Category")
.Include("People.SubCategories.Payments")
.Include("People.LoginData");
IQueryable <CFamilyDto> q2 = q1.Where(f => f.People.Select(p => p.PersonID).Contains(personId));
return(q2.FirstOrDefault());
}
}
catch (Exception ex)
{
log.Error(ex, "Some error occure while trying to find family by personId (id = {0}) in DB (full data load method version). Message: {1}", personId, ex.Message);
return(null);
}
}
private static System.Data.Entity.Infrastructure.DbQuery <T> GetInclude <T, R>(this System.Data.Entity.Infrastructure.DbQuery <T> item, Expression <Func <R, Gravitybox.GeoLocation.EFDAL.IContextInclude> > query)
where T : BaseEntity
where R : IContextInclude
{
var strings = new List <string>(query.Body.ToString().Split('.'));
strings.RemoveAt(0);
var compoundString = string.Empty;
foreach (var s in strings)
{
if (!string.IsNullOrEmpty(compoundString))
{
compoundString += ".";
}
compoundString += s;
item = item.Include(compoundString);
}
return(item);
}
static void Main(string[] args)
{
//list kus kõik tooted on
List <Food> groceries = new List <Food>
{
new Food("apple", 1.7),
new Food("bread", 1.2),
new Food("cheese", 2)
};
//Food apple = new Food("apple", 1.7);
//poekorv
ShoppingCart newCart = new ShoppingCart();
//do
//{
// chooseFood(groceries, newCart);
//}
//while (Console.ReadLine().ToLower() == "y");
//poekorvi newCart lisatakse listist groceries toidud
//foreach (Food food in groceries)
//{
// newCart.Items.Add(food);
//}
//db loomine
using (ShopDbContext db = new ShopDbContext())
{
IQueryable <ShoppingCart> cartWithZeroSum = db.ShoppingCarts.Where(x => x.Sum == 0);
foreach (ShoppingCart cart in cartWithZeroSum)
{
db.ShoppingCarts.Remove(cart);
}
//db kõik shopping cartid listakse database
db.ShoppingCarts.Add(newCart);
//salvesta muutused
db.SaveChanges();
List <ShoppingCart> carts = db.ShoppingCarts.Include("Items").OrderByDescending(x => x.DateCreated).ToList();
foreach (ShoppingCart cart in carts)
{
Console.WriteLine($"Created on {cart.DateCreated}");
foreach (Food food in cart.Items)
{
Console.WriteLine($"Name: {food.Name} Price: {food.Price}");
}
}
// Console.WriteLine("-----------------------------------");
//carts = db.ShoppingCarts.Include("Items").OrderByDescending(x => x.DateCreated).ToList();
//Console.WriteLine($"Created on {carts[0].DateCreated}");
//Console.WriteLine(carts[0].Items);
//foreach (Food food in carts[0].Items)
//{
// Console.WriteLine("test");
// Console.WriteLine($"Name: {food.Name} Price: {food.Price}");
//}
System.Data.Entity.Infrastructure.DbQuery <ShoppingCart> shcarts = db.ShoppingCarts;
System.Data.Entity.Infrastructure.DbQuery <ShoppingCart> cartsWithItems = db.ShoppingCarts.Include("Items");
System.Data.Entity.Infrastructure.DbQuery <Food> foods = db.Items;
//1.show only the last(latest created) shopping cart with all its items
ShoppingCart latest = cartsWithItems.OrderBy(cart => cart.DateCreated).ToList().First(); //lamda expression
//or
ShoppingCart latest2 = cartsWithItems.OrderBy(cart => cart.DateCreated).ToList().Last();
//2.show only the carts with sum > 5
List <ShoppingCart> largerThanFive = shcarts.Where(cart => cart.Sum > 5).ToList();
foreach (var cart in largerThanFive)
{
Console.WriteLine($"ShoppingCart {cart.DateCreated} {cart.Sum}");
}
//3.show only the carts with more than one item in it(and how many items is in cart)
IQueryable <ShoppingCart> cartsMoreThanOne = cartsWithItems.Where(cart => cart.Items.Count() > 1);
foreach (var cart in cartsMoreThanOne)
{
Console.WriteLine(cart.DateCreated);
}
// LINQ
cartsMoreThanOne = from cart in cartsWithItems where cart.Items.Count() > 1 select cart;
//4.show only the carts that contain apples
IQueryable <ShoppingCart> cartsWithApples = cartsWithItems.Where(cart => cart.Items.Any(y => y.Name == "apple"));
//5.show the total number of shopping carts
int totalShCatrs = shcarts.Count();
//6.show the cart with maximum sum
ShoppingCart maxSumShoppingcart = shcarts.OrderByDescending(cart => cart.Sum).FirstOrDefault();
//7.show the cheapest food
Food cheapestFood = foods.OrderBy(food => food.Price).FirstOrDefault();
Console.ReadLine();
}
}