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;
}