none
Creating dynamic record comparison query RRS feed

  • Question

  • I created dynamic extension methods for <= and < SQL comparison operators:

     

    Code Snippet

    public static IQueryable LessThanOrEqual(this IQueryable source,
    string property, object value);
    public static IQueryable LessThan(this IQueryable source, string
    property, object value);

     

     

    For example

     

    Code Snippet

    var q = db.Customers.LessThanOrEqual( "City", "London" );
    var res = q.Count();

     

     

     

    generates SQL

     

    Code Snippet

    SELECT COUNT(*)
    FROM Customers
    WHERE City <= 'London';

     

     

     

    I need to generate similar SQL statement for 2 property tuple comparison
    (c1,c2)<=(v1,v2):

     

     

     WHERE ... AND c1<=v1 AND ( c1

     

    like  (City,Id) <= ('London', 'CUST23' ):

     

     WHERE ... AND City<='London' AND ( City<'London' OR Id<='CUST23' );

     

    I tried

     

    Code Snippet

    var q1 = q.LessThanOrEqual( "City", "London" );
    var q2 = db.Customers.LessThan( "City", "London" );
    var q3 = db.Customers.LessThanOrEqual( "Id", "CUST23" );
    var qResult = q1.Where( q2.Or(q3) );

     

     

    but last line causes error.

    How to fix ?


     

    Should I use predicate builder or is it possible to combine IQueryable
    extension methods using OR ?

    How to create general method which compares up to 6-property tuples  (c1,c2,
    ... c6 )<=(v1, v2,  ... v6) where property names and values are passed as
    arrays ?


     

    Andrus.

    Wednesday, July 2, 2008 9:08 AM