views:

94

answers:

0

I am studying Asp.NET MVC Storefront application and there is some help needed with LINQ. For example study this Partial Code. Here is GetCategories Function which retrieves All Categories and their corresponding Products. In a View I only needed Category Names but when called this Function and checking in SQL profiler it shows too many queries. According to me it is retrieving their products and other things too. But I only needed Category Names in a view. What is the best way to do it.

public IQueryable<Category> GetCategories() {

            var culturedNames = from ct in _db.CategoryCultureDetails
                               where ct.Culture.LanguageCode ==             
                               System.Globalization.CultureInfo.
                               CurrentUICulture.TwoLetterISOLanguageName
                               select new
                               {
                                   ct.CategoryName,
                                   ct.CategoryID
                               };

            return from categ in _db.Categories
                   join culturedName in culturedNames 
                   on categ.CategoryID equals culturedName.CategoryID 
                   into cultureJoined
                   from cultureFinal in cultureJoined.DefaultIfEmpty() 
                   let products=GetProducts(categ.CategoryID)
                   select new Category
                   {
                       ID = categ.CategoryID,
                       Name = cultureFinal.CategoryName ?? categ.CategoryName, 
                       ParentID = categ.ParentID ?? 0,
                       IsDefault=categ.IsDefault,
                       Image = new CategoryImage(categ.ThumbUrl, categ.FullImageUrl),
                       Products = new LazyList<Product>(products)
                   };

        }

        IQueryable<Product> GetProducts(int categoryID) {
            var products = from p in GetProducts()
                           join cp in _db.Categories_Products on p.ID equals cp.ProductID
                           where cp.CategoryID == categoryID
                           select p;
            return products;
        }


        public IQueryable<ProductImage> GetImages(int productID)
        {
            return from i in GetProductImages()
                   where i.ProductID == productID
                   select i;
        }

        public IQueryable<ProductDescriptor> GetDescriptors(int productID) {
            return from pd in GetDescriptors()
                   where pd.ProductID == productID
                   select pd;
        }


        /// <summary>
        /// Linq To Sql Implementation for Products
        /// </summary>
        /// <returns></returns>
        public IQueryable<Product> GetProducts() {

            var cultureDetail = from cd in _db.ProductCultureDetails
                                where cd.Culture.LanguageCode == 
                                System.Globalization.CultureInfo.
                                CurrentUICulture.TwoLetterISOLanguageName
                                select cd;

            var result = from p in _db.Products
                         join detail in cultureDetail on p.ProductID equals detail.ProductID
                         into productCulture
                         from cultureJoined in productCulture.DefaultIfEmpty() 
                         let images = GetImages(p.ProductID)
                         let crosses = GetCrossSells(p.ProductID)
                         let related = GetRelated(p.ProductID)
                         let reviews = GetReviews(p.ProductID)
                         let descriptors=GetDescriptors(p.ProductID)
                         select new Product
                                    {
                                        ID = p.ProductID,
                                        Name = p.ProductName,
                                        Description = cultureJoined.Description ?? p.Description,
                                        ShortDescription = cultureJoined.ShortDescription ?? p.ShortDescription ,
                                        Price = cultureJoined.UnitPrice ?? p.BaseUnitPrice,
                                        Manufacturer = p.Manufacturer,
                                        ProductCode = p.ProductCode,
                                        Images = new LazyList<ProductImage>(images),
                                        CrossSells = new LazyList<Product>(crosses),
                                        RelatedProducts = new LazyList<Product>(related),
                                        Reviews = new LazyList<ProductReview>(reviews),
                                        Descriptors = new LazyList<ProductDescriptor>(descriptors),
                                        Delivery = (DeliveryMethod)p.DeliveryMethodID,
                                        Inventory=(InventoryStatus) p.InventoryStatusID,
                                        EstimatedDelivery=p.EstimatedDelivery,
                                        AllowBackOrder=p.AllowBackOrder,
                                        WeightInPounds=p.WeightInPounds,
                                        IsTaxable=p.IsTaxable
                         };


            return result;


        }