如何将带有联合的SQL查询转换为LINQ? [英] How to convert SQL query with Unions to LINQ?

问题描述

如何将其转换为LINQ?

How to convert this one to LINQ?

select d.UserID, d.Content, d.UpdateTime
from DiaryPosts as d
where d.UserID = 1
/* Friends posts */
Union
select d.UserID, d.Content, d.UpdateTime
from DiaryPosts as d
join Friends as fr 
on d.UserID = fr.FriendID 
where fr.UserID = 1
/* My followings */
Union
select d.UserID, d.Content, d.UpdateTime
from DiaryPosts as d
join Followers as fl 
on d.UserID = fl.FollowerID
where fl.UserID = 1
/* order by UpdateTime desc */
order by 3 desc

我尝试过:

var diaryPosts = (from d in db.DiaryPosts
                           join e in db.EstadosDeAlma
                           on d.EstadosDeAlmaID equals e.ID
                           join u in db.User
                           on d.UserID equals u.ID
                           join fr in db.Friends
                           on d.UserID equals fr.FriendID
                           where fr.UserID == userset.ID
                           join fl in db.Followers
                           on d.UserID equals fl.UserID
                           where fl.FollowerID == userset.ID
                           orderby d.ID descending
                           select new DiaryPostsSet
                           {
                              PostID = d.ID,
                              EstadoDeAlmaID = e.ID,
                              EstadoDeAlma = e.Title,
                              Author = u.Nickname,
                              Thumbnail = u.Thumbnail,
                              AuthorComment = d.Content,
                              UserID = u.ID,
                              IsDuplicated = d.IsDuplicated,
                              FriendID = d.FriendID,
                              FriendName = u.Nickname,
                              Time = d.UpdateTime,
                              MessagesCount = d.FriendMessages.Count(m => m.DiaryPostsID == d.ID)
                          }).Take(6).ToList();

它没有显示任何结果.我尝试使用EF子句，但是当我拥有Union时，我不知道如何执行下一个Join.

It doesn't show me any result. I tried with EF clauses but when I have Union, I don't know how to perform the next Join.

有人可以帮我吗?

推荐答案

有一个联合方法

这是一个提示.当事情变得困难时，将其分解，然后简化.我将帮助您解决问题，并将简化过程留给您.

Here is a hint. When things get difficult break them down, then simplify. I will help you break down the problem and leave the simplification to you.

var query1 = (from d in db.DiaryPosts
              where d.UserID = 1
              select new { 
                UserID = d.UserID
                Content = d.Content
                UpdateTime = d.UpdateTime 
              }).ToList();
var query2 = (from d in db.DiaryPosts
              join f in db.Friends
              on d.UserId = f.FriendId
              where f.UserId = 1
              select new { 
                UserID = d.UserID
                Content = d.Content
                UpdateTime = d.UpdateTime 
              }).ToList();
var query3 = (from d in db.DiaryPosts
              join f in db.Followers
              on d.UserId = f.FollowerID
              where f.UserId = 1
              select new { 
                UserID = d.UserID
                Content = d.Content
                UpdateTime = d.UpdateTime 
              }).ToList();

var myunionQuery = query1.Union(query2).Union(query3).OrderBy(d => d.UpdateTime);

这篇关于如何将带有联合的SQL查询转换为LINQ?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！