Блог

Я новичок в использовании индексов свободного текста SQL server. Я написал запрос, который я хотел бы преобразовать в запрос EF Core Linq.

Я не уверен, возможно это или нет (если нет, я создам хранимую процедуру), но в идеале я хотел бы создать ее на C#, так как несколько других запросов будут более динамичными, чем этот, и я бы предпочел не иметь нескольких хранимых процедур.

Вот запрос:

 declare @searchTerm varchar(50) = '16 Sorrento';
declare @postcode varchar(50) = 'CF10 4AR';
declare @language varchar(3) = 'ENG';
declare @offset int = 0;
declare @limit int = 100;

select 
    RankTotal = isnull(RankSAONDetails, 0)   isnull(RankPAONDetails, 0)   
                isnull(RankStreet, 0)   isnull(RankWard, 0)   
               isnull(RankPostcode, 0),
    Id, UPRN, ParentUPRN, USRN, SAONDetails, PAONDetails, 
    Street, Ward, PostTown, Postcode, Easting, Northing, 
    Usage, Language, LastUpdateDate, DateExtracted
from
    LLPG
left join 
    (select Rank * 3.0 as RankSAONDetails, [KEY] 
     from FREETEXTTABLE(LLPG, SAONDetails, @searchTerm)) as k on k.[key]= LLPG.Id
left join 
    (select Rank * 4.0 as RankPAONDetails, [KEY] 
     from FREETEXTTABLE(LLPG, PAONDetails, @searchTerm)) as l on l.[key]= LLPG.Id
left join 
    (select Rank * 3.0 as RankStreet, [KEY] 
     from FREETEXTTABLE(LLPG, Street, @searchTerm)) as m on m.[key]= LLPG.Id
left join 
    (select Rank * 3.0 as RankWard, [KEY] 
     from FREETEXTTABLE(LLPG, Ward, @searchTerm)) as w on w.[key]= LLPG.Id
left join 
    (select Rank * 10.0 as RankPostcode, [KEY] 
     from FREETEXTTABLE(LLPG, Postcode, @searchTerm)) as p on p.[key]= LLPG.Id
where
    LLPG.Language = @language and
    StatusCode = 1 and
    LPIStatusCode = 1 and
    Usage LIKE 'Res%' and
    Replace(Postcode, ' ', '') = Replace(@postcode, ' ', '') and
    (RankSAONDetails is not null or
     RankPAONDetails is not null or
     RankStreet is not null or
     RankWard is not null or
     RankPostcode is not null)
order by
    RankTotal desc
offset @offset rows
    fetch next @limit rows only