SQL Server Full-Text Search and EF Core

[utterances-bot]

SQL Server Full-Text Search and EF Core | Brice’s Blog

Full-Text Search is a feature of Microsoft SQL Server that lets you perform search engine like queries against the string properties of your entities.

https://www.bricelam.net/2020/08/08/mssql-freetext-and-efcore.html

[SoftCircuits]

Too bad they don't have support for CONTAINSTABLE, as that has always been my preferred approach to full-text search.

[bricelam]

@SoftCircuits You might be able to use it via FromSql. First-class support is tracked by https://github.com/dotnet/efcore/issues/11487.

[mahesh-anjani]

How to do full-text search on byte[] (varbinary(max) in SQL)? Can you please share sample or link with sample?

[bricelam]

@mahesh-anjani Tracked by https://github.com/dotnet/efcore/issues/11482. You might be able to work around it by defining your own function mapping.

[mahesh-anjani]

Thank you for reply @bricelam. It seems like "FromExpression" is specific to EF Core 5.x. Is there any alternative for "FromExpression" in EF Core 3.1.15?

public IQueryable PostsWithPopularComments(int likeThreshold) => FromExpression(() => PostsWithPopularComments(likeThreshold));

[bricelam]

I don't think we supported TVFs in 3.1. @pmiddleton might remember.

[pmiddleton]

TVFs were not added until 5.0.

[jrpharis]

We are using .NET Core 3.1, is it possible to do fuzzy matching via EF.Functions.Contains? I looked at the Full-Text Search documentation and it says that CONTAINS can do fuzzy/less-precise searching and even return weighted results. But looking at the EF.Functions.Contains method, it doesn't appear to have a way to specify the preciseness of a match.

[steinbachio]

Is there any way that entities.Where(x => x.Name.Contains("test")) Will result in SELECT * FROM Entities WHERE CONTAINS(Name, 'test') instead of SELECT * FROM Entities WHERE Name LIKE '%test%'? Of course it should only do that on Properties that are full-text indexed.