Search for a string across multiple tables

Search for a string across multiple tables

Postby 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
emp51302
 
Posts: 46
Joined: Fri 19 Aug 2011 20:57

Postby 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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for MySQL