none
how to write linq methods when there are conditions? RRS feed

  • Question

  • Hi,

    I tried to translate a dynamic sql into linq and felt to difficult to handle the conditions.

    string cmd = "select CategoryId, name, price, location, StockOnHand, isActive ";

    cmd = cmd + "from product ";

    cmd = cmd + "where isActive = 1"

    if (categoryId > 0)

    cmd = cmd + " and categoryId =" + categoryId.tostring();

    if (price != 0)

    cmd = cmd + " and price <= " + price.tostring(C);

    if (locationId > 0)

    cmd = cmd + " and locationId = " + locationId.tostring();

    if (StockOnHand> 0)

    cmd = cmd + " and StockOnHand > " + StockOnHand.tostring();

    ....

    One way to do this in linq could be

    ProductEntities db = new ProductEntities();

    List<product> products = db.Products.where(p=> p.isActive);

    if (categoryId > 0)

    products = products.where(p=> p.categoryId > categoryId);

    if (Price > 0)

    products = products.where(p=>p.price <= price);

    ...

    the question is how many times the sql executed? For exaple, when products = products.where(p=> p.categoryId > categoryId); the List<product> products = db.Products.where(p=> p.isActive); has been executed? If yes, then this is bad way, then what is the right way to do it?

    Thanks.

    Monday, September 3, 2012 9:55 AM

Answers

  • Hi peter 9;

    To your question, "the question is how many times the sql executed?", It has executed zero times. The statements you have shown only are building a query. Linq query only execute when you enumerate over the query. That can be done using a foreach loop or applying the ToList(), Count(), ToArray() and some other methods which will then execute. This in Linq terns is call Deferred Execution.

    To your question, "then what is the right way to do it?", You are doing it the right way. As Stated in the above question Linq queries do not execute until you enumerate the query.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by peter 9 Monday, September 3, 2012 11:00 PM
    Monday, September 3, 2012 3:01 PM