When using LINQ to SQL, it lets you retrieve entity lists as results from SQL queries. While that's usually what you would like to retrieve from LINQ query, LINQ to SQL also provides an alternative to retrieve a raw IDbCommand statement with the SQL command and all parameters set based on the query expression.

This means that you can actually bypass the Entity list generation and use the query for other purposes like generating a DataReader instead:

var q = from c in db.tt_customers

        where c.Pk > 5

        select c;                        

IDbCommand command = db.GetCommand(q);

command.Connection = Customer.context.Connection;

command.Connection.Open();

IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

this.dgGrid.DataSource = reader;

this.dgGrid.DataBind();

This may seem like blasphemy that goes against everything that LINQ stands for, but before you dismiss the above think about this:

Databinding to Entity objects in ASP.NET is VERY expensive because databinding against objects requires ASP.NET to use Reflection. For kicks I ran a few queries against Northwind using traditional business objects and using a DataReader or DataTable result and then also using LINQ to SQL with Entity results with a result set of a 1000 records.

The responses for Entities ended up taking 3-4 times longer to bind than the DataReader and still more than 3 times as long as a DataTable/DataSet. Even DataTables are much faster because they don't require Reflection but using collection lookups for binding which is actually significantly faster than Reflection.

However, using code like the above - potentially stocked away as part of a business object you can then easily turn any LINQ data query into a DataReader that is more efficient for binding. So a generic version part of a business object might look like this:

/// <summary>

/// Returns a DataReader from a Linq to Sql query

/// </summary>

/// <param name="query"></param>

/// <returns></returns>

public IDataReader QueryToDataReader(object query)

{

    TContext context = this.context;   // Activator.CreateInstance<TContext>();

    IDbCommand command = this.context.GetCommand(query as IQueryable);

    command.Connection = this.context.Connection;

    command.Connection.Open();

    IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

    return reader;

}

This makes it possible to return a LINQ query from a business method like this:

public object GetRecentCustomers(int customerCount)

{

    var q = from c in context.tt_customers

            orderby c.LastOrder descending

            select c;

    q.Take(customerCount);

    return q;

}

which can then be used like this:

busCustomer Customer = new busCustomer();

var query = Customer.GetRecentCustomers(10);

IDataReader reader = Customer.QueryToDataReader(query);

this.dgGrid.DataSource = reader;

this.dgGrid.DataBind();

or - you could return a DataReader directly from the business object method and use an object data source or whatever suits your databinding fancy.

I'm glad to see this functionality is part of the DataContext, but I wish methods that return a DataReader or DataTable where built right into the context object. We can do this on our own but the DataContext could certainly be more friendly by providing these options more explicitly. In fact the context would also do well to provide some method for generic data access that (using good old SQL strings) so that you can run queries that cannot be expressed with LINQ syntax.

Now if we could only get the reverse of the above code as well - go from a Sql Command and get us an entity result (even if we have to explicitly provide a result type!) set so we can potentially deal with queries that LINQ can't express with LINQ syntax. IOW feed a query on our own and specify the result entity type. All that logic is already in LINQ to SQL, but you can't get at it. Ah, one can dream, no? <g>