none
entity framework recommendation to retrieve data RRS feed

  • Question

  • Does entity framework recommend to retrieve chunk of data at once or small queries at a time and get whole. which is more efficient? and which is recommended to use?

    if we used lot of include so then it generates so many outer joins for generate the T-sql needed for database processing. that will be and advantage. but if we run small queries rather than get all chunk at once will call one db call so it will get small a number of database calls. 

    i couldn't found a good MSDN article regarding that. 

    Wednesday, February 22, 2017 10:09 AM

All replies

  • Hello,

    Entity Framework is a ORM Wrapper, beside this it is a quite normal SQL Client as every other one, so every suggestions count also for EF.

    But what for SQL Statements EF generates internal is a different point.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, February 22, 2017 10:43 AM
  • It will return the entire data set, unless you are using the Take or Skip methods, in which case it will return whatever you set in Take or Skip.
    Wednesday, February 22, 2017 11:56 AM
  • Hi Lakshan Meeriyagalla,

    As Hilary Cotter said. if you want to retrieve chunk of data at once, I would suggest that you could create a paging functionality, here is a sample method for your reference.

    /// <summary>
    /// Pages the specified query.
    /// </summary>
    /// <typeparam name="T">Generic Type Object</typeparam>
    /// <typeparam name="TResult">The type of the result.</typeparam>
    /// <param name="query">The Object query where paging needs to be applied.</param>
    /// <param name="pageNum">The page number.</param>
    /// <param name="pageSize">Size of the page.</param>
    /// <param name="orderByProperty">The order by property.</param>
    /// <param name="isAscendingOrder">if set to <c>true</c> [is ascending order].</param>
    /// <param name="rowsCount">The total rows count.</param>
    /// <returns></returns>
    private static IQueryable<T> PagedResult<T, TResult>(IQueryable<T> query, int pageNum, int pageSize,
                    Expression<Func<T, TResult>> orderByProperty, bool isAscendingOrder, out int rowsCount)
    {
        if (pageSize <= 0) pageSize = 20;
        
        //Total result count
        rowsCount = query.Count();
        
        //If page number should be > 0 else set to first page
        if (rowsCount <= pageSize || pageNum <= 0) pageNum = 1;
        
        //Calculate nunber of rows to skip on pagesize
        int excludedRows = (pageNum - 1) * pageSize;
    
        query = isAscendingOrder ? query.OrderBy(orderByProperty) : query.OrderByDescending(orderByProperty);
        
        //Skip the required rows for the current page and take the next records of pagesize count
        return query.Skip(excludedRows).Take(pageSize);
    } 

    #Usage:

    var articles = (from article in Articles
                    where article.Author == "Abc"
                    select article);
    
    var firstPageData =     PagedResult(articles, 1, 20, article => article.PublishedDate, false, out totalArticles);

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 24, 2017 1:41 AM
    Moderator
  • Hi Cole Wu!

    this is about efficiency and what method they recommended us to use. I wanted to know which one is the best-case scenario that EF recommended to use in applications.

    Thanks and Best regards,

    Lakshan.

    Friday, February 24, 2017 5:14 AM
  • Hello,

    Entity Framework is a ORM Wrapper, beside this it is a quite normal SQL Client as every other one, so every suggestions count also for EF.

    But what for SQL Statements EF generates internal is a different point.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Hi Olaf !

    You mean all the advantages and disadvantages that have in sql it will be added to the EF as well. how about when we are using the linq with entity framework when we trying to retrieve the data? how it will affect to the application(if we had a large linq query rather than small queries get together and retrieve the same result which given in small queries)  

    Thanks and Best regards,

    Lakshan.


    Friday, February 24, 2017 5:45 AM
  • Hello,

    Entity Framework is a ORM Wrapper, beside this it is a quite normal SQL Client as every other one, so every suggestions count also for EF.

    But what for SQL Statements EF generates internal is a different point.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Hi Olaf !

    You mean all the advantages and disadvantages that have in sql it will be added to the EF as well. how about when we are using the linq with entity framework when we trying to retrieve the data? how it will affect to the application(if we had a large linq query rather than small queries get together and retrieve the same result which given in small queries)  

    Thanks and Best regards,

    Lakshan.

    You must remember that the Linq query is generated into T-SQL that is executed by the DB engine, and an object or objects are materlized by EF as a retuned result. You can write a bad Linq query that generates bad T-SQL to execute, just like you can write bad T-SQL without EF being involved.

    https://www.simple-talk.com/dotnet/net-tools/entity-framework-performance-and-what-you-can-do-about-it/

    The key is to learn how to use the ORM effectively, which there are plenty of articles using Bing or Google.

    Friday, February 24, 2017 10:16 AM