Помощь, необходимая для оптимизации запроса LINQ

Я хочу оптимизировать свой запрос LINQ, потому что, хотя он работает правильно, генерируемый им SQL запутан и неэффективен ...

По сути, я хочу выбрать клиентов (как объекты CustomerDisplay), которые заказали требуемый продукт (reqdProdId) и зарегистрированы с помощью номера кредитной карты (хранящегося в виде строки в таблице 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 - это внешний ключ в таблице клиентов. Если я смотрю на сгенерированный SQL, я вижу, что все столбцы выбираются из таблицы Person. К сведению, DisplayName - это метод расширения, который использует Customer.FirstName и LastName. Любые идеи, как я могу ограничить столбцы от человека?

Во-вторых, я хочу избавиться от предложения Any (и использовать подзапрос) для выбора всех других идентификаторов клиентов, у которых есть требуемый ProductID, потому что он (по понятным причинам) генерирует предложение Exists. Как вы, возможно, знаете, у LINQ есть известная проблема с таблицами соединений, поэтому я не могу просто выполнить cust.CustomerProducts.Products. Как я могу выбрать всех клиентов в соединительной таблице с нужным ProductID?

Любая помощь / совет приветствуются.


linq subquery junction-table
person Ra.    schedule 16.07.2009    source источник

Ответы (3)


arrow_upward
1
arrow_downward

Первый шаг - запустить запрос из CustomerProducts (как сказал Алекс):

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

Это упростит ваш синтаксис и, надеюсь, приведет к лучшему плану выполнения.

Затем вам следует подумать о создании отношений внешнего ключа между клиентами и зарегистрированными клиентами. Это привело бы к запросу, который выглядел бы так:

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);
person Michael La Voie    schedule 16.07.2009
comment
thansk Lame Duck, никогда не думал о скомпилированном LINQ ... рассмотрит это. Это неплохая штука для функционального программирования. Кроме того, у меня есть внешний ключ между Customer и RegisteredCustomer, и я не уверен, что есть разница между custProd.Customer.RegisteredCustomer.CreditCardNumber! = Null (как вы предлагаете) и custProd.CreditCardNumber! = Null (как у меня ) LINQ определяет, что custProd - это строка в соединении, а сгенерированный SQL был INNER JOIN RegisteredCustomer как t2 на t1.Id = t2.CustomerID ... ... t2.CreditCardNumber IS NOT NULL - person Ra.; 16.07.2009
comment
Повторная компиляция запроса: запрос по-прежнему компилируется во время выполнения, а не во время компиляции. Это поможет только в том случае, если вы будете использовать один и тот же запрос много раз, поскольку вы можете кэшировать скомпилированный запрос и повторно использовать его. - person Lucas; 27.08.2009

arrow_upward
0
arrow_downward

Я предлагаю начать ваш запрос с рассматриваемого продукта, например что-то типа:

from cp in db.CustomerProducts
join .....
where cp.ProductID == reqdProdID
person Alex Black    schedule 16.07.2009
comment
большое спасибо Алекс. Я перевернул LINQ, и он позаботился о соединительном столе. У кого-нибудь есть понимание вопроса о методе расширения? - person Ra.; 16.07.2009
comment
Я бы предположил, что если бы ваш код обращался только к Person.FirstName и Person.LastName, то это все, что было бы выбрано. Общий план: пробовали ли вы без метода расширения? например Name = cust.Person.FirstName + + cust.Person.LastName - person Alex Black; 16.07.2009
comment
Я согласен, я протестировал его в LINQPad и обнаружил, что если вы напрямую укажете два поля ala DisplayName = cust.FirstName + + cust.LastName, генерируемый SQL будет иметь только эти два поля - person Michael La Voie; 16.07.2009

arrow_upward
0
arrow_downward

Как вы обнаружили, использование свойства, определенного как функция расширения или в частичном классе, потребует, чтобы сначала был гидратирован весь объект, а затем проекция выбора выполнялась на стороне клиента, поскольку сервер не знает об этих дополнительных свойствах. Радуйтесь, что ваш код вообще запустился. Если бы вы использовали неотображенное значение в другом месте вашего запроса (кроме проекции), вы, вероятно, увидели бы исключение во время выполнения. Вы можете увидеть это, если попытаетесь использовать свойство Customer.Person.DisplayName в предложении Where. Как вы обнаружили, исправление состоит в том, чтобы выполнить конкатенацию строк напрямую в предложении проекции.

Хромая утка, я думаю, что в вашем коде есть ошибка, поскольку переменная cust, используемая в вашем предложении select, не объявлена ​​где-либо еще как исходная локальная переменная (в предложениях from).

person Jim Wooley    schedule 30.07.2009