Comparison of a simple select statement in DLinq (C# 3.0) vs. ADO.Net


Six months ago I posted a comparison of a simple select statement in C-omega vs. ADO.Net which some people found very exciting.  Now that Linq has been officially unveiled, I figured I should update my comparison using C# 3.0 and DLinq.  Although Linq and C-omega have some significant differences, everything I said in that post about the benefits of C-omega applies equally well to Linq (in fact, I like Linq even better, primarily due to expression trees) [Update: Erik Meijer e-mailed me to say that C-omega also has indirect support for expression trees by exposing the parse tree to compiler plug-ins.]


Here is the example ADO.Net code using strongly-typed data sets:


      SqlDataAdapter da = new SqlDataAdapter(


            “SELECT * FROM Employees WHERE City= @city”, nwindConn );


      SqlParameter cityParam = da.SelectCommand.Parameters.Add(“@city”, SqlDbType.VarChar, 80);


      cityParam.Value = city;


 


      NorthwindDataSet ds = new NorthwindDataSet();


      da.Fill(ds, ds.Employees.TableName );


 


      foreach (NorthwindDataSet.EmployeesRow dr in ds.Employees.Rows)


      {


            string name = dr.LastName;


            int id = dr.EmployeeID;


            Console.WriteLine( id + “: ” + name);


      }


And here is the equivalent C# 3.0 code:



var rows = from e in db.Employees where e.City == city select e;
foreach( var e in rows )
{
   string name = e.LastName;
   int id = e.EmployeeID;
   Console.WriteLine( id + “: ” + name );
}


Or even more concisely:



foreach( var e in db.Employees.Where( e => e.City == city ) )
   Console.WriteLine( e.EmployeeID + “: ” + e.LastName );


There is already tons of information available about Linq (more so than there ever was for C-omega), and there are some great blogs by plenty of people more authoritative than myself (eg Matt, Dinesh, Paul, Dan, Scott, etc.), so I probably won’t say much more about it myself.  But isn’t it awesome that this is almost certainly going to become part of mainstream programming in a couple years?  Are we finally on the road to eliminating SQL injection attacks (not to mention all the other great benefits)?  It’s advances like this that make me proud to work for Microsoft!

Comments (11)

  1. Uwe Keim says:

    Were there any negative critics, yet? If not, here is a slightly one 🙂

    I do see the benefits, but I also have a big déjà vû: The C#-language seems to go the C++-way, where I, even after 10 years of programming, don’t know all of the features and somtimes still wonder "why does this thing behave this way?".

    So the complexity is raised from version to version.

    Does everyone think, this is a good way? I think it would be better to NOT enhance the language/compiler from version to version, but to enhance functionality by enhancing the library instead.

    If it is not possible to enhance the library, what about some kind of "compiler-plugins" to provide additional functions, and then only explicitely include the additional functions (like those inline-SQL) when you actually require it?

    So like the concept of MSBuild which is only the shell to the actual functions, maybe this is possible to the compiler in some kind of way, too.

  2. Patrick says:

    I’m confused.

    Isn’t Linq querying in-memory objects? Meaning you would have to pull down the entire database table into your collection in order to query it?

    If that’s the case, these two examples are VASTLY different and Linq is almost useless.

  3. damien morton says:

    How awkward is the syntax for directly using a foreach statement directly over linq query results:

    foreach(var x in from e in db.Employees where e.City == city select new{e.FirstName,e.LastName})

    {

    Console.WriteLine(x);

    }

  4. Uwe, your point deserves a full post in itself: http://blogs.msdn.com/rmbyers/archive/2005/09/25/473767.aspx. Actually it deserves much more than that, but a blog post is all I’m going to do myself <grin>.

  5. Patrick,

    No Linq isn’t just for querying in-memory objects. The language extensions (specifically expression trees) support arbitrary query execution implementation choices. DLinq implements queries by forming SQL expressions and sending them to a SQL server, then lazily reading the results back on-demand. See the DLinq overview for more details: http://download.microsoft.com/download/c/f/b/cfbbc093-f3b3-4fdb-a170-604db2e29e99/DLinq%20Overview.doc.

  6. Damien, yes that is a little awkward. Normally in the samples, you don’t see the query syntax (from …) embedded in other (non-query syntax) expressions. This is why I switched to the direct invocation of the ‘Where’ operator in my 2nd example, instead of using query syntax.

    Note however, that your example does avoid transferring ALL the fields for each matching record. If I was concerned about the performance here, I probably should have used a projection in the query (either ‘select’ query syntax or the ‘Select’ query operator) to fetch just the EmployeeID and LastName.

  7. dhchait says:

    Apologies for the slight hijack, but your passing mention of SQL injection attacks got me thinking: how are runtime-defined queries expected to be created in a Linq world? Seems to be a big issue that I haven’t seen a decent story for yet.

  8. Daniel,

    I don’t know for sure, and it depends a lot on the scenario, but there are several options. If the actual structure of the query needs to change, then you have some of the same complexity you would have had with that approach generating SQL by hand, but now you generate expression trees (instances of Expression<T>) instead of SQL text. Working at this higher level (effectively at the semantic level of abstract syntax trees instead of the syntactic level of characters and strings) should be more natural and have less opportunity for error (i.e. you can’t have a syntax error in your generated query).

  9. dhchait says:

    Thanks, Rick; I’ve seen similar feedback ever since I posted the same question over on the Lab49 blog (http://blog.lab49.com/?p=129). I’m going to play with this a bit tonight and see if I can make it work. Will relay the results on Lab49 blog if they’re interesting enough.

  10. We all know that you can use C#’s foreach keyword to iterate through all items in an enumeration. Suppose…

Skip to main content