Zapytania SQL

Program Entity Framework Core umożliwia tworzenie listy rozwijanej zapytań SQL podczas pracy z relacyjną bazą danych. Zapytania SQL są przydatne, jeśli nie można wyrazić zapytania przy użyciu LINQ lub jeśli zapytanie LINQ powoduje, że program EF generuje nieefektywny kod SQL. Zapytania SQL mogą zwracać zwykłe typy jednostek lub typy jednostek bez klucza , które są częścią modelu.

Porada

Przykład z tego artykułu można zobaczyć w witrynie GitHub.

Podstawowe zapytania SQL

Możesz użyć FromSql polecenia , aby rozpocząć zapytanie LINQ na podstawie zapytania SQL:

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.Blogs")
    .ToList();

Uwaga

FromSql wprowadzono w programie EF Core 7.0. W przypadku korzystania ze starszych wersji użyj FromSqlInterpolated zamiast tego.

Zapytania SQL mogą służyć do wykonywania procedury składowanej, która zwraca dane jednostki:

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

Uwaga

FromSql można używać tylko bezpośrednio w obiekcie DbSet. Nie można go skomponować za pomocą dowolnego zapytania LINQ.

Przekazywanie parametrów

Ostrzeżenie

Zwróć szczególną uwagę na parametryzację podczas korzystania z zapytań SQL

W przypadku wprowadzenia dowolnych wartości dostarczonych przez użytkownika do zapytania SQL należy zadbać o uniknięcie ataków polegających na wstrzyknięciu kodu SQL. Wstrzyknięcie kodu SQL występuje, gdy program integruje wartość ciągu dostarczonego przez użytkownika z zapytaniem SQL, a wartość podana przez użytkownika jest spreparowana w celu zakończenia ciągu i wykonania innej złośliwej operacji SQL. Aby dowiedzieć się więcej o wstrzyknięciu kodu SQL, zobacz tę stronę.

Metody FromSql i FromSqlInterpolated są bezpieczne przed wstrzyknięciem kodu SQL i zawsze integrują dane parametrów jako oddzielny parametr SQL. FromSqlRaw Jednak metoda może być podatna na ataki iniekcyjne SQL, jeśli jest niewłaściwie używana. Aby uzyskać więcej informacji, zobacz poniżej.

Poniższy przykład przekazuje pojedynczy parametr do procedury składowanej, dołączając symbol zastępczy parametru w ciągu zapytania SQL i podając dodatkowy argument:

var user = "johndoe";

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToList();

Chociaż ta składnia może wyglądać jak zwykła interpolacja ciągów języka C#, podana wartość jest opakowana w element DbParameter i wygenerowana nazwa parametru wstawiona, gdzie {0} określono symbol zastępczy. > Dzięki temu usługa Jest FromSql bezpieczna od ataków polegających na wstrzyknięciu kodu SQL i wysyła wartość wydajnie i poprawnie do bazy danych.

Podczas wykonywania procedur składowanych przydatne może być użycie nazwanych parametrów w ciągu zapytania SQL, zwłaszcza gdy procedura składowana ma opcjonalne parametry:

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser={user}")
    .ToList();

Jeśli potrzebujesz większej kontroli nad wysyłanym parametrem bazy danych, możesz również utworzyć DbParameter obiekt i podać go jako wartość parametru. Dzięki temu można ustawić dokładny typ bazy danych parametru lub aspekty, takie jak jego rozmiar, precyzja lub długość:

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToList();

Uwaga

Przekazane parametry muszą być dokładnie zgodne z definicją procedury składowanej. Zwróć szczególną uwagę na kolejność parametrów, dbając o to, aby nie przegapić lub zagubić żadnego z nich - lub rozważyć użycie notacji nazwanych parametrów. Upewnij się również, że typy parametrów odpowiadają, a ich aspekty (rozmiar, precyzja, skala) są ustawione zgodnie z potrzebami.

Dynamiczny język SQL i parametry

FromSql i jego parametryzacja powinna być używana wszędzie tam, gdzie to możliwe. Istnieją jednak pewne scenariusze, w których język SQL musi być dynamicznie rozdzielony, a nie można używać parametrów bazy danych. Załóżmy na przykład, że zmienna języka C# przechowuje nazwę właściwości do filtrowania. Może to być kuszące, aby użyć zapytania SQL, takiego jak następujące:

var propertyName = "User";
var propertyValue = "johndoe";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM [Blogs] WHERE {propertyName} = {propertyValue}")
    .ToList();

Ten kod nie działa, ponieważ bazy danych nie zezwalają na parametryzowanie nazw kolumn (ani żadnej innej części schematu).

Najpierw ważne jest, aby wziąć pod uwagę implikacje dynamicznego konstruowania zapytania — za pośrednictwem języka SQL lub w inny sposób. Zaakceptowanie nazwy kolumny od użytkownika może pozwolić im wybrać kolumnę, która nie jest indeksowana, co sprawia, że zapytanie działa bardzo wolno i przeciąża bazę danych; lub może zezwolić im na wybranie kolumny zawierającej dane, których nie chcesz uwidocznić. Z wyjątkiem naprawdę dynamicznych scenariuszy, zwykle lepiej jest mieć dwa zapytania dotyczące dwóch nazw kolumn, a nie przy użyciu parametryzacji, aby zwinąć je do pojedynczego zapytania.

Jeśli zdecydujesz, że chcesz dynamicznie skonstruować bazę danych SQL, musisz użyć FromSqlRawmetody , która umożliwia interpolowanie danych zmiennych bezpośrednio do ciągu SQL, zamiast używać parametru bazy danych:

var columnName = "Url";
var columnValue = new SqlParameter("columnValue", "http://SomeURL");

var blogs = context.Blogs
    .FromSqlRaw($"SELECT * FROM [Blogs] WHERE {columnName} = @columnValue", columnValue)
    .ToList();

W powyższym kodzie nazwa kolumny jest wstawiana bezpośrednio do języka SQL przy użyciu interpolacji ciągów języka C#. Twoim obowiązkiem jest upewnienie się, że ta wartość ciągu jest bezpieczna, czyszcząc ją, jeśli pochodzi z niebezpiecznego źródła; Oznacza to wykrywanie znaków specjalnych, takich jak średniki, komentarze i inne konstrukcje SQL, i ucieczka ich prawidłowo lub odrzucanie takich danych wejściowych.

Z drugiej strony wartość kolumny jest wysyłana za pośrednictwem DbParameterelementu i dlatego jest bezpieczna w obliczu wstrzyknięcia kodu SQL.

Ostrzeżenie

Podczas korzystania z metody FromSqlRawzawsze upewnij się, że wartości pochodzą z bezpiecznego źródła lub są prawidłowo oczyszczone. Ataki polegających na wstrzyknięciu kodu SQL mogą mieć katastrofalne konsekwencje dla aplikacji.

Komponowanie za pomocą LINQ

Możesz utworzyć na początku zapytania SQL przy użyciu operatorów LINQ; Program EF Core będzie traktować język SQL jako podquery i komponować go w bazie danych. W poniższym przykładzie użyto zapytania SQL wybranego z funkcji Table-Valued (TVF). Następnie komponuje się na nim przy użyciu LINQ do filtrowania i sortowania.

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Where(b => b.Rating > 3)
    .OrderByDescending(b => b.Rating)
    .ToList();

Powyższe zapytanie generuje następujący kod SQL:

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url]
FROM (
    SELECT * FROM dbo.SearchBlogs(@p0)
) AS [b]
WHERE [b].[Rating] > 3
ORDER BY [b].[Rating] DESC

Operator Include może służyć do ładowania powiązanych danych, podobnie jak w przypadku dowolnego innego zapytania LINQ:

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToList();

Komponowanie za pomocą LINQ wymaga, aby zapytanie SQL było komposowalne, ponieważ platforma EF Core traktuje podany język SQL jako podquery. Zapytania SQL, które można przechować, zazwyczaj zaczynają się od słowa kluczowego SELECT i nie mogą zawierać funkcji SQL, które nie są prawidłowe w podquerii, na przykład:

  • Średnik końcowy
  • W SQL Server końcowa wskazówka na poziomie zapytania (na przykład OPTION (HASH JOIN))
  • W SQL Server klauzulaORDER BY, która nie jest używana z klauzulą OFFSET 0 OR TOP 100 PERCENT w klauzuli SELECT

SQL Server nie zezwala na komponowanie wywołań procedury składowanej, więc każda próba zastosowania dodatkowych operatorów zapytań do takiego wywołania spowoduje nieprawidłowy kod SQL. Użyj AsEnumerable polecenia lub AsAsyncEnumerable bezpośrednio po FromSql lub FromSqlRaw , aby upewnić się, że program EF Core nie próbuje utworzyć procedury składowanej.

Śledzenie zmian

Zapytania korzystające FromSql z reguł śledzenia zmian lub FromSqlRaw zgodne z tymi samymi regułami śledzenia zmian co inne zapytanie LINQ w programie EF Core. Jeśli na przykład typy jednostek projektów zapytań, wyniki są domyślnie śledzone.

W poniższym przykładzie użyto zapytania SQL wybranego z funkcji Table-Valued (TVF), a następnie wyłącza śledzenie zmian za pomocą wywołania metody AsNoTracking:

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .AsNoTracking()
    .ToList();

Wykonywanie zapytań względem typów skalarnych (innych niż jednostka)

Uwaga

Ta funkcja została wprowadzona w programie EF Core 7.0.

Chociaż FromSql jest to przydatne w przypadku wykonywania zapytań dotyczących jednostek zdefiniowanych w modelu, usługa SqlQuery umożliwia łatwe wykonywanie zapytań dotyczących typów skalarnych, innych niż jednostki za pośrednictwem języka SQL bez konieczności tworzenia listy rozwijanej do interfejsów API dostępu do danych niższego poziomu. Na przykład następujące zapytanie pobiera wszystkie identyfikatory z Blogs tabeli:

var ids = context.Database
    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
    .ToList();

Operatory LINQ można również tworzyć za pośrednictwem zapytania SQL. Jednak ponieważ usługa SQL staje się podquerią, do której należy odwołać się kolumna danych wyjściowych przez program SQL EF, musisz nazwać kolumnę Valuewyjściową . Na przykład następujące zapytanie zwraca identyfikatory, które są powyżej średniej identyfikatora:

var overAverageIds = context.Database
    .SqlQuery<int>($"SELECT [BlogId] AS [Value] FROM [Blogs]")
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToList();

FromSql może być używany z dowolnym typem skalarny obsługiwanym przez dostawcę bazy danych. Jeśli chcesz użyć typu, który nie jest obsługiwany przez dostawcę bazy danych, możesz użyć konfiguracji przed konwencją , aby zdefiniować konwersję wartości.

Funkcja SqlQueryRaw umożliwia dynamiczną konstrukcję zapytań SQL, podobnie jak FromSqlRaw w przypadku typów jednostek.

Wykonywanie niesłaniających zapytań SQL

W niektórych scenariuszach może być konieczne wykonanie programu SQL, który nie zwraca żadnych danych, zazwyczaj do modyfikowania danych w bazie danych lub wywoływania procedury składowanej, która nie zwraca żadnych zestawów wyników. Można to zrobić za pomocą polecenia ExecuteSql:

using (var context = new BloggingContext())
{
    var rowsModified = context.Database.ExecuteSql($"UPDATE [Blogs] SET [Url] = NULL");
}

Spowoduje to wykonanie podanego kodu SQL i zwrócenie liczby zmodyfikowanych wierszy. Funkcja ExecuteSql chroni przed wstrzyknięciem kodu SQL przy użyciu bezpiecznej parametryzacji, podobnie jak , i ExecuteSqlRaw umożliwia dynamiczną konstrukcję zapytań SQL, podobnie jak FromSqlFromSqlRaw w przypadku zapytań.

Uwaga

Przed programem EF Core 7.0 czasami konieczne było użycie ExecuteSql interfejsów API do wykonania "zbiorczej aktualizacji" w bazie danych, jak powyżej. Jest to znacznie bardziej wydajne niż wykonywanie zapytań dotyczących wszystkich pasujących wierszy, a następnie używanie SaveChanges ich do modyfikowania. Program EF Core 7.0 wprowadził funkcję ExecuteUpdate i ExecuteDelete, co umożliwiło wyrażenie wydajnych operacji aktualizacji zbiorczych za pośrednictwem LINQ. Zaleca się używanie tych interfejsów API zawsze, gdy jest to możliwe, zamiast ExecuteSql.

Ograniczenia

Istnieje kilka ograniczeń, które należy wziąć pod uwagę podczas zwracania typów jednostek z zapytań SQL:

  • Zapytanie SQL musi zwracać dane dla wszystkich właściwości typu jednostki.
  • Nazwy kolumn w zestawie wyników muszą być zgodne z nazwami kolumn, do których są mapowane właściwości. Należy pamiętać, że to zachowanie różni się od ef6; Program EF6 zignorował mapowanie właściwości na kolumnę dla zapytań SQL, a nazwy kolumn zestawu wyników musiały być zgodne z tymi nazwami właściwości.
  • Zapytanie SQL nie może zawierać powiązanych danych. Jednak w wielu przypadkach można utworzyć na podstawie zapytania, używając Include operatora do zwracania powiązanych danych (zobacz Dołączanie powiązanych danych).