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)
                .OrderBy(t => t.Collection.Count);




source to share

1 answer

Well, you can always try to remove those two lines from joinQuery

Where! a.Collection.Any (v => v.AccountId.Contains (account.Id))


number of sequence numbers in descending order

the first line has already been filtered in the first request and order, well do the order in the last request, so it doesn't make sense to do it twice



All Articles