CODEWITHSUNDEEP
c#sqllinqnhibernate
JConstantine
JConstantine

Reputation: 3931

LINQ to count number of rows in link table

I have the following model

public class Account
{
    public int Id { get; set; }
    public List<Note> Notes { get; set; }
}

I'm trying to query my nhibernate repository to count the number of notes for a specific account.

return this.Data.Where(x => x.Id == accountId).Select(x => x.Notes).Count();

However no matter how many notes there are, it always returns 1.

I am trying to do this in the most efficient way possible without having to get the account object and then count the number of notes.

Can anyone suggest the Linq equivalent of the following SQL.

SELECT Count(*) FROM NoteToAccount WHERE AccountId=?

Where NoteToAccount is a link table which sits between the Account and Note tables.

Upvotes: 3

Views: 3621

Answers (2)

Aducci
Aducci

Reputation: 26694

You want to use

.SelectMany(x => x.Notes)

Upvotes: 2

Colin Mackay
Colin Mackay

Reputation: 19185

You are getting back an IEnumerable<List<Note>> which has one element which is the list of notes for the account.

You can use SelectMany instead of Select to flatten it down into one large entity, or you can use:

this.Data.First(x => x.Id == accountId).Notes.Count;

I am, of course, assuming that accountId is the primary key, so you'll only ever get one item back. You could also use Single instead of First to ensure you get exactly one item back.

Upvotes: 4

Related Questions