[SQL] T-SQL FUNCTION and WHERE

I am using .NET Core and Entity Framework. I am working on search querying in JobList that query has keys, datetime, sectors, positions etc. filters.

My JobList table is set Full Text Search and I need best approach for search querying.

I created a function for Full Text Search. This function search keys in JobList and I need filter with relationship tables



ALTER FUNCTION F_SearchQuery
(@query nvarchar(3995),
@offset integer,
@next integer)
RETURNS @List TABLE(Id integer)
AS
BEGIN
DECLARE @string nvarchar(4000)
SET @string = '"'+@query +'*"'

INSERT INTO @List(Id)
SELECT FT_TBL.Id
FROM PostJob AS FT_TBL
INNER JOIN CONTAINSTABLE (PostJob, (Title, SearchTags, Info), @string) AS KEY_TBL
ON FT_TBL.Id = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
OFFSET @offset ROWS FETCH NEXT @next ROWS ONLY

RETURN
END


My question is :

SELECT *
FROM F_SearchQuery('software', 0, 10)
INNER JOIN JobLocation J on J.JobId = JobId
INNER JOIN JobFeatures F on F.JobId = JobId
WHERE
J.LocationId IN (filter locations)
AND J.FeatureId IN (filter features)


This query first execute and get table from F_SearchQuery() and after filter with where keywords ?

I need search query + filter relationship tables with SQL function or Entity Framework IQueryable methods.

What is the best way/approach for search querying ?

My other question : Entitiy Framework How to improve to perfom on querying many relationship tables?

Continue reading...