Hello, guys!
It is not a secret, that we need to filter data on the database side. But, I tried and did not find good solution to do this using IQueryable in my code.
That is why I used some a data materialization syntax in several places, like ToList() .
I would like to filtere on the database side.
Is this possible in my case?
It is not a secret, that we need to filter data on the database side. But, I tried and did not find good solution to do this using IQueryable in my code.
That is why I used some a data materialization syntax in several places, like ToList() .
I would like to filtere on the database side.
Is this possible in my case?
C#:
//getting elements tree:
var clientsDalCollection = db.Clients
.Include(x => x.ServiceHistoryDalCollection.Select(y => y.ActDalCollection))
//.SelectMany(el=>el.ClientBalanceDalCollection.Where(xx=>xx.Value<=0))
.AsQueryable();
//manual mapping part:
var mappedClientBusinessCollection = await clientsDalCollection
.Select(x => new ClientBusinessModel()
{
ClientId = x.ClientId,
ContractNumber = x.ContractNumber,
IsReseller = x.IsReseller,
ClientName = x.ClientName,
ClientBalanceBusinessCollection = x.ClientBalanceDalCollection.Select(clientBalanceDal =>
new ClientBalanceBusinessModel()
{
ClientBalanceId = clientBalanceDal.ClientBalanceId,
ClientId = clientBalanceDal.ClientId,
BalanceTypeId = clientBalanceDal.BalanceTypeId,
Value = clientBalanceDal.Value,
IsCurrent = clientBalanceDal.IsCurrent,
CreditLimit = clientBalanceDal.CreditLimit
})
// 1 filter balance value less or equal 0:
.Where(clientBalanceBusinessModel => clientBalanceBusinessModel.Value <= 0).ToList(),
ServiceHistoryBusinessCollection = x.ServiceHistoryDalCollection.Select(serviceHistoryDal =>
new ServiceHistoryBusinessModel()
{
ServiceHistoryId = serviceHistoryDal.ServiceHistoryId,
ClientId = serviceHistoryDal.ClientId,
DomainHistoryId = serviceHistoryDal.DomainHistoryId,
ServiceTypeId = serviceHistoryDal.ServiceTypeId,
DurationDays = serviceHistoryDal.DurationDays,
PaymentDate = serviceHistoryDal.PaymentDate,
OrderActionId = serviceHistoryDal.OrderActionId,
VatId = serviceHistoryDal.VatId,
CostId = serviceHistoryDal.CostId,
Discount = serviceHistoryDal.Discount,
ActBusinessCollection = serviceHistoryDal.ActDalCollection.Select(actDal => new ActBusinessModel()
{
ActId = actDal.ActId,
CostId = actDal.CostId,
VatId = actDal.VatId,
TariffPlanId = actDal.TariffPlanId,
ReportDate = actDal.ReportDate,
ActivationDate = actDal.ActivationDate,
DurationDays = actDal.DurationDays,
Discount = actDal.Discount,
ReservedBalanceByService = actDal.ReservedBalanceByService,
ServiceHistoryId = actDal.ActId
})
// 2 filter reservedBalanceByService more or equal 0 or just null:
.Where(actBusinessModel
=> actBusinessModel.ReservedBalanceByService == null || actBusinessModel.ReservedBalanceByService >= 0)
.ToList()
}).ToList()
})
//filtering clientBalanceBusinessCollections that did not satisfy 2 nd condition.
.Where(clientBusinessModel => clientBusinessModel.ClientBalanceBusinessCollection.Count > 0)
.ToListAsync();