c # – EF6 Query with Optimized Filters

I am working in a Backoffice.

My problem

"Queries with filters take me between 40 and 60 seconds or more, I would like to know if it is a problem of Performance of my Query, or if the database that is old, scarce of Indices is fucking me because Locally when I tried it worked perfect . "

Current Panorama

I have just done a Deploy on PreProd.

Database Tables testing:

        Logs <== Transactions ==> TransactionType

Therefore Entity in the query Brings all the Logs of each Transaction and the type of transaction. For each Transaction, 4-5 Logs are generated.
The database has 3 million Transactions and some 19 million logs.

At the moment of making a db.Transactions.Take (100) makes it fast.

But when using a filter for a much more personalized query it takes between 40 and 50 seconds, or more and this sometimes generates TimeOut.

This is an example of the consultation with Filter

The Query performs a Where (x = x.Id == Filter.Object.Id or Filter.Object.Id <= 0 0)

With this I try to make you ignore the Id filter if this is Id <= 0

listTrans = db.Transactions.Where (
                (x =>
                (x.Id == pObject.Filter.Id || pObject.Filter.Id == 0)
                && (pObject.Filter.StatusTypeArray.Contains (x.Status_Id) || pObject.Filter.StatusTypeArray.Count () == 0)
                && (AppType.Contains (x.AppId) || AppType.Count () == 0)
                && (pObject.Filter.TransactionTypeArray.Contains (x.TransactionType_Id) || pObject.Filter.TransactionTypeArray.Count () == 0)
                && (pObject.Filter.CurrencyTypeArray.Contains (x.Currency) || pObject.Filter.CurrencyTypeArray.Count () == 0)
                && (bankAccountArr.Contains (x.BankAccountType) || bankAccountArr.Count () == 0)
                && (x.Amount> = pObject.Filter.Amount_From || pObject.Filter.Amount_From == -1)
                && (x.Amount <= pObject.Filter.Amount_To || pObject.Filter.Amount_To == -1)
                && (x.CreationDate >= pObject.Filter.CreationDate_From || pObject.Filter.CreationDate_From == null)
                && (x.CreationDate <= pObject.Filter.CreationDate_To || pObject.Filter.CreationDate_To == null)
                && (x.AuditNumber.Contains (pObject.Filter.AuditNumber) || pObject.Filter.AuditNumber == "" || pObject.Filter.AuditNumber == null)
                && (x.BankAccount.Contains (pObject.Filter.BankAccount) || pObject.Filter.BankAccount == "" || pObject.Filter.BankAccount == null)
                && (x.BillingNumber.Contains (pObject.Filter.BillingNumber) || pObject.Filter.BillingNumber == "" || pObject.Filter.BillingNumber == null)
                && (x.ServiceCode.Contains (pObject.Filter.ServiceCode) || pObject.Filter.ServiceCode == "" || pObject.Filter.ServiceCode == null)
                && (x.Client.Contains (pObject.Filter.Client) || pObject.Filter.Client == "" || pObject.Filter.Client == null)
                && (x.TransactionNumber.ToString (). Contains (pObject.Filter.TransactionNumber.ToString ()) || pObject.Filter.TransactionNumber == -2)
                )). OrderBy (orderByFunc) .Skip (pObject.Pagination.Index) .Take (pObject.Pagination.Length) .ToList ();

Thank you very much and I hope someone can be of help.