使用LINQ to SQL和动态表

I am trying to migrate an old project to using LINQ but I have run into a pretty major problem. The problem is we have dynamic tables for search indexing (CM-system with dynamic attributes). The search index has columns for each searchable attribute { attribute_x, attribute_y, ... }. Now the problem is I cannot statically define which columns are available (or even which table to use as we divide search indexes), so I need a way to do this on the fly.

我正在尝试将旧项目迁移到使用LINQ,但我遇到了一个非常重要的问题。问题是我们有用于搜索索引的动态表(具有动态属性的CM系统)。搜索索引具有每个可搜索属性{attribute_x,attribute_y,...}的列。现在问题是我无法静态定义哪些列可用(或者甚至在我们划分搜索索引时使用哪个表),所以我需要一种方法来实现这一点。

I have tried using Dynamic Expressions, but they still require a type to build the expression from, and I haven't been able to generate a valid type by reflection. (It seems no MemberInfo is generated).

我尝试过使用动态表达式,但它们仍然需要一个类型来构建表达式,并且我无法通过反射生成有效类型。 (似乎没有生成MemberInfo)。

I could also satisfy with just being able to generate an expression for searching ( but I guess this is no easier task ). Something along the lines of

我也可以满足于只能生成一个表达式进行搜索(但我想这不是一件容易的事)。有点像

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.

是可取的。

Has anyone come across a solution to this problem? I have been looking through blog posts and forums for the past two days in vain.

有没有人遇到过这个问题的解决方案?过去两天我一直在浏览博客文章和论坛,徒劳无功。

3 个解决方案

#1

I don't believe that there is a solution to this using LINQ. At least not that I have ever heard or found. LINQ breaks if the table structure changes and you have to recreate the DataModel every time.

我不相信使用LINQ可以解决这个问题。至少不是我听过或发现过的。如果表结构发生更改,LINQ会中断,并且每次都必须重新创建DataModel。

If I am incorrect I am anxious to hear about it. :)

如果我不正确,我很想知道。 :)

#2

You may want to try Linq to Dataset:

您可能想尝试Linq to Dataset:

LINQ to DataSet makes it easier and faster to query over data cached in a DataSet object. Specifically, LINQ to DataSet simplifies querying by enabling developers to write queries from the programming language itself, instead of by using a separate query language.

LINQ to DataSet使查询DataSet对象中缓存的数据变得更加容易和快捷。具体来说,LINQ to DataSet使开发人员能够编写来自编程语言本身的查询,而不是使用单独的查询语言,从而简化了查询。

#3

You're working outside of Linq to SQL's design, but I think it could be done if you really need to. Your best bet to explore is probably the XmlMappingSource that you can use in your context's constructor. I had some success with this when it came to handling table renaming on the fly.

你在Linq之外的SQL设计工作,但我认为如果你真的需要它可以做到。探索的最佳选择可能是您可以在上下文的构造函数中使用的XmlMappingSource。在处理动态表重命名时,我取得了一些成功。

In mapping XML, the actual column name on the database is the "Name" attribute of the column element whereas the class property is the "Member". If you change that in the XML it should map the eventual query appropriately.

在映射XML中,数据库上的实际列名是列元素的“Name”属性,而class属性是“Member”。如果在XML中更改它,它应该适当地映射最终查询。

#1