CODEWITHSUNDEEP
asp.net-mvcentity-frameworkcountinner-join
Diego
Diego

Reputation: 2362

ASP.NET MVC Entity Framework select count inner join another table

I am writing an ASP.NET MVC & Entity Framework app.

I have 2 tables with a relationship 0-to-many. I have to do a select from the first table and also retrieve the count() of the second tables with different filters.

In Ansi SQL is more or less like this.

select 
    *,
    convert(varchar(3), (select count(User_id) 
                         from ActivityGroups G with(nolock) 
                         where A.Activity_id = G.Activity_id 
                           and G.ProcessState_id = 1)) + '  (' + 
    convert(varchar(3), (select count(User_id) 
                         from ActivityGroups G with(nolock) 
                         where A.Activity_id = G.Activity_id 
                           and G.ProcessState_id = 2)) + ')' as Members 
from 
    Activities A

It retrieves a string like 5(3) under name members.

The LINQ query so far:

IEnumerable<ActivitySearch> viewModel = (from activities in _db.Activities
                        .Where(p => p.ProcessState_id == Security.APPROVED)
                        .OrderByDescending(p => p.CreationDate)
                        select new ActivitySearch
                        {
                          Activity_Id = activities.Activity_Id,
                          User_id = activities.User_id,
                          Type = activities.Type,
                          SeekName = activities.SeekName,
                        });

I do not how add the Count() to another table in return value.

Thanks

Upvotes: 0

Views: 985

Answers (1)

Brian Mains
Brian Mains

Reputation: 50728

You can use the "let" keyword in the query.

 var viewModel = from activities in _db.Activities
                        let ag1 = activities.ActivityGroups.Where(i => i.ProcessState_Id == 1).Count()
                        where p.ProcessState_id == Security.APPROVED
                        order by p.CreationDate descening
                        select new ActivitySearch
                        {
                          Activity_Id = activities.Activity_Id,
                          User_id = activities.User_id,
                          Type = activities.Type,
                          SeekName = activities.SeekName,
                          Group1Count = ag
                        };

Essentially the let keyword issues a subquery like your example above. Just be careful how you craft the query because it can lead to performance issues if not careful.

Upvotes: 2

Related Questions