Как вывести результат SQL запроса в .net core?

236

21 сентября 2018, 10:50

Есть RAW SQL запрос с параметрами реализующий Full-text search. Необходимо преобразовать результаты запроса в список.

WITH
Hospital_CTE as ( 
    SELECT Hospitals.Id AS Id, ROW_NUMBER() OVER(ORDER BY HospitalFTS.[Rank] DESC, HospitalFTS.[Key]) as rownum
    FROM CONTAINSTABLE(Hospitals, (Name, Address), @query) AS HospitalFTS
        JOIN Hospitals AS Hospitals ON Hospitals.Id = HospitalFTS.[Key] AND (CityId = @cityId)
),
Doctors_CTE as (
    SELECT Doctors.Id AS Id, ROW_NUMBER() OVER(ORDER BY ISNULL(DoctorFTS.Rank, 0)+SpecialityFTSRank DESC, Doctors.Id) as rownum
    FROM (
            SELECT DoctorId as Id, ISNULL(MAX(SpecialityFTS.Rank),0) as SpecialityFTSRank
            FROM Employments As Employments
                JOIN Hospitals As Hospitals ON Hospitals.Id = Employments.HospitalId AND (CityId = @cityId)
                LEFT JOIN CONTAINSTABLE(Specialities, Name, @query) AS SpecialityFTS ON Employments.SpecialityId = SpecialityFTS.[Key]
            GROUP BY DoctorId
        ) AS Doctors
        LEFT JOIN CONTAINSTABLE(Doctors, Name, @query) as DoctorFTS ON Doctors.Id = DoctorFTS.[Key]
    WHERE DoctorFTS.Rank > 0 OR SpecialityFTSRank > 0
)
SELECT Id, " + (int)SearchScope.Hospitals + @" as Scope, ScopeTotal
FROM Hospital_CTE cte CROSS JOIN (SELECT COUNT(*) FROM Hospital_CTE) _t(ScopeTotal)
WHERE rownum BETWEEN 1 AND 10
UNION ALL
SELECT Id, " + (int)SearchScope.Doctors + @" as Scope, ScopeTotal
FROM Doctors_CTE cte CROSS JOIN (SELECT COUNT(*) FROM Doctors_CTE) _t(ScopeTotal)
WHERE rownum BETWEEN 1 AND 10";

ExecuteSqlCommand запрос обрабатывает и выводит в лог сообщение об успешности. Но мне необходимо получить результаты для последующего использования. FromSql, SqlQuery к context.Database не подключаются.

DbContext db;
public List<string> Search(string query, int cityId)
{
   string sql = "Запрос";
   var a = db.Database.?(sql, query, cityId);
   //т.е. надо вернуть список результатов запроса
   return a.ToList();
}

Answer 1

using (var connection = new SqlConnection("ConnectionString"))
{
    using (var comm = connection.CreateCommand())
    {
        comm.CommandType = CommandType.Text;
        comm.CommandText = "sql-query";
        comm.Parameters.AddWithValue("a", b);
        await connection.OpenAsync();
        using (var items = await comm.ExecuteReaderAsync())
        {
            if (!items.HasRows)
            {
                ...
            }
            else
                while (await items.ReadAsync())
                {
                    ...
                }
            items.Close();
        }
        connection.Close();
    }
}

Где items[0] и items[1] и будут полями строк вашего датасета Scope и ScopeTotal соответственно. Обрабатывайте их в блоке while.