Select list of objects from navigation property in Entity Framework Core

user_2975931 0
Viewed 1283

I have the below entity which has a navigation property.

public class Member
    {
        public Member()
        {
            Id = 0;
            IsActive = true;
            BillDetails = new List<BillDetail>();            
        }

        public int Id { get; set; }        
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public bool IsActive { get; set; }
        public int? OrgId { get; set; }    
        public IList<BillDetail> BillDetails { get; set; }
    }

And I have a generic repository which is:

public abstract class BaseRepository<TEntity, TId> : IRepository<TEntity, TId> where TEntity : class
    {
        private readonly IApplicationDbContext _db;

        public BaseRepository(IApplicationDbContext db)
        {
            _db = db;
        }

        public virtual void Add(TEntity entity)
        {
            _db.Set<TEntity>().Add(entity);
            _db.Entry(entity).State = EntityState.Added;
        }

        public virtual bool Update(TEntity entity)
        {
            _db.Set<TEntity>().Attach(entity);
            _db.Entry(entity).State = EntityState.Modified;
            return true;
        }

        public virtual bool Delete(TEntity entity)
        {
            _db.Set<TEntity>().Remove(entity);
            _db.Entry(entity).State = EntityState.Deleted;
            return true;
        }

        public virtual IQueryable<TEntity> Query()
        {
            return _db.Set<TEntity>().AsQueryable();
        }

        public virtual EntityEntry<TEntity> Entry(TEntity entity)
        {
            return _db.Entry(entity);
        }

        public void Attach(TEntity entity)
        {
            if (entity == null || _db.Entry(entity).State != EntityState.Detached)
                return;

            _db.Set<TEntity>().Attach(entity);
        }

        public void Detach(TEntity entity)
        {
            if (entity == null)
                return;

            _db.Entry(entity).State = EntityState.Detached;
        }

        public abstract Task<TEntity> FindAsync(TId id);
    }

From my service class I want to get collection of objects of below type

public class MemberBill
{
    public int MemberId {get; set;}
    public decimal BillAmount {get; set;}
}

In my service class I have the following query. Now How can I get the list of objects (MemberBill) from the below query?

var query = (_memberRepository.Query().Where(x => x.IsActive == true && x.OrgId == orgId)
                .Include(x => x.BillDetails.Where(y => y.IsActive == true))).AsNoTracking().AsQueryable();

            List<IList<BillDetail>> memberBills = query.Select(x => x.BillDetails).ToList();

            memberBills.Select(x => new MemberBill
            {
                MemberId = x.
            }).ToList();

Please help me to find the solution.

entity-framework-core .net-core entity-framework linq c#
2 Answers

try this

var query = _memberRepository.Query()
.Where(x => x.IsActive == true && x.OrgId == orgId)
.Include(x => x.BillDetails.Where(y => y.IsActive == true))
.AsNoTracking()
.ToList();

var memberBills=new List<MemberBill>();

foreach(var m in query)
{
memberBills.Add( new MemberBill 
                  {
                   MemberId= m.Id, 
                   BillAmount=m.BillDetails.Sum(i=> i.Amount)
                  });
}
user_113922900

Leaving here performant solution. Current accepted answer uses a lot of memory and server resources.

var query = 
    from m in _memberRepository.Query()
    where m => m.IsActive == true && m.OrgId == orgId
    from bd in m.BillDetails.Where(bd => bd.IsActive == true)
    group bd by new { m.Id } into g
    select new MemberBill
    {
        MemberId = g.Key.Id,
        BillAmount = g.Sum(x => x.Amount
    };

var memberBills = query.ToList();
user_106463160
Related