LINQ to SQL 查询优化

Question

主要从事java工作，对LINQ很陌生，所以我需要一些关于此查询性能的建议。我有3个表，

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

我想从产品表中选择所有产品及其任何给定子类别的供应商。以下查询应该正常工作吗？或者需要什么优化？

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
             where sub.CategoryId == 19 && CustomFunction(p.ID) > 100 
             select new { p, p.Vendor, trend = CustomFunction(p.ID) })
             .Skip((pageNum - 1) * pageSize)
             .Take(pageSize);

我将创建一个与查询结果匹配的自定义类。

1. 在这种情况下，创建视图/存储过程是更好的方案吗？
2. 可以命名 CustomFunction(p.ID)>100 以便函数不会被调用两次吗？它是数据库中的自定义函数。
3. 对于分页，Skip 和 take 表现良好吗？

Answer 1

您的方向是正确的，但 LINQ to SQL 无法理解 CustomFunction()，因此您需要先使用 AsEnumerable() 切换到 LINQ to Objects可以调用它。您还可以使用 let 捕获 CustomFunction() 的结果一次，以便在查询中的其他地方使用：

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

更新： 要回答列出的问题：

1. 这是相对简单的 SQL，因此 L2SQL 生成的内容应该没问题。您可以使用 L2SQL 日志记录或 SQL Profiler 来确认 SQL 是否足够好。
2. 请参阅上面的 let。
3. IQueryable 上的 Skip() 和 Take() （如上面的 listFromSql）将转换为适当的 SQL，限制通过线路发送的结果集。 IEnumerable<> 上的 Skip() 和 Take() 只是枚举序列以获取请求的结果，但对完整结果进行操作从 SQL 返回的集合。

Answer 2

由于 CustomFunction 是数据库中的 ScalarFunction，因此 LINQ to SQL 应该能够有效地评估它。您可能希望使用 LET 提取一次值，但请检查生成的 SQL 和查询执行计划，看看它是否提供了任何改进，或者 SQL Server 是否在内部自动进行了适当的优化。

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

如果元素之间存在关联，您可能需要使用它们而不是联接。它不会（太多）改变生成的查询，但可能更易于维护，因为连接是由模型中建立的关联抽象出来的。

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