LINQ to Entities 三表连接查询

发布于 2024-12-27 05:33:07 字数 1112 浏览 2 评论 0原文

我在 Linq to Entities 中的查询遇到了一些麻烦，我希望有人能够阐明:-) 我想做的是创建一个连接三个表的查询。

到目前为止，它有效，但由于我尝试连接的最后一个表是空的，因此查询结果不包含任何记录。当我删除最后一个连接时，它会给出正确的结果。

我的查询如下所示：

var query = from p in db.QuizParticipants
            join points in db.ParticipantPoints on p.id 
            equals points.participantId into participantsGroup
            from po in participantsGroup
            join winners in db.Winners on p.id 
            equals winners.participantId into winnersGroup
            from w in winnersGroup
            where p.hasAttended == 1 && p.weeknumber == weeknumber
            select new
            {
                ParticipantId = p.id,
                HasAttended = p.hasAttended,
                Weeknumber = p.weeknumber, 
                UmbracoMemberId = p.umbMemberId,
                Points = po.points,
                HasWonFirstPrize = w.hasWonFirstPrize,
                HasWonVoucher = w.hasWonVoucher                                    
            };

我想要的是获取一些记录，即使 Winners 表为空或其中没有匹配项。

对此的任何帮助/提示都非常感谢！ :-)

提前非常感谢。

/ 博

原文

I'm having a bit trouble with a query in Linq to Entities which I hope someone can shed a light on :-) What I'm trying to do is to create a query that joins three tables.

So far it works, but since the last table I'm trying to join is empty, the result of the query doesn't contain any records. When I remove the last join, it gives me the right results.

My query looks like this:

var query = from p in db.QuizParticipants
            join points in db.ParticipantPoints on p.id 
            equals points.participantId into participantsGroup
            from po in participantsGroup
            join winners in db.Winners on p.id 
            equals winners.participantId into winnersGroup
            from w in winnersGroup
            where p.hasAttended == 1 && p.weeknumber == weeknumber
            select new
            {
                ParticipantId = p.id,
                HasAttended = p.hasAttended,
                Weeknumber = p.weeknumber, 
                UmbracoMemberId = p.umbMemberId,
                Points = po.points,
                HasWonFirstPrize = w.hasWonFirstPrize,
                HasWonVoucher = w.hasWonVoucher                                    
            };

What I would like is to get some records even if the Winners table is empty or there is no match in it.

Any help/hint on this is greatly appreciated! :-)

Thanks a lot in advance.

/ Bo

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

触ぅ动初心 2025-01-03 05:33:07

如果您将它们设置为相关实体而不是进行联接，我认为您想做的事情会更容易。

var query = from p in db.QuizParticipants
            where p.hasAttended == 1 && p.weeknumber == weeknumber
            select new
            {
                ParticipantId = p.id,
                HasAttended = p.hasAttended,
                Weeknumber = p.weeknumber, 
                UmbracoMemberId = p.umbMemberId,
                Points = p.ParticipantPoints.Sum(pts => pts.points),
                HasWonFirstPrize = p.Winners.Any(w => w.hasWonFirstPrize),
                HasWonVoucher = p.Winners.Any(w => w.hasWonVoucher)
            };

假设 hasWonFirstPrize 和 hasWonVoucher 是布尔字段，但您可以使用任何聚合函数来获取所需的结果，例如 p.Winners.Any(w => w.hasWonFirstPrize == 1)

If you set these up as related entities instead of doing joins, I think it will be easier to do what you're trying to do.

var query = from p in db.QuizParticipants
            where p.hasAttended == 1 && p.weeknumber == weeknumber
            select new
            {
                ParticipantId = p.id,
                HasAttended = p.hasAttended,
                Weeknumber = p.weeknumber, 
                UmbracoMemberId = p.umbMemberId,
                Points = p.ParticipantPoints.Sum(pts => pts.points),
                HasWonFirstPrize = p.Winners.Any(w => w.hasWonFirstPrize),
                HasWonVoucher = p.Winners.Any(w => w.hasWonVoucher)
            };

This is assuming hasWonFirstPrize and hasWonVoucher are boolean fields, but you can use any aggregate function to get the results you need, such as p.Winners.Any(w => w.hasWonFirstPrize == 1)

回复收藏 0 原文