Using the EntitySpaces "Contains" Where Operand

Full-Text Searching for SQL Server only

The CONTAINS predicate for SQL Server is "used to search columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases." The MSDN documentation in the link contains the syntax and examples.

NOTE: This feature was introduced starting with EntitySpaces 2007 Beta v1.1112.0 or later.

Enabling Full-Text Searching

In order to try out the AdventureWorks examples shown in the MSDN docs, or to use the "Contains" Where Operand within EntitySpaces DynamicQuery, Full-Text indexing must be enabled at the database, table, and column levels. The SQL Server FullText Search service must be installed and running. The table must have a single-column, unique index defined. The primary key will do, unless it is a composite key. In that case, you must create a unique index on the table that contains only one column before following these steps.

Management Studio has a wizard that will walk you through full-text enabling process:

• Right-click your database in Management Studio and click Properties.
• Click the Files Page.
• Make sure there is a check for "Use full-text indexing".
• Click OK.
• Navigate to a table in the database.
• Right-click, and under Full-text Index, click Define Full-text Index.
• If the "Welcome" page appears, click "Next".
• Choose a unique index, and click "Next".
• Place a check next to the columns to include, and click "Next".
• On the Track Changes screen, we chose "Automatic", and clicked "Next". Read the information at the bottom of the screen, and consult the documentation, if you wish to change this.
• On the "Select a Catalog" screen, either choose an existing Full-text Catalog, or create a new one, and click "Next".
• Creating a population schedule is optional. We just clicked "Next".
• If everything looks good on the "Summary" screen, click "Finished". The catalog and index will be created.
• Assuming a "Successful" status, click "Close".
• Now the Full-text features will be available on that table's right-click context menu, including "Start Full Population".

The EntitySpaces API for "Contains"

Here is a simple example using NEAR in the search term:

CustomerCollection collection = new CustomerCollection();
string nameTerm = "Acme NEAR Company";

collection.Query.Select(
    collection.Query.CustomerID,
    collection.Query.CustomerSub,
    collection.Query.CustomerName,
    collection.Query.Notes);
collection.Query.Where(
    collection.Query.CustomerName.Contains(nameTerm));

collection.Query.Load();

This is the SQL syntax generated:

SELECT [CustomerID],[CustomerSub],[CustomerName],[Notes]  
FROM [ForeignKeyTest].[dbo].[Customer] 
WHERE  CONTAINS([CustomerName], @CustomerName1)

In this case, our query returned two results, one for "Acme Company" and one for "Acme East Company".

This shows how to use "Contains" as the where clause in a Join, and demonstrates the use of wild cards and Booleans in a search term. Our goal - for the CustomerName, "Acme" must be in close approximation to "Company", and the "Notes" column must contain "Road" and any form of "St", "St.", Street", "Ave", or "Avenue", etc.:

EmployeeCollection collection = new EmployeeCollection();
string nameTerm =
    "acme NEAR company";
string notesTerm =
    "Road AND (\"St*\" OR \"Ave*\")";

EmployeeQuery eq = new EmployeeQuery("eq");
CustomerQuery cq = new CustomerQuery("cq");

eq.Select(eq.EmployeeID, eq.LastName, cq.CustomerName);
eq.LeftJoin(cq).On(eq.EmployeeID == cq.StaffAssigned);
eq.Where(cq.CustomerName.Contains(nameTerm));
eq.Where(cq.Notes.Contains(notesTerm));

collection.Load(eq);

The generated SQL syntax:

SELECT eq.[EmployeeID],eq.[LastName],cq.[CustomerName]  
FROM [ForeignKeyTest].[dbo].[Employee] eq 
LEFT JOIN [ForeignKeyTest].[dbo].[Customer] cq 
ON (eq.[EmployeeID] = cq.[StaffAssigned]) 
WHERE  CONTAINS(cq.[CustomerName], @CustomerName1) AND  
CONTAINS(cq.[Notes], @Notes2)

If you are having trouble producing the results you want, we ask that you post both your desired outcome, and the EntitySpaces code that is not working for you in this forum. Please, do not just ask, "How do I accomplish such-and-such with Contains?" We will probably just reply with, "What does your EntitySpaces code look like?" And, delay the resolution to your issue. If you already have SQL syntax that works in Management Studio, you can post that as well, as it may speed things along.