Using LINQ to SQL to search entire database

Question

Is it possible with LINQ to SQL to search the entire database (obviously only the parts that are mapped in the .dbml file) for a string match? I'm trying to write a function that will take a string of "Search Term" and search all mapped entities and return a List(Of Object) that can contain a mixture of entities i.e. if I have a table "Foo" and table "Bar" and search for "wibble", if there is a row in "Foo" and one in "Bar" that contain "wibble" i would like to return a List(Of Object) that contains a "Foo" object and a "Bar" object. Is this possible?

Answer 1

LINQ to SQL, ORMs in general, even SQL is a bad match for such a query. You are describing a full-text search so you should use SQL Server's full text search functionality. Full Text Search is available in all versions and editions since 2000, including SQL Server Express. You need to create an FTS catalog and write queries that use the CONTAINS, FREETEXT functions in your queries.

Why do you need such functionality? Unless you specifically want to FTS-enable your application, this is a ... strange ... way to access your data.

Answer 2

It's probably 'possible', but most databases are accessed through web or network, so its a very expensive operation. So it sounds like bad design.

Also there is the problem of table and column names, this is probably your biggest problem. It's possible to get the column names through reflection, but I don't know for table names:

foreach (PropertyInfo property in typeof(TEntity).GetProperties())
   yield return property.Name;

edit: @Ben, you'r right my mistake.

Answer 3

This can be done but will not be pretty. There are several possible solutions.

1. Write the queries for every table yourself and execute them all in your query method.

var users = context.Users
    .Where(x => x.FirstName.Contains(txt) || x.LastName.Contains(txt))
    .ToList();

var products = context.Products
    .Where(x => x.ProductName.Contains(txt));

var result = user.Cast<Object>().Concat(products.Cast<Object>());

2. Fetch all (relevant) tables into memory and perform the search using reflection. Less code to write payed with a huge performance impact.

3. Build the expression trees for the searches using reflection. This is probably the best solution but it is probably challenging to realize.

4. Use something designed for full-text search - for example full-text search integrated into SQL Server or Apache Lucene.

All LINQ solution will (probably) require one query per table which imposes a non-negligible performance impact if you have many tables. Here one should look for a solution to batch this queries into a single one. One of our projects using LINQ to SQL used a library for batching queries but I don't know what it name was and what exactly it could do because I worked most of the time in the front-end team.

Answer 4

Ask your boss the following:

"Boss, when you go to the library to find a book about widgets, do you walk up to the first shelf and start reading every book to see if it is relevant, or do you use some sort of pre-compiled index that the librarian has helpfully configured for you, ahead of time?"

If he says "Well, I would use the index" then you need a Full Text index.

If he says "Well, I would start reading every book, one by one" then you need a new job, a new boss, or both :-)