NHibernate object-relational mapping discipline

What will happen to your language-embedded query(e.g. Linq) techniques if you are not yet weaned off on mapping table fields to objects by its physical implementation, i.e. you are using public virtual int ProductId { get; set; }
instead of using public virtual Product Product { get; set; }


That is, instead of this logical mapping, see line #18:

public class SalesHeader
{
 public virtual int SalesHeaderId { get; set; }
 public virtual string CustomerName { get; set; }

 public virtual string OrNum { get; set; }
 public virtual DateTime OrDate { get; set; }

 public virtual IList<SalesDetail> Sales { get; set; }      
}


public class SalesDetail 
{
 public virtual SalesHeader SalesHeader { get; set; }

 public virtual int SalesDetailId { get; set; }
 public virtual Product Product { get; set; }
 public virtual int Qty { get; set; }
 public virtual decimal UnitPrice { get; set; }
 public virtual decimal Amount { get; set; }            
}

You just do physical mapping, note line# 6:

public class SalesDetail 
{
 public virtual SalesHeader SalesHeader { get; set; }

 public virtual int SalesDetailId { get; set; }
 public virtual int ProductId { get; set; }
 public virtual int Qty { get; set; }
 public virtual decimal UnitPrice { get; set; }
 public virtual decimal Amount { get; set; }            
}

If you want to fashion your query to something like this (find all SalesHeader which has a Product that starts with M):

select *
from SalesHeader h
where exists(
 select * 
 from SalesDetail d 
 join Product p on p.ProductId = d.ProductId 
 where d.SalesHeaderId = h.SalesHeaderId
  and p.Product.Name like 'M%'
 )

This is how your Linq will look like if you map your database to objects physically:

var x =
 from h in s.Query<SalesHeader>()
 where h.Sales.Any(d =>
  s.Query<Product>().Where(p => p.ProductId == d.ProductId && p.ProductName.StartsWith("M")).Any())
 select h;

that looks convoluted, and the resulting query(columns removed for brevity) is even more:

exec sp_executesql 
N'select * 
from [SalesHeader] salesheade0_ 
where exists (
 select * from [SalesDetail] sales1_ 
 where salesheade0_.SalesHeaderId=sales1_.SalesHeaderId and 
  (exists (select *  from [Product] product2_ 
    where product2_.ProductId=sales1_.ProductId and (product2_.ProductName like (@p0+''%'')))))',N'@p0 nvarchar(4000)',@p0=N'M'

The resulting query, though achieves the same output with our desired query, is a far cry from simple query, it results to a bit complex query, it doesn't link SalesDetail to Product, it uses another EXISTS.


Another attempt, one-to-one with our desired query:

var x = from h in s.Query<SalesHeader>()
 where
 (
  (from d in s.Query<SalesDetail>()
  join p in s.Query<Product>() on d.ProductId equals p.ProductId
  where d.SalesHeader.SalesHeaderId == h.SalesHeaderId
   && p.ProductName.StartsWith("M")
  select d).Any()
 )
 select h;

And that generates this:

exec sp_executesql 
N'select *
from [SalesHeader] salesheade0_ 
where exists (
 select *
 from [SalesDetail] salesdetai1_, [Product] product2_ 
 where product2_.ProductId=salesdetai1_.ProductId and salesdetai1_.SalesHeaderId=salesheade0_.SalesHeaderId 
  and (product2_.ProductName like (@p0+''%'')))',N'@p0 nvarchar(4000)',@p0=N'M'  

Now it look the same with our desired query, though Linq-wise, it can be argued that it doesn't achieve so much in terms of readability and productivity.



To make Linq matters simple, use proper modelling with your ORM

public class SalesDetail 
{
 public virtual SalesHeader SalesHeader { get; set; }

 public virtual int SalesDetailId { get; set; }
 public virtual Product Product { get; set; }
 public virtual int Qty { get; set; }
 public virtual decimal UnitPrice { get; set; }
 public virtual decimal Amount { get; set; }            
}

The Linq will be now this simple, and it achieves the same desired query. The difference is minor, it use table comma table instead of join.

var x = from h in s.Query<SalesHeader>()
  where h.Sales.Any(d => d.Product.ProductName.StartsWith("M"))
  select h;

The generated query:

exec sp_executesql 
N'select *
from [SalesHeader] salesheade0_ 
where exists (
 select sales1_.SalesDetailId 
 from [SalesDetail] sales1_, [Product] product2_ 
 where salesheade0_.SalesHeaderId=sales1_.SalesHeaderId 
  and sales1_.ProductId=product2_.ProductId and (product2_.ProductName like (@p0+''%'')))',N'@p0 nvarchar(4000)',@p0=N'M'
  

Now the Linq is more maintainable and intuitive, and follows our desired optimized query :-)