优化 LINQ 查询所需的帮助

Question

我希望优化我的 LINQ 查询，因为虽然它工作正常，但它生成的 SQL 很复杂且效率低下...

基本上，我希望选择订购所需产品 (reqdProdId) 并注册的客户（作为 CustomerDisplay 对象）带有信用卡号（以行的形式存储在 RegisteredCustomer 表中，带有外键 CustId）

var q = from cust in db.Customers
        join regCust in db.RegisteredCustomers on cust.ID equals regCust.CustId
        where cust.CustomerProducts.Any(co => co.ProductID == reqdProdId)
        where regCust.CreditCardNumber != null && regCust.Authorized == true  
        select new  CustomerDisplay
            {
              Id = cust.Id,
              Name = cust.Person.DisplayName,
              RegNumber = cust.RegNumber
            };

总而言之，客户有一个相应的人员，该人员具有姓名； PersonID 是 Customer 表中的外键。 如果我查看生成的 SQL，我会看到从 Person 表中选择了所有列。 仅供参考，DisplayName 是一种使用 Customer.FirstName 和 LastName 的扩展方法。 有什么想法可以限制 Person 的列吗？

其次，我想摆脱 Any 子句（并使用子查询）来选择具有所需 ProductID 的所有其他 CustomerId，因为它（可以理解）生成 Exists 子句。 您可能知道，LINQ 在连接表方面存在一个已知问题，因此我不能只执行 cust.CustomerProducts.Products。 如何在联结表中选择具有所需 ProductID 的所有客户？

任何帮助/建议表示赞赏。

Answer 1

第一步是从 CustomerProducts 开始查询（正如 Alex 所说）：

IQueryable<CustomerDisplay> myCustDisplay =
    from custProd in db.CustomerProducts
    join regCust in db.RegisteredCustomers 
        on custProd.Customer.ID equals regCust.CustId
    where
        custProd.ProductID == reqProdId
        && regCust.CreditCardNumber != null
        && regCust.Authorized == true
    select new CustomerDisplay
    {
      Id = cust.Id,
      Name = cust.Person.Name,
      RegNumber = cust.RegNumber
    };

这将简化您的语法，并有望产生更好的执行计划。

接下来，您应该考虑在 Customers 和 RegisteredCustomers 之间创建外键关系。 这将产生如下所示的查询：

IQueryable<CustomerDisplay> myCustDisplay =
    from custProd in db.CustomerProducts
    where
        custProd.ProductID == reqProdId
        && custProd.Customer.RegisteredCustomer.CreditCardNumber != null
        && custProd.Customer.RegisteredCustomer.Authorized == true
    select new CustomerDisplay
    {
      Id = cust.Id,
      Name = cust.Person.Name,
      RegNumber = cust.RegNumber
    };

最后，为了获得最佳速度，让 LINQ 在编译时编译您的查询，而不是使用编译查询运行时：

Func<MyDataContext, SearchParameters, IQueryable<CustomerDisplay>> 
    GetCustWithProd =
    System.Data.Linq.CompiledQuery.Compile(
        (MyDataContext db, SearchParameters myParams) =>
        from custProd in db.CustomerProducts
        where
            custProd.ProductID == myParams.reqProdId
            && custProd.Customer.RegisteredCustomer.CreditCardNumber != null
            && custProd.Customer.RegisteredCustomer.Authorized == true
        select new CustomerDisplay
        {
          Id = cust.Id,
          Name = cust.Person.Name,
          RegNumber = cust.RegNumber
        };
    );

您可以像这样调用编译查询：

IQueryable<CustomerDisplay> myCustDisplay = GetCustWithProd(db, myParams);

Answer 2

我建议从有问题的产品开始查询，例如：

from cp in db.CustomerProducts
join .....
where cp.ProductID == reqdProdID

Answer 3

正如您所发现的，使用定义为扩展函数或分部类中的属性将要求首先对整个对象进行水合，然后在客户端完成选择投影，因为服务器不知道这些附加属性。 很高兴您的代码能够运行。 如果您要在查询中的其他位置（投影之外）使用非映射值，您可能会看到运行时异常。 如果您尝试在Where 子句中使用Customer.Person.DisplayName 属性，您会看到这一点。 正如您所发现的，修复方法是直接在投影子句中进行字符串连接。

Lame Duck，我认为您的代码中有一个错误，因为 select 子句中使用的 cust 变量没有在其他地方声明为源局部变量（在 from 子句中）。