Linq statements need optimizing

I have written the following code:

//get the user from the DB
var tmpuser = _db.aspnet_Users.First(q => q.UserName == user.Identity.Name);

//list the direct connections to Verbond
List<Verbond> verb1 = tmpuser.UsersVerbondens
                             .Where(q => q.Schooljaar.Sch_Schooljaar == schooljaarparam)
                             .Select(q => q.Verbond)
                             .ToList();

//list the connected Facturatieverbonden
List<FacturatieVerbonden> verb2 = tmpuser.UsersFacturatieVerbondens
                                         .Where(q => q.Schooljaar.Sch_Schooljaar == schooljaarparam)
                                         .Select(q => q.FacturatieVerbonden)
                                         .ToList();

//loop through the facturatieverbonden and add their verbonds to the first list
foreach (FacturatieVerbonden v in verb2) {
    verb1.AddRange(v.Verbonds);
}

//make a distinct list
List<Verbond> test = verb1.Distinct().ToList();

So, Users can be connected to 0 or more facturatieverbonden and also can be connected to verbond.

A facturatieverbonden can have one or more verbond's under itself.

What I need is a list of all the verbond's the user is connected to, directly, or indirectly via the facturatieverbonden.

My code works but I don't think it's very efficient.开发者_运维知识库 Any hints on making it cleaner?

Your query isn't very LINQy. Here's a potential improvement:

           //list the direct connections to Verbond 
var test = (from q in tmpuser.UsersVerbondens
            where q.Schooljaar.Sch_Schooljaar == schooljaarparam
            select q.Verbond)
           //return distinct values
           .Union
           //list the connected Facturatieverbonden 
           (from q in tmpuser.UsersFacturatieVerbondens
            where q.Schooljaar.Sch_Schooljaar == schooljaarparam
            from v in q.FacturatieVerbonden.Verbonds
            select v)
           //return a List
           .ToList();

By making ToList the very last thing it does, the whole computation can be done in the database, avoiding all the intermediate lists.

Your performance problem is here. Each AddRange call will enumerate v.Verbonds, and each will be a database roundtrip.

//loop through the facturatieverbonden and add their verbonds to the first list 
foreach (FacturatieVerbonden v in verb2) { 
    verb1.AddRange(v.Verbonds); 
} 

To fix it:

//query the connected Facturatieverbonden 
IQueryable<FacturatieVerbonden> verbQuery2 = tmpuser.UsersFacturatieVerbondens 
  .Where(q => q.Schooljaar.Sch_Schooljaar == schooljaarparam) 
  .Select(q => q.FacturatieVerbonden);

verb1.AddRange(verbQuery2.SelectMany(fv => fv.Verbonds));

There's still plenty of room to tighten the performance, but at least this address the gross problem. You could easily observe a 10 times speed up.