none
Does this code require database call for each record or list RRS feed

  • Question

  •      

    I'm fetching products from a database and preparing them as DTO's which will be returned to frontend:

    public async Task<IEnumerable<ProductDTO>> Get(Request request)
    {
    
    	IQueryable<Product> query = _context.Products;
    
    	var data = query.ToList();
    
    	var products = await query.ToListAsync();
    	// WARNING :) FOR EACH ROW NEW DB CALL?
    	return Map(products).Select(c =>
    	{
    		c.HasChildren = data.Any(cc => cc.ParentProductId == c.Id);
    		return c;
    	});
    }
    
    
    private IEnumerable<ProductDTO> Map(IEnumerable<Product> products)
    {
    	return products.Select(p => MapData(p)).ToList();
    }

    Now I'm suspicious with this code here where I'm checking if my product has some children products:

    return Map(products).Select(c =>
            {
            c.HasChildren = data.Any(cc => cc.ParentProductId == c.Id);
            return c;
     });
    

    My friend said that this code mean that for each item in my `products` list I will make database request to check if there are any items with this condition :

        cc => cc.ParentProductId == c.Id

    And I thought it's not true because I'm querying memory list??

    If I'm making new request for each item than this will be really bad for performance so how this might be solved?

    Thanks 

    CHEERS

    • Edited by Billy_1991 Tuesday, October 8, 2019 5:50 PM
    Tuesday, October 8, 2019 5:48 PM

All replies

  • How can a ParentProduct have children  Parentproducts?

    Also since you never closed the _context with EF and the database, the objects in 'Data' and 'query'  are  going back to the database on each iteration of objects in the collections.

    It look's like you're  hitting the database 4 times, becuase of usage of a single _context that is left open.


    Tuesday, October 8, 2019 11:34 PM
  • Hi Billy_1991,

    Thank you for posting here.

    As we all known, DTO is come from asp.net web API.

    Then, I want to know if your thread is related to asp.net.

    If so, I suggest that you could post it in the Asp.net forum. If not, could you provide a sample code with me so that we could reproduce your problem.

    Best Regards,

    Jack


    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.


    Wednesday, October 9, 2019 2:13 AM
    Moderator
  • You are hitting database twice with these lines where you convert queryable to list. You actually get products twise, but you could only use the single products list?

    var data = query.ToList();
    
    var products = await query.ToListAsync();

    The latter where you check ParentProductId I think will not hit database since data is already in list in memory and ParentProductId value is already retrieved.

    Wednesday, October 9, 2019 5:13 AM
  • How can a ParentProduct have children  Parentproducts?

    Also since you never closed the _context with EF and the database, the objects in 'Data' and 'query'  are  going back to the database on each iteration of objects in the collections.

    It look's like you're  hitting the database 4 times, becuase of usage of a single _context that is left open.


    So by saying this ' are  going back to the database on each iteration' this really mans that for each 

    c.HasChildren = data.Any(cc => cc.ParentProductId == c.Id);

    Database call will be triggered?

    If so, what's than solution to this?

    Wednesday, October 9, 2019 7:14 AM
  • You are hitting database twice with these lines where you convert queryable to list. You actually get products twise, but you could only use the single products list?

    var data = query.ToList();
    
    var products = await query.ToListAsync();

    The latter where you check ParentProductId I think will not hit database since data is already in list in memory and ParentProductId value is already retrieved.

         Thanks for your help, so how could we test this? To determine if it's hitting DB or not ?

    Wednesday, October 9, 2019 7:15 AM
  • Since code does not tell what _context is I assume it is Entity Framework context. ToList methods execute the query and get products to memory and you work with those using Enumerable.Any method in checking ParentProductId.

    Your code could be like this without getting products twice. The Any does not hit database again because Product instances are queried to memory when ToList is called and it does not get products twice.

    List<Product> products = _context.Products.ToList();
    
    var dtos = Map(products).Select(c =>
    {
        c.HasChildren = products.Any(cc => cc.ParentProductId == c.Id);
        return c;
    });

    Wednesday, October 9, 2019 9:45 AM
  • Database call will be triggered?

    Yes

    If so, what's than solution to this?

    You need to fashion the code to destroy _context, which disconnects the objects in the collections from the database leaving them in a disconnected state. When the connection is destroyed, the connection to the database is closed.

    You can use a using statment that destroys the connection when done.  How is _context being created that you are using?

    https://docs.microsoft.com/en-us/ef/ef6/fundamentals/connection-management

    var data = new List<Products>();
    
    using (var thecontext = new EFDBConnectionContext())
    {
          data = thecontext.Products.Tolist();
    }

    Your other code after the using statement.


    • Edited by DA924x Wednesday, October 9, 2019 10:55 AM
    Wednesday, October 9, 2019 10:53 AM
  • The context disposing is not required. The additional SQL is not required by Any method if results are already in context or persisted to list in memory like ToList method does.

    This can be verified at least using SQL Profiler if not other way.

    So for example the following code

    List<Product> products = _context.Product.ToList();
    
    var dtos = Map(products).Select(c =>
    {
        c.HasChildren = products.Any(cc => cc.ParentProductId == c.Id);
        return c;
    });

    Can be seen in profiler like this where only one select is needed to get products to in memory. The Any filtering does not required additional queries since we already have all Products in memory.

    SQL profiler image

    Of course there might be other problems with this like if there is thousands of products then we get them all to memory at once.


    Thursday, October 10, 2019 5:08 AM