"When Do Queries Execute"

Julie’s got a great post about when stored procedures execute when using LINQ to Entities.

It made me think about LINQ to SQL where if I’ve got something like;

      using (NorthwindDataContext ctx = new NorthwindDataContext())
      {
        var query = ctx.GetCustomersByCountry("UK"); // SQL Happens Here!

        foreach (Customer c in query)
        {
          Console.WriteLine(c.CustomerID );
        }
      }

Then we see the same effect as Julie’s seeing in LINQ to Entities (note GetCustomersByCountry is a stored procedure) whereas if I’ve got something like;

     using (NorthwindDataContext ctx = new NorthwindDataContext())
      {
        var query = ctx.FnGetCustomersByCountry("UK"); 

        foreach (Customer c in query) // SQL Happens Here
        {
          Console.WriteLine(c.CustomerID );
        }
      }

where FnGetCustomersByCountry is a table-valued function then the query is executed at a different point in time.

I guess one reason for this is that table-valued functions are composable in LINQ to SQL so I can do something like;

      using (NorthwindDataContext ctx = new NorthwindDataContext())
      {
        var query = ctx.FnGetCustomersByCountry("UK");

        query =
          from c in query
          where c.CustomerID.StartsWith("B")
          select c;

        foreach (Customer c in query) // SQL Happens Here
        {
          Console.WriteLine(c.CustomerID );
        }
      }

whereas I can’t do that with a stored procedure so maybe it makes sense to execute the stored procedure ASAP as deferring the execution would add no value but ( on the other hand and as Julie says ) it’s a bit confusing to the developer who’s got used to queries occurring when we enumerate.

For the table-valued-function case, not to defer the query would be a problem because we’d then be applying the StartsWith clause in memory on the client-side rather than adding it to the query on the server-side.

If you look at the generated code on the DataContext you see;

    [Function(Name="dbo.FnGetCustomersByCountry", IsComposable=true)]
    public IQueryable<Customer> FnGetCustomersByCountry([Parameter(DbType="NVarChar(MAX)")] string country)
    {
      return this.CreateMethodCallQuery<Customer>(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), country);
    }
    
    [Function(Name="dbo.GetCustomersByCountry")]
    public ISingleResult<Customer> GetCustomersByCountry([Parameter(DbType="NVarChar(MAX)")] string country)
    {
      IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), country);
      return ((ISingleResult<Customer>)(result.ReturnValue));
    }

so on the one hand we have CreateMethodCallQuery and on the other we have ExecuteMethodCall and, just to be devious, I wondered if I could do a cheap hack here and try and get deferred execution of my stored procedure just by trying to use CreateMethodCallQuery but I didn’t manage to get that going yet – clearly, there’d be other ways of achieving the same thing.

Finally, there’s also the question of when the data reader is actually read from the wire when you’re doing this kind of work which I talked about a little here.