How to create a multiple column Group By using LINQ to Entities VB.NET?

I believe that multiple column Group By on the raw detail data is one of the most important query for reports and user output analysis, because only in the raw detail data you can sum, count and calculate transactional information based on a certain header information.

For example, you want to sum the Quantity of an Item sold in a given date range.  Normal designs requires the Date field to be saved and stored only in the header record, e.g., Sales Invoice, while the Items Sold are recorded in the list of items of the Sales Invoice.

To solve the problem you have no choice but to Group By the Item and Date to sum up the Quantity.  This problem should be simple, because in Microsoft Access it is pretty darn simple using Queries, but unfortunately I could not find any simple LINQ to Entities code for this problem in the Internet.

After three days of testing and trying here is the sample simple Group By LINQ to Entities with multiple columns.

FROM s IN trnsalesitems
GROUP BY salesDate = s.sales.salesdate,
         salesrep = s.sales.salesrepresentative.salesrepresentativecode,
         itemId = s.ItemId,
         lotnumber = s.lotnumber
INTO g=GROUP
SELECT NEW WITH {
        .salesDate=salesDate,
        .salesRep=salesRep,
        .itemId = itemId,
        .lotnumber=lotnumber,
        .totalQuantity = g.sum(function(i) i.quantity)
}

In my example I added a SalesRep and LotNumber aside from the Item and Date.  As you can see in the above example you can have multiple columns in the Group By section, which is very important.

To test whether our example is correct I open a LinqPad software, which you can download for free here.

There you go, if it isn’t simple enough let me know.

If you find this blog helpful, you can make the donation by clicking the Paypal button below.

Advertisements
About

Software developer living in the Philippines.

Posted in Lightswitch
4 comments on “How to create a multiple column Group By using LINQ to Entities VB.NET?
  1. mrrcomp says:

    HI

    Great Blog!

    Any chance you can send me a code snippet how to use the linq groupby as a result for a computedfield in my table?

    Regards
    Meir

    • hgminerva says:

      Use a parameter RIA Service. The computed field is sent through a parameter. Below is an example group by with parameter. By the way, you need to create first a default query before you could create a parameter query.

      Public Function TrnInventoryR002Q002(BranchId As Nullable(Of Integer), DateStart As Nullable(Of DateTime), DateEnd As Nullable(Of DateTime)) As IQueryable(Of TrnInventoryR002)
      Return From i In Me.Context.TrnInventories
      Where i.BranchId = BranchId.Value
      Group By Branch = i.MstBranch,
      Product = i.MstArticleInventory.MstArticle
      Into g = Group
      Select New TrnInventoryR002 With {
      .ProductId = Product.Id,
      .ProductCode = Product.ArticleNumber,
      .ProductDescription = Product.ArticleDescription,
      .UnitId = (From u In Me.Context.MstArticleUnits Where u.BaseUnit = True).FirstOrDefault.Id,
      .Unit = (From u In Me.Context.MstArticleUnits Where u.BaseUnit = True).FirstOrDefault.Unit,
      .BranchId = Branch.Id,
      .Branch = Branch.Branch,
      .CompanyId = Branch.MstBusinessUnit.MstCompany.Id,
      .Company = Branch.MstBusinessUnit.MstCompany.Company,
      .DateStart = g.Min(Function(q) q.InventoryDateTime),
      .DateEnd = g.Max(Function(q) q.InventoryDateTime),
      .QuantityBeginning = If(g.Where(Function(q) q.InventoryDateTime 0) = 0, 0, g.Where(Function(q) q.InventoryDateTime = DateStart.Value And q.InventoryDateTime 0) = 0, 0, g.Where(Function(q) q.InventoryDateTime >= DateStart.Value And q.InventoryDateTime = DateStart.Value And q.InventoryDateTime 0) = 0, 0, g.Where(Function(q) q.InventoryDateTime >= DateStart.Value And q.InventoryDateTime <= DateEnd.Value).Sum(Function(q) q.QuantityOut)),
      .QuantityEnding = If(g.Where(Function(q) q.InventoryDateTime 0) = 0, 0, g.Where(Function(q) q.InventoryDateTime <= DateEnd.Value).Sum(Function(q) q.QuantityIn - q.QuantityOut))
      }
      End Function

      The above code is a running inventory date range per branch report.

      • mrrcomp says:

        Hi

        Thanks for the swift response.

        1) I forgot to mention I was looking for C#

        2) Can you elaborate on where to create the parameter query?

        Regards

  2. Roman says:

    Thank you so much! It helps me to group two fields in vb.net project.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Author

Harold Glenn P. Minerva
Software Developer / Tech Enthusiast
Living in the Philippines

View Harold Glenn Minerva's profile on LinkedIn

Instagram

Software Engineer - Seasonal and Range Trading Software. Magenta Trader is a powerful stock market visualization software that increases your probability of trading success.

Software Architect and Founder - Easyfis.com is a multi-tenant cloud-based Software-as-a-Service (SaaS) business app that caters to micro, small and medium trading businesses.

CTO and Co-Founder - We give your company the leverage by providing innovative software solutions products such as Point-of-Sales (POS), Financial Information System (FMIS), Payroll and DTR (HRIS), and many more.

%d bloggers like this: