How could I optimize my filtering on the database side?

Ant6729

Well-known member
Joined
Jan 22, 2019
Messages
56
Programming Experience
Beginner
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?

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();
 
It should be like this.

C#:
var clientsDalCollection = db.Clients
                            .Include(clientDal => clientDal.ServiceHistoryDalCollection.Select(y => y.ActDalCollection))
                            .Where(clientDal => clientDal.ClientBalanceDalCollection.Any(clientBalanceDal => clientBalanceDal.Value <= 0))
                            .Where(clientDal => clientDal.ServiceHistoryDalCollection.Any(serviceHistoryDal => serviceHistoryDal.ActDalCollection.Any(actDal => actDal.ReservedBalanceByService == null || actDal.ReservedBalanceByService >= 0)))
                            .Where(clientDal => clientDal.ClientBalanceDalCollection.Any());
 
In your simplified post #3, do you need the Where() clause on line #5? Anything that makes it through the Where() on line #3 will have at least one item in the collection, so line #5 would therefore also be true.
 
I believe that EntityFramework has a way for you to get back the SQL execution plan for any given query. That may help you identify the bottlenecks and let you strategically create indexes at best, or at worse let you figure out the how to best change your query patterns -- maybe it's better to query bottom-up rather than top-down.
 
I believe that EntityFramework has a way for you to get back the SQL execution plan for any given query.
Off the top of my head, I believe that it's calling ToQueryString on the query.
 
Back
Top Bottom