Building a dynamic query with LINQ

Question

I am rebuilding this website php/mysql into asp.net. I am working on building the main card search form in the header of the page. There is a text input, multiple dropdowns (that all point to different columns) and some conditional search options.

For the mysql version I was able to use conditionals to build a sting containing the query and then execute it.

 //MySql/PHP example
 $query = "SELECT * FROM cards WHERE ";

    //Encounter_set 
if (isset($_GET['Encounter_Set']){
    $query.= "Encounter_Set=:Encounter_Set AND ";
    $queryArray['Encounter_Set'] = $_GET['Encounter_Set'];
}

 //radio statements
switch ($_GET['radio']) {
    case "All":     $query.= "(Title LIKE :terms OR Traits LIKE :terms OR Shadow_Text LIKE :terms OR Text LIKE :terms)";break;
    case "Title":   $query.= "(Title LIKE :terms)";break;
    case "Traits":  $query.= "(Traits LIKE :terms)";break;
    case "Text":    $query.= "(Shadow_Text LIKE :terms OR Text LIKE :terms)"; break;
    default:        $query.= "(Title LIKE :terms OR Traits LIKE :terms OR Shadow_Text LIKE :terms OR Text LIKE :terms)";
}

 //Finally
 $result = $db_con->prepare($query);

How would I go about doing that in LINQ?

LINQ allows you to compose the query. stackoverflow.com/questions/11194/conditional-linq-queries — Darren Kopp
– Darren Kopp, Commented Dec 15, 2012 at 17:21
Won't this inital statement: var logs = from log in context.Logs select log; will get all the rows from the table? It seems inefficient to start by pulling every row from the table. — Mason240
– Mason240, Commented Dec 15, 2012 at 17:24
no, linq is lazy, it doesn't execute the query until you actually attempt to enumerate over the values — Darren Kopp
– Darren Kopp, Commented Dec 21, 2012 at 4:20

Markus Jarderot · Accepted Answer · 2012-12-15 17:33:24Z

2

using (var context = new MyDbContext())
{
    IQueryable<Card> query = context.Cards;
    if (!string.IsNullOrEmpty(Encounter_Set))
    {
        query = query.Where(c => c.Encounter_Set == Encounter_Set);
    }
    switch (radio)
    {
        default:
        case "All":
            query = query.Where(c => c.Title.Contains(terms)
                || c.Traits.Contains(terms)
                || c.Shadow_Text.Contains(terms)
                || c.Text.Contains(terms));
            break;
        case "Title":
            query = query.Where(c => c.Title.Contains(terms));
            break;
        case "Traits":
            query = query.Where(c => c.Traits.Contains(terms));
            break;
        case "Text":
            query = query.Where(c => c.Shadow_Text.Contains(terms)
                || c.Text.Contains(terms));
            break;
    }
    // ...
    return query.ToList(); // Execute the query
}

edited Dec 15, 2012 at 17:33

answered Dec 15, 2012 at 17:27

Markus Jarderot

89.7k23 gold badges141 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Building a dynamic query with LINQ

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related