LINQ to SQL, using subquery with IN operator

Question

I am a beginner in LINQ to SQL. I am trying to use a subquery with the SQL IN operator in LINQ. I am trying to integrate the queries friends_A and friends_B as subqueries with SQL IN operators in the posts query:

where p.UserName IN (friends_A)
where p.UserName IN (friends_B)

I don't care whether to integrate the subqueries inline or using variables.

The Question is:
How to integrate the queries friends_A and friends_B (inline or using variables) with the SQL IN operator?

Extra question: how to order the whole result (in var posts) from the Unions: orderby p.DateAndTime?

using (FacebookDataEntities entities = new FacebookDataEntities())
{

    var friends_A = from f in entities.Friends
                   where f.Friend_A != User.Identity.Name
                   && f.Friend_B == User.Identity.Name
                   select f.Friend_A;

    var friends_B = from f in entities.Friends
                   where f.Friend_A == User.Identity.Name
                   && f.Friend_B != User.Identity.Name
                   select f.Friend_B;

   var posts = ((from p in entities.Posts
     where p.UserName.ToLower() == User.Identity.Name
     select new { p.UserName, p.DateAndTime, p.PostText })

     .Union(from p in entities.Posts
            where p.UserName IN (friends_A)
            select new { p.UserName, p.DateAndTime, p.PostText })

     .Union(from p in entities.Posts
            where p.UserName IN (friends_B)
            select new { p.UserName, p.DateAndTime, p.PostText }));

   ListViewPosts.DataSource = posts.ToList();
   ListViewPosts.DataBind();
}

Magnus · Accepted Answer · 2015-04-12 21:14:33Z

1

Looking at your code I think this is what you need. By doing it this way you can also avoid the unions.

var friends_A = from f in entities.Friends
                   where f.Friend_A != User.Identity.Name
                   && f.Friend_B == User.Identity.Name
                   select f.Friend_A;

var friends_B = from f in entities.Friends
                   where f.Friend_A == User.Identity.Name
                   && f.Friend_B != User.Identity.Name
                   select f.Friend_B;

var posts = 
     from p in entities.Posts
     let userName = p.UserName.ToLower()
     where 
        userName == User.Identity.Name ||
        friends_A.Concat(friends_B).Contains(userName)
     orderby 
        p.DateAndTime
     select new 
     { 
        p.UserName, 
        p.DateAndTime, 
        p.PostText 
     };

answered Apr 12, 2015 at 21:14

Magnus

47.3k8 gold badges88 silver badges128 bronze badges

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

Comments

Leandro Sousa · Accepted Answer · 2015-04-12 21:08:09Z

0

Can you try this.

 .Union(from p in entities.Posts
        where friends_A.Any(f => f.Name == p.UserName)
        select new { p.UserName, p.DateAndTime, p.PostText })

 .Union(from p in entities.Posts
        where friends_B.Any(f => f.Name == p.UserName)
        select new { p.UserName, p.DateAndTime, p.PostText }));

answered Apr 12, 2015 at 21:08

Leandro Sousa

423 bronze badges

Collectives™ on Stack Overflow

LINQ to SQL, using subquery with IN operator

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related