LINQ and Lambdas and Sprocs….Oh My! – by Dan Wahlin | Interface Technical Training

Home > Blogs > Developer Visual Studio / ASP.NET > LINQ and Lambdas and Sprocs….Oh My! – by Dan Wahlin

LINQ and Lambdas and Sprocs….Oh My! – by Dan Wahlin

Added by Dan Wahlin February 17, 2008

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.

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

    <span style="color: blue;">END</span>

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:

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

}

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):

<span style="color: #2b91af;">IEnumerable</span>&lt;<span style="color: #2b91af;">OrderDescription</span>&gt; orderDetails =
  <span style="color: blue;">from </span>o <span style="color: blue;">in </span>db.Orders
  <span style="color: blue;">where </span>o.OrderID == orderID
  <span style="color: blue;">from </span>od <span style="color: blue;">in </span>o.OrderDetails
  <span style="color: blue;">let </span>total = od.Quantity * od.UnitPrice
  <span style="color: blue;">select new </span><span style="color: #2b91af;">OrderDescription </span>{
      Product = od.Product.ProductName,
      Quantity = od.Quantity,
      ShipperName = o.Shipper.CompanyName,
      Total = total,
      UnitPrice = od.UnitPrice,
      SupplierName = od.Product.Supplier.CompanyName
  };
<span style="color: blue;">return </span>orderDetails;

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.

<span style="color: blue;">public override </span><span style="color: #2b91af;">IEnumerable</span>&lt;<span style="color: #2b91af;">OrderDescription</span>&gt; GetOrderDetails(<span style="color: blue;">int </span>orderID)
{
<span style="color: #2b91af;"> NorthwindDataContext </span>db = <span style="color: blue;">this</span>.DataContext;
      <span style="color: #2b91af;">IEnumerable</span>&lt;<span style="color: #2b91af;">OrderDescription</span>&gt; orderDetails =
        db.Orders.Where(order =&gt; order.OrderID == orderID).
        Join(db.Shippers, o =&gt; o.ShipVia, s =&gt; s.ShipperID,
          (o, s) =&gt; <span style="color: blue;">new </span>{ o.OrderID, ShipCompanyName = s.CompanyName }).
        Join(db.OrderDetails, o =&gt; o.OrderID, od =&gt; od.OrderID,
          (o, od) =&gt; <span style="color: blue;">new </span>{o.ShipCompanyName, od.ProductID, od.Quantity, od.UnitPrice }).
        Join(db.Products, od =&gt; od.ProductID, p =&gt; p.ProductID,
          (OrderDetails, p) =&gt; <span style="color: blue;">new </span>{ OrderDetails, p.ProductName, p.SupplierID }).
        Join(db.Suppliers, p =&gt; p.SupplierID, s =&gt; s.SupplierID,
          (OrderData, s) =&gt; <span style="color: blue;">new </span>{ OrderData, SupplierName = s.CompanyName}).
        Select(o =&gt; <span style="color: blue;">new </span><span style="color: #2b91af;">OrderDescription </span>{
            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
        });
    <span style="color: blue;">return </span>orderDetails;
}

{

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):

<span style="color: blue;">public override </span><span style="color: #2b91af;">IEnumerable</span>&lt;<span style="color: #2b91af;">OrderDescription</span>&gt; GetOrderDetails(<span style="color: blue;">int </span>orderID)
{
<span style="color: #2b91af;"> NorthwindDataContext </span>db = <span style="color: blue;">this</span>.DataContext;

<span style="color: #2b91af;"> IEnumerable</span>&lt;<span style="color: #2b91af;">OrderDescription</span>&gt; orders =
         db.Orders.Where(order =&gt; order.OrderID == orderID).
         Join(db.OrderDetails, o =&gt; o.OrderID, od =&gt; od.OrderID,
         (o, od) =&gt; <span style="color: blue;">new </span>{ ShipCompanyName = o.Shipper.CompanyName,
                          od.ProductID,
                          ProductName = od.Product.ProductName,
                          Quantity = od.Quantity,
                          UnitPrice = od.UnitPrice,
                          SupplierName = od.Product.Supplier.CompanyName }).

                         Select(o =&gt; <span style="color: blue;">new </span><span style="color: #2b91af;">OrderDescription </span>{
                             Product = o.ProductName,
                             Quantity = o.Quantity,
                             ShipperName = o.ShipCompanyName,
                             Total = o.Quantity * o.UnitPrice,
                             UnitPrice = o.UnitPrice,
                             SupplierName = o.SupplierName
                         });
       <span style="color: blue;">return </span>orders;
}

{

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.

<span style="color: blue;">public override </span><span style="color: #2b91af;">IEnumerable</span>&lt;<span style="color: #2b91af;">OrderDescription</span>&gt; GetOrderDetails(<span style="color: blue;">int </span>orderID)
{
    <span style="color: #2b91af;">IEnumerable</span>&lt;<span style="color: #2b91af;">OrderDescription</span>&gt; orderDetails = DataContext.ap_GetOrderDetailsByOrderID(orderID);
    <span style="color: blue;">return </span>orderDetails;
}

{

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.

Subscribe to this author's posts feed via RSS