CODEWITHSUNDEEP
c#linq-to-sql
Matt Grande
Matt Grande

Reputation: 12157

Multi-Table LinqToSql Aggregate Function

I've been having a heck of a time getting this query right, so I'm hoping that StackOverflow can point me in the right direction.

I have three tables:

I need to get all the Territories that have one or more Users with a StatusId of (let's say) 3.

All I've really been able to get to compile is linking up all the tables :(

IEnumerable<Territory> territories = (from t in db.Territories
                                      join uXt in db.User_x_Territories on t.TerritoryId equals uXt.UserID into tJoin
                                      from uXt in tJoin.DefaultIfEmpty()
                                      join u in db.Users on uXt.UserID equals u.Id into uJoin
                                      from u in uJoin.DefaultIfEmpty()
                                      select t);

Can anyone help me out? All I've been able to find online are fairly basic examples.

Upvotes: 1

Views: 157

Answers (2)

eglasius
eglasius

Reputation: 36037

var territories = context.Territories
    .Where(t=> t.UserTerritories.Any(ut=>ut.User.StatusId == 3));

It works as it reads :)

Just gets the territories that match the desired condition, which is to have any user with status id 3. Using the relations simplifies many of the queries.

Update: if you like it more the same with the query syntax

var territories = from t in context.Territories
                  where t.UserTerritories.Any(ut=>ut.User.StatusId == 3))
                  select t;

Upvotes: 4

Adam Robinson
Adam Robinson

Reputation: 185643

Give this a shot...

(from u in users where u.StatusId==3 
    join ut in userTerritories on u.UserId equals ut.UserId 
    join t in territories on ut.TerritoryId equals t.TerritoryId 
    group t by t into gg select gg.Key)

Upvotes: 2

Related Questions