public List <Member> GetMembersWithOrderStatistics(string sortExpression)
{
using (var context = new actionEntities())
{
// get members with orders
var members = from m in context.MemberEntities
where context.OrderEntities.Any(o => o.MemberId == m.MemberId)
select m;
var orders = context.OrderEntities.OrderBy(o => o.MemberId);
return(members.AsQueryable().Select(m =>
new Member
{
MemberId = m.MemberId,
Email = m.Email,
CompanyName = m.CompanyName,
City = m.City,
Country = m.Country,
NumOrders = orders.Where(o => o.MemberId == m.MemberId).Count(),
LastOrderDate = orders.Where(o => o.MemberId == m.MemberId).Max(o => o.OrderDate)
})
.OrderBy(sortExpression)
.ToList());
}
}
public List<Member> GetMembersWithOrderStatistics(string sortExpression)
{
using (var context = new actionEntities())
{
// get members with orders
var members = from m in context.MemberEntities
where context.OrderEntities.Any(o => o.MemberId == m.MemberId)
select m;
var orders = context.OrderEntities.OrderBy(o => o.MemberId);
return members.AsQueryable().Select(m =>
new Member
{
MemberId = m.MemberId,
Email = m.Email,
CompanyName = m.CompanyName,
City = m.City,
Country = m.Country,
NumOrders = orders.Where(o => o.MemberId == m.MemberId).Count(),
LastOrderDate = orders.Where(o => o.MemberId == m.MemberId).Max(o => o.OrderDate)
})
.OrderBy(sortExpression)
.ToList();
}
}
public Member GetMember(int memberId)
{
using (var context = new actionEntities())
{
var member = context.MemberEntities.FirstOrDefault(c => c.MemberId == memberId) as MemberEntity;
return(Mapper.Map <MemberEntity, Member>(member));
}
}
public List <Member> GetMembers(string sortExpression = "MemberId ASC")
{
using (var context = new actionEntities())
{
var members = context.MemberEntities.AsQueryable().OrderBy(sortExpression).ToList();
return(Mapper.Map <List <MemberEntity>, List <Member> >(members));
}
}
public List <Order> GetOrdersByDate(DateTime dateFrom, DateTime dateThru)
{
using (var context = new actionEntities())
{
var orders = context.OrderEntities.Where(o => o.OrderDate >= dateFrom && o.OrderDate <= dateThru).ToList();
return(Mapper.Map <List <OrderEntity>, List <Order> >(orders));
}
}
public Member GetMemberByEmail(string email)
{
using (var context = new actionEntities())
{
var member = context.MemberEntities.FirstOrDefault(c => c.Email == email) as MemberEntity;
return(Mapper.Map <MemberEntity, Member>(member));
}
}
public List<Order> GetOrdersByDate(DateTime dateFrom, DateTime dateThru)
{
using (var context = new actionEntities())
{
var orders = context.OrderEntities.Where(o => o.OrderDate >= dateFrom && o.OrderDate <= dateThru).ToList();
return Mapper.Map<List<OrderEntity>, List<Order>>(orders);
}
}
public Order GetOrder(int orderId)
{
using (var context = new actionEntities())
{
var order = context.OrderEntities.SingleOrDefault(o => o.OrderId == orderId);
return(Mapper.Map <OrderEntity, Order>(order));
}
}
public Product GetProduct(int productId)
{
using (var context = new actionEntities())
{
var product = context.ProductEntities.SingleOrDefault(p => p.ProductId == productId);
return Mapper.Map<ProductEntity, Product>(product);
}
}
public Member GetMember(int memberId)
{
using (var context = new actionEntities())
{
var member = context.MemberEntities.FirstOrDefault(c => c.MemberId == memberId) as MemberEntity;
return Mapper.Map<MemberEntity, Member>(member);
}
}
public List <Order> GetOrdersByMember(int memberId)
{
using (var context = new actionEntities())
{
var orders = context.OrderEntities.Where(o => o.MemberId == memberId).ToList();
return(Mapper.Map <List <OrderEntity>, List <Order> >(orders));
}
}
public Member GetMemberByEmail(string email)
{
using (var context = new actionEntities())
{
var member = context.MemberEntities.FirstOrDefault(c => c.Email == email) as MemberEntity;
return Mapper.Map<MemberEntity, Member>(member);
}
}
public List <Category> GetCategories()
{
using (var context = new actionEntities())
{
var categories = context.CategoryEntities.ToList();
return(Mapper.Map <List <CategoryEntity>, List <Category> >(categories));
}
}
public Product GetProduct(int productId)
{
using (var context = new actionEntities())
{
var product = context.ProductEntities.SingleOrDefault(p => p.ProductId == productId);
return(Mapper.Map <ProductEntity, Product>(product));
}
}
public Order GetOrder(int orderId)
{
using (var context = new actionEntities())
{
var order = context.OrderEntities.SingleOrDefault(o => o.OrderId == orderId);
return Mapper.Map<OrderEntity, Order>(order);
}
}
public List<Category> GetCategories()
{
using (var context = new actionEntities())
{
var categories = context.CategoryEntities.ToList();
return Mapper.Map<List<CategoryEntity>, List<Category>>(categories);
}
}
public List<Order> GetOrdersByMember(int memberId)
{
using (var context = new actionEntities())
{
var orders = context.OrderEntities.Where(o => o.MemberId == memberId).ToList();
return Mapper.Map<List<OrderEntity>, List<Order>>(orders);
}
}
public List<Member> GetMembers(string sortExpression = "MemberId ASC")
{
using (var context = new actionEntities())
{
var members = context.MemberEntities.AsQueryable().OrderBy(sortExpression).ToList();
return Mapper.Map<List<MemberEntity>, List<Member>>(members);
}
}
public void DeleteMember(Member member)
{
using (var context = new actionEntities())
{
var entity = context.MemberEntities.SingleOrDefault(m => m.MemberId == member.MemberId);
context.MemberEntities.Remove(entity);
context.SaveChanges();
}
}
public List <Product> GetProductsByCategory(int categoryId, string sortExpression)
{
using (var context = new actionEntities())
{
var products = context.ProductEntities.Where(p => p.CategoryId == categoryId)
.AsQueryable().OrderBy(sortExpression).ToList();
return(Mapper.Map <List <ProductEntity>, List <Product> >(products));
}
}
public List<Product> GetProductsByCategory(int categoryId, string sortExpression)
{
using (var context = new actionEntities())
{
var products = context.ProductEntities.Where(p => p.CategoryId == categoryId)
.AsQueryable().OrderBy(sortExpression).ToList();
return Mapper.Map<List<ProductEntity>, List<Product>>(products);
}
}
public Category GetCategoryByProduct(int productId)
{
using (var context = new actionEntities())
{
var product = context.ProductEntities.SingleOrDefault(p => p.ProductId == productId);
var category = context.CategoryEntities.SingleOrDefault(c => c.CategoryId == product.CategoryId);
return Mapper.Map<CategoryEntity, Category>(category);
}
}
public Member GetMemberByOrder(int orderId)
{
using (var context = new actionEntities())
{
var order = context.OrderEntities.Where(o => o.OrderId == orderId).SingleOrDefault() as OrderEntity;
var member = context.MemberEntities.SingleOrDefault(c => c.MemberId == order.MemberId);
return(Mapper.Map <MemberEntity, Member>(member));
}
}
public Member GetMemberByOrder(int orderId)
{
using (var context = new actionEntities())
{
var order = context.OrderEntities.Where(o => o.OrderId == orderId).SingleOrDefault() as OrderEntity;
var member = context.MemberEntities.SingleOrDefault(c => c.MemberId == order.MemberId);
return Mapper.Map<MemberEntity, Member>(member);
}
}
public Category GetCategoryByProduct(int productId)
{
using (var context = new actionEntities())
{
var product = context.ProductEntities.SingleOrDefault(p => p.ProductId == productId);
var category = context.CategoryEntities.SingleOrDefault(c => c.CategoryId == product.CategoryId);
return(Mapper.Map <CategoryEntity, Category>(category));
}
}
public void InsertMember(Member member)
{
using (var context = new actionEntities())
{
var entity = Mapper.Map <Member, MemberEntity>(member);
context.MemberEntities.Add(entity);
context.SaveChanges();
// update business object with new id
member.MemberId = entity.MemberId;
}
}
public void UpdateMember(Member member)
{
using (var context = new actionEntities())
{
var entity = context.MemberEntities.SingleOrDefault(m => m.MemberId == member.MemberId);
entity.Email = member.Email;
entity.CompanyName = member.CompanyName;
entity.Country = member.Country;
entity.City = member.City;
//context.Members.Attach(entity);
context.SaveChanges();
}
}
public List<Product> SearchProducts(string productName, double priceFrom, double priceThru, string sortExpression)
{
using (var context = new actionEntities())
{
var query = context.ProductEntities.AsQueryable();
if (!string.IsNullOrEmpty(productName))
query = query.Where(p => p.ProductName.Contains(productName));
if (priceFrom != -1 && priceThru != -1)
query = query.Where(p => p.UnitPrice >= (decimal)priceFrom && p.UnitPrice <= (decimal)priceThru);
var products = query.OrderBy(sortExpression, null).ToList();
return Mapper.Map<List<ProductEntity>, List<Product>>(products);
}
}
public List <OrderDetail> GetOrderDetails(int orderId)
{
using (var context = new actionEntities())
{
var orderDetails = context.OrderDetailEntities.Where(d => d.OrderId == orderId);
int[] keys = orderDetails.Select(d => d.ProductId).ToArray();
var products =
(from p in context.ProductEntities
where keys.Contains(p.ProductId)
select p).ToDictionary(p => p.ProductId);
var details = Mapper.Map <IEnumerable <OrderDetailEntity>, List <OrderDetail> >(orderDetails);
details.ForEach(d => d.ProductName = products[d.ProductId].ProductName);
return(details);
}
}
public List<OrderDetail> GetOrderDetails(int orderId)
{
using (var context = new actionEntities())
{
var orderDetails = context.OrderDetailEntities.Where(d => d.OrderId == orderId);
int[] keys = orderDetails.Select(d=>d.ProductId).ToArray();
var products =
(from p in context.ProductEntities
where keys.Contains(p.ProductId)
select p).ToDictionary(p=> p.ProductId);
var details = Mapper.Map<IEnumerable<OrderDetailEntity>, List<OrderDetail>>(orderDetails);
details.ForEach(d => d.ProductName = products[d.ProductId].ProductName);
return details;
}
}
public List <Product> SearchProducts(string productName, double priceFrom, double priceThru, string sortExpression)
{
using (var context = new actionEntities())
{
var query = context.ProductEntities.AsQueryable();
if (!string.IsNullOrEmpty(productName))
{
query = query.Where(p => p.ProductName.Contains(productName));
}
if (priceFrom != -1 && priceThru != -1)
{
query = query.Where(p => p.UnitPrice >= (decimal)priceFrom && p.UnitPrice <= (decimal)priceThru);
}
var products = query.OrderBy(sortExpression, null).ToList();
return(Mapper.Map <List <ProductEntity>, List <Product> >(products));
}
}
public void DeleteMember(Member member)
{
using (var context = new actionEntities())
{
var entity = context.MemberEntities.SingleOrDefault(m => m.MemberId == member.MemberId);
context.MemberEntities.Remove(entity);
context.SaveChanges();
}
}
public void InsertMember(Member member)
{
using (var context = new actionEntities())
{
var entity = Mapper.Map<Member, MemberEntity>(member);
context.MemberEntities.Add(entity);
context.SaveChanges();
// update business object with new id
member.MemberId = entity.MemberId;
}
}
public void UpdateMember(Member member)
{
using (var context = new actionEntities())
{
var entity = context.MemberEntities.SingleOrDefault(m => m.MemberId == member.MemberId);
entity.Email = member.Email;
entity.CompanyName = member.CompanyName;
entity.Country = member.Country;
entity.City = member.City;
//context.Members.Attach(entity);
context.SaveChanges();
}
}
