Using LINQ to SQL with dynamic tables

Question

I am trying to migrate an old project to LINQ, but I ran into a pretty serious problem. The problem is that we have dynamic tables for indexing the search (CM system with dynamic attributes). The search index contains columns for each search attribute {attribute_x, attribute_y, ...}. Now the problem is that I cannot statically determine which columns are available (or even which table to use when dividing the search indexes), so I need a way to do this on the fly.

I tried using dynamic expressions, but they still need a type to create the expression, and I could not create the correct type by reflection. (It seems MemberInfo is not generated).

I could also satisfy just by being able to generate an expression to search for (but I think this is not an easy task). Something along the lines

 var mySearchIndex = db.GetTable (myTableType);  var query = from p in db.Products from idx in mySearchIndex;  query = query.Where ("idx." + attributeName + "> 50.0 && idx." + attributeName + " 

would be desirable.

Can anyone find a solution to this problem? I watched blogs and forums for the last two days in vain.

Answer 1

You can try Linq to Dataset :

LINQ to DataSet makes it easier and faster to query data cached in a DataSet. In particular, LINQ to DataSet simplifies the query by allowing developers to write queries from the programming language itself, rather than using a separate query language.

Answer 2

I do not think there is a solution for this with LINQ. At least not what I have ever heard or found. LINQ breaks if the table structure changes, and you must recreate the DataModel each time.

If I'm wrong, I really want to hear about it. :)

Answer 3

You work outside of the Linq to SQL design, but I think this can be done if you really need to. The best way to explore is probably the XmlMappingSource, which you can use in your context constructor. I had some success with this when it came to handling renaming tables on the fly.

In XML mapping, the actual column name in the database is the "Name" attribute of the column element, while the class property is "Member". If you change this value in XML, it must match the possible request accordingly.