Which .net ORM can deal with this scenario

Question

From a previous question, I learn that Linq to SQL is not able to eager load only certain files of foreingKey tables.

So I'm asking what .net ORM can support including this type of statement inside a entity class, with only one DB call and without manually mapping the result with the object.

  -- simplified for clarity
  SELECT Order.*, Product.Name, Customer.Name, OrderType.Name, Provider.Name
  FROM Order
      INNER JOIN Product on Order.ProductID = Product.ProductID
      INNER JOIN Customer on Order.CustomerID = Customer.CustomerID
      INNER JOIN OrderType on Order.OrderTypeID = OrderType.OrderTypeID
      INNER JOIN Provider on Order.ProviderID = Provider.ProviderID

I want to keep it as simple as possible (trying to avoid NHibernate and such)

Answer 1

As part of a projection, LINQ-to-SQL should handle that - have you tried

select new {Order = order, ProductName = order.Product.Name,
             CustomerName = order.Customer.Name,
             OrderType = order.OrderType.Name } // etc

If you want those properties to become part of the object model itself... trickier, and not very entity related. You could add properties in the partial class, but you'd have to do lots of load-options to make them load eagerly. In reality, it probably isn't worth it given how it complicated the object model.

Answer 2

There are times that you're just going to have to 'do the work yourself' and not rely on an ORM for specific function. This may be one of those times. You could put this information into a stored procedure, and pass in the objects as parameters.

Answer 3

DataObjects.Net handles this by absolutely the same way as LINQ 2 SQL:

from order in Query<Order>.All
where ...
select new {
  Order = order, 
  ProductName = order.Product.Name,
  CustomerName = order.Customer.Name,
  OrderType = order.OrderType.Name 
}