locked
Different way of fetching data from db by EF RRS feed

  • Question

  • I am not very good in EF. i have few questions on fetching data from db by EF. tell me when EF start fetching data from db? what function or indication is there to inform EF to fetch data ?

    1) NorthwindEntities entities = new NorthwindEntities();
    return View(from customer in entities.Customers.Take(10)
    select customer);
    see my point 1 and tell me in this case Take function will fetch data from db ?

    2) TestEntities context = new TestEntities();
    var query = from data in context.Employee
    orderby data.name
    select data;
    see my point 2. here they do not use ToList() function how how data will be fetch from db?
    3) public IEnumerable<UserName> GetUserFirstName() {
    var user = from o in dre.SysUsers
    select new UserName {
    SysUserID = o.SysUserID,
    FirstName = o.FirstName
    };
    return user.ToList();
    }
    here ToList(); will fetch data i know.

    4) using (var context = new UnivercityContext())
    {
    var query = from p in context.Professors
    orderby p.Name
    select p;
    
    foreach (var professor in query)
    {
    Console.WriteLine(professor.Name);
    }
    }
    here .ToList(); is not used so How EF will fetch data? in this case EF wil fetch when we iterate query in foreach ?

    so please tell me what other way exist to fetch data from db by EF. please discuss.
    Monday, February 26, 2018 11:59 AM

Answers

  • EF is going to use a collection as a returned result, unless Single() SingleOrDefault(), First() or FirstOrDefult() is used when a collection is not returned,  and an individual object is returned.

    ToList() means return a List<T> of the objects. 

    • Marked as answer by Sudip_inn Tuesday, February 27, 2018 9:05 AM
    Monday, February 26, 2018 11:02 PM
  • Hi Sudip_inn,

    >>see my point 1 and tell me in this case Take function will fetch data from db ?

    Entity framework translates Take by using a top clause instead of fetch data from database at once.

    >>see my point 2. here they do not use ToList() function how how data will be fetch from db?

    Related records are retrieved from database on demand (when needed) while iterating, which means a different SQL query is sent to the database for each record.

    >>here .ToList(); is not used so How EF will fetch data? in this case EF wil fetch when we iterate query in foreach ?

    Same as point2:

    For more information, please refer to:

    https://chsakell.com/2013/08/24/retrieving-data-with-dbcontext/

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you.
    Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there.
    There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Best regards,

    Zhanglong


    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.

    • Marked as answer by Sudip_inn Tuesday, February 27, 2018 9:04 AM
    Tuesday, February 27, 2018 2:00 AM
  • see what i am trying to ask.

    using (var context = new UnivercityContext())
    {
    var query = from p in context.Professors
    orderby p.Name
    select p;
    
    foreach (var professor in query)
    {
    Console.WriteLine(professor.Name);
    }
    }

    in your query you have tolist() but in my query there is no tolist() so how data will be fetch from db?

    i guess when foreach loop will start then it will fetch data one by one......am i right?

    Your way the data is being read twice, because all of it is inside if the "using" statement the Linq query is reading all the data from the database putting it into a collection. And then you are going into a forloop on the collection inside the 'using' statement that has ties still to the database. And each iteration of the objects being accessed in the loop,  EF is going back to the database. 

    My way, the collection is not defined inside the 'using' statement with a List<T>. It is outside of the using statement so when the using statement closes the connection, the objects in the collection of objects are disconnected from the database.  EF cannot go back to the database and read each record AGAIN from the database while the loop is being executed.

    Understand what the ToList() is doing.

    https://www.dotnetperls.com/tolist

    Your way, if  query fetched 100,000 records being read,  that's 200,000 reads being done when it should have been only 100,000 reads being done, because of the way you have your code implemented that's doing a double read of the same data from the database.

    • Marked as answer by Sudip_inn Sunday, March 4, 2018 3:29 PM
    Thursday, March 1, 2018 8:34 PM

All replies

  • EF is going to use a collection as a returned result, unless Single() SingleOrDefault(), First() or FirstOrDefult() is used when a collection is not returned,  and an individual object is returned.

    ToList() means return a List<T> of the objects. 

    • Marked as answer by Sudip_inn Tuesday, February 27, 2018 9:05 AM
    Monday, February 26, 2018 11:02 PM
  • Hi Sudip_inn,

    >>see my point 1 and tell me in this case Take function will fetch data from db ?

    Entity framework translates Take by using a top clause instead of fetch data from database at once.

    >>see my point 2. here they do not use ToList() function how how data will be fetch from db?

    Related records are retrieved from database on demand (when needed) while iterating, which means a different SQL query is sent to the database for each record.

    >>here .ToList(); is not used so How EF will fetch data? in this case EF wil fetch when we iterate query in foreach ?

    Same as point2:

    For more information, please refer to:

    https://chsakell.com/2013/08/24/retrieving-data-with-dbcontext/

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you.
    Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there.
    There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Best regards,

    Zhanglong


    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.

    • Marked as answer by Sudip_inn Tuesday, February 27, 2018 9:04 AM
    Tuesday, February 27, 2018 2:00 AM
  • Oh, and about #4 again, you should disconnected from the database using EF connection, because you are doing a double read of data from the database

    1st query to get the data, And then, the  forloop will go back again and read each record from the database as the loop is iterated. 

    So you disconnect/close the connection after the 1st query and then loop on a disconnected collection.

    Tuesday, February 27, 2018 7:58 AM
  • 1) see this below code and tell me how to get customer data

    1) NorthwindEntities entities = new NorthwindEntities();
    var cust= from customer in entities.Customers.Take(10)
    select customer;

    how to retrieve customer id and name after executing above code.

    2) using (var context = new UnivercityContext())
    {
    var query = from p in context.Professors
    orderby p.Name
    select p;
    
    foreach (var professor in query)
    {
    Console.WriteLine(professor.Name);
    }
    }

    tell me if there would be 5 professor in db table then foreach loop will run 5 times...am i right. so tell me in each foreach iteration every time db table will be hit for getting single data from table ? what sql EF will execute for each iteration...just give me a sample sql in this case.

    thanks

    Tuesday, February 27, 2018 9:11 AM
  • var professors = new List<Professors>();
    
     using (var context = new UnivercityContext())
    {
       professors = (from p in context.Professors
    orderby p.Name
    select p).ToList();
    
    }
    
    //the connection is closed after the using statement, and the collection professors is never within the scope of the using statement. It's a disconnected collection  professors so when you iterate over professors, it's not going back to the database. 



    Tuesday, February 27, 2018 11:16 AM
  • see what i am trying to ask.

    using (var context = new UnivercityContext())
    {
    var query = from p in context.Professors
    orderby p.Name
    select p;
    
    foreach (var professor in query)
    {
    Console.WriteLine(professor.Name);
    }
    }

    in your query you have tolist() but in my query there is no tolist() so how data will be fetch from db?

    i guess when foreach loop will start then it will fetch data one by one......am i right?

    Thursday, March 1, 2018 6:12 PM
  • see what i am trying to ask.

    using (var context = new UnivercityContext())
    {
    var query = from p in context.Professors
    orderby p.Name
    select p;
    
    foreach (var professor in query)
    {
    Console.WriteLine(professor.Name);
    }
    }

    in your query you have tolist() but in my query there is no tolist() so how data will be fetch from db?

    i guess when foreach loop will start then it will fetch data one by one......am i right?

    Your way the data is being read twice, because all of it is inside if the "using" statement the Linq query is reading all the data from the database putting it into a collection. And then you are going into a forloop on the collection inside the 'using' statement that has ties still to the database. And each iteration of the objects being accessed in the loop,  EF is going back to the database. 

    My way, the collection is not defined inside the 'using' statement with a List<T>. It is outside of the using statement so when the using statement closes the connection, the objects in the collection of objects are disconnected from the database.  EF cannot go back to the database and read each record AGAIN from the database while the loop is being executed.

    Understand what the ToList() is doing.

    https://www.dotnetperls.com/tolist

    Your way, if  query fetched 100,000 records being read,  that's 200,000 reads being done when it should have been only 100,000 reads being done, because of the way you have your code implemented that's doing a double read of the same data from the database.

    • Marked as answer by Sudip_inn Sunday, March 4, 2018 3:29 PM
    Thursday, March 1, 2018 8:34 PM