How could I optimize my filtering on the database side?

Ant6729

Well-known member
Joined
Jan 22, 2019
Messages
53
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();
 

Ant6729

Well-known member
Joined
Jan 22, 2019
Messages
53
Programming Experience
Beginner
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());
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,251
Location
Chesapeake, VA
Programming Experience
10+
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.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,251
Location
Chesapeake, VA
Programming Experience
10+
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.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,466
Location
Sydney, Australia
Programming Experience
10+
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.
 
Top Bottom