none
LoadOptions taking forever! RRS feed

  • Question

  • hi,

    i am having a preformance issue with the loadOptions, this is my code:

     

    using(var db = new DataBaseClassesDataContex())
    {
      var loadOptions = new DataLoadOptions();
      loadOptions.LoadWith<TraineesTable>(t=>t.TraineesInMiunTables);
      db.LoadOptions = loadOptions;
    
      var Trainees = db.TraineesInMiunTables.Where(tm=>tm.miun == miun).
                                  .select(t=>t.TraineesTable)
    
    
      return Trainees.ToList();
    }
    


    if i delete the loadoptions it works perfect

    otherwise it takes 12 secends to run the code!

    why is that?

    and of can i fix that??

     

    p.s

     the code retrieves 700 results of TraineesTable, and 2 to 4 results for each TraineesTable of type TraineesInMiunTable

     

    however i actualy need only 1 result per result but if i try to write:

     

    loadOptions.LoadWith<traineesTable>(t=>t.TraineesInMiunTables.take(1))
    

    i get a runtime error...

     

     

    thanks in advance

    jony [=

    Tuesday, August 23, 2011 5:21 AM

Answers

  • Hi Jony,

    The LoadWith method is to retrieve specified data related to the main target by using a lambda expression. 

    When I reproed your scenario, I got the error when I take(1), I think you could try to use the AssociateWith method. It filters the objects retrieved for a particular relationship.

    See the following code from MSDN library:

    In the following example, the AssociateWith method limits the Orders retrieved to those that have not been shipped today. Without this approach, all Orders would have been retrieved even though only a subset is desired.

    Northwnd db = new Northwnd(@"c:\northwnd.mdf");
    DataLoadOptions dlo = new DataLoadOptions();
    dlo.AssociateWith<Customer>(c => c.Orders.Where(p => p.ShippedDate != DateTime.Today));
    db.LoadOptions = dlo;
    var custOrderQuery =
        from cust in db.Customers
        where cust.City == "London"
        select cust;

    foreach (Customer custObj in custOrderQuery)
    {
        Console.WriteLine(custObj.CustomerID);
        foreach (Order ord in custObj.Orders)
        {
            Console.WriteLine("\t {0}",ord.OrderDate);
        }
    }

    I hope this can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by jony feldman Sunday, August 28, 2011 11:54 AM
    Thursday, August 25, 2011 8:01 AM
    Moderator

All replies

  • Hi Jony,

    I have never seen, let alone used, the DataLoadOptions before so I cannot be sure what it's doing but from reading the manual you seem to be loading all Trainees and all related TraineesInMiuns. I don't see any benefit to doing that in the context of your sample code.

    Your query looks like two parts: 1) find any TraineesInMiuns where tm.miun == miun and then 2) find each of their related Trainees - but only once.

    The first query could find multiple TraineesInMiuns for the same Trainee which is why you may get "2 to 4 results", but you only want it to find 0 or 1 result. you could try using .Distinct() somewhere [e.g. Trainees.Distinct().ToList()].

    In SQL I would expect to write it something like

    SELECT t.*
    
    FROM Trainees t
    
    INNER JOIN ( Select Distinct(tim.TraineeId) AS TraineeId
    
    			 FROM TraineesInMiun tm
    
    			 WHERE tm.miun = @miun
    
    		 ) AS distinctMiun
    
    ON t.Id = distinctMiun.TraineeId
    
    


    (I've assumed the names of the trainee ID column)

     

    Cheers,

    John.

     

    Wednesday, August 24, 2011 8:35 AM
  • hi John,

    thanks again for helping i really appreciate it

     

    i need the loadwith so that in another part of the code ill be able to write for example

    TraineesTable trainee = new TraineesTable();

    trainee.TraineesInMiunTables.where(blablalba);

    (yes i know that this will give me nothing on this example it was just the main idea)

    the problem is not how to get a single value i can do that with take(1)

    but with the assosiantion of the trainee on traiineeInMiun table...

     

     

    in short i need to get all the data from Trainees and the coresponding data from TraineeInMiun

     

    you see the problem?


    Wednesday, August 24, 2011 2:09 PM
  • Hi Jony,

    The LoadWith method is to retrieve specified data related to the main target by using a lambda expression. 

    When I reproed your scenario, I got the error when I take(1), I think you could try to use the AssociateWith method. It filters the objects retrieved for a particular relationship.

    See the following code from MSDN library:

    In the following example, the AssociateWith method limits the Orders retrieved to those that have not been shipped today. Without this approach, all Orders would have been retrieved even though only a subset is desired.

    Northwnd db = new Northwnd(@"c:\northwnd.mdf");
    DataLoadOptions dlo = new DataLoadOptions();
    dlo.AssociateWith<Customer>(c => c.Orders.Where(p => p.ShippedDate != DateTime.Today));
    db.LoadOptions = dlo;
    var custOrderQuery =
        from cust in db.Customers
        where cust.City == "London"
        select cust;

    foreach (Customer custObj in custOrderQuery)
    {
        Console.WriteLine(custObj.CustomerID);
        foreach (Order ord in custObj.Orders)
        {
            Console.WriteLine("\t {0}",ord.OrderDate);
        }
    }

    I hope this can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by jony feldman Sunday, August 28, 2011 11:54 AM
    Thursday, August 25, 2011 8:01 AM
    Moderator
  • thanks ill try that [=
    Sunday, August 28, 2011 11:54 AM