SQL 쿼리

Entity Framework Core를 사용하면 관계형 데이터베이스로 작업할 때 SQL 쿼리로 드롭다운할 수 있습니다. SQL 쿼리는 원하는 쿼리를 LINQ를 사용하여 표현할 수 없거나 LINQ 쿼리로 인해 EF가 비효율적인 SQL을 생성하는 경우에 유용합니다. SQL 쿼리는 기본 엔터티 형식 또는 모델의 일부인 키 없는 엔터티 형식을 반환할 수 있습니다.

기본 SQL 쿼리

FromSql을 사용하여 SQL 쿼리를 기반으로 LINQ 쿼리를 시작할 수 있습니다.

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

SQL 쿼리를 사용하여 엔터티 데이터를 반환하는 저장 프로시저를 실행할 수 있습니다.

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

매개 변수 전달

다음 예에서는 SQL 쿼리 문자열에 매개 변수 자리 표시자를 포함하고 추가 인수를 제공하여 단일 매개 변수를 저장 프로시저에 전달합니다.

var user = "johndoe";

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

이 구문은 일반적인 C# 문자열 보간처럼 보일 수 있지만 제공된 값은 DbParameter로 래핑되고 생성된 매개 변수 이름은 {0} 자리 표시자가 지정된 위치에 삽입됩니다. 이렇게 하면 FromSql을 SQL 삽입 공격으로부터 안전하게 지키고 데이터베이스에 효율적이고 올바르게 값을 보낼 수 있습니다.

저장 프로시저를 실행할 때 특히 저장 프로시저에 선택적 매개 변수가 있는 경우 SQL 쿼리 문자열에서 명명된 매개 변수를 사용하는 것이 유용할 수 있습니다.

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

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

전송되는 데이터베이스 매개 변수에 대한 더 많은 제어가 필요한 경우 DbParameter를 생성하고 매개 변수 값으로 제공할 수도 있습니다. 이렇게 하면 매개 변수의 정확한 데이터베이스 형식 또는 크기, 전체 자릿수 또는 길이와 같은 패싯을 설정할 수 있습니다.

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

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

동적 SQL 및 매개 변수

FromSql 및 해당 매개 변수화는 가능한 경우 항상 사용해야 합니다. 그러나 SQL을 동적으로 조각화해야 하고 데이터베이스 매개 변수를 사용할 수 없는 특정 시나리오가 있습니다. 예를 들어 C# 변수에 필터링할 속성의 이름이 있다고 가정해 보겠습니다. 다음과 같은 SQL 쿼리를 사용하려고 할 수 있습니다.

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

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

데이터베이스는 열 이름(또는 스키마의 다른 부분)을 매개 변수화할 수 없으므로 이 코드는 작동하지 않습니다.

첫째, SQL을 통해 또는 다른 방법으로 쿼리를 동적으로 생성할 때의 의미를 고려하는 것이 중요합니다. 사용자의 열 이름을 수락하면 인덱싱되지 않은 열을 선택할 수 있으므로 쿼리가 매우 느리게 실행되고 데이터베이스가 오버로드됩니다. 또는 노출하지 않으려는 데이터가 포함된 열을 선택할 수 있습니다. 진정한 동적 시나리오를 제외하고 일반적으로 매개 변수화를 사용하여 단일 쿼리로 축소하는 대신 두 개의 열 이름에 대해 두 개의 쿼리를 사용하는 것이 좋습니다.

SQL을 동적으로 생성하기로 결정한 경우 데이터베이스 매개 변수를 사용하는 대신 변수 데이터를 SQL 문자열로 직접 보간할 수 있는 FromSqlRaw를 사용해야 합니다.

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

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

위의 코드에서 열 이름은 C# 문자열 보간을 사용하여 SQL에 직접 삽입됩니다. 이 문자열 값이 안전한지 확인하고 안전하지 않은 원본에서 가져온 경우 삭제해야 합니다. 즉, 세미콜론, 주석 및 기타 SQL 구문과 같은 특수 문자를 검색하고 제대로 이스케이프하거나 이러한 입력을 거부합니다.

반면에 열 값은 DbParameter를 통해 전송되므로 SQL 삽입이 발생할 때 안전합니다.

LINQ로 작성

LINQ 연산자를 사용하여 초기 SQL 쿼리를 기반으로 작성할 수 있습니다. EF Core는 SQL을 하위 쿼리로 처리하고 데이터베이스에서 구성합니다. 다음 예제에서는 TVF(테이블 반환 함수)에서 선택하는 SQL 쿼리를 사용한 후 LINQ를 사용하여 이에 대해 구성하여 필터링과 정렬을 수행합니다.

var searchTerm = "Lorem ipsum";

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

위의 쿼리는 다음 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

관련 데이터 포함

Include 연산자는 다른 LINQ 쿼리와 마찬가지로 관련 데이터를 로드하는 데 사용할 수 있습니다.

var searchTerm = "Lorem ipsum";

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

LINQ를 사용하여 구성하려면 SQL 쿼리가 구성 가능해야 합니다. EF Core가 제공된 SQL을 하위 쿼리로 취급하기 때문입니다. 작동 가능한 SQL 쿼리는 일반적으로 키워드로 SELECT 시작하며 하위 쿼리에서 유효하지 않은 SQL 기능을 포함할 수 없습니다( 예: ).

• 후행 세미콜론
• SQL Server의 후행 쿼리 수준 힌트(예: OPTION (HASH JOIN))
• SQL Server에서, SELECT 절에서 OFFSET 0 또는 TOP 100 PERCENT와 함께 사용되지 않는 ORDER BY 절

SQL Server는 저장 프로시저 호출에 대한 구성을 허용하지 않으므로 그러한 호출에 추가 쿼리 연산자를 적용하려고 하면 잘못된 SQL이 발생합니다. EF Core가 저장 프로시저에 대해 구성을 시도하지 않도록 FromSql 또는 FromSqlRaw 메서드 직후에 AsEnumerable 또는 AsAsyncEnumerable을 사용하세요.

변경 내용 추적

FromSql 또는 FromSqlRaw를 사용하는 쿼리는 EF Core에서 다른 LINQ 쿼리와 완전히 동일한 변경 내용 추적 규칙을 따릅니다. 예를 들어 쿼리 프로젝트 엔터티 형식의 경우 기본적으로 결과가 추적됩니다.

다음 예제에서는 TVF(테이블 반환 함수)에서 선택하는 SQL 쿼리를 사용한 이후에 AsNoTracking을 호출하여 변경 내용 추적을 사용하지 않도록 설정합니다.

var searchTerm = "Lorem ipsum";

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

스칼라(비 엔터티) 형식 쿼리

FromSql은 모델에 정의된 엔터티를 쿼리하는 데 유용하지만 SqlQuery는 하위 수준 데이터 액세스 API로 드롭다운할 필요 없이 SQL을 통해 스칼라 비 엔터티 형식을 쉽게 쿼리할 수 있습니다. 예를 들어 다음 쿼리는 Blogs 테이블에서 모든 ID를 가져옵니다.

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

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

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

SQL 쿼리를 통해 LINQ 연산자를 작성할 수도 있습니다. 그러나 SQL은 SQL EF에서 출력 열을 참조해야 하는 하위 쿼리가 되므로 출력 열 Value의 이름을 로 지정해야 합니다. 예를 들어 다음 쿼리는 ID 평균을 초과하는 ID를 반환합니다.

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

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

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

SqlQuery은 데이터베이스 공급자가 지원하는 스칼라 형식과 함께 사용할 수 있습니다. 데이터베이스 공급자가 지원하지 않는 형식을 사용하려는 경우 사전 규칙 구성을 사용하여 값 변환을 정의할 수 있습니다.

SqlQueryRaw는 FromSqlRaw가 엔터티 형식에 대해 그러는 것처럼 SQL 쿼리를 동적으로 생성할 수 있습니다.

쿼리하지 않는 SQL 실행

일부 시나리오에서는 일반적으로 데이터베이스에서 데이터를 수정하거나 결과 집합을 반환하지 않는 저장 프로시저를 호출하기 위해 데이터를 반환하지 않는 SQL을 실행해야 할 수 있습니다. 이 작업은 ExecuteSql을 통해 수행할 수 있습니다.

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

그러면 제공된 SQL이 실행되고 수정된 행 수가 반환됩니다. ExecuteSql은 FromSql과 같은 안전한 매개 변수화를 사용하여 SQL 삽입을 방지하고 ExecuteSqlRaw는 FromSqlRaw가 쿼리에 대해 그러는 것처럼 SQL 쿼리를 동적으로 생성할 수 있도록 합니다.

제한 사항

SQL 쿼리에서 엔터티 형식을 반환할 때 알아야 할 몇 가지 제한 사항이 있습니다.

• SQL 쿼리는 엔터티 형식의 모든 속성에 대해 데이터를 반환해야 합니다.
• 결과 집합의 열 이름은 속성이 매핑되는 열 이름과 일치해야 합니다. 이 동작은 EF6와 다릅니다. EF6은 SQL 쿼리에 대한 속성-열 매핑을 무시했으며 결과 집합 열 이름은 해당 속성 이름과 일치해야 했습니다.
• SQL 쿼리는 관련 데이터를 포함할 수 없습니다. 그러나 대부분의 경우 쿼리의 맨 위에서 Include 연산자를 사용하여 관련 데이터를 반환하도록 작성할 수 있습니다(관련 데이터 포함 참조).