locked
how to group and select related data loading by eager loading RRS feed

  • Question

  • User1984354752 posted

    Hi over there:

    I have been struggling for weeks with the following: I have an asp,net core project Viewcomponent that shows the details of  every contract . The Contract entity and ContractAmounts are related in a one to many relationship and the ContractAmmounts is related to a Funding entity in a  a many to one relationship. In simple terms:    a Contract can have  many ContractAmounts  and t Contract Amounts can have one assigned. Funding .EF core has created the navigation properties as ContractAmounts.IdFundingNavigation that links the Contract entity with the ContractAmounts and Funding entities. . 

    The code  below load eagerly the contractamounts and funding related data. As can be seen, the Viewmodel has an Ienumerable property called Fundlist where I want to put the list of the related fundings with the respective contract amounts. 

     public IViewComponentResult Invoke(int? Id = null)
            {
                IQueryable<ContractViewModel> query = _UoW.Repository<Contracts>().Getlist(includeProperties: "ContractAmounts.IdFundingNavigation", filter: j => j.Id == Id).Select(f => new ContractViewModel
                {

                 FundList = f.ContractAmounts.Select(p => p.IdFundingNavigation).Select(s => new FundingViewModel { FundingDescription = s.Fundingdescription, ProjectNumber = s.ProjectNumber, Resolution = s.Resolution, Funding_Amount = s.ContractAmounts.Sum(s => s.ContractBreakdownAmount) }),

            });
                return View("Default", query.SingleOrDefault());
            }

    The view returns a list of the related funding associated to the contract.. The problems that is driving me crazy is that I want to calculated the sum of the contractamounts of each funding which  can be achieved easily as the  related contractamounts is loaded . The code highlighted in yellow  runs but isn't giving me what I want 

    1) isn't grouping the funding 

    2) isn't calculating the sum properly 

    Obviously the missing part is the Groupby operation but I have failed to group the data correctly.  The challenge that I'm finding in doing this is that the  first part of the code ( f.ContractAmounts) is returning a collection and the selection of p.IdFundingNavigation return the properties of the funding entity ......in other words the select represent a many to one relationship.

     FundList = f.ContractAmounts.Select(p => p.IdFundingNavigation).Groupby ????? .....or selectMany ??????

    I don't know how to group and perform aggregated operation in this relationship .....I've read something about the selectmany but it has been a week since I have been trying .....please assist.

    Best regards. 

    Sunday, January 10, 2021 12:52 PM

All replies

  • User1686398519 posted

    Hi 9peculiar, 

    You can modify your code like this:

                List<ContractViewModel> test = new List<ContractViewModel>();
                var result = _UoW.Repository<Contract>().Get(
                    includeProperties: "ContractAmounts.Fundings"
                    ).ToList();
                result.ForEach(r =>
                {
                    ContractViewModel model = new ContractViewModel(); 
                    model.ContractId = r.ContractId;
                    model.ContractName = r.ContractName;
                    model.FundingViewModel = new List<FundingViewModel>();
                    test.Add(model);
                    var contractAmounts = r.ContractAmounts;
                    contractAmounts.GroupBy(i => i.FundingId).ToList().ForEach(t =>
                    {
    
                        model.FundingViewModel.Add(new FundingViewModel
                        {
                            FundingId = t.Key,
                            Fundingdescription = t.FirstOrDefault().Fundings.Fundingdescription,
                            Funding_Amount = t.ToList().Sum(m => m.Fundings.Funding_Amount),
                        });
                    });
                });

    Model

        public class ContractViewModel
        {
            public int ContractId { get; set; }
            public string ContractName { get; set; }
            public List<FundingViewModel> FundingViewModel { get; set; }
    
        }
        public class FundingViewModel
        {
            public int FundingId { get; set; }
            public decimal Funding_Amount { get; set; }
            public string Fundingdescription { get; set; }
            public decimal Funding_AmountSum { get; set; }
    
        }
        public class Contract
        {
            [Key]
            public  int ContractId { get; set; }
            public string ContractName { get; set; }
            public List<ContractAmounts> ContractAmounts { get; set; }
        }
        public class ContractAmounts
        {
            [Key]
            public int ContractAmountsId { get; set; }
            public string ContractAmountsName { get; set; }
            public int ContractId { get; set; }
            [ForeignKey("ContractId")]
            public Contract Contract { get; set; }
            public int FundingId { get; set; }
            [ForeignKey("FundingId ")]
            public Funding Fundings { get; set; }
        }
        public class Funding
        {
            [Key]
            public int FundingId { get; set; }
            public decimal Funding_Amount { get; set; }
            public string Fundingdescription { get; set; }
            public List<ContractAmounts> ContractAmounts { get; set; }
        }

    Best Regards,

    YihuiSun

    Monday, January 11, 2021 10:05 AM
  • User1984354752 posted

    Hi YihuiSun:

    Though the code may run, it doesn't suit to my context. In my initial post you can see that actually what you advised me to do, shall be done as a subquery to get the list of fundings. Your coding has numerous rounds to the database and doesn't answer my question.

    Monday, January 11, 2021 1:26 PM