Reputation: 3912

Using LINQ to SQL to search entire database

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?

Upvotes: 4

Answers (7)

Mr. B

Reputation: 2985

I ended up writing this little custom Gem (finds all matching records given a search term):

namespace SqlServerMetaSearchScan
{
    using Newtonsoft.Json;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using System.Linq;
    using System.Security.Cryptography;
    using System.Text;
    using System.Threading;
    using System.Xml;

public class Program
{
    #region Ignition
    public static void Main(string[] args)
    {
        // Defaulting
        SqlConnection connection = null;

        try
        {               
            // Questions
            ColorConsole.Print("SQL Connection String> ");
            string connectionString = Console.ReadLine();
            ColorConsole.Print("Search Term (Case Ignored)> ");
            string searchTerm = Console.ReadLine();
            ColorConsole.Print("Skip Databases (Comma Delimited)> ");
            List<string> skipDatabases = Console.ReadLine().Split(',').Where(item => item.Trim() != string.Empty).ToList();

            // Search
            connection = new SqlConnection(connectionString);
            connection.Open();

            // Each database
            List<string> databases = new List<string>();
            string databasesLookup = "SELECT name FROM master.dbo.sysdatabases";
            SqlDataReader reader = new SqlCommand(databasesLookup, connection).ExecuteReader();
            while (reader.Read())
            {
                // Capture
                databases.Add(reader.GetValue(0).ToString());
            }

            // Build quintessential folder
            string logsDirectory = @"E:\Logs";
            if (!Directory.Exists(logsDirectory))
            {
                // Build
                Directory.CreateDirectory(logsDirectory);
            }
            string baseFolder = @"E:\Logs\SqlMetaProbeResults";
            if (!Directory.Exists(baseFolder))
            {
                // Build
                Directory.CreateDirectory(baseFolder);
            }

            // Close reader
            reader.Close();                

            // Sort databases
            databases.Sort();

            // New space
            Console.WriteLine(Environment.NewLine + " Found " + databases.Count + " Database(s) to Scan" + Environment.NewLine);

            // Deep scan
            foreach (string databaseName in databases)
            {
                // Skip skip databases
                if (skipDatabases.Contains(databaseName))
                {
                    // Skip 
                    continue;
                }

                // Select the database
                new SqlCommand("USE " + databaseName, connection).ExecuteNonQuery();

                // Table count
                int tablePosition = 1;

                try
                {
                    // Defaulting
                    List<string> tableNames = new List<string>();

                    // Schema examination
                    DataTable table = connection.GetSchema("Tables");

                    // Query tables
                    string tablesLookup = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES";
                    using (SqlDataReader databaseReader = new SqlCommand(tablesLookup, connection).ExecuteReader())
                    {
                        // Get data
                        while (databaseReader.Read())
                        {
                            // Push
                            if (databaseReader.GetValue(0).ToString().Trim() != string.Empty)
                            {
                                tableNames.Add(databaseReader.GetValue(0).ToString());
                            }
                        }

                        // Bail
                        databaseReader.Close();
                    }

                    // Sort
                    tableNames.Sort();                        

                    // Cycle tables
                    foreach (string tableName in tableNames)
                    {
                        // Build data housing
                        string databasePathName = @"E:\Logs\\SqlMetaProbeResults" + databaseName;
                        string tableDirectoryPath = @"E:\Logs\SqlMetaProbeResults\" + databaseName + @"\" + tableName;

                        // Count first
                        int totalEntityCount = 0;
                        int currentEntityPosition = 0;                            
                        string countQuery = "SELECT count(*) FROM " + databaseName + ".dbo." + tableName;
                        using (SqlDataReader entityCountReader = new SqlCommand(countQuery, connection).ExecuteReader())
                        {
                            // Query count
                            while (entityCountReader.Read())
                            {
                                // Capture
                                totalEntityCount = int.Parse(entityCountReader.GetValue(0).ToString());
                            }

                            // Close
                            entityCountReader.Close();
                        }

                        // Write the objects into the houseing
                        string jsonLookupQuery = "SELECT * FROM " + databaseName + ".dbo." + tableName;
                        using (SqlDataReader tableReader = new SqlCommand(jsonLookupQuery, connection).ExecuteReader())
                        {                                                
                            // Defaulting
                            List<string> fieldValueListing = new List<string>();

                            // Read continue
                            while (tableReader.Read())
                            {                                   
                                // Increment
                                currentEntityPosition++;

                                // Defaulting
                                string identity = null;

                                // Gather data
                                for (int i = 0; i < tableReader.FieldCount; i++)
                                {
                                    // Set
                                    if (tableReader.GetName(i).ToUpper() == "ID")
                                    {
                                        identity = tableReader.GetValue(0).ToString();
                                    }
                                    else
                                    {
                                        // Build column data entry
                                        string thisColumn = tableReader.GetValue(i) != null ? "'" + tableReader.GetValue(i).ToString().Trim() + "'" : string.Empty;

                                        // Piece
                                        fieldValueListing.Add(thisColumn);
                                    }
                                }

                                // Path-centric
                                string explicitIdentity = identity ?? Guid.NewGuid().ToString().Replace("-", string.Empty).ToLower();
                                string filePath = tableDirectoryPath + @"\" + "Obj." + explicitIdentity + ".json";
                                string reStringed = JsonConvert.SerializeObject(fieldValueListing, Newtonsoft.Json.Formatting.Indented);
                                string percentageMark = ((double)tablePosition / (double)tableNames.Count * 100).ToString("#00.0") + "%";                                                      
                                string thisMarker = Guid.NewGuid().ToString().Replace("-", string.Empty).ToLower();
                                string entityPercentMark = string.Empty;
                                if (totalEntityCount != 0 && currentEntityPosition != 0)
                                {
                                    // Percent mark
                                    entityPercentMark = ((double)currentEntityPosition / (double)totalEntityCount * 100).ToString("#00.0") + "%";
                                }

                                // Search term verify
                                if (searchTerm.Trim() != string.Empty)
                                {
                                    // Search term scenario
                                    if (reStringed.ToLower().Trim().Contains(searchTerm.ToLower().Trim()))
                                    {
                                        // Lazy build
                                        if (!Directory.Exists(tableDirectoryPath))
                                        {
                                            // Build
                                            Directory.CreateDirectory(tableDirectoryPath);
                                        }

                                        // Has the term
                                        string idMolding = identity == null || identity == string.Empty ? "No Identity" : identity;
                                        File.WriteAllText(filePath, reStringed);
                                        ColorConsole.Print(percentageMark + " => " + databaseName + "." + tableName + "." + idMolding + "." + thisMarker + " (" + entityPercentMark + ")", ConsoleColor.Green, ConsoleColor.Black, true);
                                    }
                                    else
                                    {
                                        // Show progress                                            
                                        string idMolding = identity == null || identity == string.Empty ? "No Identity" : identity;
                                        ColorConsole.Print(percentageMark + " => " + databaseName + "." + tableName + "." + idMolding + "." + thisMarker + " (" + entityPercentMark + ")", ConsoleColor.Yellow, ConsoleColor.Black, true);
                                    }
                                }
                            }

                            // Close
                            tableReader.Close();
                        }

                        // Increment
                        tablePosition++;
                    }                        
                }
                catch (Exception err)
                {
                    ColorConsole.Print("DB.Tables!: " + err.Message, ConsoleColor.Red, ConsoleColor.White, false);                        
                }
            }
        }
        catch (Exception err)
        {
            ColorConsole.Print("KABOOM!: " + err.ToString(), ConsoleColor.Red, ConsoleColor.White, false);                
        }
        finally
        {
            try { connection.Close(); }
            catch { }
        }


        // Await
        ColorConsole.Print("Done.");
        Console.ReadLine();
    }
    #endregion

    #region Cores
    public static string GenerateHash(string inputString)
    {
        // Defaulting
        string calculatedChecksum = null;

        // Calculate
        SHA256Managed checksumBuilder = new SHA256Managed();
        string hashString = string.Empty;
        byte[] hashBytes = checksumBuilder.ComputeHash(Encoding.ASCII.GetBytes(inputString));
        foreach (byte theByte in hashBytes)
        {
            hashString += theByte.ToString("x2");
        }
        calculatedChecksum = hashString;

        // Return
        return calculatedChecksum;
    }
    #endregion


    #region Colors
    public class ColorConsole
    {
        #region Defaulting
        public static ConsoleColor DefaultBackground = ConsoleColor.DarkBlue;
        public static ConsoleColor DefaultForeground = ConsoleColor.Yellow;
        public static string DefaultBackPorch = "                                                                              ";
        #endregion

        #region Printer Cores
        public static void Print(string phrase)
        {
            // Use primary
            Print(phrase, DefaultForeground, DefaultBackground, false);
        }
        public static void Print(string phrase, ConsoleColor customForecolor)
        {
            // Use primary
            Print(phrase, customForecolor, DefaultBackground, false);
        }
        public static void Print(string phrase, ConsoleColor customBackcolor, bool inPlace)
        {
            // Use primary
            Print(phrase, DefaultForeground, customBackcolor, inPlace);
        }
        public static void Print(string phrase, ConsoleColor customForecolor, ConsoleColor customBackcolor)
        {
            // Use primary
            Print(phrase, customForecolor, customBackcolor, false);
        }
        public static void Print(string phrase, ConsoleColor customForecolor, ConsoleColor customBackcolor, bool inPlace)
        {
            // Capture settings
            ConsoleColor captureForeground = Console.ForegroundColor;
            ConsoleColor captureBackground = Console.BackgroundColor;

            // Change colors
            Console.ForegroundColor = customForecolor;
            Console.BackgroundColor = customBackcolor;

            // Write
            if (inPlace)
            {
                // From beginning of this line + padding
                Console.Write("\r" + phrase + DefaultBackPorch);
            }
            else
            {
                // Normal write
                Console.Write(phrase);
            }

            // Revert
            Console.ForegroundColor = captureForeground;
            Console.BackgroundColor = captureBackground;
        }
        #endregion
    }
    #endregion
}
}

Upvotes: 0

Drunken Code Monkey

Reputation: 1836

Late answer, but since I just had to come up with something for myself, here goes. I wrote the following to search all columns of all tables for a string match. This is related to a data forensics task that was given to me to find all occurences of a string match in a database weighing around 24GB. At this size, you can imagine using cursors or single threaded queries will be rather slow and searching the entire database would take ages. I wrote the following CLR stored procedure to do the work for me server side and return results in XML, while forcing parallelization. It is impressively fast. A database-wide search on the standard AdventureWorks2017 database completes in less than 2 seconds. Enjoy!

Example usages:

Using all available processors on the server:
EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john michael'
Limiting the server to 4 concurrent threads:
EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john michael', @maxDegreeOfParallelism = 4
Using logical operators in search terms:
EXEC [dbo].[SearchAllTables] @valueSearchTerm = '(john or michael) and not jack', @tablesSearchTerm = 'not contact'
Limiting search to table names and/or column names containing some search terms:
EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john michael', @tablesSearchTerm = 'person contact', @columnsSearchTerm = 'address name'
Limiting search results to the first row of each table where the terms are found:
EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john michael', @getOnlyFirstRowPerTable = 1
Limiting the search to the schema only automatically returns only the first row for each table:
EXEC [dbo].[SearchAllTables] @tablesSearchTerm = 'person contact'
Only return the search queries:
EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john michael', @tablesSearchTerm = 'person contact', @onlyOutputQueries = 1
Capturing results into temporary table and sorting:
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

Upvotes: 0

Singleton

Reputation: 3679

Possible but from my point of view, it is not recommended. Consider having 1000K of records of 100 of tables. Slow performance you can do that by Linq to SQL by making a Sp at database level and calling through entities. It will be much faster then the one you trying to achieve =)

Upvotes: 0

PeteL

Reputation: 336

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 :-)

Upvotes: 9

MrFox

Reputation: 5126

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.

Upvotes: 3

Daniel Brückner

Reputation: 59705

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.

Upvotes: 2

Panagiotis Kanavos

Reputation: 131712

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.

Upvotes: 7

Using LINQ to SQL to search entire database

Answers (7)

Related Questions