LINQ选择动态列和值 [英] LINQ Select Dynamic Columns and Values
问题描述
由于各种原因，我需要能够允许用户根据他们对列和值的选择从数据库中选择一项.例如，如果我有一张桌子:
For various reasons I need to be able to allow the user to select an item from a database based on their choice of columns and values. For instance, if I have a table:
Name | Specialty | Rank
-------+-----------------+-----
John | Basket Weaving | 12
Sally | Basket Weaving | 6
Smith | Fencing | 12
用户可以请求1列，2列或更多列，并且他们请求的列可能不同.例如，用户可以请求以下项:Specialty == Basket Weaving和Rank == 12. What I do currently is gather the user's request and create a list of KeyValuePair where the Key is the column name and the Value`是该列的期望值:
The user may request a 1, 2, or more columns and the columns that they request may be different. For example, the user may request entries where Specialty == Basket Weaving and Rank == 12. What I do currently is gather the user's request and create a list ofKeyValuePairwhere theKeyis the column name and theValue` is the desired value of the column:
class UserSearch
{
private List<KeyValuePair<string, string> criteria = new List<KeyValuePair<string, string>>();
public void AddTerm(string column, string value)
{
criteria.Add(new KeyValuePair<string, string>(column, value);
}
public void Search()
{
using (var db = new MyDbContext())
{
// Search for entries where the column's (key's) value matches
// the KVP's value.
var query = db.MyTable.Where(???);
}
}
}
/* ... Somewhere else in code, user adds terms to their search
* effectively performing the following ... */
UserSearch search = new UserSearch();
search.Add("Specialty", "Basket Weaving");
search.Add("Rank", "12");
使用此KeyValuePair列表，我如何最简洁地选择符合所有条件的数据库项目?
Using this list of KeyValuePair's, how can I most succinctly select database items which match all the criteria?
using (var db = new MyDbContext)
{
// Where each column name (key) in criteria matches
// the corresponding value in criteria.
var query = db.MyTable.Where(???);
}
如果可以的话，我想使用EntityFramework而不是原始SQL.
I would like to use EntityFramework instead of raw SQL if I can help it.
更新3 :我越来越近了.下载后，我发现了使用LINQ的方法 表中的所有值.这显然不是超级理想，因为它下载了 桌子上的所有东西.所以我想最后一步是找出一种方法 我不必每次都下载整个表.这是我在做什么的解释:
UPDATE 3: I am getting closer. I have discovered a way to use LINQ once I've downloaded all the values from the table. This is obviously not super ideal because it downloads everything in the table. So I guess the last step would be to figure out a way where I don't have to download the whole table every time. Here is an explanation of what I am doing:
对于表中的每一行
db.MyTable.ToList().Where(e => ...
我列出了一个布尔值列表，表示该列是否符合条件.
I make a list of bools representing if the column matches the criteria.
criteria.Select(c => e.GetType()?.GetProperty(c.Key)?.GetValue(e)?.ToString() == c.Value)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Basically just gets the value of specific column
by string
然后我检查一下此布尔列表是否全部正确
Then I check to see if this bool list is all true
.All(c => c == true)
完整代码示例如下:
// This class was generated from the ADO.NET Entity Data Model template
// from the database. I have stripped the excess stuff from it leaving
// only the properties.
public class MyTableEntry
{
public string Name { get; }
public string Specialty { get; }
public string Rank { get; }
}
class UserSearch
{
private List<KeyValuePair<string, string> criteria = new List<KeyValuePair<string, string>>();
public void AddTerm(string column, string value)
{
criteria.Add(new KeyValuePair<string, string>(column, value);
}
public async Task<List<MyTableEntry>> Search()
{
using (var db = new MyDbContext())
{
var entries = await db.MyTable.ToListAsync();
var matches = entries.Where(e => criteria.Select(c => e.GetType()
?.GetProperty(c.Key)
?.GetValue(e)
?.ToString() == c.Value)
.All(c => c == true));
return matches.ToList();
}
}
}
似乎我的问题出在这段代码上:
It seems as if my problem lies with this segment of code:
e.GetType()?.GetProperty(c.Key)?.GetValue(e)?.ToString()
我对表达式树不熟悉，所以答案可能就在其中.我也可以尝试使用动态LINQ.
I am unfamiliar with Expression trees so perhaps the answer lies in them. I may also try Dynamic LINQ.
推荐答案
由于您的列和过滤器是动态的，因此动态LINQ 库可能会在这里为您提供帮助
Since your columns and filters are dynamic, Dynamic LINQ library may help you here
NuGet: https://www.nuget.org/packages/System. Linq.Dynamic/
文档: http://dynamiclinq.azurewebsites.net/
using System.Linq.Dynamic; //Import the Dynamic LINQ library
//The standard way, which requires compile-time knowledge
//of the data model
var result = myQuery
.Where(x => x.Field1 == "SomeValue")
.Select(x => new { x.Field1, x.Field2 });
//The Dynamic LINQ way, which lets you do the same thing
//without knowing the data model before hand
var result = myQuery
.Where("Field1=\"SomeValue\"")
.Select("new (Field1, Field2)");
另一种解决方案是使用 Eval Expression.NET 在运行时动态C#代码.
Another solution is to use Eval Expression.NET which lets you evaluate dynamically c# code at runtime.
using (var ctx = new TestContext())
{
var query = ctx.Entity_Basics;
var list = Eval.Execute(@"
q.Where(x => x.ColumnInt < 10)
.Select(x => new { x.ID, x.ColumnInt })
.ToList();", new { q = query });
}
免责声明:我是该项目的所有者 Eval Expression.NET
Disclaimer: I'm the owner of the project Eval Expression.NET
修改:回答评论
请注意，参数值类型必须与属性类型兼容.例如，如果"Rank"属性是INT，则仅与INT兼容的类型起作用(而不是字符串).
Be careful, the parameter value type must be compatible with the property type. By example, if the "Rank" property is an INT, only type compatible with INT will work (not string).
很显然，您将需要重构此方法以使其更适合您的应用程序.但是正如您所看到的，您甚至可以轻松地使用Entity Framework中的异步方法.
Obviously, you will need to refactor this method to make it more suitable for your application. But as you can see, you can easily use even async method from Entity Framework.
如果还自定义选择(返回类型)，则可能需要使用反射获取异步结果，或者将ExecuteAsync替换为ToList().
If you customize the select also (the return type) you may need to either get the async result using reflection or use ExecuteAsync instead with ToList().
public async Task<List<Entity_Basic>> DynamicWhereAsync(CancellationToken cancellationToken = default(CancellationToken))
{
// Register async extension method from entity framework (this should be done in the global.asax or STAThread method
// Only Enumerable && Queryable extension methods exists by default
EvalManager.DefaultContext.RegisterExtensionMethod(typeof(QueryableExtensions));
// GET your criteria
var tuples = new List<Tuple<string, object>>();
tuples.Add(new Tuple<string, object>("Specialty", "Basket Weaving"));
tuples.Add(new Tuple<string, object>("Rank", "12"));
// BUILD your where clause
var where = string.Join(" && ", tuples.Select(tuple => string.Concat("x.", tuple.Item1, " > p", tuple.Item1)));
// BUILD your parameters
var parameters = new Dictionary<string, object>();
tuples.ForEach(x => parameters.Add("p" + x.Item1, x.Item2));
using (var ctx = new TestContext())
{
var query = ctx.Entity_Basics;
// ADD the current query && cancellationToken as parameter
parameters.Add("q", query);
parameters.Add("token", cancellationToken);
// GET the task
var task = (Task<List<Entity_Basic>>)Eval.Execute("q.Where(x => " + where + ").ToListAsync(token)", parameters);
// AWAIT the task
var result = await task.ConfigureAwait(false);
return result;
}
}
这篇关于LINQ选择动态列和值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
