开发者

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 ...
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜