Page 1 of 1
Search for a string across multiple tables
Posted: Wed 09 Nov 2011 17:24
by emp51302
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
Posted: Fri 11 Nov 2011 18:01
by StanislavK
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.