Contact   •   Products   •   Search

Rick Strahl's Web Log

Wind, waves, code and everything in between...
ASP.NET • C# • HTML5 • JavaScript • AngularJs

LINQ To Sql - Getting a Command Object from a Query


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>

Make Donation
Posted in LINQ  DataBinding  


Feedback for this Post

 
# re: LINQ To Sql - Getting a Command Object from a Query
by Andy Robinson August 24, 2007 @ 5:46am
Rick,

In general, when binding to a reader, how do you insure that the reader and associated connection get closed? or do you care?
# re: LINQ To Sql - Getting a Command Object from a Query
by Brian August 24, 2007 @ 7:33am
Rick,

I ran into the exact same problem when tested my old way of datareaders, vs. the new LINQ way. The solution I came to was to use Compiled Queries. I seriously got the speed of the binding to almost match datareaders... just a tiny bit slower (fraction of a second over 1000 iterations).

public static Func<Model.TestDataContext>, int, IQueryable<Model.Content>>
GetContentByPageTypeId = CompiledQuery.Compile((Model.TestDataContext context, int pageTypeId) =>
from pageContents in context.PageContents
where pageContents.Page.PageTypeId == pageTypeId
select pageContents.Content);

and then i called the compiled query like this:

Model.TestDataContent context = new TestDataContext(connectionString);
context.ObjectTrackingEnabled = false;

return GetContentByPageTypeId(context, pageTypeId).Single<Model.Content>();

It is a little more work then you had shown, but it also is much faster than manually filling objects with datareaders.
# re: LINQ To Sql - Getting a Command Object from a Query
by Rick Strahl August 24, 2007 @ 11:46am
@Brian - Uh, compiled queries might improve query times and *object loading times* (loading data into a list), but I can't imagine it improving binding in the UI <s>. Compiled queries - from what I understand - parse the query only once so the parsing overhead is removed. But I can't see how it could possibly change binding scenarios.

And besides that syntax is gnarly. Do you really want stuff like this even in your business layer?

@Andy - CommandBehavior.CloseConnection will close the reader when the reader is closed or disposed. That's usually the easiest way to deal with it especially in scenarios where a method returns the reader.
# re: LINQ To Sql - Getting a Command Object from a Query
by Ryan Haney August 25, 2007 @ 3:36am
You can use extension methods to extend the functionality of the context for returning data readers and tables and the like. Just import the namespace with the extension classes and you are good to go :-).
# re: LINQ To Sql - Getting a Command Object from a Query
by Rick Strahl August 25, 2007 @ 12:33pm
@Ryan - yup I know. You can also create a subclass (which I think is preferrable actually in this case since you can explicitly do it) and then tell the generator to use that subclass.
# re: LINQ To Sql - Getting a Command Object from a Query
by Matt Warren August 25, 2007 @ 9:28pm
Rick, you actually can go from a SQL query to entity objects.

var custs = dataContext.ExecuteQuery<Customer>("SELECT * FROM Customers");

You can even use parameters.

var custs = dataContext.ExecuteQuery<Customer>(
"SELECT * FROM Customers WHERE city = {0}",
"London"
);

You can also turn any DataReader you might have into entities.

DbDataReader dr = ...;
var custs = dataContext.Translate<Customer>(dr);
# re: LINQ To Sql - Getting a Command Object from a Query
by foobar August 27, 2007 @ 10:02am
Holy premature optimization.

So binding to entities is 3-4 times slower? So if my datareader takes 10ms, it'll take 40ms to bind to my object collection?

So, what, pray tell, am I going to do with the 30ms I've saved? Buy a coffee?

Hey, who needs an object model, right? Might as well just treat everything as data. Apparently it's too difficult to create an object domain. Flinging around datareaders and datasets, now that's progress!
# re: LINQ To Sql - Getting a Command Object from a Query
by Rick Strahl August 27, 2007 @ 11:08am
If you don't care about perf fine. But remember if you have a fair amount of traffic in ASP.NET especially that this will scale all the way down even on slow requests so that 3-4 times slower binding is going to show up in your CPU usage, and yeah it does matter very quickly when things are THAT much slower. In that scenario you're just throwing away server resources.

Nobody is suggesting to ditch the object output, but just keep in mind that a small change can result in drastically better performance for databinding.
# re: LINQ To Sql - Getting a Command Object from a Query
by Egil August 28, 2007 @ 1:30am
Really interesting article once again.

I'm glad to see somebody actually starting to take a look at how to optimize, get maximum performance out of LINQ to SQL.
# re: LINQ To Sql - Getting a Command Object from a Query
by Mike Griffin August 28, 2007 @ 11:27am
Very interesting indeed, didn't know that was in there. Earlier versions of the Beta actually had a way to execute a LINQ query and get back a DataTable, however, they removed that to prevent OR/M vendors from being able to use LINQ to extend thier architectures. I wonder if they'll rip this now?
# re: LINQ To Sql - Getting a Command Object from a Query
by Rick Strahl August 28, 2007 @ 11:51am
@Mike - yeah I think it would definitely be nice if there were more ways to get the data out. But then again once you have a reader plus the Mapping object creating a DataTable isn't too difficult I suppose.
# re: LINQ To Sql - Getting a Command Object from a Query
by Nuri Kevenoglu September 04, 2007 @ 2:04am
Are you able to verify LINQ slowness with some comparison code? (i.e. traditional ADO.NET vs. LINQ). I ask this because in his "Using LINQ to SQL (Part 1)" blog post, Scott Guthrie states "In terms of performance, LINQ to SQL is really, really fast. In general I'd expect it to be faster than using a DataSet approach"

Also, what's your view on the LINQ object binding speed improvement if you associate the DataContext.GetCommand() object with an SqlCacheDependency() object?
# re: LINQ To Sql - Getting a Command Object from a Query
by Rick Strahl September 04, 2007 @ 2:24am
Please read the whole post and comments.

The slowness I'm describing isn't the query, it's the databinding and in that scenario LINQ Entities will be considerably slower than DataReaders or Datasets because of Reflection use in binding. Using hte LINQ DataSource that may not be an issue because most likely it too binds to a DataReader but that involves two-tier data access. I haven't checked the LINQ Datasource scenario, but I have run through a number of different binding scenarios with DataReader, DataSet, LINQ Entities and DataReaders from LINQ queries. LINQ Entities by far were the slowest in data binding.
# re: LINQ To Sql - Getting a Command Object from a Query
by Nuri Kevenoglu September 04, 2007 @ 5:48pm
I did read the whole post and comments. That's why I asked for your code for the following so we can see the 3-4 fold drop in performance for ourselves:

"I have run through a number of different binding scenarios with DataReader, DataSet, LINQ Entities and DataReaders from LINQ queries. LINQ Entities by far were the slowest in data binding"
# re: LINQ To Sql - Getting a Command Object from a Query
by John December 07, 2007 @ 11:07am
Thanks for this info. I am using this to create a DataTable from the datareader, and returning the DataTable from a WCF service. It sure does simplify things in those cases where a strongly typed object is not needed, or in some cases even desired.
# re: LINQ To Sql - Getting a Command Object from a Query
by Rene May 31, 2008 @ 2:12am
Hi Brian,
what is the return value of the complied query just like your sample code. and how can i bind the data into a gridview or datagrid or treeview.
Thanks
Rene
# re: LINQ To Sql - Getting a Command Object from a Query
by Rene May 31, 2008 @ 2:13am
Hi Brian,
what is the return value of the complied query just like your sample code. and how can i bind the data into a gridview or datagrid or treeview.
Please do email me at rene_florendo2005@yahoo.com
Thanks
Rene
# re: LINQ To Sql - Getting a Command Object from a Query
by BianchiAndrea.com November 27, 2008 @ 1:49am
I try to do the same possibility with "LINQ to Entities" over "ADO.net Entity Framework"

http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=4151490&SiteID=1
# re: LINQ To Sql - Getting a Command Object from a Query
by Jooma January 20, 2009 @ 8:25pm
Can anyone help me with the VB code please ? The above QueryToDataReader function.
# re: LINQ To Sql - Getting a Command Object from a Query
by jason palmer January 04, 2010 @ 8:13am
awesome, spent ages jumping around the web, with people going nuts finding ways to get a datatable returned with sql to linq

if the old way is quicker and faster

but we want to learn to use linq

this is IDEAL :)

Thanks soooo much for posting this on the web

"Earlier versions of the Beta actually had a way to execute a LINQ query and get back a DataTable, however, they removed that to prevent OR/M vendors from being able to use LINQ to extend thier architectures." says it all !!!
 


West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2014