Как заставить EF использовать объединения вместо разделения сложного запроса?

Question

У меня есть сложный IQueryable, который я бы хотел, чтобы EF заполнял один запрос к базе данных, чтобы я мог использовать его с отложенным выполнением. Пожалуйста, рассмотрите следующий пример.

Для этих моделей:

public enum AlphaState { Unknown = '\0', Good = 'G', Bad = 'B' }

[Table("MY_ALPHA")]
public class Alpha
{
    [Key]
    [Column("alpha_index")]
    public long Index { get; set; }

    [Column("alpha_id")] // user-editable field that users call ID
    public string AlphaId { get; set; }

    [Column("deleted")]
    public char? Deleted { get; set; }

    [Column("state")]
    public AlphaState State { get; set; }

    [InverseProperty("Alpha")]
    public ICollection<Bravo> Bravos { get; set; }
}

[Table("MY_BRAVO")]
public class Bravo
{
    [Key]
    [Column("bravo_index")]
    public long BravoIndex { get; set; }

    [ForeignKey("Alpha")]
    [Column("alpha_index")] // actually a 1:0..1 relationship
    public long? AlphaIndex { get; set; }
    public virtual Alpha Alpha { get; set; }

    [InverseProperty("Bravo")]
    public ICollection<Charlie> Charlies { get; set; }
}

[Table("MY_CHARLIE_VIEW")]
public class Charlie
{
    [Key]
    [Column("charlie_index")]
    public int CharlieIndex { get; set; }

    [Column("deleted")]
    public char? Deleted { get; set; }

    [Column("created_at")]
    public DateTime CreatedAt { get; set; }

    [ForeignKey("Bravo")]
    [Column("bravo_index")]
    public long BravoIndex { get; set; }
    public virtual Bravo Bravo { get; set; }

    [ForeignKey("Delta")]
    [Column("delta_index")]
    public long DeltaIndex { get; set; }
    public virtual Delta Delta { get; set; }

    [InverseProperty("Charlie")]
    public virtual ICollection<Delta> AllDeltas { get; set; }
}

[Table("MY_DELTA")]
public class Delta
{
    [Key]
    [Column("delta_index")]
    public long DeltaIndex { get; set; }

    [ForeignKey("Charlie")]
    [Column("charlie_index")]
    public long CharlieIndex { get; set; }
    public virtual Charlie Charlie { get; set; }

    [InverseProperty("Delta")] // actually a 1:0..1 relationship
    public ICollection<Echo> Echoes { get; set; }
}

public enum EchoType { Unknown = 0, One = 1, Two = 2, Three = 3 }

[Table("MY_ECHOES")]
public class Echo
{
    [Key]
    [Column("echo_index")]
    public int EchoIndex { get; set; }

    [Column("echo_type")]
    public EchoType Type { get; set; }

    [ForeignKey("Delta")]
    [Column("delta_index")]
    public long DeltaIndex { get; set; }
    public virtual Delta Delta { get; set; }
}

... рассмотрим этот запрос:

IQueryable<Alpha> result = context.Alphas.Where(a => a.State == AlphaState.Good)
                                         .Where(a => !a.Deleted.HasValue)
                                         .Where(a => a.Bravos.SelectMany(b => b.Charlies)
                                                             .Where(c => !c.Deleted.HasValue)
                                                             .Where(c => c.Delta.Echoes.Any())
                                                             .OrderByDescending(c => c.CreatedAt).Take(1)
                                                             .Any(c => c.Delta.Echoes.Any(e => e.Type == EchoType.Two)))
var query = result as System.Data.Objects.ObjectQuery;
string queryString = query.ToTraceString();

ПРИМЕЧАНИЕ: Чарли на самом деле является видом на стол; У Delta есть FK для таблицы Чарли, но представление дает поддельный FK для самой последней Delta, связанной с этим Charlie, поэтому модель использует это, потому что планируется использовать EF только для запросов, а не для обновления.

Мне бы хотелось, чтобы этот запрос заполнялся одним запросом к базе данных, но как написано, это не то, что происходит. Как я могу изменить этот запрос, чтобы получить те же результаты, но EF просто встроить условие в results IQueryable вместо предварительной выборки данных для него?

Как я знаю, что он использует два запроса

Я точно знаю, что он разбивается на несколько запросов, потому что по причинам, выходящим за рамки этого вопроса, я намеренно дал контексту неверную строку соединения. result является IQueryable, поэтому он должен использовать отложенное выполнение и на самом деле не пытаться получить какие-либо данные до тех пор, пока они не будут использованы, но я получаю исключение из-за сбоя соединения, как только я его объявляю.

Фон

У нас есть существующая структура базы данных, уровень доступа к базе данных и несколько сотен тысяч строк кода с использованием указанной структуры и DAL. Мы хотели бы добавить пользовательский интерфейс, позволяющий пользователям создавать собственные сложные запросы, и EF выглядел как хороший способ построить базовую модель для этого. Тем не менее, мы никогда не использовали EF раньше, поэтому Powers That Be заявили, что он никогда не сможет подключиться к базе данных; мы должны использовать EF для генерации IQueryable, извлечь из него строку запроса и использовать существующий DAL для выполнения запроса.

Answer 1

Как я знаю, что он использует два запроса

То, что вы наблюдаете, не EF начинает выполнять ваш запрос. После того, как вы назначите запрос result переменная, у вас все еще есть определение запроса, а не набор результатов. Если вы присоедините профилировщик к вашей базе данных, вы увидите, что нет SELECT Заявление выполнено по вашему запросу.

Так почему же происходит соединение с базой данных? Причина в том, что при первом создании запроса для заданного производного DbContext типа, EF создает и кэширует свою модель в памяти для этого типа. Это достигается путем применения различных соглашений к типам, свойствам и атрибутам, которые вы определили. Теоретически, этот процесс не должен соединяться с базой данных, но провайдер для SQL Server делает это в любом случае. Это делается для определения используемой версии SQL Server, чтобы можно было определить, могут ли он использовать более свежие функции SQL Server в модели, которую он создает.

Интересно, что эта модель кэшируется для типа, а не для экземпляра контекста. Вы можете увидеть это, избавившись от своих context затем создайте новый и повторите строки кода, которые строят запрос. Во втором случае вы вообще не увидите соединения с базой данных, поскольку EF будет использовать свою кэшированную модель для вашего типа контекста.

"Силы, которые будут" заявили, что никогда не смогут подключиться к базе данных; мы должны использовать EF для генерации IQueryable, извлечь из него строку запроса и использовать существующий DAL для выполнения запроса.

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

Также имейте в виду, что есть еще одна причина, по которой EF может подключиться к вашему серверу при первом обращении к вашему серверу. DbContext Тип: инициализация. Если вы не отключили инициализацию (с чем-то вроде Database.SetInitializer<MyContext>(null)) он проверит, что база данных существует, и попытается создать ее, если нет.

Обратите внимание, что вы можете позвонить ToString() непосредственно на первый запрос кода EF, чтобы получить запрос T-SQL. Вам не нужно проходить через промежуточный ObjectQuery.ToTraceString() метод, который на самом деле является частью устаревшего EF API. Однако оба эти метода предназначены для отладки и ведения журнала. Довольно необычно использовать EF для построения запросов, но не для их выполнения. С этим подходом вы, вероятно, столкнетесь с проблемами - наиболее очевидно, когда EF определит, что он должен генерировать параметризованный запрос. Кроме того, нет гарантии, что разные версии EF будут генерировать одинаковый T-SQL для одного и того же ввода, поэтому ваш код может оказаться довольно хрупким при обновлении до новых версий EF. Убедитесь, что у вас есть много тестов!

Если вас беспокоит то, что пользователи будут подключаться к базе данных напрямую - и это абсолютно законная проблема безопасности - вы можете рассмотреть альтернативный подход. У меня нет большого опыта с этим, но кажется, что OData могла бы быть хорошей подгонкой. Это позволяет создавать запросы на клиенте, сериализовать их через удаленное соединение, а затем заново создавать их на вашем сервере. Затем на сервере вы можете выполнить их для своей базы данных. Вашему клиенту вообще ничего не нужно знать о базе данных.

Если вы решили (или получили указание) продолжать подход, который вы подробно изложили в своем вопросе, потратьте время на изучение того, как профилировать соединение с SQL Server. Это будет абсолютно необходимым инструментом для понимания того, как EF переводит ваши запросы.

Answer 2

context.Foos.Where(f => f.Bars.Any(b => b.SomeOtherData == "baz"));

Я попытался выполнить запрос, аналогичный вашему, в имеющейся у меня базе данных (используя LINQPad), и в результате я получил

SELECT 
  [Extent1].[Property1] AS [Property1]
  -- other properties of Foo
  FROM [dbo].[Foos] AS [Extent1]
  WHERE EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[Bars] AS [Extent2]
    WHERE ([Extent1].[FooId] = [Extent2].[FooId]) AND (N'baz' = [Extent2].[SomeOtherData])
  )

... что определенно выглядит как один запрос для меня.

Выражения в функциях IQueryable не выполняются напрямую - они используются для генерации SQL, который затем используется для выполнения запроса, когда необходимо материализовать результаты.

Answer 3

Попробуйте использовать синтаксис запроса LINQ.

var result = (
    from f in foo
    join b in bar
        on f.fooid equals b.fooid
    where b.someotherdata = "baz"
    select new { f.fooid, f.somedata }
).Distinct().ToEnumerable();

Это будет отложено до перечисления.