public BusinessObjects.Member GetMemberByOrder(int orderId)
{
using (var context = DataContextFactory.CreateContext())
{
var member = context.Members.SelectMany(c => context.Orders
.Where(o => c.MemberId == o.MemberId && o.OrderId == orderId),
(c, o) => c).SingleOrDefault(c => true);
return(Mapper.Map <Member, BusinessObjects.Member>(member));
}
}
public void DeleteMember(BusinessObjects.Member member)
{
var entity = Mapper.Map <BusinessObjects.Member, Member>(member);
using (var context = DataContextFactory.CreateContext())
{
context.Members.Attach(entity, false);
context.Members.DeleteOnSubmit(entity);
context.SubmitChanges();
}
}
public BusinessObjects.Category GetCategoryByProduct(int productId)
{
using (var context = DataContextFactory.CreateContext())
{
var category = context.Categories.SelectMany(c => context.Products
.Where(p => c.CategoryId == p.CategoryId)
.Where(p => p.ProductId == productId),
(c, p) => c).SingleOrDefault(c => true);
return(Mapper.Map <Category, BusinessObjects.Category>(category));
}
}
public void InsertMember(BusinessObjects.Member member)
{
var entity = Mapper.Map <BusinessObjects.Member, Member>(member);
using (var context = DataContextFactory.CreateContext())
{
context.Members.InsertOnSubmit(entity);
context.SubmitChanges();
// update business object with new id
member.MemberId = entity.MemberId;
}
}
public void UpdateMember(BusinessObjects.Member member)
{
var entity = Mapper.Map <BusinessObjects.Member, Member>(member);
using (var context = DataContextFactory.CreateContext())
{
var original = context.Members.SingleOrDefault(m => m.MemberId == member.MemberId);
original.Email = member.Email;
original.CompanyName = member.CompanyName;
original.Country = member.Country;
original.City = member.City;
context.SubmitChanges();
}
}
public List <BusinessObjects.Product> SearchProducts(string productName, double priceFrom, double priceThru, string sortExpression)
{
using (var context = DataContextFactory.CreateContext())
{
var query = context.Products.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 <Product>, List <BusinessObjects.Product> >(products));
}
}
public List <BusinessObjects.Member> GetMembersWithOrderStatistics(string sortExpression)
{
using (var context = DataContextFactory.CreateContext())
{
var customers = context.Members.Select(c =>
new BusinessObjects.Member
{
MemberId = c.MemberId,
Email = c.Email,
CompanyName = c.CompanyName,
City = c.City,
Country = c.Country,
NumOrders = context.Orders.Where(o => o.MemberId == c.MemberId).Count(),
LastOrderDate = context.Orders.Where(o => o.MemberId == c.MemberId).Max(o => o.OrderDate)
}).OrderBy(sortExpression);
// Exclude customers without orders
return(customers.Where(c => c.NumOrders > 0).ToList());
}
}
