Trying to search db with Entity/Linq with conditions

Question

So I have this action on my app i'm building. It needs to search the database based on some criteria and return the list of items meeting the criteria

My database context looks like this

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

Each InventoryAsset has a column category and market that are related. So each item belongs to a category and each item also belongs to a market.

In my service I have the context in

  private readonly InventoryContext _context;

I am trying to write this method but i'm a little naive with entity and 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")
    }

The item model:

  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; }

Both ItemCategory and MarketCategory are similar

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

If there's not like a single line of code I can write, I was going to so something like

 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 if this is a good solution though

Please also show the class models. – Gert Arnold Aug 31 '19 at 18:53 — Gert Arnold, Aug 31 '19 at 18:53

havij · Answer 1 · 2019-08-31T21:30:13.940

I am not sure why you have a type-specific context InventoryContext. But assuming that your context looks like:

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

and _context is an instance of Context class, something like the following should work:

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();
}

Or alternatively, you could use Expression for a more generalized approach (I haven't tested it):

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()
}

source for CreatePredicate method: LINQ where condition with dynamic column

Trying to search db with Entity/Linq with conditions

1 Answers1