Search for a string across multiple tables

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
emp51302
Posts: 46
Joined: Fri 19 Aug 2011 20:57

Search for a string across multiple tables

Post by emp51302 » Wed 09 Nov 2011 17:24

I need to search for a string across multiple tables and display the results. Is it possible to do it with LinqConnect/dotConnect?

I short;

I have a search field where I type in my search word and it searches across multiple tables and displays the results in a grid etc. Also I need to know for each result, from which table it came from.

Is this achievable using LINQConnect for MySQL? or any other way?



Thanks

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 11 Nov 2011 18:01

You should be able to do this via LinqConnect and the LINQ Dynamic Query library. For example, you can detect all tables that have a column with a specific name, and then query these tables:

Code: Select all

// Enumerate through the table metadata.
foreach (MetaTable metaTable in myContext.Mapping.GetTables()) {

  // Check that the table has the specific column.  
  if (metaTable.RowType.DataMembers
    .Any(member => member.MappedName == "[key column name here]")) {

    // Get the list of the table's columns.
    var table = myContext.GetTable(metaTable.RowType.Type);
    string columns = "";
    bool first = true;
    foreach (var member in metaTable.RowType.DataMembers
      .Where(dataMember => !dataMember.IsAssociation)) {

      if (!first)
        columns += ", ";
      else
        first = false;
      columns += member.MappedName;
      columns += " AS " + member.Name;
    }

    // Execute the query.
    var query = table.Where("[key column] = [keyword]")
      .Select("new(" + columns + ")");
    foreach (object obj in query)
      [process the results]
      // Note that [obj] is a dynamically created class,
      // not the entity class defined in your model.
  }
}
Please tell us if this helps.

Post Reply