How to merge 5 different type of linq queries into one based on a column

I am really confused on a report I need. As of today, most of my reports were simple, so I was able to do them myself easily. But being a newbie in sql/dlinq, I cannot find my way through the following:

var closingStock =
    (from p in session.Query<Product>()
    select new
    {
        p.Id,
        p.Name,
        p.Batch,
        p.Rate,
        ClosingStock = p.Quantity - p.AllocatedQuantity,
        p.DivisionId
    }).ToList();

var distributedQuantityAfterPeriod =
    (from i in session.Query<OutwardInvoiceItem>()
    where i.ParentInvoice.Date > ToDate 
    select new
    {
        Id = i.Product.Id,
        DistributedAfter = i.Quantity
    }).ToList();

var distributedQuantityInPeriod =
    (from i in session.Query<OutwardInvoiceItem>()
    where i.ParentInvoice.Date >= FromDate && i.ParentInvoice.Date <= ToDate
    select new
    {
        Id = i.Product.Id,
        Distributed = i.Quantity
    }).ToList();

var receivedQuantityAfterPeriod =
    (from i in session.Query<InwardInvoiceItem>()
    where i.ParentInvoice.Date > ToDate
    select new
    {
        Id = i.Product.Id,
        ReceivedAfter = i.Quantity
    }).ToList();

var receivedQuantityInPeriod =
    (from i in session.Query<InwardInvoiceItem>()
    where i.ParentInvoice.Date >= FromDate && i.ParentInvoice.Date <= ToDate
    select new
    {
        Id = i.Product.Id,
        Received = i.Quantity
    }).ToList();

As you can see, I am trying to build a inventory movement report for a specific date. I have the following problems:

1. How can I reduce the five queries? Is it possible?

2. How can I merge the data provided by these queries into one tab开发者_开发问答le which is grouped on the product id and summed on the quantity related columns? As of now, I am using for loops which are really slow.

What I am using:

C# 4, nHibernate, Sqlite

Any help will be very highly appreciated.

Regards, Yogesh.

1. to reduce roundtrips use .Future() instead of .List()

2. let all queries return

   group i by i.Id into g
   select new
   {
       Id = g.Key,
       Quantity = g.Sum(x => x.Quantity)
   }).Future();
   

and do

var alltogether = groupedDistributedQuantityAfterPeriod
    .Concat(groupedDistributedQuantityInPeriod)
    .Concate(...);

from g in alltogether
group g by g.key into all
select new
{
    Id = all.Key,
    Quantity = all.Sum(x => x.Quantity)
};

Update:

you can reduce the number of queries with

from i in session.Query<OutwardInvoiceItem>()
where (i.ParentInvoice.Date > ToDate) || (i.ParentInvoice.Date >= FromDate && i.ParentInvoice.Date <= ToDate)
select ...

from i in session.Query<InwardInvoiceItem>()
where (i.ParentInvoice.Date > ToDate) || (i.ParentInvoice.Date >= FromDate && i.ParentInvoice.Date <= ToDate)
select ...