CODEWITHSUNDEEP
linq
user30803
user30803

Reputation: 875

Linq how to join tables with a where clause on each table and a count

The following LINQ query isn't allowed when I use multiple where elements - it stops liking the 'into':

var query =
 from ph in _db.PlayHits
 join ua in _db.UserAgents on ph.UserAgentId equals ua.UserAgentId
 where (ph.VideoId == 1 && ua.AgentString.Contains("test"))
 into hits
 select new
 {
      ResultCount = hits.Count()
 };

Any idea why or how I amend this?

The equivilent sql I want is:

select count(*) as ResultCount
from Playhits ph
join UserAgents ua on ph.UserAgentId = ua.UserAgentId
where ph.VideoId = 1
and ua.AgentString like '%test%'

Upvotes: 1

Views: 65

Answers (1)

Codor
Codor

Reputation: 17605

To my understanding, counting of the results can be done using the extension methond Count as follows.

var query =
    from ph in _db.PlayHits
    join ua in _db.UserAgents on ph.UserAgentId equals ua.UserAgentId
    where (ph.VideoId == 1 && ua.AgentString.Contains("test"));
int Result = query.Count();

Upvotes: 2

Related Questions