What is Full-Text Search? Implementing SQL Server Full-Text Search In An ASP.NET MVC Websites With Entity Framework

April 24, 2017 4 min read

Hi Developers!

Today here, I am going to introduce What is Full-Text Search in SQL Server? and how we can implement modern product searching options in ASP.NET MVC eCommerce websites with Entity Framework for .NET MVC developers.

What is Full-Text Search in ASP.NET eCommerce Websites?

In easy terms, Full-Text Search depends on the indexing of character information, making it lots faster to perform matches against the index than the scans needed by the SQL statements that use sort predicate. Full-text looking is additionally plenty more flexible than using a sort predicate, that solely supports the wildcard operator.

Why Do We Need a Product Search Functionality in our eCommerce Websites?

Literally, each website, from huge eCommerce sites like Amazon.com, all the way down to tiny blogs or personal sites has a large need of product search functionality. Once your site’s content starts to grow, your guests can wish to be able to search your products, accessories and other site pieces of information. Such search utility can make your website a lot of interactive, from a customer’s purpose of view, and can encourage them to stay longer and buy options. whereas there are many main approaches in implementing search functionality, like full-text search, we are attending to cover the fundamentals of the ‘Full-Text Search within the underlying SQL DataBase’, which is additionally useful for ASP.NET MVC developers who are working with eCommerce and shopping carts.

Important Steps for the Full-text Search functionality for .NET Website:

Here are the important steps to implementing SQL Server Full-Text Search in an ASP.NET MVC web application with Entity Framework.

Create a Table for the Product Search.


CREATE TABLE [dbo].[ProductSearch] (
	[Id] INT IDENTITY(1,1) NOT NULL,
	[ProductId] INT NOT NULL,
	[ProductCode] VARCHAR(50) NOT NULL,
	[ProductName] VARCHAR(250) NULL,
	[ProductDescription] NVARCHAR(MAX) NULL,
	[ProductActive] BIT NOT NULL,
	CONSTRAINT [Pk_ProductSearch] PRIMARY KEY CLUSTERED ([Id] ASC)
);

Create a Full-Text Catalog for the searching.


CREATE FULLTEXT CATALOG FTSearch;

Create the Full-Text index.


CREATE FULLTEXT INDEX ON ProductSearch
(
	ProductCode,
	ProductName,
	ProductDescription
)
KEY INDEX Pk_ProductSearch
ON FTSearch
WITH
(
	LANGUAGE 1033
);

Create Procedure for the search functionality.


CREATE PROCEDURE [dbo].[Sp_ProductSearch]
@SearchTerm NVARCHAR(250)
AS
BEGIN
	IF (@SearchTerm LIKE '% %')
	BEGIN
		SELECT 
			Id,
			(CASE 
				WHEN ProductCode LIKE '%' + @SearchTerm + '%' 
					OR ProductName LIKE '%' + @SearchTerm + '%' 
					OR ProductDescription LIKE '%' + @SearchTerm + '%' 
				THEN 0 
				ELSE 1 
			END) AS RowIndex
		FROM ProductSearch
		INNER JOIN FREETEXTTABLE(ProductSearch, (ProductCode, ProductName, ProductDescription), @SearchTerm) AS fttpd
			ON Id = fttpd.[KEY]
		ORDER BY RowIndex;
	END
	ELSE
	BEGIN
		SELECT 
			Id,
			(CASE 
				WHEN ProductCode LIKE '%' + @SearchTerm + '%' 
					OR ProductName LIKE '%' + @SearchTerm + '%' 
					OR ProductDescription LIKE '%' + @SearchTerm + '%' 
				THEN 0 
				ELSE 1 
			END) AS RowIndex
		FROM ProductSearch
		WHERE CONTAINS((ProductCode, ProductName, ProductDescription), @SearchTerm)
		ORDER BY RowIndex;
	END
END;

Performing a Full-Text Search. Now here I am going to introduce fastest searching options is “Contains” and “FREETEXT”, yes it’s faster than using “LIKE” while we are dealing with SQL Server. In our example, we need Contains and FREETEXT both searching options both.
- FREETEXT
- FREETEXTTABLE
- CONTAINS
- CONTAINSTABLE
Let’s now code it!

FREETEXT:

Is a predicate used in the Transact-SQL WHERE clause of a Transact-SQL SELECT statement to perform a SQL Server full-text search on full-text indexed columns containing character-based data types? This predicate searches for values that match the meaning and not just the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches:

Separates the string into individual words based on word boundaries (word-breaking).
Generates inflectional forms of the words (stemming).
Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.


-- Example Syntax
SELECT * 
FROM table 
WHERE FREETEXT(Column1, 'test');

-- Query to search in ProductSearch table
SELECT * 
FROM ProductSearch 
WHERE FREETEXT(ProductName, ProductDescription, 'Pop Rivets');

CONTAINS:

You can use “*” before and after in containing syntax same as a like operator. but you need to use double quote before and after the search string

You can not use space in using Contains.


-- Example Syntax
SELECT * 
FROM table 
WHERE CONTAINS(Column1, 'test');

-- Query to search in ProductSearch table
SELECT * 
FROM ProductSearch 
WHERE CONTAINS(ProductName, 'Pop Rivets') 
	OR CONTAINS(ProductDescription, 'Pop Rivets');

Example:


-- Query to search for the term 'Pop' in the ProductName and ProductDescription columns
SELECT * 
FROM ProductSearch 
WHERE CONTAINS(ProductName, 'Pop') 
	OR CONTAINS(ProductDescription, 'Pop');

-- Query to search for terms starting with 'Po' in the ProductName and ProductDescription columns
SELECT * 
FROM ProductSearch 
WHERE CONTAINS(ProductName, '"Po*"') 
	OR CONTAINS(ProductDescription, '"Po*"');

The example used in Entity Framework:

Here is the full code with a real-time example of full-text searching products that I am done with my ASP.NET MVC eCommerce website.


public class ProductSearch
{
	public int RowIndex { get; set; }
	public int Id { get; set; }
}

// Add this method to your repository class and interface
public class ProductSearchRepository
{
	private readonly string ConnectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];

	public IList ProductSearchs(string searchTerm)
	{
		using (var connection = new SqlConnection(ConnectionString))
		{
			IList productSearchs = new List();
			try
			{
				using (var command = connection.CreateCommand())
				{
					command.CommandText = "[dbo].[Sp_ProductSearch]";
					command.CommandType = CommandType.StoredProcedure;

					// Input parameters
					command.Parameters.Add(new SqlParameter("@SearchTerm", SqlDbType.NVarChar, 250) { Value = searchTerm });

					command.CommandTimeout = 0;
					connection.Open(); // Ensure the connection is open before executing commands
					var rdr = command.ExecuteReader();
					while (rdr.Read())
					{
						productSearchs.Add(new ProductSearch
						{
							Id = rdr.GetInt32(rdr.GetOrdinal("Id")),
							RowIndex = rdr.GetInt32(rdr.GetOrdinal("RowIndex"))
						});
					}
					rdr.Close();
					return productSearchs;
				}
			}
			catch (Exception ex)
			{
				throw; // Avoid using `throw ex`, just `throw` to maintain stack trace
			}
			finally
			{
				if (connection.State == ConnectionState.Open)
				{
					connection.Close();
				}
			}
		}
	}
}

// Example usage
string searchTerm = "Search keyword";

IQueryable productQuery = productSearchRepository.Query
	.Where(p => p.ProductActive == "Y");

var products = new List();
IList freeTextProduct = productSearchRepository.ProductSearchs(searchTerm);

// Add Exact Match Products, RowIndex == 0
products.AddRange(productQuery.ToList()
	.Where(x => freeTextProduct.Any(z => z.RowIndex == 0 && z.Id == x.Id))
	.ToList());

// Add Relevant Products, RowIndex == 1
products.AddRange(productQuery.ToList()
	.Where(x => freeTextProduct.Any(z => z.RowIndex == 1 && z.Id == x.Id))
	.ToList());

Summary:

I provided a basic introduction to Full-Text Search and showed how to create a Full-Text Search index from inside SQL Server with Entity Framework. It then mentioned some basic queries and showed how to create a stored procedure to come up with paged results from a Full-Text Search. Finally, it showed the way to use the database.SqlQuery technique of the Entity Framework DbContext to execute the stored procedure and to pass the result back to C# code.

Find more useful blogs related to ASP.NET MVC development for the unique Asp.Net MVC developers.

You may also Hire Experienced Asp.Net MVC developers from our Satva Solutions company.

Thanks…!!