0

If I have a query like:

var results = from j1 in context.users
              join j2 in context.points on j1.UserId equals j2.UserId
              join j3 in context.addresses on j1.UserId equals j3.UserId
              select new { j1.Username, j2.points, j3.address1 }

Now if I want to group by:

var results = from j1 in context.users
              join j2 in context.points on j1.UserId equals j2.UserId
              join j3 in context.addresses on j1.UserId equals j3.UserId
          group j1 by j1.CountryCode into g1
              select new { j1.Username, j2.points, j3.address1 }

So when I group by, it seems I have to use the 'into' keyword which I guess does some sort of projection? Now I'm not sure how to reference j1.Username, j2.points now?? I see there is a 'Key' property but that doesn't let me navigate to the columns.

How to I get access to the joined columsn after grouping?

loyalflow
  • 14,275
  • 27
  • 107
  • 168
  • What you are trying to achieve by this grouping? What data each group should contain? – Sergey Berezovskiy Jun 19 '13 at 17:44
  • The answer to this question might help, which shows how to access grouped items: http://stackoverflow.com/questions/6618847/linq-groupby-a-key-and-then-put-each-grouped-item-into-seperate-buckets – neontapir Jun 19 '13 at 17:45
  • @lazyberezovsky I want to show the Username, address1 and SUM of points. – loyalflow Jun 19 '13 at 17:50
  • What user `name` and `address1` would you like to use? Since you're grouping by the country code, it is conceivable that there would be lots of `name`s and `address1`s. – Sergey Kalinichenko Jun 19 '13 at 17:51

1 Answers1

1

So when I group by, it seems I have to use the 'into' keyword which I guess does some sort of projection?

There are two pure projection operators - Select and SelectMany.

Now I'm not sure how to reference j1.Username, j2.points now?

You have groups, which contain j1 entities only. And each group is a collection of j1 entities. You can aggregate results. But there is no single j1 entity, which you can get Username of. Thus you have groups of j1, you can't access j2 or j3. Your joins used only to filter users by points and addresses. After that you continued to query only users.

I see there is a 'Key' property but that doesn't let me navigate to the columns.

Key property is a key of each group. In your case it will be CountryCode, which is same for all users in group.

Here is query which returns user name, address, and total points. How it works? First you join user and address with simple join. But you should project results into anonymous object which contain both user and answer. Otherwise answer will be 'lost' from scope, just as in your original query. So far so good - we have both user and answer. Now we need total points. For this GroupJoin is used - its not just correlates user-address pair with points, but also groups results (i.e. points) into points group. Now you have all data accessible - user and address are in ua object, and points related to user are in points group. You can calculate totals:

from u in context.users
join a in context.addresses on u.UserId equals a.UserId
select new { u, a } into ua
join p in context.points on ua.u.UserId equals p.UserId into points
select new
{  
   ua.u.Username,
   ua.u.CountryCode, // need for grouping by country
   ua.a.address1,
   Points = points.Sum()
} 
// last grouping by country. You can omit it
into x
group x by x.CountryCode into g
select g;

BTW why not to use u, p and a variables for user, point and address? Why j1, j2, j3?

Sergey Berezovskiy
  • 232,247
  • 41
  • 429
  • 459
  • 1
    `Select` and `SelectMany` are the two operators that *just* do projections, but many operators (including `GroupBy`) also perform projections *among other things*. There is an overload of `GroupBy` that takes a function which is used to *project* each item in the group into something else. Having said all of that the `into` isn't defining the projection, it's just defining an identifier for that gives the group a name. The expression between `group` and `by` is the projection. – Servy Jun 19 '13 at 18:06
  • @Servy agree about operators which do only projections (that's what I meant). And btw there is sample of two more `into` in my query :) – Sergey Berezovskiy Jun 19 '13 at 18:08
  • This doesn't really exmplain the reasoning for the changes in code. Here you're doing a `GroupJoin` instead of a `Join` followed by a `GroupBy`. It's not immediately obvious to most people how adding the `into` has that affect. It is a lot easier to use a `GroupJoin` instead of a `join` followed by `GroupBy` though, if that's what the OP semantically wants. – Servy Jun 19 '13 at 18:11
  • @Servy sorry, dont understand you here. Which reasoning should be explained? – Sergey Berezovskiy Jun 19 '13 at 18:14
  • The OP was asking for how to access the identifiers used at the start of the query after a group by. You just came out and gave an entirely different query. Depending on what the OP actually wants to do (I still can't figure out what he wants his output to be) this looks like it's probably right, but your answer doesn't use `GroupBy` at all, it uses a `GroupJoin` instead of a `Join`. This isn't explained; you just dumped the code. – Servy Jun 19 '13 at 18:17
  • @Servy actually I added code sample as a bonus. Answers to OP questions are above the line :) – Sergey Berezovskiy Jun 19 '13 at 18:18
  • I know that. My point is, if you're going to choose to add an entirely different approach as well, it would be best if you explained why you think the other approach would be better, along with how it works, rather than just a code dump. Just a suggestion, not a demand or anything. – Servy Jun 19 '13 at 18:20
  • hi, so if I have 10 joins, I have to keep projecting using a select to carry the columns forward right? – loyalflow Jun 19 '13 at 20:03
  • @user1361315 if you are going to use them all, then yes. Otherwise join acts simply as filter – Sergey Berezovskiy Jun 19 '13 at 20:58