locked
Query the model gets all the database rows RRS feed

  • Question

  • Hi Everyone

    I am still learning entity framework and have a question about the number of rows in the memory.

    When i query an entity and put in a where clause, entity framework still gets all the rows from the database, and then applies the where statement.

    In case a table has hunderds of thousands of rows this will not perform and making the application slow. Am i missing something or can i change my data acces layer queries?

    I greatly appreciate any help, thanks.

    Here's an example of my data acces code

         Dim list As List(Of T)
    
                'Use context of this class
                Using context = New XEntities()
                    Dim dbQuery As IQueryable(Of T) = context.[Set](Of T)()
    
                    'Apply eager loading
                    For Each navigationProperty As Expression(Of Func(Of T, Object)) In navigationProperties
                        dbQuery = dbQuery.Include(navigationProperty)
                    Next
    
                    list = dbQuery.AsNoTracking().Where(where).ToList()
                    
    
    
                End Using

    It gets called by the business logic layer, for example get some application specific texts from the database

      Return _AppTextRepository.GetList(Function(m) m.AppTextTypeId.Equals(AppTextTypeId))


    • Edited by Hans1983 Wednesday, February 28, 2018 10:37 AM
    Wednesday, February 28, 2018 10:36 AM

All replies

  • Where did you get the code from?

    Wednesday, February 28, 2018 4:04 PM
  • Hi Hans1983,

    >>In case a table has hunderds of thousands of rows this will not perform and making the application slow. Am i missing something or can i change my data acces layer queries?

    If you want to query special rows, please refer to the following code, which only return the rows named ProductId, ProductName.

    Using context As New AdventureWorksEntities
        Dim products As ObjectSet(Of Product) = context.Products
    
        Dim query = _
            From product In products _
            Select New With _
            { _
                .ProductId = product.ProductID, _
                .ProductName = product.Name _
            }
    
        Console.WriteLine("Product Info:")
        For Each productInfo In query
            Console.WriteLine("Product Id: {0} Product name: {1} ", _
                    productInfo.ProductId, productInfo.ProductName)
        Next
    End Using

    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.

    Thursday, March 1, 2018 1:26 AM