LINQ and Lambdas and Sprocs....Oh My!

Building an N-Layer ASP.NET Application with LINQ, Lambdas and Stored Procedures (Updated)

Community Blogs — Thu, 28 Feb 2008 18:22:30 GMT

Update: I refactored some of the code and also did a better job ensuring Dispose() is called everywhere

re: LINQ and Lambdas and Sprocs....Oh My!

dwahlin — Tue, 19 Feb 2008 15:21:25 GMT

Dug,

Thanks for commenting. Yeah....the Lambda query is overly complex. I was trying to mirror the LINQ query as closely as possible just to show what it would look like. I should probably post what you list above as well since in reality people will be leveraging the data relationships in the object model.

re: LINQ and Lambdas and Sprocs....Oh My!

Dug — Tue, 19 Feb 2008 05:11:46 GMT

Interesting post - Could you explain why are you using such complicated linq/lambda expressions. Are you concerned about the sql that is being produced?

I tried the following lambda expression and got the same result and a very similar sql

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

});

I also tried:

List<OrderDescription> orders = new List<OrderDescription>();

Order order = db.Orders.Where(o => o.OrderID == orderID).Single<Order>();

foreach (OrderDetail od in order.OrderDetails)

{

orders.Add(new OrderDescription

{

Product = od.Product.ProductName,

Quantity = od.Quantity,

ShipperName = order.Shipper.CompanyName,

SupplierName = od.Product.Supplier.CompanyName,

Total = od.Quantity * od.UnitPrice,

UnitPrice = od.UnitPrice

});

}

Which resulted in two additional sql query for each order detail to look up the product and the supplier. This can be prevented by adding the following immediate loading directives to the method:

System.Data.Linq.DataLoadOptions loadoptions = new DataLoadOptions();

loadoptions.LoadWith<Order>(o => o.OrderDetails);

loadoptions.LoadWith<Order>(o => o.Shipper);

loadoptions.LoadWith<OrderDetail>(od => od.Product);

loadoptions.LoadWith<Product>(p => p.Supplier);

db.LoadOptions = loadoptions;

This results in a some very ugly sql which seems to be reasonable similar to the original query.

I did not check any of the execution plans when comparing the sql that was produced.

Building an N-Layer ASP.NET Application with LINQ, Lambdas and Stored Procedures

Dan Wahlin's WebLog — Tue, 19 Feb 2008 01:20:52 GMT

.NET 3.5 has a lot of great new features that can significantly enhance developer productivity. 

re: LINQ and Lambdas and Sprocs....Oh My!

dwahlin — Mon, 18 Feb 2008 21:44:51 GMT

Damien,

In cases where a developer is contracting and shipping DB changes in a migration script then I would agree with you (having worked in that environment...which can definitely be painful at times) as long as the DB is SQL 2000/2005 of course. In other cases where developers work directly at a company and have access to either the database or DBAs then I'd still prefer stored procedures there.

The nice thing is that it really doesn't matter what I think since people can choose what's best for their situation and LINQ gives them a lot of flexibility in that choice.

re: LINQ and Lambdas and Sprocs....Oh My!

Damien Guard — Mon, 18 Feb 2008 17:47:31 GMT

I disagree that LINQ complicates application maintenance - if anything does it's stored procedures.

SP's are often outside of the source control mechanism and complicate shipping upgrades because you then need a db migration script.

Internally it means you can't try changes locally without replicating the database environment or giving the SP a different name and remembering to rename both references before you go live... at the same time.

Sure SP's if there needs to be a lot of server-side processing or additional security though.

[)amien

» Daily Bits - February 18, 2008 Alvin Ashcraft’s Daily Geek Bits: Daily links, development, gadgets and raising rugrats.

Mon, 18 Feb 2008 14:20:37 GMT

Pingback from » Daily Bits - February 18, 2008 Alvin Ashcraft’s Daily Geek Bits: Daily links, development, gadgets and raising rugrats.

Weekly Link Post 29 « Rhonda Tipton’s WebLog

Weekly Link Post 29 « Rhonda Tipton’s WebLog — Mon, 18 Feb 2008 02:36:44 GMT

Pingback from Weekly Link Post 29 « Rhonda Tipton’s WebLog

re: LINQ and Lambdas and Sprocs....Oh My!

dwahlin — Sun, 17 Feb 2008 21:38:43 GMT

Gregor,

Glad the post was beneficial. I agree with the "uneasy feeling" you mentioned. We've been told for years to use stored procedures and avoid inline SQL but now LINQ tends to put things into more of a gray area. But, people can choose what they like most which makes it cool.

re: LINQ and Lambdas and Sprocs....Oh My!

dwahlin — Sun, 17 Feb 2008 21:36:55 GMT

Thanks for the comment Ryan. Views would definitely be a good idea in this example especially for people that don't necessarily want stored procedures but yet want simplified LINQ or lambda queries. I haven't taken the time to benchmark the three options...that would definitely be interesting though.