CODEWITHSUNDEEP
c#asp.netlinqlinq-to-sql
Tom Gullen
Tom Gullen

Reputation: 61729

Linq for distinct count

I'm not sure how to do this, I have this part of a query:

let DistinctHits = db.tblTrackerVisits
    .Where()
    .Select(d=>d.IPID)
    .Distinct()
    .Count()

There is a table called db.tblTrackerVisitVariables:

enter image description here

What I'm trying to do is modify the query above so that it only counts the distinct records where one of the relating tblTrackerVisitVariable records has a VariableID of n.

An pseudo example that might make it clearer:

let DistinctHits = db.tblTrackerVisits
    .Where(d=> db.tblTrackerVisitVariables.where(v=>v.VisitID == d.ID AND v.VariableID == n))
    .Select(d=>d.IPID)
    .Distinct()
    .Count()

Upvotes: 2

Views: 1514

Answers (1)

Magnus
Magnus

Reputation: 46909

Perhaps something like the following:

let DistinctHits = db.tblTrackerVisits
    .Where(d => d.tblTrackerVisitVariables.Any(v => v.VariableID == n))
    .Select(d => d.IPID)
    .Distinct()
    .Count()

Upvotes: 4

Related Questions