How to properly assign my getPur() method to Transaction.PurchasedItems
I need a resultset which is a combination of Transaction and PurchasedItems
I'm looking for the result same like in image. Right now I'm getting the desired result with making a new model which contains all the columns. but is it possible to this without making a new model.
What I've tried is below but I'm getting an error on this line
VM.Transaction.PurchasedItems = getPur().ToList().Where(t => t.CustomerId == id).ToList();
Object reference not set to an instance of an object.
Kindly guide me to make this compact.
Models:
public class Transaction
{
public int TransactionId { get; set; }
public string Progress { get; set; }
public int ProgressId { get; set; }
public string Status { get; set; }
public int StatusId { get; set; }
public decimal Net { get; set; }
public DateTime Date_ { get; set; }
public virtual ICollection<PurchasedItem> PurchasedItems { get; set; }
}
public class PurchasedItem
{
public int CustomerId { get; set; }
string Product { get; set; }
decimal Total { get; set; }
public string ProductRemarks { get; set; }
}
Controller
public ActionResult History(int? id)
{
CustomerViewModel VM = new CustomerViewModel();
VM.Transactions = getTransactions().Where(t => t.CustomerId == id);
VM.Transaction.PurchasedItems = getPur().Where(t => t.CustomerId == id).ToList();
return View(VM);
}
public IEnumerable<PurchasedItem> getPur()
{
var prod = (from c in db.Customers.Include(c => c.CUSTOMERTYPE)
join ch in db.CUSTOMERHEADs.Include(ch => ch.CustomerDatas).Include(ch => ch.Customer).Include(ch => ch.CustomerProgress).Include(ch => ch.CustomerStatu) on c.Id equals ch.CustomerId into l2
from ch in l2.DefaultIfEmpty()
join sm in db.SalesMen on ch.SalesManId equals sm.MasterId into l1
from sm in l1.DefaultIfEmpty()
join cd in db.CustomerDatas on ch.id equals cd.Headid into l3
from cd in l3.DefaultIfEmpty()
select new
{
cid = c.Id,
prod = cd.ProductCode,
price = cd.Price,
qty = cd.Qty,
disc = cd.Disc,
amount = cd.Amount,
remarks = cd.Remarks,
progressid = ch.CustomerProgress == null ? 0 : ch.CustomerProgress.Id,
});
var details = new List<PurchasedItem>();
foreach (var t in prod)
{
details.Add(new PurchasedItem()
{
CustomerId = t.cid,
ProgressId = t.progressid,
Product = t.prod,
Qty = Convert.ToDecimal(t.qty),
Price = Convert.ToDecimal(t.price),
Disc = Convert.ToDecimal(t.disc),
Total = Convert.ToDecimal(t.amount),
ProductRemarks = t.remarks,
});
}
return details;
}
UPDATE
CORRECTION THAT I DID :
public ActionResult History(int? id)
{
var details = getTransactions().Where(t => t.CustomerId == id);
return View(details);
}
public IEnumerable<Transaction> getTransactions()
{
var cust = (from c in db.Customers.Include(c => c.CUSTOMERTYPE)
join ch in db.CUSTOMERHEADs.Include(ch => ch.Customer).Include(ch => ch.CustomerProgress).Include(ch => ch.CustomerStatu)
.Include(ch => ch.CustomerDatas) on c.Id equals ch.CustomerId into l2
from ch in l2.DefaultIfEmpty()
join sm in db.SalesMen on ch.SalesManId equals sm.MasterId into l1
from sm in l1.DefaultIfEmpty()
select new
{
hid = ch==null?0:ch.id,
customerid = c.Id,
voucherno = ch.VoucherNo,
progress = ch.CustomerProgress.Status,
date = ch.Date_==null?DateTime.MinValue : ch.Date_,
appointmentDate = ch.Date2_,
status = ch.CustomerStatu.Status,
sales = sm.Name,
notes = ch.Notes,
progressid = ch.CustomerProgress == null?0 :ch.CustomerProgress.Id,
cd = ch.CustomerDatas.ToList(),
});
var details = new List<Transaction>();
foreach (var t in cust)
{
details.Add(new Transaction()
{
CustomerId = t.customerid,
voucherno = t.voucherno,
Progress = t.progress,
ProgressId = t.progressid,
Date_ = t.date,
Date2_ = t.appointmentDate,
Status = t.status,
Salesman = t.sales,
AppointmentRemarks = t.notes,
PurchasedItems = getPurchase().Where(m => m.Headid == t.hid).ToList(),
});
}
return details;
}
public IEnumerable<PurchasedItem> getPurchase()
{
var prod = (from c in db.Customers.Include(c => c.CUSTOMERTYPE)
join ch in db.CUSTOMERHEADs.Include(ch => ch.CustomerDatas).Include(ch => ch.Customer).Include(ch => ch.CustomerProgress).Include(ch => ch.CustomerStatu) on c.Id equals ch.CustomerId into l2
from ch in l2.DefaultIfEmpty()
join sm in db.SalesMen on ch.SalesManId equals sm.MasterId into l1
from sm in l1.DefaultIfEmpty()
join cd in db.CustomerDatas on ch.id equals cd.Headid into l3
from cd in l3.DefaultIfEmpty()
select new
{
cid = c.Id,
prod = cd.ProductCode,
price = cd.Price,
qty = cd.Qty,
disc = cd.Disc,
amount = cd.Amount,
remarks = cd.Remarks,
progressid = ch.CustomerProgress == null ? 0 : ch.CustomerProgress.Id,
hid = cd==null?0:cd.Headid,
});
var details = new List<PurchasedItem>();
try
{
foreach (var t in prod)
{
details.Add(new PurchasedItem()
{
Headid = Convert.ToInt32(t.hid),
CustomerId = t.cid,
ProgressId = t.progressid,
Product = t.prod,
Qty = Convert.ToDecimal(t.qty),
Price = Convert.ToDecimal(t.price),
Disc = Convert.ToDecimal(t.disc),
Total = Convert.ToDecimal(t.amount),
ProductRemarks = t.remarks,
});
}
}
catch (Exception ex)
{
throw ex;
}
return details;
}
