locked
batching multiple queries into on DB roundtrip RRS feed

  • Question

  •   I like how LINQ to SQL will batch update operations into a single DB roundtrip when I call submitChanges().  I wonder if there is a way to achieve the same result with multiple unrelated queries.  Say I have one query which hits table A, then another unrelated query that hits table B.  Is there a way to perform the two requests in a single DB round-trip?  This can be achieved with the traditional SqlCommand.
      Bear with me if this sounds silly.  It is a bit.

      The below code is a hacky example of what I'm trying to achieve.  urls1 and urls2 are two unrelated queries.  combinedquery then is a query that contains both of those query.  Although combinedquery returns a P, I am not actually interested in that row.  I just picked up a single row to join the other queries with.
      This code doesn't achieve the desired affect however.  When I trace operations to the DB, I see that there are two separate calls to the db being made

        SomeDataContext db = new SomeDataContext()
        IEnumerable<string> urls1 = (
                     from P in db.Products1
                     where P.Source == 0
                     where P.Url.Length > 0
                     select P.Url
                 );

        IEnumerable<string> urls2 = (
                     from P in db.Products2
                     where P.Source == 4
                     where P.Url.Length > 0
                     select P.Url
                 );

        var combinedquery =
            from P in db.Products where P.Id == 1
            join R1 in urls1 on 1 equals 1 into R1C
            join R2 in urls2 on 1 equals 1 into R2C
            select new { R1C = R1C, R2C = R2C };
    Tuesday, September 30, 2008 10:15 PM

Answers

  • > IEnumerable<string> urls1 =

     

    Try IQueryable<string> instead of IEnumerable<string>

     

     

    Also try:

     

    var combined query =

      from R1 in urls1

      from R2 in urls2

      select new { R1, R2 };

     

     

    Edit: warning - cartesian joins found hazardous to health.

    Wednesday, October 1, 2008 2:11 PM

All replies

  • Unfortuntely, it's not generally possible to batch multiple queries into a single round-trip with LINQ to SQL.

    If you fiddle enough, you might find some way to achieve what you want through joins or subqueries, although the query might be end up being fairly inefficient and awkward. You should ask yourself, "what would the SQL translation look like"?

    Joe
    Wednesday, October 1, 2008 12:07 AM
  • > IEnumerable<string> urls1 =

     

    Try IQueryable<string> instead of IEnumerable<string>

     

     

    Also try:

     

    var combined query =

      from R1 in urls1

      from R2 in urls2

      select new { R1, R2 };

     

     

    Edit: warning - cartesian joins found hazardous to health.

    Wednesday, October 1, 2008 2:11 PM