CODEWITHSUNDEEP
c#asp.netsql.netlinq-to-sql
Genelia D'Souza
Genelia D'Souza

Reputation: 77

Unable to query database using the values in array using linq

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.

Upvotes: 0

Views: 209

Answers (2)

Devraj Gadhavi
Devraj Gadhavi

Reputation: 3611

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.

Upvotes: 0

Damir Arh
Damir Arh

Reputation: 17855

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();

Upvotes: 2

Related Questions