Get one record for each member


i have a checkOut table that store transaction of member , i want a summery table to show summery of all member as list of users and show total of transaction as a column 

how can i do this?

this is my code

                var checkOuts =
                    db.CheckOuts.Select(check =>
                        new CheckOutIndexViewModel()
                            Id = check.Id,
                            Creditor = check.Users.CheckOuts.Sum(x => x.Creditor),
                            Debtor = check.Users.CheckOuts.Sum(x => x.Debtor),
                            Total = check.Total,
                            ApplicationUserId = check.ApplicationUserId,
                            UserFname = check.Users.FName,
                            UserLname = check.Users.Lname
                        }).Where(x => x.UserLname.Contains(q) || q == null)
                        .OrderBy(x => x.UserLname);
                const int pageSize = 25;
                int pageNumber = (page ?? 1);

                if (Request.IsAjaxRequest())
                    return PartialView("_Index", checkOuts.ToPagedList(pageNumber, pageSize));
                return View(checkOuts.ToPagedList(pageNumber, pageSize));

try going in reverse

var checkOuts =
        db.Users.Select(user =>
            new CheckOutIndexViewModel()
                Id = User.Id,
                Creditor = user.CheckOuts.Sum(x => x.Creditor),
                Debtor = user.CheckOuts.Sum(x => x.Debtor),
                Total = user.CheckOuts.Sum(x=>x.Total),
                ApplicationUserId = user.Id,
                UserFname = user.FName,
                UserLname = user.Lname,
                count= user.CheckOuts.Sum(x=>x.Id),
            }).Where(x => x.UserLname.Contains(q) || q == null)
            .OrderBy(x => x.UserLname);
            const int pageSize = 25;
            int pageNumber = (page ?? 1);
            if (Request.IsAjaxRequest())
                return PartialView("_Index", checkOuts.ToPagedList(pageNumber, pageSize));
            return View(checkOuts.ToPagedList(pageNumber, pageSize));

ok i try do that but i have this error:


The cast to value type ‘System.Decimal’ failed because the materialized value is null. Either the result type’s generic parameter or the query must use a nullable type.

Make sure that you have all the properties with correct data type in the view model

Leave a Reply