Use linq to count in many to many table

Question

I have two table with many-to-many relation ship: director(id, name), movie(id, name, year) and the relation ship table is dir_mov(did,mid) I want to use and group by the number of movie a director direct using linq. How can i solve this?

Have a look at https://stackoverflow.com/questions/7325278/group-by-in-linq - seems similiar to your example ? — auburg, Oct 15 '19 at 08:09

score 1 · Accepted Answer · answered Oct 15 '19 at 08:31

1

You can use join and group by. Joining director table and relationship table is enough.

var result = yourDbContext.director
                .Join(yourDbContext.Dir_Mov, d => d.Id, m => m.did,
                    (d, m) => new {DirectorId = d.Id, MovieId = m.mid})
.GroupBy(g => g.DirectorId)
.Select(s => new {s.Key, CountOfMovie = s.Count()}).ToList();

You have to change yourDbContext is your own context or repository.

answered Oct 15 '19 at 08:31

is_oz

813
1
8
27

Your welcome, if you accept the answer, it's looking solved. – is_oz Oct 15 '19 at 13:15
If you have a Join followed by a GroupBy, wouldn't it be better to use a GroupJoin? – Harald Coppoolse Oct 15 '19 at 14:05
I usually prefer groupJoin for outer joins. In here, it would use. However I am not sure about performance. – is_oz Oct 15 '19 at 19:09

Use linq to count in many to many table

1 Answers1