none
LINQ Where Clause for UI RRS feed

  • Question

  • I have a UI that has some date fields used to specify date ranges for results as well as a few text boxes to filter results like the contents entered in the boxes. Obviously, these fields aren't always filled in, so my where clause can have various filtering needs depending on what the user enters. Does anyone have any examples of handling this in LINQ? I use dynamic sql in other older apps but this app is already using LINQ with SQL so I was wondering if that could handle this or not.

    Thanks,
    Adam
    Tuesday, November 17, 2009 10:11 PM

Answers

  • DateTime? someDate = null;
    DateTime? someOtherDate = new DateTime(2009, 11, 18);
    string someTextField = null;
    string someOtherTextField = "abc";

    var query =
      from t in dc.SomeTable
      where (someDate == null || t.SomeDate == someDate)
         && (someOtherDate == null || t.SomeOtherDate == someOtherDate)
         && (someTextField == null || t.SomeTextField == someTextField)
         && (someOtherTextField == null || t.SomeOtherTextField == someOtherTextField)
      select t;

    When running the queries, L2S will eliminate anything that can be eliminated client-side from the query before generating SQL. As a result, the sql query generated for the sample above will only contain the second and fourth criteria. The first and the third are eliminated client-side and omitted from the TSQL query.

    ...this is one of the [many] beautiful things with L2S... :)


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    • Proposed as answer by Syed Mehroz Alam Thursday, November 19, 2009 5:52 AM
    • Marked as answer by Yichun_Feng Tuesday, November 24, 2009 5:44 AM
    Wednesday, November 18, 2009 3:25 AM
    Answerer

  • Adam , Linq supports to write Dynamic query too.


    May be this link provide some basic understanding on dynamic Linq Query

    http://aspalliance.com/1569_Dynamic_LINQ_Part_1_Using_the_LINQ_Dynamic_Query_Library.1


    Hope this helps

    Jbasingh

    • Marked as answer by Yichun_Feng Tuesday, November 24, 2009 5:45 AM
    Wednesday, November 18, 2009 3:34 AM

All replies

  • DateTime? someDate = null;
    DateTime? someOtherDate = new DateTime(2009, 11, 18);
    string someTextField = null;
    string someOtherTextField = "abc";

    var query =
      from t in dc.SomeTable
      where (someDate == null || t.SomeDate == someDate)
         && (someOtherDate == null || t.SomeOtherDate == someOtherDate)
         && (someTextField == null || t.SomeTextField == someTextField)
         && (someOtherTextField == null || t.SomeOtherTextField == someOtherTextField)
      select t;

    When running the queries, L2S will eliminate anything that can be eliminated client-side from the query before generating SQL. As a result, the sql query generated for the sample above will only contain the second and fourth criteria. The first and the third are eliminated client-side and omitted from the TSQL query.

    ...this is one of the [many] beautiful things with L2S... :)


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    • Proposed as answer by Syed Mehroz Alam Thursday, November 19, 2009 5:52 AM
    • Marked as answer by Yichun_Feng Tuesday, November 24, 2009 5:44 AM
    Wednesday, November 18, 2009 3:25 AM
    Answerer

  • Adam , Linq supports to write Dynamic query too.


    May be this link provide some basic understanding on dynamic Linq Query

    http://aspalliance.com/1569_Dynamic_LINQ_Part_1_Using_the_LINQ_Dynamic_Query_Library.1


    Hope this helps

    Jbasingh

    • Marked as answer by Yichun_Feng Tuesday, November 24, 2009 5:45 AM
    Wednesday, November 18, 2009 3:34 AM