MVC Efficient dynamic EF database calls

Question

I'm working on an MVC app. A lot of the views are search engine like, allowing the user to select parameters to get the data he wants.

I am looking for an efficient way to make dynamic calls to the database so that I can retrieve only the wanted data instead of taking a bunch of data and sorting them out.

So far I have been using Dynamic Linq, but I have had huge troubles working with this and I was wondering if there was anything better and less troublesome. The only factor is that I know the fields of the table I'm looking on, and I need to be able to use operators like >, < or =.

EDIT

Here's a table example based on my app:

TABLE CARD
(
    CARD_IDE INT NOT NULL IDENTITY,
    CARD_NAME VARCHAR(50) NOT NULL,
    CARD_NUMBER NUMERIC(4) NOT NULL,
    CARD_COLOR VARCHAR(10),
    CARD_MANA_COST VARCHAR(30),
    CARD_MANA_CONVT VARCHAR(3),
    CARD_TYPE VARCHAR(50),
    CARD_POWER VARCHAR(2),
    CARD_TOUGH VARCHAR(2),
    CARD_RARTY VARCHAR(1) NOT NULL,
    CARD_TEXT_ABILT VARCHAR(800),
    CARD_TEXT_FLAVR VARCHAR(800),
    CARD_ARTST_NAME VARCHAR(100),
    CARD_SET_IDE INT NOT NULL,
    CARD_FLAG_FACE INT NOT NULL DEFAULT 0,
    CARD_CHILD_IDE INT,
    CARD_MASTER_IDE INT,
    CARD_UNIT_COST NUMERIC(5,2) NOT NULL DEFAULT 0
)

And a few examples:

1. A user look for any item which type is "Creature" (String), number is 3 and card set IDE is 6;
2. Any cards which contains the word Rat;
3. All the cards of color Blue and White which unit cost is higher than 3.00
4. Any cards which power is less than 3 but higher than 1.

EDIT 2

After much research (and thanks to Chris Pratt below), I've managed to look a bit and dive into something.

Based on this:

First I create my object context like this:

var objectContext = ((IObjectContextAdapter) mDb).ObjectContext;

Then I create the ObjectSet:

ObjectSet<CARD> priceList = objectContext.CreateObjectSet<CARD>();

Then I check if any values as been chosen by the user:

if (keyValuePair.Key == CARDNAME)
{
    queryToLoad = TextBuilder.BuildQueryStringForTextboxValue(keyValuePair.Value);

    //valuesToUse.Add("CARD_NAME.Contains(\"" + queryToLoad + "\")");

    priceList = priceList.Where(_item => _item.CARD_NAME.Contains(queryToLoad)) as ObjectSet<PRICE_LIST>;
}

Where the queryToLoad is, in fact, the value to look for. Example: if my user search for an Angel, the queryToLoad will be "Angel". I'm trying to get to the result without having to rewrite my whole code.

And then I gather the result in a List like this:

listToReturn.AddRange(priceList.ToList());

HOWEVER: I have a problem using this approach. As the priceList = priceList.Where(_item => _item.CARD_NAME.Contains(queryToLoad)) as ObjectSet<PRICE_LIST>; like is struck, the value is always null and I don't know why.

Answer 1

There is no way to optimize something that is inherently dynamic in nature. The only thing you can do in your app is feed whatever filters the end-user chooses into a Where clause and let Entity Framework fetch the result from your database in the most efficient way it deems fit.

However, there's some things you can potentially do if there's certain known constraints. At the very least, if you know which fields will be searched on, you can add proper indexes to your database so that searches on those particular fields will be optimized. You can reach a point of over-optimization, though (if you index every field, you might as well not have an index). It's usually better to monitor the queries the database handles and add indexes for the most used fields based on real-world user behavior.

You can also investigate using stored procedures for your queries. Depending on the complexity and number of filters being applied, creating a stored procedure to handle the queries could be difficult, but if you can condense the logic enough to make it possible, using a stored procedure will highly optimize the queries.

UPDATE

Here's what I mean by building a query. I'll take your first use case scenario above. You have three filters: type, number, and IDE. The user may specify any one or two, all three, or none. (We'll assume that cardType is a string and cardNumber and cardIde are nullable ints.

var cards = db.Cards;

if (!string.IsNullOrEmpty(cardType))
{
    cards = cards.Where(m => m.Type == cardType);
}

if (cardNumber.HasValue)
{
    cards = cards.Where(m => m.Number == cardNumber);
}

if (cardIde.HasValue)
{
    cards = cards.Where(m => m.IDE == cardIde);
}

return View(cards);

Entity Framework will not actually issue the query until you do something that requires the data from the query (iterate over the list, count the items, etc.). Up until that point, anything additional you do to the DbSet is just tacked on to the query EF will eventually send. You can therefore build your query by conditionally handling each potential filter one at a time before finally utilizing the end result.

UPDATE #2

Sorry, I apparently hadn't consumed enough coffee yet when I did my last update. There's obviously a type issue there. If you store db.Cards into cards it will be a DbSet<Card> type, while the result of any call to Where would be an IQueryable<Card> type, which pretty obviously can't be stored in the same variable.

So you just need to initially cast the variable to something that works for both: IEnumerable<Card>:

IEnumerable<Card> cards = db.Cards;

Then you shouldn't get any type errors.