How to perform grouping on first few characters of a column in LINQ and get the count

Question

0.00/5 (No votes)

See more:

Hi,

I have an entity table by name "seats". Records in this table are like this :

Code

10A
10B
10C
10D
11A
11C
11D
11E
312A
312B... so on

Here, For Ex : 10A -> A is the Seat Id and 10 is the table to which the seat is attached, if its 312B then 312 is table Id and B is the seat id.

I need to get the distinct table names from the above collection along with the count of seats around that table, like this :

Code        Seats
10------------4
11------------4
312-----------2

How can I achieve this. Please suggest. Thank you in advance.

What I have tried:

C#

private class tables
{
    public String code { get; set; }
    public Int32 noseats { get; set; }
}

List<tables> lstTables = new List<tables>();
	
lstTables = (from tablrow in Context.seats.AsEnumerable()
            group tablrow by 
            new { tabcode = apputils.Left(tablrow.code, tablrow.code.Length - 1) } 
            into g
            select new tables { code = g.Key.tabcode, noseats = g.Count(g) }).ToList();

This returns an error,

Argument 2: cannot convert from 'System.Linq.IGrouping<<anonymous type: string tabcode>, db.seats>' to 'System.Func<db.seats, bool>'

Posted 8-Mar-19 23:31pm

Priya-Kiko

Updated 11-Mar-19 11:18am

Maciej Los

v5

Add a Solution

Comments

[no name] 9-Mar-19 8:41am

Obviously, if you format the data (into a table and seat) "before" you query it, things would be easier.

Priya-Kiko 11-Mar-19 1:27am

Thank you for the response.

2 solutions

Solution 2

Assuming that Context.seats returns a list of string...

C#

string pattern = @"\d+";
List<tables> lstTables = Context.seats.ToList()
    .GroupBy(s=>Regex.Match(s, pattern).Value)
    .Select(grp=>new tables()
        {
            code = grp.Key,
            noseats = grp.Count()
        })
    .ToList();

Posted 11-Mar-19 11:18am

Maciej Los

Updated 11-Mar-19 11:19am

v2

Comments

Priya-Kiko 12-Mar-19 0:46am

Thank you for your time and suggestion.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

BillWoodruff · Accepted Answer · 2019-03-09T02:56:00

try this:

Dictionary<int, int> SeatsByTableID = lstTables
    .GroupBy(tbl => tbl.TableId)
    .ToDictionary(grp => grp.Key, grp => grp.Count());

Note that I find your naming incongruent: if the table id is the integer, why do you call it 'noseats ?

I assume here your Class should be:

private class Table
{
    public Table(int tableId, string seatcode)
    {
        this.TableId = tableId;
        this.SeatCode = seatcode;
    }

    public int TableId { get; set; }
    public String SeatCode { get; set; }
}

Update:

If you really want an IEnumerable;

C#

var IESeatsByTableID = lstTables
    .GroupBy(tbl => tbl.TableId)
    .Select(grp => new {TblId = grp.Key, NSeats = grp.Count()});

Keep in mind the result is a:

{System.Linq.Enumerable.WhereSelectEnumerableIterator<system.linq.igrouping<int, fourformui.table="">, <>f__AnonymousType0<int, int="">>}

And, it's up to you to remember the internal field names, 'TableId and 'NSeats, when you use the IEnumerable.