none
linq optimization and dataloadoptions RRS feed

  • Question

  • Hi

    After reading some articles about linq I understood that by doing like this...

            Dim db As New DataClassesDataContext
            Dim FormatList = From o In db.tbl_Orientations Select o
            For Each _o In FormatList

            Next


    It would generate x number of calls to the database, I really don't like that idea. Lets say that there are 500 rows, thats not very efficiant to make 500 calls to the database. Lets say that I would call a stored procedure in the same way, would that also make 500 calls? or is that different?

    Then I read about dataloadoptions, If understand it correct, by using that I would only generate one call to the database. But what would be the equivaliant of the code above but using dataloadoptions?


    Best Regards
    Saturday, November 7, 2009 2:05 PM

Answers

  • Hi,

     

    Damien, I think OP is retrieving the navigation properties inside each element of FormatList, then LINQ to SQL will retrieve the children data each time via the parent table primary key, is my understanding correct?  (Parent Table has an 1:Many relationship with the Child Table)   Please feel free to correct me if my understanding is incorrect here. 

    ====================================================================
                    var list = from p in db.ParentTables select p;

                    foreach (var p in list)

                    {

                        foreach (var c in p.ChildTables)

                        {

                            Console.WriteLine(c.Name);

                        }

                    }
    ====================================================================

     

    The database call to retrieve all the parent table objects:

    ====================================================================
    SELECT [t0].[id], [t0].[Name]

    FROM [dbo].[ParentTable] AS [t0]
    ====================================================================

    The corresponding calls to retrieve certain children objects based on the parent table primary key:

    ====================================================================
    SELECT [t0].[id], [t0].[pid], [t0].[Name]

    FROM [dbo].[ChildTable] AS [t0]

    WHERE [t0].[pid] = @p0
    ====================================================================

     

     

    If that is the case, I think the DataLoadOption can solve the problem. 

    ====================================================================
                    DataLoadOptions dlo = new DataLoadOptions();

                    dlo.LoadWith<ParentTable>(p => p.ChildTables);

                    db.LoadOptions = dlo;

     

                    var list = from p in db.ParentTables select p;

                    foreach (var p in list)

                    {

                        foreach (var c in p.ChildTables)

                        {

                            Console.WriteLine(c.Name);

                        }

                    }
    ====================================================================

    The database call:

    ====================================================================
    SELECT [t0].[id], [t0].[Name], [t1].[id] AS [id2], [t1].[pid], [t1].[Name] AS [Name2], (

        SELECT COUNT(*)

        FROM [dbo].[ChildTable] AS [t2]

        WHERE [t2].[pid] = [t0].[id]

        ) AS [value]

    FROM [dbo].[ParentTable] AS [t0]

    LEFT OUTER JOIN [dbo].[ChildTable] AS [t1] ON [t1].[pid] = [t0].[id]

    ORDER BY [t0].[id], [t1].[id]
    ====================================================================

    The situation should be similar if we try to get the parent table from each children tables. 


    magmo, if you are retrieving the parent table objects via stored procedures (only the parent table columns), the situation should be similar with the condition without using DataLoadOptions.   If stored procedure is used here, I recommend you write some JOIN query inside the stored procedure and retrieve all the necessary columns based on your scenario. 

     

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, November 9, 2009 4:00 AM
    Moderator
  • Hi magmo,

     

    In this thread, I shared some of my ideas about using stored procedures to load the relationships in LINQ to SQL, http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/58566b46-3462-4109-afc4-21eadbc04195.  

     

    Besides, could you please provide us with more information about your stored procedure and your detailed scenario?  

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, November 10, 2009 9:42 AM
    Moderator

All replies

  • You don't need to use DataLoad options here - that specifies when you load an entity you should automatically join to it's navigation properties (e.g. load the Customer when you load Orders).

    All you need to do here is change the FormatList line to be:

            Dim FormatList = (From o In db.tbl_Orientations Select o).ToList()

    Now it will be loaded out in a single database call and turned into a List of T (previously it was actually IQueryable of T.

    [)amien
    Sunday, November 8, 2009 1:05 AM
    Moderator
  • Hi,

     

    Damien, I think OP is retrieving the navigation properties inside each element of FormatList, then LINQ to SQL will retrieve the children data each time via the parent table primary key, is my understanding correct?  (Parent Table has an 1:Many relationship with the Child Table)   Please feel free to correct me if my understanding is incorrect here. 

    ====================================================================
                    var list = from p in db.ParentTables select p;

                    foreach (var p in list)

                    {

                        foreach (var c in p.ChildTables)

                        {

                            Console.WriteLine(c.Name);

                        }

                    }
    ====================================================================

     

    The database call to retrieve all the parent table objects:

    ====================================================================
    SELECT [t0].[id], [t0].[Name]

    FROM [dbo].[ParentTable] AS [t0]
    ====================================================================

    The corresponding calls to retrieve certain children objects based on the parent table primary key:

    ====================================================================
    SELECT [t0].[id], [t0].[pid], [t0].[Name]

    FROM [dbo].[ChildTable] AS [t0]

    WHERE [t0].[pid] = @p0
    ====================================================================

     

     

    If that is the case, I think the DataLoadOption can solve the problem. 

    ====================================================================
                    DataLoadOptions dlo = new DataLoadOptions();

                    dlo.LoadWith<ParentTable>(p => p.ChildTables);

                    db.LoadOptions = dlo;

     

                    var list = from p in db.ParentTables select p;

                    foreach (var p in list)

                    {

                        foreach (var c in p.ChildTables)

                        {

                            Console.WriteLine(c.Name);

                        }

                    }
    ====================================================================

    The database call:

    ====================================================================
    SELECT [t0].[id], [t0].[Name], [t1].[id] AS [id2], [t1].[pid], [t1].[Name] AS [Name2], (

        SELECT COUNT(*)

        FROM [dbo].[ChildTable] AS [t2]

        WHERE [t2].[pid] = [t0].[id]

        ) AS [value]

    FROM [dbo].[ParentTable] AS [t0]

    LEFT OUTER JOIN [dbo].[ChildTable] AS [t1] ON [t1].[pid] = [t0].[id]

    ORDER BY [t0].[id], [t1].[id]
    ====================================================================

    The situation should be similar if we try to get the parent table from each children tables. 


    magmo, if you are retrieving the parent table objects via stored procedures (only the parent table columns), the situation should be similar with the condition without using DataLoadOptions.   If stored procedure is used here, I recommend you write some JOIN query inside the stored procedure and retrieve all the necessary columns based on your scenario. 

     

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, November 9, 2009 4:00 AM
    Moderator
  • Ok, so I only need to use the dataloadoption if my query using some kind of joins, seem to me that I would be better of creating a stopred procedure that contains the query then.

     
    Monday, November 9, 2009 7:29 AM
  • Hi magmo,

     

    I think using a stored procedure is similar with directly querying via LINQ.  Only if we try to retrieve the navigation properties, the database calls will increase.  If we just access the regular properties, only one database call will be executed. 

     

    If there is any unclear, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, November 9, 2009 10:24 AM
    Moderator
  • Hi Lingzhi

    Well the result might be the same, but there is at least less code sent over the network to the database when calling a stored procedure + that I find it easier to create sql queries in Sql server's  SQL server manager. And there is no Linq "query designer" in VS.

    And I checked the Sql profiler when running a stored procedure that had joins in itself, there was only one request whe I ran that code.


    Regards
    Monday, November 9, 2009 10:51 AM
  • Hi magmo,

     

    In this thread, I shared some of my ideas about using stored procedures to load the relationships in LINQ to SQL, http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/58566b46-3462-4109-afc4-21eadbc04195.  

     

    Besides, could you please provide us with more information about your stored procedure and your detailed scenario?  

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, November 10, 2009 9:42 AM
    Moderator
  • Hi magmo,


    How is the problem?   If you need any further assistance, please feel free to let me know. 

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, November 16, 2009 7:04 AM
    Moderator