none
Optional Where Clauses? RRS feed

  • Question

  • Hi,

     

    Probably a really simple issue this - but at the moment we are performing a search on a data context to be used as the source for a grid. At the moment it works fine just with the code we already have, but really slowly.

     

    The problem comes from determining whether the user has entered data to be used in the where clause or not (therefore leaving us with the only option of testing for user input with if's and else's) and writing the linq accordingly. So what we currently do is just retrieve the whole table (I suspect this high-traffic (50,000 rows) is our problem) on the first line and then simply test if the user has chosen a customer from a combo box (for example) and then apply where methods there and then. Similar to the following:

    query = GetAllRecords();

    if(!String.IsNullOrEmpty(cbxCustomer.Text))

    query = query.where(o=>o.CustomerId.Equals(cbxCustomer.Text));

     

    There's a lot of these where clauses to be applied though, and I only ever need 20 records from the final set.

     

    Any suggestions on how I can optimise this query??

     

    Thanks a LOT in advance,

     

    Dan

    Monday, September 26, 2011 12:52 PM

Answers

  • Hi,

    Were you have a sample of how Func (functions) works. Is also includes their uses with Expressions:

    http://tomasp.net/articles/dynamic-linq-queries.aspx

    But you can declare a function in this way:

    Func<Entity, bool> func = entity => entity.Name == "David";
    

    Regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.
    • Marked as answer by Nulty08 Tuesday, September 27, 2011 10:06 AM
    Monday, September 26, 2011 2:55 PM

All replies

  • Hi,

    If I understand you at all, you can join where clauses using "and". More or less like this:

    query = query.where(o=>o.CustomerId == cbxCustomer.Text and o.ProductID == 32);
    

    In order to get only 20 records you can use Take method:

    var result = query.where(o=>o.CustomerId.Equals(cbxCustomer.Text)).Take(20).ToList();
    

    Best regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.
    Monday, September 26, 2011 1:00 PM
  • Hi JA,

    Thanks a lot for your feedback. Yer I understand the basics of constructing a LINQ statement using && or || operators, it's more the fact that sometimes the where clause will not need to be applied, and in some cases - a different where clause for different data types we can parse from a text box the user has available.

    For example:

     

    query = GetAllRecords();
    
    if(Int32.TryParse(txtSearch.Text))
    
    query = query.where(o=>o.Id.Equals(txtSearch.Text));
    
    else
    
    qeury = query.where(o=>o.TextField == txtSearch.Text);

     

     


    • Edited by Nulty08 Monday, September 26, 2011 1:14 PM
    Monday, September 26, 2011 1:14 PM
  • Well,

    Then your need to build a Func<Entity, bool> with all your required conditions, and then apply this function as parameter of your Where method. You can do that calling a private method directly.

    If your Func parameter is null, then you don't need to apply your where condition.  But maybe, as a "trick" you colud initialize your function with a dummy condition (something like 1 == 1), so in this way your Where will be applied always.

    Hope this can help you.

    Best regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.
    Monday, September 26, 2011 1:51 PM
  • Hi JA,

     

    Again, many thanks for your reply. Could you point me in the right direction of an article explaining the use of Func<Entity, bool> or just the 'correct' name for it so that I can google it now? It sounds from what you've said there that it might be just what I'm after.

     

    Thanks again,

     

    Dan

    Monday, September 26, 2011 2:09 PM
  • Hi,

    Were you have a sample of how Func (functions) works. Is also includes their uses with Expressions:

    http://tomasp.net/articles/dynamic-linq-queries.aspx

    But you can declare a function in this way:

    Func<Entity, bool> func = entity => entity.Name == "David";
    

    Regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.
    • Marked as answer by Nulty08 Tuesday, September 27, 2011 10:06 AM
    Monday, September 26, 2011 2:55 PM