尝试使用带有条件的实体/Linq 搜索数据库

Question

所以我在我正在构建的应用程序上有这个动作。它需要根据某些条件搜索数据库，并返回满足条件的项目列表

我的数据库上下文如下所示

public DbSet<MarketCategory> MarketCategories { get; set; } //has .name
public DbSet<InventoryAsset> InventoryAssets { get; set; } //has .name
public DbSet<ItemCategory> ItemCategories { get; set; } // has .name

每个库存资产都有一个相关的列类别和市场。因此，每个项目都属于一个类别，每个项目也属于一个市场。

在我的服务中，我有上下文

private readonly InventoryContext _context;

我正在尝试编写此方法，但我对实体和linq有点天真

public IEnumerable<InventoryAsset> searchInventoryAssets(string query, string category, string market, string column)
{
return _context.//items that are part of the passed category, are also part of the passed market and where the passed column selected contains the passed query (such as InventoryAsset.ItemTitle == "red book")
}

物料型号：

public string Title { get; set; }
public string Description { get; set; } // + more things like upc/brand/price etc 
public virtual ItemCategory Category { get; set; }
public virtual MarketCategory Market { get; set; }

项目类别和市场类别相似

public string Name {get; set;}
public virtual IEnumerable<InventoryAsset> CategoryAssets { get; set; }

如果没有一行代码可以写，我会这样

var catItems = functionToReturnCatItems(category);
catItems.FirstOrDefault(a => a.Market.MarketPlaceName == market);

then do a if switch statement for each column in the db 
if ( column == "Title"){ // search query for title...
} 

Idk，如果这是一个很好的解决方案

Answer 1

我不确定为什么您有一个特定于类型的上下文InventoryContext.但假设您的上下文如下所示：

public class Context : DbContext {
public DbSet<MarketCategory> MarketCategories { get; set; } 
public DbSet<InventoryAsset> InventoryAssets { get; set; } 
public DbSet<ItemCategory> ItemCategories { get; set; } 
}

_context是Context类的实例，则应该可以执行以下操作：

public IEnumerable<InventoryAsset> searchInventoryAssets(string query, string category, string market, string column)
{
var assets = from inventoryAsset in _context.InventoryAssets 
join marketCategory in _context.MarketCategories on inventoryAsset.MarketCategory_Identity equals marketCategory.Identity
join itemCategory in _context.ItemCategories on inventoryAsset.ItemCategory_Identity equals itemCategory.Identity
select inventoryAsset;
switch (column)
{
case "Title":
assets = from inventoryAsset in assets where inventoryAsset.Title equals query select inventoryAsset;
break;
default:
// throw or whatever suits
break;
}
return  assets.ToList();
}

或者，您可以使用Expression进行更通用的方法(我还没有测试过(：

public static Expression<Func<General, bool>> CreatePredicate(string columnName, object searchValue)
{
var xType = typeof(General);
var x = Expression.Parameter(xType, "x");
var column = xType.GetProperties().FirstOrDefault(p => p.Name == columnName);
var body = column == null
? (Expression) Expression.Constant(true)
: Expression.Equal(
Expression.PropertyOrField(x, columnName),
Expression.Constant(searchValue));
return Expression.Lambda<Func<General, bool>>(body, x);
}

public IEnumerable<InventoryAsset> searchInventoryAssets(string query, string category, string market, string column)
{
return  (from inventoryAsset in _context.InventoryAssets 
join marketCategory in _context.MarketCategories on inventoryAsset.MarketCategory_Identity equals marketCategory.Identity
join itemCategory in _context.ItemCategories on inventoryAsset.ItemCategory_Identity equals itemCategory.Identity
where CreatePredicate(column, query)).ToList()
}

CreatePredicate方法的来源：LINQ 其中条件与动态列