LINQ to SQL: Returning Complex Objects

In my previous post about using LINQ to SQL in multi layered apps, I mentioned that this query was not optimal because I would need to build the POCOs from the objects returned by LINQ.  This would mean that objects would be created twice. 

var q = from o in ctx.Orders
        where o.CustomerID == id
        select new { Detail = o.Order_Details, CustomerID = o.CustomerID, OrderDate = o.OrderDate, OrderID = o.OrderID, ShippedDate = o.ShippedDate, ShipCity = o.ShipCity };

Not ideal so I asked if anyone had an idea on how to do that.  Stefan Sedich suggested this query:

var q = from o in ctx.Orders
where o.CustomerID == id
select new TransportObjects.Northwind.Order {

Detail = o.Order_Details.Select(item => new  TransportObjects.Northwind.OrderDetail {
ProductID = item.ProductID 
}).ToArray(),

CustomerID = o.CustomerID,
OrderDate = o.OrderDate,
OrderID = o.OrderID,
ShippedDate = o.ShippedDate,
ShipCity = o.ShipCity};

Cool!  The query returns the Order complex object with the Detail property filled from orders Details.

Thanks a lot Stefan!

 

4 Comments

  • Ick. Is there a reason you have to use Linq to SQL? Other ORM's wouldn't be as clumsy in that situation.

  • Be aware that this query will be utterly slow, as it will execute 1 query per order for the order details as linq to sql's hierarchical fetches are... not optimal to say the least ;)

  • Jeremy: I want to learn the benefits and drawbacks of using LINQ.

    Frans: You're right. I now have to check what is more costly: creating the objects twice or a slow query. I guess that it would depend on the number of records.

    Any other suggestions?

  • OK, so I did a quick test using 50,000 orders each having 10 orders detail lines for a total of 500,000 rows or objects. And the winner is....both!

    Yep, both returned the complex objects in about the same time (+-2-3 seconds).

Comments have been disabled for this content.