Help required to optimize LINQ query

Question

I am looking to optimize my LINQ query because although it works right, the SQL it generates is convoluted and inefficient...

Basically, I am looking to select customers (as CustomerDisplay objects) who ordered the required product (reqdProdId), and are registered with a credit card number (stored as a row in RegisteredCustomer table with a foreign key 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
            };

As an overview, a Customer has a corresponding Person which has the Name; PersonID is a foreign key in Customer table. If I look at the SQL generated, I see all columns being selected from the Person table. Fyi, DisplayName is an extension method which uses Customer.FirstName and LastName. Any ideas how I can limit the columns from Person?

Secondly, I want to get rid of the Any clause (and use a sub-query) to select all other CustomerIds who have the required ProductID, because it (understandably) generates an Exists clause. As you may know, LINQ has a known issue with junction tables, so I cannot just do a cust.CustomerProducts.Products. How can I select all Customers in the junction table with the required ProductID?

Any help/advice is appreciated.

Answer 1

I'd suggest starting your query from the product in question, e.g. something like:

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

Answer 2

The first step is to start your query from CustomerProducts (as Alex Said):

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

This will simplify your syntax and hopefully result in a better execution plan.

Next, you should consider creating a foreign key relationship between Customers and RegisteredCustomers. This would result in a query that looked like this:

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

Finally, for optimum speed, have LINQ compile your query at compile time, rather than run time by using a compiled query:

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

You can call the compiled query like this:

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

Answer 3

As you have found, using a property defined as an extension function or in a partial class will require that the entire object is hydrated first and then the select projection is done on the client side because the server has no knowledge of these additional properties. Be glad that your code ran at all. If you were to use the non-mapped value elsewhere in your query (other than in the projection), you would likely see a run-time exception. You can see this if you try to use the Customer.Person.DisplayName property in a Where clause. As you have found, the fix is to do the string concatenation in the projection clause directly.

Lame Duck, I think there is a bug in your code as the cust variable used in your select clause isn't declared elsewhere as a source local variable (in the from clauses).