为了使用组简单的LINQ查询返回一个一对多的关系 [英] Simple linq query using group in order to return a one to many relationship

问题描述

我有用户数据表和user_ratings的另一个表。这是一个一对多的关系使他们由用户ID ，用户表作为主键和具有外键关系的user_ratings加盟。每个user_ratings有一列称为RATING_VALUE及其填充有一个int，负的或正的，并且它被加在一起以计算作为用户的评级。我希望能够拉动收视率取决于日期定等级每个用户。以下是我迄今为止我的LINQ语句，但我不认为这是正确的。

I have a data table of users and another table of user_ratings. It is a one to many relationship so the they are joined by UserId, the users table being the primary key and the user_ratings having the foreign key relationship. Each user_ratings has a column called rating_value and its filled with an int, negative or positive, and it gets summed together to be calculated as the user's rating. I want to be able to pull ratings for each user depending on the date for given ratings. Here is what I have so far with my linq statement, but I don't think it is right.

var profiles = from userprofs in Ent.UserProfiles
               join userratings in Ent.UserRatings on userprofs.UserId equals userratings.UserId
               where userratings.DateAdded >= drm.Start
               group userprofs by userprofs.UserId into g
               select new { ... };

不是真的知道从哪里何去何从，我不知道如果我正确地使用该组。我希望能够通过这个集合迭代以后能够显示基于user_ratings总和的每个用户和他或她相关联的评价

Not really sure where to go from here, I'm not sure if I'm using the group correctly. I want to be able to iterate through this collection later to be able to display each user and his or her associated rating based of the sum of the user_ratings

推荐答案

如果你想知道关于收视率的信息是信息汇总（求和，平均值，计数等），那么你可以做这样的事：

If all the information you wish to know about the ratings is aggregate information (sum, average, count, etc) then you can do the likes of this:

var profiles = from userprof in Ent.UserProfiles
  join userrating in Ent.UserRatings on userprofs.UserId equals userratings.UserId
  where userrating.DateAdded >= drm.Start
  group userrating by userprof into g
  select new {g.Key.UserID, g.Key.UserName, Count = g.Count(), Avg = g.Avg(r => r.RatingValue) };

请注意，我改变了复数奇异的名字，因为我们在每一个单独的项目来定义LINQ查询（是的UserProfiles多元，而userprof中的每一个项目的话）。

Note that I changed the plurals to singular names, since we define linq queries in terms of each individual item (UserProfiles is plural, and userprof each item "in" it).

这有一个很好的直接的转化为SQL：

This has a nice straight-forward conversion to SQL:

SELECT up.userID, up.userName, COUNT(ur.*), AVG(ur.ratingValue)
FROM UserProfiles up JOIN UserRatings ur
ON up.userID = ur.userID
WHERE dateAdded > {whatever drm.Start is}
GROUP BY up.userID, up.userName

如果我们希望能够进入个人收视率，这不符合单个SQL查询匹配太好了，我们可以这样做：

If we want to be able to get into individual ratings, this does not match too well with a single SQL query, we could do:

var profiles = from userprof in Ent.UserProfiles
  join userrating in Ent.UserRatings on userprofs.UserId equals userratings.UserId
  where userrating.DateAdded >= drm.Start
  group new{userrating.RatingValue, userrating.SomeThing} by new{userprof.UserID, userprof.UserName};

这会给我们一个的IEnumerable＆LT;分组＆LT; {匿名对象}，{匿名对象}＆GT;＆GT; ，我们可以遍历，得到一个新的 Key.UserID 和 Key.UserName 在每次迭代，并能够通过遍历反过来越来越 item.RatingValue 和 item.SomeThing （我添加在演示中，如果我们想只值这将是一个的IEnumerable＆LT;组合＆LT; {匿名对象}，诠释＆GT;＆GT; ）是这样的：

This would give us an IEnumerable<Grouping<{anonymous object}, {anonymous object}>> that we could iterate through, getting a new Key.UserID and Key.UserName on each iteration, and being able to iterate through that in turn getting item.RatingValue and item.SomeThing (that I added in for demonstration, if we want just the value it would be an IEnumerable<Grouping<{anonymous object}, int>>) like this:

foreach(var group in profiles)
{
  Console.WriteLine("The user is :" + group.Key.UserName + " (id: " + group.Key.UserID ")");
  foreach(var item in group)
  {
    Console.WriteLine(item.SomeThing + " was rated " + item.RatingValue);
  }
}

然而，这样做的问题是，没有一个很好的单个SQL查询，这映射到。 LINQ将竭尽所能，但是这将意味着执行多个查询，所以你最好还是帮它：

However, the problem with this, is that there isn't a nice single SQL query that this maps to. Linq will do its best, but that'll mean executing several queries, so you're better off helping it out:

var profiles = from item in (from userprof in Ent.UserProfiles
  join userrating in Ent.UserRatings on userprofs.UserId equals userratings.UserId
  where userrating.DateAdded >= drm.Start
  select new{userrating.RatingValue, userrating.SomeThing, userprof.UserID, userprof.UserName}).AsEnumerable()
  group new{item.RatingValue, item.SomeThing} by new{item.UserID, item.UserName}

此作为前具有相同的输出，但翻译成SQL允许对要由单一的查询，与工件的其余部分在存储器正在做。的99％的时间，拖了一些工作到这样的内存使得事情效率较低，但由于previous没有一个单一的SQL查询它可以映射到，这是一个例外。

This has the same output as before, but the translation into SQL allows for a single query to be made, with the rest of the work being done in memory. 99% of the time, dragging some work into memory like this makes things less efficient, but because the previous doesn't have a single SQL query it can map to, it's an exception.

这篇关于为了使用组简单的LINQ查询返回一个一对多的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！