CODEWITHSUNDEEP
linqasp.net-mvc-3entity-frameworkc#-4.0
Rubia Gardini
Rubia Gardini

Reputation: 815

LINQ query for social network: how to perform a friends feed?

I have a social networking website and I'm getting some difficulties to filter who can see the updates users post.

As in Facebook, I see all posts from my friends, even if I didn't post anything. I have these tables:

DiaryPosts table:

--------------------------------------
| ID | UserID | Content | UpdateTime |
--------------------------------------

Friends table:

--------------------------
| ID | UserID | FriendID |
--------------------------

Followers table:

----------------------------
| ID | UserID | FollowerID |
----------------------------

And I have this query that now it can't filter anything:

var diaryPosts = (from d in db.DiaryPosts
                  orderby d.ID descending
                  select new DiaryPostsSet
                  {
                      PostID = d.ID,
                      Author = db.User.Where(m => m.ID == d.UserID).FirstOrDefault().Nickname,
                      Thumbnail = db.User.Where(m => m.ID == d.UserID).FirstOrDefault().Thumbnail,
                      AuthorComment = d.Content, 
                      UserID = d.UserID,
                      Time = d.UpdateTime }).Take(6).ToList();

I tried to write a where clause but it didn't work. Do you have any suggestions on how to write this query?

Upvotes: 1

Views: 507

Answers (4)

Rubia Gardini
Rubia Gardini

Reputation: 815

The answer is:

//queries for diary Posts
        var myDiaryPosts = (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
                              where d.UserID == userset.ID
                              select new DiaryPostsSet { 
                                  PostID = d.ID,
                                  EstadoDeAlmaID = e.ID,
                                  EstadoDeAlma = e.Title,
                                  Author = u.Nickname,
                                  Thumbnail = u.Thumbnail,
                                  UserID = u.ID,
                                  IsDuplicated = d.IsDuplicated,
                                  FriendID = d.FriendID,
                                  FriendName = u.Nickname,
                                  Time = d.UpdateTime,
                                  MessagesCount = d.FriendMessages.Count(m => m.DiaryPostsID == d.ID)
                              });

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

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

        var diaryPosts = myDiaryPosts.Union(friendsPosts).Union(followingsPosts).OrderByDescending(d => d.Time).Take(6).ToList();

Upvotes: 0

AD.Net
AD.Net

Reputation: 13399

        var diaryPosts = (from d in db.DiaryPosts
let friendsId = d.Friends.Select(f=>f.FriendID)
 where d.UserID == currentUserId || friendsId.Any(d.UserID)
                          orderby d.ID descending
                          select new DiaryPostsSet
                          {
                              PostID = d.ID,
                              Author = d.Author.Nickname,
                              Thumbnail = d.Author.Thumbnail,
                              AuthorComment = d.Content, 
                              UserID = d.UserID,
                              Time = d.UpdateTime 
                          }).Take(6).ToList();

I think you need something like this, plz check syntax and you can come up with a join in stead of let if you like.

Upvotes: 1

Ricky Smith
Ricky Smith

Reputation: 2389

You should have relationships setup in your database and in your data tier (in your entity diagram). If so, you should rely on those relationships by using navigation properties, not by using recursive database calls. Consider this: for each of the items in your DiaryPosts table, you will make 2 calls to the database. So, 6 posts = 13 total database calls (1 to get the posts, then 6x2 to get the users).

Your query should look something like this:

var diaryPosts = (from d in db.DiaryPosts
                  orderby d.ID descending
                  select new DiaryPostsSet
                  {
                      PostID = d.ID,
                      Author = d.Author.Nickname,
                      Thumbnail = d.Author.Thumbnail,
                      AuthorComment = d.Content, 
                      UserID = d.UserID,
                      Time = d.UpdateTime }).Take(6).ToList();

Upvotes: 0

Rondel
Rondel

Reputation: 4951

What did you try that failed? You can try moving your where clause outside of your dynamic select query:

var diaryPosts = (from d in db.DiaryPosts
              where d.UserID==2 && d.SomethingElse=="someValue"
              orderby d.ID descending
              select new DiaryPostsSet
              {
                ....

This will ensure that you are filtering the results before you get into your dynamic selection.

Upvotes: 0

Related Questions