Unable to query database using the values in array using linq

Question

I have two tables. Post table and Follow table. Post table has all the posts of a user and Follow table has the list of user followed by a user.

Post Table

PostID    UserID      Post
1            2       TextOne
2            1       TextTwo
3            1       Text3
4            2       Text4

Follow Table

ID     Following        FollowedBy
1        2                  1
2        3                  1

And I have a list view.

<ItemTemplate >       
    Post:
    <asp:Label ID="Label1" runat="server" Text='<%# Eval("Post") %>' />
    <br />
    UserID:
    <asp:Label ID="Label2" runat="server" Text='<%# Eval("UserID") %>' />
    <br />          
 </ItemTemplate>

I want to show the posts of the user and the people who he are following. I wrote the following code.

int UserId = Convert.ToInt32(Session["User"]);
int[] OthersPosts = (from s in Data.Follow where s.FollowedBy == UserId) select s.Following1).ToArray();

foreach (int post in OthersPosts)
{
    var DisplayPost = (from s in Data.Posts where s.UserID == post && s.UserID == UserId) select s).ToList();
    ListViewPostTable.DataSourceID = "";
    ListViewPostTable.DataSource = DisplayPost;
    ListViewPostTable.DataBind();
}

But no data is displayed on the ListView?

I have checked the watch window for the value in DisplayPost variable, it says Enumeration yielded no results.

i thinks you have correct the query may be like this: var DisplayPost = (from s in Data.Posts where s.PostID == post && s.UserID == UserId) select s).ToList(); — Nayeem Mansoori
– Nayeem Mansoori, Commented Jan 7, 2014 at 5:37
but the OtherPosts array is array of UserId ,how can i search in PostID ? i just named it post but it is user — Genelia D'Souza
– Genelia D'Souza, Commented Jan 7, 2014 at 5:42

Damir Arh · Accepted Answer · 2014-01-07 05:48:52Z

2

The error is in the following predicate:

s.UserID == post && s.UserID == UserId

Since UserID is always different from post which is one of the users he is following, this predicate always returns false.

You should at least change it to

s.UserID == post || s.UserID == UserId

I.e. you want to find posts by the user himself or one of the users he's following.

This still won't work as expected if he's following more than one user because in the foreach loop you'll be binding data to ListViewPostTable multiple times. Of course at the end you'll only see the last result.

Try replacing the foreach loop like this:

var DisplayPost = (
    from s in Data.Posts 
    where s.UserID == UserId || OthersPosts.Contains(s.UserID)
    select s).ToList();
ListViewPostTable.DataSourceID = "";
ListViewPostTable.DataSource = DisplayPost;
ListViewPostTable.DataBind();

answered Jan 7, 2014 at 5:48

Damir Arh

17.9k2 gold badges49 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Devraj Gadhavi Over a year ago

+ 1 for using Contains, instead of foreach.

Devraj Gadhavi Over a year ago

@GeneliaD'Souza you would want to remove the s.UserID == UserId || from the where clause as it would load the Session["User"]'s posts as well. Which not what you want, I guess.

Genelia D'Souza Over a year ago

@DevrajGadhavi i want that too

Devraj Gadhavi · Accepted Answer · 2014-01-07 05:50:14Z

0

Change your code like this.

int UserId = Convert.ToInt32(Session["User"]);
int[] OthersPosts = (from s in Data.Follow where s.FollowedBy == UserId) select s.Following1).ToArray();

List<Posts> lstPosts = new List<Posts>();

foreach (int post in OthersPosts)
{
    var displayPost = (from s in Data.Posts where s.UserID == post) select s).ToList();
    lstPosts.AddRange(displayPost);
}

ListViewPostTable.DataSourceID = "";
ListViewPostTable.DataSource = lstPosts;
ListViewPostTable.DataBind();

You were matching the s.UserID == post && s.UserID == UserId. Which is wrong, because a post will not belong to two users.

edited Jan 7, 2014 at 5:50

answered Jan 7, 2014 at 5:22

Devraj Gadhavi

3,6613 gold badges40 silver badges69 bronze badges

5 Comments

Genelia D'Souza Over a year ago

Following and FollowedBy are the user Id's of the user in follow table, in the query "int[] OthersPosts = (from s in Data.Follow where s.FollowedBy == UserId) select s.Following1).ToArray();" how can i load Person that iam following into the array 'OthersPost' and search for 'PostId' ? and still it is not working i think it is wrong

Genelia D'Souza Over a year ago

OthersPost is loading UserId's

Devraj Gadhavi Over a year ago

Yes, it would load UserIDs. And in the foreach loop you would get their posts.

Genelia D'Souza Over a year ago

but how to display posts of all users that a user follows ?

Devraj Gadhavi Over a year ago

See the edited answer. The foreach loop would fetch of all the posts from users who are being followed by the Session["User"]. And those posts would be added to the List variable lstPosts. Finally after getting all posts from all users, lstPosts would be assigned to ListViewPostTable.DataSource.

Collectives™ on Stack Overflow

Unable to query database using the values in array using linq

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related