CODEWITHSUNDEEP
c#entity-frameworkt-sqlcountentity-framework-5
Jared
Jared

Reputation: 6090

EF Count instances of duplicate field value

Does EntityFramework provide any mechanism for finding the number of times a value is repeated in a column? I'm aware of grouping/counting in pure SQL, but I'm not sure how to translate this into EF Lambda Expressions.

The project is using EF5 on .NET4

Run the snippet below to see desired output

<h3>Original Table</h3>
<table border="1">
  <thead>
    <tr>
      <th>
        Some Column
      </th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>asdf</td>
    </tr>
    <tr>
      <td>asdf</td>
    </tr>
    <tr>
      <td>fdsa</td>
    </tr>
    <tr>
      <td>fdsa</td>
    </tr>
    <tr>
      <td>fdsa</td>
    </tr>
  </tbody>
</table>

<h3>Desired Table</h3>
<table border="1">
  <thead>
    <tr>
      <th>Some Column</th>
      <th>Count</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>asdf</td>
      <td>2</td>
    </tr>
    <tr>
      <td>fdsa</td>
      <td>3</td>
    </tr>
  </tbody>
</table>

Upvotes: 0

Views: 796

Answers (2)

John Castleman
John Castleman

Reputation: 1561

I'll not show your POCO for your original table, but can tell from your question it at least has a "Some Column" property:

// a list, simulating your table, and seeded with values from your example
var originalData = new List<OriginalTable>
{
    new OriginalTable {SomeColumn = "asdf"},
    new OriginalTable {SomeColumn = "asdf"},
    new OriginalTable {SomeColumn = "fdsa"},
    new OriginalTable {SomeColumn = "fdsa"},
    new OriginalTable {SomeColumn = "fdsa"},
};

The following will return a collection of objects with distinct values of SomeColumn in the key property, and the count of that value from originalData in the count property:

var desiredTable = originalData.GroupBy(o => o.SomeColumn,
                                        o => 1, // don't need the whole object
                                        (key, g) => new {key, count = g.Sum()});

You could, of course, return the entire object from your group select in the second parameter, but this is overkill, since you are just going to count the things returned.

It seems likely you're going to want this in a dictionary – something pretty common to do with a GroupBy anyway – so I'll throw this out there, too:

var desiredDictionary = desiredTable.ToDictionary(x => x.key, x => x.count);

Upvotes: 1

Mike Cole
Mike Cole

Reputation: 14743

You're going to want to use LINQ syntax like so:

from item in Table
group item by item.FieldName into g
select new {Key = g.Key, Count = g.Count()}

Replace Table with your EntitySet name, replace FieldName with the property on which you want to group.

Edit: And requested Lambda syntax:

Table.GroupBy(l => l.FieldName , l => l, (key,g) => new {Key = key, Count=g.Count()})

Upvotes: 1

Related Questions