I need help speeding up this EF LINQ query

I am using EntityFramework 6 and am facing some important speed issues - this request takes two seconds to run. I spent most of the day using LinqPad to speed up the query, but I could only get it in 4 to 2 seconds. I've tried grouping, joining, etc., but the generated SQL looks too complicated to me. I am guessing that I am just using LINQ wrong.

Here is what I am trying to do

• Find everything A
  
  where Valid
  
  is null and AccountId
  
  is not the current user
• Make sure Collection
  
  from B
  
  does not contain B
  
  where AccountId
  
  is the current user
• Order received A
  
  by number B
  
  in your collection in descending order
• Anyone A
  
  that doesn't have B
  
  to be at the end of the returned results.

I have models that look like this:

public class A
{
    public int Id { get; set; }
    public bool? Valid { get; set; }
    public string AccountId { get; set; }
    public virtual ICollection<B> Collection { get; set; }
}

public class B
{
    public int Id { get; set; }
    public bool Valid { get; set; }
    public string AccountId { get; set; }
    public DateTime CreatedDate { get; set; }
    public virtual A Property { get; set; }
}

      

        
        
        
      

    

The table for A

contains about a million rows, and B

will eventually total about ten million. B

Sits at 50,000 right now .

This is what the request looks like. This gives me the expected results, but I have to run several times orderby

and do other unnecessary steps:

var filterA = this.context.A.Where(gt => gt.Valid == null && !gt.AccountId.Contains(account.Id));

var joinedQuery = from b in this.context.B.Where(gv => !gv.AccountId.Contains(account.Id))
                            join a in filterA on gv.A equals a
                            where !a.Collection.Any(v => v.AccountId.Contains(account.Id))
                            let count = gt.Collection.Count()
                            orderby count descending 
                            select new { A = gt, Count = count };

IQueryable<GifTag> output = joinedQuery
                .Where(t => t.A != null)
                .Select(t => t.A)
                .Distinct()
                .Take(20)
                .OrderBy(t => t.Collection.Count);

      

        
        
        
      

    

thank