.NET Core MVC] EF Core - 9. raw SQL queries

raw SQL queries 실행

EF의 장점 중 하나는, data 저장 특정 방법에 매여있지 않다는 것이다. SQL queries를 작성할 수 있다. 하지만, 특정 SQL queries를 실행할때 예외적인 시나리오가 있다. 이 시나리오를 위해, SQL 구문을 database에 직접적으로 전달할 수 있는 함수를 제공한다. 

 

EF Core 1.0에 다음의 option이 있다. 

• entity type을 return하는 queries를 위해서는 DbSet.FromSql 함수를 사용한다. return object는 DbSet object이어야 한다. turn tracking off 하지 않는 이상 database context에 의해 자동으로 추적된다. 
• non-query 명령어를 위해서는 Database.ExecuteSqlCommand 함수를 사용한다. 

 

entity return type이 필요하지 않다면, EF가 제공하는 ADO.NET 을 사용해도 된다. entity type을 return 해도 database context에 의해 추적되지 않는다. 

 

언제나 그렇듯, SQL command를 실행할 떄는 SQL injection 공격으로부터 웹앱을 보호해야 한다. 한 가지 방법은 web page로부터 submit된 string이 SQL command로 변환되지 않도록 parameterized query를 사용해야 한다. 이번 POST에서는 사용자 입력을 query로 만들때 parameterized query를 사용할 것이다. 

 

entities를 return하는 query 호출

DbSet<TEntity> class는 TEntity type의 entity를 반환하는 query를 실행할 수 있는 함수를 제공한다. Department controller의 Details 함수 코드를 수정해보자. 

public async Task<IActionResult> Details(int? id)
{
    if (id == null)
    {
        return NotFound();
    }

    string query = "SELECT * FROM Department WHERE DepartmentID = {0}";
    var department = await _context.Departments
        .FromSql(query, id)
        .Include(d => d.Administrator)
        .AsNoTracking()
        .FirstOrDefaultAsync();

    if (department == null)
    {
        return NotFound();
    }

    return View(department);
}

다른 type을 반환하는 query 호출

LINQ 대신에, SQL 을 작성해보자. entity objects가 아닌 다른 값을 반환할 수 있다. ADO.NET 코드를 작성하고, EF에서 database connection을 얻는다. 

    public async Task<IActionResult> About()
    {  
        /*
        IQueryable<EnrollmentDateGroup> data =
            from student in _context.Students
            group student by student.EnrollmentDate into dateGroup
            select new EnrollmentDateGroup()
            {
                EnrollmentDate = dateGroup.Key,
                StudentCount = dateGroup.Count()
            };


        return View(await data.AsNoTracking().ToListAsync());
        */
       
        List<EnrollmentDateGroup> groups = new List<EnrollmentDateGroup>();
        var conn = _context.Database.GetDbConnection();
        try
        {
            await conn.OpenAsync();
            using (var command = conn.CreateCommand())
            {
                string query = "SELECT EnrollmentDate, COUNT(*) AS StudentCount "
                    + "FROM Person "
                    + "WHERE Discriminator = 'Student' "
                    + "GROUP BY EnrollmentDate";
                command.CommandText = query;
                DbDataReader reader = await command.ExecuteReaderAsync();


                if (reader.HasRows)
                {
                    while (await reader.ReadAsync())
                    {
                        var row = new EnrollmentDateGroup { EnrollmentDate = reader.GetDateTime(0), StudentCount = reader.GetInt32(1) };
                        groups.Add(row);
                    }
                }
                await reader.DisposeAsync();


            }
        }
        finally
        {
            conn.Close();
        }
        return View(groups);
    }

update query 호출

Contoso University 관리자가 database에 전역 변경(예를들어, 모든 강의의 학점을 변경)을 하려고 한다. 강의수가 많은 학교라면, 모든 entities를 불러와서 하나씩 수정하는 것은 비효율적이다. 이 POST에서 SQL UPDATE 구문을 실행하여, 모든 강의의 학점수를 변경할 수 있는 페이지를 구현해보자. 

public IActionResult UpdateCourseCredits()
{
    return View();
}

public async Task<IActionResult> UpdateCourseCredits(int? multiplier)
{
    if (multiplier != null)
    {
        ViewData["RowsAffected"] =
            await _context.Database.ExecuteSqlRawAsync("UPDATE Course SET Credits = Credits * {0}",
                                                           parameters: multiplier);
    }
    return View();
}

abstraction layer 생성

많은 개발자들은 repository, EF와 함께 동작하는 코드를 감싸는 wrapper로서 unit of work patterns을 사용한다. 이러한 패턴은 data access layer와 앱의 business login layer 사이에 abstraction layer를 생성하기 위한 목적으로 사용된다. 이러한 패턴의 구현은 앱을 data 저장의 변경으로부터 분리보호하고, unit 테스트와 test-driven development를 가능하게 한다. 하지만, 이 패턴을 구현하기 위해서 추가코드를 작성하는 것이 EF를 사용하는 앱에 아래의 이유로 항상 최선의 선택은 아니다. 

• EF context class 자체로 앱 코드를 데이터 저장 관련 코드로부터 분리보호한다. 
• EF를 사용하면서 하는 database update를 위해 EF context class는 unit-of-work class처럼 동작한다.
• EF는 repository code작성 없이 TDD 구현 기능을 포함한다. 

EF Core는 테스트에 사용할 수 있는 in-memory database provider를 구현한다. 

 

자동 변경 감지

EF는 entity의 원래 값과 현재 값을 비교하여 어떻게 변경되었는지 결정한다. entity가 쿼리되거나 attacked 되었을 때의 원래 값이 저장된다. 자동 변경 감지를 일으키는 함수는 아래와 같다. 

• DbContext.SaveChanges
• DbContext.Entry
• ChangeTracker.Entries

 

임시적으로 ChangeTracker.AutoDetectChangesEnabled property를 사용해서 자동 변경 감지를 off 함으로 퍼포먼스를 향상시킬 수 있다. 

_context.ChangeTracker.AutoDetectChangesEnabled = false;