LINQ select data from many table

Question

I have a complicated query that select from many entity from database. but i'm facing a performance issue. the data takes a long time until it retrieved unlike the sql it takes only seconds.

Tables:

Product
productPrice (it contains more than 20k rows)
Category
Store

the query:

        var grid = from p in db.Products.ToList()
                   from productPrice in db.ProductPrices.ToList()
                   where p.ProductID == productPrice.ProductID
                   from c in db.Categories.ToList()
                   where c.CategoryID == p.ProductCategoryID
                   where c.CategoryID == subCategoryID
                   from u in db.Users.ToList()
                   where u.UserID == productPrice.UserID
                   where u.UserID == storeID
                   select new
                   {
                       ProductID = p.ProductID,
                       StoreName = u.Name,
                       ProductCateogry = c.CategoryName,
                       CategoryHsCode = c.CategoryHsCode,
                       ProductName = p.ProductName,
                       ProductBarcode = p.ProductBarcode,
                       Price = productPrice.Price,
                       PriceUnit = productPrice.Unit,
                       LoggedDate = productPrice.LoggedDate
                   };

is there any problem with the query or entity frame work select all data then it filters it?

Remove all the `ToList` which basically loads your database completely into memory _before_ the `Where` filters the records. — Tim Schmelter, Sep 09 '14 at 07:35
`ToList` should only be used at the end of the query if you want to retrieve a `List`. — Patrice Gahide, Sep 09 '14 at 07:37

score 2 · Accepted Answer · edited May 23 '17 at 12:21

Remove all the ToList which basically loads your database completely into memory before the Where filters the records. ToList creates a list from the table.

var grid = from p in db.Products 
           from productPrice in db.ProductPrices 
           where p.ProductID == productPrice.ProductID
           from c in db.Categories 
           where c.CategoryID == p.ProductCategoryID
           where c.CategoryID == subCategoryID
           from u in db.Users 
           where u.UserID == productPrice.UserID
           where u.UserID == storeID
           select new
           {
               ProductID = p.ProductID,
               StoreName = u.Name,
               ProductCateogry = c.CategoryName,
               CategoryHsCode = c.CategoryHsCode,
               ProductName = p.ProductName,
               ProductBarcode = p.ProductBarcode,
               Price = productPrice.Price,
               PriceUnit = productPrice.Unit,
               LoggedDate = productPrice.LoggedDate
           };

If you want a list as final result (although i would initialize a custom type instead of an anonymous type):

var gridList = grid.ToList();   // loads only the filtered records into memory

By using ToList in the query you're also using Linq-To-Objects which cannot benefit from database indexes or optimizations. So the tables won't be linked with an efficient JOIN anymore but with an inefficient (in-memory) Where using a Cartesian product.

LINQ select data from many table

1 Answers1