EF6 query with Optimized Filters

I'm working in a Backoffice.

My Problem

"Queries with filters take between 40 and 60 seconds or more, I'd like to know if it's a Performance problem that belongs to my Query, or if the database is old, scant of Indexes, it worked perfectly locally."

Current Panorama

Currently I just did 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 doing a db.Transactions.Take(100) it does 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 query with Filter

The Query improved it with the generation of a Dynamic Query but it remains the same

I'm reviving the Theme because I'm interested

1- Class to Obtain from Database:

public partial class Transaction
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Transaction()
    {
        Logs = new HashSet<Log>();
    }

    public int Id { get; set; }

    public int Status_Id { get; set; }

    public int TransactionNumber { get; set; }

    [Required]
    [StringLength(50)]
    public string Client { get; set; }

    public decimal Amount { get; set; }

    public int Currency { get; set; }

    [Required]
    [StringLength(50)]
    public string BankCode { get; set; }

    [Required]
    [StringLength(50)]
    public string BankAccount { get; set; }

    [Required]
    [StringLength(50)]
    public string BankAccountType { get; set; }

    [Required]
    [StringLength(50)]
    public string ServiceCode { get; set; }

    public DateTime CreationDate { get; set; }

    [Required]
    [StringLength(1000)]
    public string Request { get; set; }

    public int? TransactionType_Id { get; set; }

    public DateTime? LastModificationDate { get; set; }

    [StringLength(50)]
    public string AuditNumber { get; set; }

    [StringLength(50)]
    public string AppId { get; set; }

    [StringLength(255)]
    public string EntityCode { get; set; }

    [StringLength(255)]
    public string BillingNumber { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Log> Logs { get; set; }

    public virtual Status Status { get; set; }

    public virtual TransactionType TransactionType { get; set; }
}

2- Classes Belong to Transaction

public partial class Log
{
    public int Id { get; set; }

    public int Operation_Id { get; set; }

    public DateTime TimeStamp { get; set; }

    [Required]
    public string Information { get; set; }

    public int Transaction_Id { get; set; }
    [JsonIgnore]
    public virtual Transaction Transaction { get; set; }
}

public partial class Status
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Status()
    {
        Transfers = new HashSet<Transfer>();
        Transactions = new HashSet<Transaction>();
    }

    public int Id { get; set; }

    [Required]
    [StringLength(45)]
    public string Description { get; set; }
    [JsonIgnore]
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Transfer> Transfers { get; set; }
    [JsonIgnore]
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Transaction> Transactions { get; set; }
}

[Table("TransactionType")]
public partial class TransactionType
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public TransactionType()
    {
        Transactions = new HashSet<Transaction>();
    }

    public int Id { get; set; }

    [Required]
    [StringLength(50)]
    public string Description { get; set; }
    [JsonIgnore]
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Transaction> Transactions { get; set; }
}

3- Query generation for the Where with Filters

public IQueryable<Crosslayer.Transaction> GetQuery(Crosslayer.Filters.TransactionModel filter)
    {
        IQueryable<Crosslayer.Transaction> query = db.Set<Crosslayer.Transaction>();
        string[] AppType = FormatIntToArrayString(filter.AppTypeArray, 2);
        string[] bankAccountArr = FormatIntToArrayString(filter.BankAccountTypeArray, 2);
        // assuming that you return all records when nothing is specified in the filter

        //if (filter.Id > 0)
        //    query = query.Where(t =>
        //        t.Name >= filter.NameFrom && t.Name <= filter.NameTo);
        if (filter.Id > 0)
            query = query.Where(t =>
                t.Id == filter.Id);

        if (filter.StatusTypeArray.Count() > 0)
            query = query.Where(t =>
                filter.StatusTypeArray.Contains(t.Status_Id));
        if (AppType.Count() > 0)
            query = query.Where(t =>
                 AppType.Contains(t.AppId));
        if (bankAccountArr.Count() > 0)
            query = query.Where(t =>
                bankAccountArr.Contains(t.BankAccount));
        if (filter.TransactionTypeArray.Count() > 0)
            query = query.Where(t =>
                filter.TransactionTypeArray.Contains(t.TransactionType_Id));
        if (filter.CurrencyTypeArray.Count() > 0)
            query = query.Where(t =>
                filter.CurrencyTypeArray.Contains(t.Currency));
        if (filter.Amount_From > 0)
            query = query.Where(t =>
                t.Amount >= filter.Amount_From);
        if (filter.Amount_To > 0 && filter.Amount_To >= filter.Amount_From)
            query = query.Where(t =>
                t.Amount >= filter.Amount_To);
        if (filter.CreationDate_From != null)
            query = query.Where(t =>
                t.CreationDate >= filter.CreationDate_From);
        if (filter.CreationDate_To != null)
            query = query.Where(t =>
                t.CreationDate <= filter.CreationDate_To);
        if (filter.AuditNumber != null && filter.AuditNumber != "")
            query = query.Where(t =>
                t.AuditNumber.Contains(filter.AuditNumber));
        if (filter.BankAccount != null && filter.BankAccount != "")
            query = query.Where(t =>
                t.BankAccount.Contains(filter.BankAccount));
        if (filter.BillingNumber != null && filter.BillingNumber != "")
            query = query.Where(t =>
                t.BillingNumber.Contains(filter.BillingNumber));
        if (filter.ServiceCode != null && filter.ServiceCode != "")
            query = query.Where(t =>
                t.ServiceCode.Contains(filter.ServiceCode));
        if (filter.TransactionNumber >= -1)
            query = query.Where(t =>
                t.TransactionNumber.ToString().Contains(filter.TransactionNumber.ToString()));
        if (filter.Client != null && filter.Client != "")
            query = query.Where(t =>
                t.Client.Contains(filter.Client));

        return query;
    }

4- Recovery of Transactions

var query = GetQuery(pObject.Filter);
var listTrans = query.OrderByDescending(orderByFunc).Skip(pObject.Pagination.Index).Take(pObject.Pagination.Length).ToList();

5- Is it possible to do a Select () in point 4 to avoid bringing Logs, Status and transactionType? to improve the query even more, something like that?

listTrans = query.OrderBy(orderByFunc).Skip(pObject.Pagination.Index).Take(pObject.Pagination.Length)
                    .Select(t => new Transaction()
                    {
                        Id = t.Id,
                        Amount = t.Amount,
                        AppId = t.AuditNumber
                    }).ToList();