Преобразование инструкции sql, содержащей cte 'with', в linq

#linq #common-table-expression

#linq #общее табличное выражение

Вопрос:

У меня есть этот фрагмент кода здесь, я сражался с ним в течение нескольких часов. по сути, что делает эта инструкция sql, так это возвращает ВСЕ вложенные папки указанной папки (@compositeId).

 WITH auto_table (id, Name, ParentID) AS
(
SELECT
    C.ID, C.Name, C.ParentID
FROM Composite_Table AS C
    WHERE C.ID = @compositeId

UNION ALL

SELECT
    C.ID, C.Name, C.ParentID
FROM Composite_Table AS C
    INNER JOIN auto_table AS a_t ON C.ParentID = a_t.ID
)

SELECT * FROM auto_table

Этот запрос вернул бы что-то вроде этого:

 Id   |    Name    | ParentId
1    | StartFolder| NULL
2    | Folder2    | 1
4    | Folder3    | 1
5    | Folder4    | 4

Теперь я хочу преобразовать код в linq. Я знаю, что это включает в себя некоторую форму рекурсии, но все еще застряло благодаря инструкции with.

Ответ №1:

Не существует эквивалента Linq для SQL, который мог бы это сделать (эффективным способом). Лучшим решением было бы вызвать SP / View / UDF из Linq, содержащего эту инструкцию.

1. Да, но я должен бойкотировать все SPS и фактический SQL-код. В этом случае выбора нет

2. Почему?! Зачем писать плохой код, когда у вас уже есть хорошее решение. Вы знаете, что Linq в конечном итоге все равно генерирует SQL? Обычно я использую Linq для большинства своих запросов доступа к данным, но когда требуется высокая производительность, я всегда пишу SQL напрямую. Sql-запросы в коде в контексте вашей базы данных могут нормально отображаться на объекты Linq.

3. Ах, теперь я определенно понял вашу точку зрения, большое спасибо за совет!

Ответ №2:

Вы могли бы написать код (рекурсивный или нет), который повторно запрашивает базу данных, пока не получит все результаты.

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

Ответ №3:

Существует известный плагин ‘LinqToDb‘, который предоставляет методы для получения эквивалента CTE в Linq

Ответ №4:

 public static List<Composite> GetSubCascading(int compositeId)
{
    List<Composite> compositeList = new List<Composite>();

    List<Composite> matches = (from uf in ctx.Composite_Table
    where uf.Id == compositeId
    select new Composite(uf.Id, uf.Name, uf.ParentID)).ToList();

    if (matches.Any())
    {
        compositeList.AddRange(TraverseSubs(matches));
    }

    return compositeList;
}

private static List<Composite> TraverseSubs(List<Composite> resultSet)
{
    List<Composite> compList = new List<Composite>();

    compList.AddRange(resultSet);

    for (int i = 0; i < resultSet.Count; i  )
    {
        //Get all subcompList of each folder
        List<Composite> children = (from uf in ctx.Composite_Table
        where uf.ParentID == resultSet[i].Id
        select new Composite(uf.Id, uf.Name, uf.ParentID)).ToList();

        if (children.Any())
        {
            compList.AddRange(TraverseSubs(children));
        }
    }

    return compList;
}

//All where ctx is your DataContext