There is a RAW SQL query with parameters that implements Full-text search. You must convert the query results to a list.

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 request processes and outputs to the log a message of success. But I need to get results for later use. FromSql , SqlQuery to context.Database not connected.

 DbContext db; public List<string> Search(string query, int cityId) { string sql = "Запрос"; var a = db.Database.?(sql, query, cityId); //т.е. надо вернуть список результатов запроса return a.ToList(); }
  • ExecuteReaderAsync() ? show the code where you work with this query, then it will be easier to give a comprehensive solution, rather than a hint - Anatol
  • Most likely you will have to use ExecuteReaderAsync() since DatabaseFacade has no methods for sending direct requests. - naitosnait

1 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(); } }

Where items[0] and items[1] will be the field strings of your Scope and ScopeTotal respectively. Process them in a while block.