Using LINQ to SQL to search the entire database

Question

Is it possible that LINQ to SQL can search the entire database (obviously, only the parts that appear in the .dbml file) to match the string? I am trying to write a function that takes the string "Search term" and will look for all the matching objects and return a list (Of Object), which may contain a mixture of objects, i.e. If I have a table “Foo” and a table “Bar” and look for “wibble”, if there is a row in “Foo” and in “Bar” containing “wibble”, I would like to return a List (Of Object) that contains object "Foo" and "Foo", Bar. Is this possible?

Answer 1

Ask your boss the following:

"Boss, when you go to the library to find a book about widgets, you go to the first shelf and start reading each book to find out if it fits, or are you using some kind of pre-compiled index that the librarian helped you set up in advance?"

If he says, “Well, I would use an index,” then you need a Full Text index.

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

Answer 2

LINQ to SQL, ORM , SQL - . , SQL Server. 2000 , SQL Server Express. FTS , CONTAINS, FREETEXT .

? FTS-, ... .

Answer 3

, , , . .

, , , . , , :

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

edit: @Ben, .

Answer 4

, . .

1. .

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. () . .

3. . , , , , , .

4. -, , , SQL Server Apache Lucene.

LINQ () , , . . , LINQ to SQL, , , , , .

Answer 5

, , . , 1000K 100 . , Linq to SQL, Sp . , , =)

Answer 6

, - , . . , ​​, 24 . , , . CLR XML, . . AdventureWorks2017 2 . !

:

:

EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john michael'

4 :

EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john michael', @maxDegreeOfParallelism = 4

:

EXEC [dbo].[SearchAllTables] @valueSearchTerm = '(john or michael) and not jack', @tablesSearchTerm = 'not contact'

/ , :

EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john michael', @tablesSearchTerm = 'person contact', @columnsSearchTerm = 'address name'

, :

EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john michael', @getOnlyFirstRowPerTable = 1

:

EXEC [dbo].[SearchAllTables] @tablesSearchTerm = 'person contact'

:

EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john michael', @tablesSearchTerm = 'person contact', @onlyOutputQueries = 1

:

CREATE TABLE #temp (Result NVARCHAR(MAX));
INSERT INTO #temp
    EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john';
SELECT * FROM #temp ORDER BY Result ASC;
DROP TABLE #temp;

https://pastebin.com/RRTrt8ZN