LINQ to SQL Query Optimization

Have been working mainly on java and new to LINQ so I need some suggestion regarding performance of this query .I have 3 tables

1. Products  -->                   ProductID | Name | Price | VendorID
2. Category  -->                   CategoryID | Label
3. SubCategory-->                  SubCategoryID | Label | ParentID
4. Product_SubCategory_Mapping-->  ProductID | SubCategoryID 
5. Vendor     -->                  VendorID | VendorName

I want to select all products from product table along with its Vendors for any given subcategory .Should following query work fine ? Or it needs any optimization ?

var list =  (from p in Products 
             join cat in Product_SubCategory_Mapping 
             on p.UserId equals cat.UserId 
             join sub in SubCate开发者_运维技巧gories 
             on cat.SubCategoryId equals sub.SubCategoryId
             where sub.CategoryId == 19 && CustomFunction(p.ID) > 100 
             select new { p, p.Vendor, trend = CustomFunction(p.ID) })
             .Skip((pageNum - 1) * pageSize)
             .Take(pageSize);

I will be creating a custom class that will match result of query .

1. Is creating a view/Stored procedure a better scenario in case like this ?
2. Can CustomFunction(p.ID)>100 be named so function is not called twice ? Its a custom function in Database.
3. For paging will Skip and take perform well ?

You're on the right track, but LINQ to SQL won't understand CustomFunction() so you'll need to switch to LINQ to Objects with AsEnumerable() before you can call it. You can also use let to capture the result of CustomFunction() once for use elsewhere in the query:

var listFromSql = from p in Products
                  join cat in Product_SubCategory_Mapping
                    on p.UserId equals cat.UserId
                  join sub in SubCategories
                    on cat.SubCategoryId equals sub.SubCategoryId
                  where sub.CategoryId == 19
                  select p;

var list = from p in listFromSql.AsEnumerable()
           let trend = CustomFunction(p.ID)
           where trend > 100
           select new { p, p.Vendor, trend };

Update: To answer your listed questions:

1. This is relatively straightforward SQL, so what L2SQL generates should be fine. You can use L2SQL logging or SQL Profiler to confirm that the SQL is good enough.
2. See let above.
3. Skip() and Take() on an IQueryable<> (like listFromSql above) will translate into the appropriate SQL, limiting the result set sent across the wire. Skip() and Take() on an IEnumerable<> just enumerate the sequence to get the requested results, but operate on the full result set returned from SQL.

Since CustomFunction is a ScalarFunction in the database, LINQ to SQL should be able to evaulate it effectively. You may want to use LET to pull the value once, but check the generated SQL and the Query Execution Plan to see if it offers any improvement or if SQL Server automatically makes the appropriate optimizations internally.

var list =  (from p in Products  
             join cat in Product_SubCategory_Mapping  
             on p.UserId equals cat.UserId  
             join sub in SubCategories  
             on cat.SubCategoryId equals sub.SubCategoryId
             let trend = CustomFunction(p.ID) 
             where sub.CategoryId == 19 && trend > 100  
             select new { p, p.Vendor, trend }) 
             .Skip((pageNum - 1) * pageSize) 
             .Take(pageSize); 

If there are associations between your elements, you may want to use them rather than joins. It doesn't change the generated query (much), but may be a bit more maintainable since the joins are abstracted out by the associations established in the model.

var list = (from p in Products
           from cat in p.Product_SubCategory_Mappings
           let trend = CustomFunction(p.ID)
           where cat.SubCategory.CategoryId == 19 && trend > 100
           select new { Product = p, p.Vendor, trend})
           .Skip(pageNum - 1) * pageSize)
           .Take(pageSize);