Interface Blogs

Silverlight 2.0 provides a new and exciting framework for building rich applications using C#, VB.NET or other languages that are capable of running on multiple operating systems and in multiple browsers.  Scott Guthrie recently posted a great set of tutorials on Silverlight 2.0 that are an excellent resource for getting started building Silverlight 2.0 applications. 

Scott recently approved converting the tutorials into video so I've been busy the past few days putting together video tutorials that cover Silverlight 2.0 and the Digg.com application Scott wrote about.  Links to the written tutorials and video tutorials are shown below.

Silverlight 2.0 Video Tutorials

View other cool videos on Silverlight 2.0 and additional .NET technologies from the Mix 08 conference:

Joshua Allen's Mix 08 Blog

Last night we released a new version of the OrgChart.NET ASP.NET server control under a new name of "SmartChartPro".  We decided to give the control a new name since it's capable of doing more than just OrgCharts (although that's what most companies use it for).  We also released a  new company website named SmartWebControls.com based on .NET 3.5 where SmartChartPro and other upcoming controls will now reside. 

I decided to use LINQ and Lambdas in conjunction with LINQ to SQL in the back-end data classes for the new site.  Using these new technologies saved an enormous amount of time and made the process a lot more fun since we didn't have to go through the tedious process of mapping DataReader properties to custom data entity class properties as we'd done in the past.

If you or your boss are holding off on .NET 3.5 I'd definitely recommend taking a closer look as your productivity will increase a lot by using the new features it offers plus your code base will be more maintainable into the future.

Update:  I refactored some of the code and also did a better job ensuring Dispose() is called everywhere so that the DataContext object gets cleaned up properly.

Download the Application Here

.NET 3.5 has a lot of great new features that can significantly enhance developer productivity.  I've been spending some time lately working on a little sample application that demonstrates how an N-Layer ASP.NET 3.5 application can be built using LINQ, lambdas and LINQ with stored procedures.  The application is for a talk I'll be giving at DevConnections in April discussing how LINQ technologies can be used in an N-Layer architecture.  In a previous post comparing different LINQ options I mentioned that I'd be posting the code download as soon as it was ready.

The application provides a presentation layer, business layer, data layer and model layer through separate projects as shown next:

It also demonstrates how the new ListView control can be used to display data, perform insert, update and delete operations and nest other controls such as the GridView.  Databinding on the presentation layer is mainly done using the ObjectDataSource control.

All of the queries performed in the application go against an object model created using the Visual Studio 2008 LINQ to SQL Designer. 

Note:  The included Northwind SQL Express database has been modified slightly to add a TimeStamp field into the Customer and Orders tables.  Doing so simplifies updates so be aware that if you change the connection string to point to a standard Northwind database you'll get an error since the TimeStamp fields will be missing.

3 Options for Data Access

Rather than focusing solely on LINQ, I wanted to show different options for data access that .NET 3.5 offers so that developers can get a feel for what's available in addition to standard LINQ queries that seem to get most of the attention these days.  I ended up creating six main data layer classes as shown next:

Customer Query Classes:

• CustomerDBLINQ - Executes customer related queries using inline LINQ
• CustomerDBLambda - Executes customer related queries using lambda expressions
• CustomerDBSprocs - Executes customer related queries using stored procedures and LINQ
  

Order Query Classes:

• OrderDBLINQ - Executes order related queries using inline LINQ
• OrderDBLambda - Executes order related queries using lambda expressions
• OrderDBSprocs - Executes order related queries using stored procedures and LINQ

I still lean toward using stored procedures due to the security and maintenance benefits they offer in more enterprise environments, but for small queries I actually prefer lambda expressions over LINQ (not sure why...just feels more object oriented I guess).  If you currently use stored procedures in your applications and haven't checked out the new LINQ to SQL Designer you'll be impressed with how easy it is to call stored procedures and pass parameters.  You never have to see or create another SqlCommand or SqlParameter object again (well...in many cases anyway).

Switching Between Data Access Classes

By changing a value in web.config you can switch between the different data layer classes and see which option you prefer (LINQ, lambdas or LINQ with sprocs).  All of the data access classes perform the same overall tasks, they just use different techniques to do it.

<appSettings>
  <!-- 
    Used to define which DB layer class should be loaded and used. 
    Valid customer values include:  Data.CustomerDBSprocs, Data.CustomerLINQ, Data.CustomerLambda
    Valid order values include: Data.OrderDBSprocs, Data.OrderLINQ, Data.OrderLambda
  -->
  <add key="CustomerDBType" value="Data.CustomerDBLINQ" />
  <add key="OrderDBType" value="Data.OrderDBLINQ" />
  <!-- When the following key is set to "true" ensure that 
       EnablePartialRendering is set to false on the Default.aspx ScriptManager control -->
  <add key="EnableDataContextLogging" value="false" />
</appSettings>

Over the next few weeks I'm hoping to make some time to walk through the application pieces.  I may create some video tutorials about it as well....we'll see how time goes.

There's a lot of great stuff in .NET 3.5 and several different ways to work with LINQ technologies such as LINQ to SQL.  I'm currently putting together some demonstration code for a talk I'll be giving at DevConnections in Orlando and showing how LINQ, Lambdas and LINQ with stored procedures can be used to do the same thing so that people get a feel for each technique.  For shorter queries I generally prefer lambdas since it's more object-oriented feeling compared to LINQ (to me anyway).  For more complex queries LINQ is much easier though.  Overall, I still prefer stored procedures since you have much more control over security that way and can maintain queries without resorting to C#/VB.NET code changes in some cases.  Plus, LINQ makes it really easy to pass parameters to stored procedures without having to create SqlParameter objects (something I've always despised).

Although I've found that I like lambdas a lot for more simple queries, I was working on some lambda code yesterday that was just plain out of control and much more complex when compared to using LINQ or LINQ against a sproc.  Here's an example of the overall query I was after which has several inner joins.  This particular query was automatically generated using LINQ code and I logged the output and converted it to a stored procedure named ap_GetOrderDetailsByOrderID.  It's structured a little differently than I would typically write, but accomplishes the same end goal.

CREATE PROCEDURE dbo.ap_GetOrderDetailsByOrderID
    (
        @OrderID int
    )
AS
    BEGIN
        SELECT [t5].[CompanyName] AS [ShipperName], 
        [t5].[ProductName] AS [Product], 
        [t5].[value] AS [Total], 
        CONVERT(Int,[t5].[Quantity]) AS [Quantity], 
        [t5].[UnitPrice], 
        [t5].[CompanyName2] AS [SupplierName] 
        FROM ( 
            SELECT [t0].[OrderID], 
                    [t1].[CompanyName], 
                    [t2].[UnitPrice], 
                    [t2].[Quantity], 
                    [t3].[ProductName], 
                    [t4].[CompanyName] AS [CompanyName2], 
                    (CONVERT(Decimal(29,4),[t2].[Quantity])) * [t2].[UnitPrice] AS [value] 
            FROM [dbo].[Orders] AS [t0] 
            INNER JOIN [dbo].[Shippers] AS [t1] ON [t0].[ShipVia] = ([t1].[ShipperID]) 
            INNER JOIN [dbo].[Order Details] AS [t2] ON [t0].[OrderID] = [t2].[OrderID] 
            INNER JOIN [dbo].[Products] AS [t3] ON [t2].[ProductID] = [t3].[ProductID] 
            INNER JOIN [dbo].[Suppliers] AS [t4] ON [t3].[SupplierID] = ([t4].[SupplierID]) ) 
        AS [t5] WHERE [t5].[OrderID] = @OrderID 

    END

The examples that follow go against the LINQ to SQL objects shown next that I created in Visual Studio 2008 using the LINQ to SQL Designer.  All of the objects came from the Northwind database except the custom OrderDescription object.

Using LINQ

LINQ can be used to automatically generate the query shown above by doing the following:

public override IEnumerable<OrderDescription> GetOrderDetails(int orderID)
{
    NorthwindDataContext db = this.DataContext;
    IEnumerable<OrderDescription> orderDetails =
        from o in db.Orders
        where o.OrderID == orderID
        join s in db.Shippers on o.ShipVia equals s.ShipperID
        join od in db.OrderDetails on o.OrderID equals od.OrderID
        join p in db.Products on od.ProductID equals p.ProductID
        join supplier in db.Suppliers on p.SupplierID equals supplier.SupplierID
        let total = od.Quantity * od.UnitPrice
        select new OrderDescription {Product = p.ProductName, Quantity = od.Quantity, 
                     ShipperName = s.CompanyName, Total = total, UnitPrice=od.UnitPrice,
                     SupplierName = supplier.CompanyName};
    return orderDetails;

}

This code joins 5 tables to grab order details and adds the target fields to the custom OrderDescription object.  By using this code the SQL is created on the fly from LINQ expression trees and sent to the database so any changes to the query require changes to the code of course.  For those that don't like working with stored procedures this certainly is the next best thing.

A better way of doing this that leverages relationships between objects defined in the LINQ to SQL data model is shown next (thanks to Christian Nagel):

IEnumerable<OrderDescription> orderDetails =
  from o in db.Orders
  where o.OrderID == orderID
  from od in o.OrderDetails
  let total = od.Quantity * od.UnitPrice
  select new OrderDescription
  {
      Product = od.Product.ProductName,
      Quantity = od.Quantity,
      ShipperName = o.Shipper.CompanyName,
      Total = total,
      UnitPrice = od.UnitPrice,
      SupplierName = od.Product.Supplier.CompanyName
  };
return orderDetails;

Using Lambdas

I mentioned earlier that I'm a big fan of lambdas when a particular query is reasonable.  However, they can get out of control.  The fairly straightforward LINQ query shown above gets pretty nasty when switching to lambdas since the joins require identifying the primary, foreign keys and fields to select.  This is lambda overkill....there are too many => characters in there for me, but it matches up with the LINQ query shown above pretty well.

public override IEnumerable<OrderDescription> GetOrderDetails(int orderID)
{
    NorthwindDataContext db = this.DataContext;
      IEnumerable<OrderDescription> orderDetails =
        db.Orders.Where(order => order.OrderID == orderID).
        Join(db.Shippers, o => o.ShipVia, s => s.ShipperID, 
          (o, s) => new { o.OrderID, ShipCompanyName = s.CompanyName }).
        Join(db.OrderDetails, o => o.OrderID, od => od.OrderID, 
          (o, od) => new {o.ShipCompanyName, od.ProductID, od.Quantity, od.UnitPrice }).
        Join(db.Products, od => od.ProductID, p => p.ProductID, 
          (OrderDetails, p) => new { OrderDetails, p.ProductName, p.SupplierID }).
        Join(db.Suppliers, p => p.SupplierID, s => s.SupplierID, 
          (OrderData, s) => new { OrderData, SupplierName = s.CompanyName}).
        Select(o => new OrderDescription
        {
            Product = o.OrderData.ProductName,
            Quantity = o.OrderData.OrderDetails.Quantity,
            ShipperName = o.OrderData.OrderDetails.ShipCompanyName,
            Total = o.OrderData.OrderDetails.Quantity * o.OrderData.OrderDetails.UnitPrice,
            UnitPrice = o.OrderData.OrderDetails.UnitPrice,
            SupplierName = o.SupplierName
        });
    return orderDetails;
}

By leveraging relationships in the object model generated by the LINQ to SQL Designer you can simplify this query a lot.  Here's an example of doing that (thanks to Dug for commenting and posting the refactored version):

public override IEnumerable<OrderDescription> GetOrderDetails(int orderID)
{
       NorthwindDataContext db = this.DataContext;

       IEnumerable<OrderDescription> orders =
         db.Orders.Where(order => order.OrderID == orderID).
         Join(db.OrderDetails, o => o.OrderID, od => od.OrderID,
         (o, od) => new { ShipCompanyName = o.Shipper.CompanyName, 
                          od.ProductID, 
                          ProductName = od.Product.ProductName, 
                          Quantity = od.Quantity, 
                          UnitPrice = od.UnitPrice, 
                          SupplierName = od.Product.Supplier.CompanyName }).

                         Select(o => new OrderDescription
                         {
                             Product = o.ProductName,
                             Quantity = o.Quantity,
                             ShipperName = o.ShipCompanyName,
                             Total = o.Quantity * o.UnitPrice,
                             UnitPrice = o.UnitPrice,
                             SupplierName = o.SupplierName
                         });
       return orders;
}

Using LINQ with Stored Procedures

This is my favorite technique.  While LINQ makes it easy to query against a database without embedding inline SQL into C# or VB.NET, using pure LINQ code still doesn't provide the same level of security that stored procedures can provide, requires that SQL be generated dynamically from LINQ expression trees and can complicate application maintenance down the road in my opinion.  To call the stored procedure shown at the beginning of this post using LINQ to SQL techniques you can use the following code once the stored procedure has been drag and dropped onto the LINQ to SQL designer surface.  This code is simple and easy to maintain.  Plus, I can filter the results even more by using LINQ or by adding lambdas onto the ap_GetOrderDetailsByOrderID() method if needed.

public override IEnumerable<OrderDescription> GetOrderDetails(int orderID)
{
    IEnumerable<OrderDescription> orderDetails = DataContext.ap_GetOrderDetailsByOrderID(orderID);
    return orderDetails;
}

Ultimately it all comes down to personal preference.  Having worked through many LINQ, lambda and stored procedure queries I'll be sticking with LINQ to SQL with sprocs since the code is squeaky clean.  I have a few friends who prefer using inline LINQ as shown in the first example and we've argued the pros and cons of each technique back and forth.  The beauty of it all is that we get to use what we want and have multiple options to choose from!

I'll post the demo code I've been working on soon so those who are interested in getting into LINQ, lambdas and LINQ with sprocs can see how each technique can be used in an n-tier application architecture.

Let me start off by saying that I'm a huge fan of Language Integrated Query (LINQ).  If you've ever had to write code to loop through objects to locate properties and then filter the collection based upon specific values then you'll definitely have an appreciation for what LINQ has to offer.  It's a phenomenal technology that will definitely make developing applications a more productive process.  Scott Guthrie has some really nice tutorials that help jumpstart learning more about LINQ and LINQ to SQL that you can view here.  For those that haven't seen many LINQ samples, here's a simple example of using LINQ to filter a collection of Customer types based upon a Country property:

In addition to querying objects, LINQ can also be combined with other technologies such as SQL and ADO.NET (XML support is also included) to query databases and automatically fill collections without manually writting O/R mapping code.  This is a great feature and a big time saver.  However, it makes it really easy for developers to embed SQL logic directly into application classes without necessarily realizing it.  This leads to the LINQ dilemma....

Inline LINQ/SQL Versus Stored Procedures

If you've been developing for very long then you've certainly heard the old recommendation of "use stored procedures when calling a database".  There's a lot of wisdom to this recommendation since it promotes more fine-grained security, better re-use, and simpler maintenance as compared to embedding SQL directly in code.  It also lends itself well to schema modifications since changes can quickly be made to tables, views, triggers and stored procedures directly within a database without even touching any application code potentially (depending upon the changes of course). 

With the LINQ to SQL designer in Visual Studio 2008 you can use stored procedures (Scott demonstrates this in a few of the tutorials mentioned earlier), but many of the demos that you'll find out there covering LINQ to SQL perform LINQ queries that generate dynamic SQL directly within .NET classes.  Is that bad?  It depends on who you talk to.  Experienced developers will weigh their options here, but those who are newer to the world of LINQ to SQL may not consider the ramifications of embedding LINQ code to query databases directly in their application classes.

I personally think that embedding LINQ/SQL logic directly into classes will lead to maintenance nightmares down the road especially in large applications since any changes to a database now require not only updating the database tables, views, triggers, etc., but also locating the related C# or VB.NET code that also needs to be changed (similar to what happens with embedded SQL code now).  In enterprise situations there may be multiple applications that need to be modified.

Security is also a potential issue since instead of being able to lock-down specific stored procedures to defined users and roles, more generic queries against source tables are made (views can certainly be used as well though).  In talking with several DBAs about this at the DevConnections conference in Las Vegas, they're certainly not supportive of moving query code out of the database for security and maintenance reasons.  Some of them just didn't want to give up that type of control, but the majority just thought it was a bad idea from a security and maintenance standpoint. 

I think that adding LINQ to SQL code directly into application classes is extremely productive.  It's pretty amazing how quickly and easily you can access data.  However, I'm still on the stored procedures bandwagon when using LINQ to SQL because I'm sold on the security, re-use and maintenance story that goes along with them.  However, in talking with several other developers (some who are very experienced) they don't necessarily agree with this view and will be putting their LINQ/SQL queries directly within code since it's simply more productive.  If you have an opinion here I'd be interested in hearing it since I think this "LINQ dilemma" will certainly stir up a lot of discussions as .NET 3.5 is released.