none
Linking Linq Queries RRS feed

  • Question

  •  

    I'm tasked with replacing an existing search operation which joins search operations in a SearchOperationsList. The existing functionality builds a giant SQL statement which becomes unmanageable as the number of joined search operations increases.

     

    My question is, can we somehow join linq queries in a pre-execution state? For example:

    Code Snippet

    var blueCars = from c in db.Cars

    where c.color == "Blue"

    select c;

    var redCars = from c in db.Cars

    where c.color == "Red"

    select c;

     

     

    Is there a way to join the two statements to be executed in a single AND request? This will be done in a foreach loop as follows:

    Code Snippet

    foreach (CarSearchOperation operation in operations)

    {

    //Join above queries to yield a result pre execution

    }

     

     

    Any help is greatly appreciated.

     

    Tuesday, August 26, 2008 1:23 PM

Answers

  • If you look at the type of "blueCars" and "redCars" in your example you'll see they are both IQueryable - this is a query definition attached to a datasource - check out Deferred Execution in relation to LINQ

    for more info.

     

    What this means is that you can join queries together: see my answer to this post: http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3791779&SiteID=1 

     

    However if you take the example code you've just posted, the result would be an empty set, as it equates to:

    Code Snippet
    from c in db.Cars where c.color == "Blue" && c.colour == "Red" select c;

     

     

    Since Red <> Blue you will always get an empty set. If I assume that the CarSearchOperation takes a property and a value and you AND together different operations, then you could do this:

     

    Code Snippet

    // assume CarSearchOp has Type and Value properties

    var operations = new List<CarSearchOperation>();

    operations.Add(new CarSearchOperation() with { .Type ="color", .Value ="red"});

    operations.Add(new CarSearchOperation() with { .Type ="make", .Value= "Ford"));

     

    // base query: select all cars: query is IQueryable<Car>

    var query = from c in db.Cars select c;

     

    foreach (CarSearchOrperation operation in operations)

    {

    switch (operation.Type)

    {

    case "color":

    query = from c in query where c.color == operation.value select c;

    break;

     

    case "make":

    query = from c in query where c.make == operation.value select c;

    break;

    }

    }

     

     

    Have not put that code into C# so there may be a few syntax errors in my typing, but that's the essence. If you want to avoid the switch statement and use property names you need to look at dynamic expression generation.

    Tuesday, August 26, 2008 1:50 PM
    Answerer

All replies

  • If you look at the type of "blueCars" and "redCars" in your example you'll see they are both IQueryable - this is a query definition attached to a datasource - check out Deferred Execution in relation to LINQ

    for more info.

     

    What this means is that you can join queries together: see my answer to this post: http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3791779&SiteID=1 

     

    However if you take the example code you've just posted, the result would be an empty set, as it equates to:

    Code Snippet
    from c in db.Cars where c.color == "Blue" && c.colour == "Red" select c;

     

     

    Since Red <> Blue you will always get an empty set. If I assume that the CarSearchOperation takes a property and a value and you AND together different operations, then you could do this:

     

    Code Snippet

    // assume CarSearchOp has Type and Value properties

    var operations = new List<CarSearchOperation>();

    operations.Add(new CarSearchOperation() with { .Type ="color", .Value ="red"});

    operations.Add(new CarSearchOperation() with { .Type ="make", .Value= "Ford"));

     

    // base query: select all cars: query is IQueryable<Car>

    var query = from c in db.Cars select c;

     

    foreach (CarSearchOrperation operation in operations)

    {

    switch (operation.Type)

    {

    case "color":

    query = from c in query where c.color == operation.value select c;

    break;

     

    case "make":

    query = from c in query where c.make == operation.value select c;

    break;

    }

    }

     

     

    Have not put that code into C# so there may be a few syntax errors in my typing, but that's the essence. If you want to avoid the switch statement and use property names you need to look at dynamic expression generation.

    Tuesday, August 26, 2008 1:50 PM
    Answerer
  • Thanks for the quick reply.

     

    Also on this topic, we found you needed to do the union or intersect on these IQueryable objects using the same DataContext.

    Tuesday, August 26, 2008 1:58 PM