How to count group of records using LINQ Query?

Question

I have a database table that have a structure like this:

• ID Integer Auto Incresement
• Name VarChar
• Status VarChar

Sample records:

ID      Name            Status
1       record 1        Outstanding
2       record 2        Outstanding
3       record 3        Aging
4       record 4        Outstanding
5       record 5        Aging
6       record 6        Outstanding

In the table, there are two main status: "Outstanding" and "Aging". I want to count how many records with status of "Outstanding" and how many records with status of "Aging" available in the table.

This is a sample LINQ Query:

Using DC = DataClassesDataContext.Create()
        Dim dataTable = From Count(Outstanding), Count(Aging) In DC.MyTable _
                        Where item.Status = "Outstanding" OrElse item.Status = "Aging" _
                        Group By item.Status _
                        Select item
End Using

The expected result should be:

Outstanding     Aging
4               2

Can you help me to design a LINQ to achive the result?

Answer 1

Try this :-

using System;
using System.Collections.Generic;
using System.Linq;


public class Program
{
    public static void Main()
    {   
        var test = new List<Sample>();

        test.Add(new Sample{ID=1,Name="record 1",Status="Outstanding"});
        test.Add(new Sample{ID=2,Name="record 2",Status="Outstanding"});
        test.Add(new Sample{ID=3,Name="record 3",Status="Aging"});
        test.Add(new Sample{ID=4,Name="record 4",Status="Outstanding"});
        test.Add(new Sample{ID=5,Name="record 5",Status="Outstanding"});
        test.Add(new Sample{ID=6,Name="record 6",Status="Aging"});


        var result = from row in  test
                            group row by "Count" into g
                            where g.FirstOrDefault() != null
                            select new
                    {
                            //Status = g.Key,
                            Outstanding = g.Where(C => C.Status == "Outstanding").Count(),
                            Aging = g.Where(C => C.Status == "Aging").Count()
                    };

        Console.WriteLine("Outstanding"+"   "+"Aging");
        foreach(var item in result)
        {
            Console.WriteLine(" "+item.Outstanding+"            "+item.Aging);
        }

    }   
}

public class Sample
{
    public int ID {get;set;}
    public string Name {get;set;}
    public string Status {get; set;}
}

result :-

you can run above sample code using following link - https://dotnetfiddle.net/xC2NXm

suggest improvement :)

Answer 2

Try this:

Dim MyTable = { _
    New With {.ID = 1, .Name = "record 1", .Status = "Outstanding"},
    New With {.ID = 2, .Name = "record 2", .Status = "Outstanding"},
    New With {.ID = 3, .Name = "record 3", .Status = "Aging"},
    New With {.ID = 4, .Name = "record 4", .Status = "Outstanding"},
    New With {.ID = 5, .Name = "record 5", .Status = "Aging"},
    New With {.ID = 6, .Name = "record 6", .Status = "Outstanding"}
}

Dim dataTable = _
    From item In MyTable
    Group By Key = item.Status Into Xs = Group
    Select New With {.Status = Key, .Count = Xs.Count()}

I get this result: