-1

I have two tables:

User
  (
   UserId int primary key,
   Firstname,
   Lastname, ...
   info ...
  )

Friend
  (
   User1Id int,
   User2Id int,
   Status varchar(1), -- '1' is friend, '0' is pending friend request
   primary key(User1Id,User2Id)
  )

(User1Id, User2Id) is foreign key that references to [User] table.

I have this SQL query:

select * from User
where UserId in
(
  (select User1Id from Friend where User2Id='@id' and Status='1') 
   union
  (select User2Id from Friend where User1Id='@id' and Status='1')
)

It executes successfully and returns data as expected.

But I'm in trouble with in when translating from SQL to Linq. I can't use .Contains() because the data type cannot be converted. How can I translate this?

I tried this Linq code:

var query = (from u in Users 
             where u.UserId.Contains(
                 (from f in Friends 
                  where f.User1Id==1 && Status=='1' 
                  select f.User2Id
                 ).Union(from f in Friends 
                         where f.User2Id==1 && Status=='1' 
                         select f.User1Id))
             select u );

but I get this error:

.Contains() cannot use with Int and IQuery

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
Jen
  • 1
  • 4
  • *"the data type is cannot convert."*? What does that mean? – ypercubeᵀᴹ Oct 06 '15 at 09:14
  • @ypercube i don't know but when execute it has that error. .Contains() cannot use with Int and IQuery?? – Jen Oct 06 '15 at 09:30
  • well you have provided neither the code that produces the error nor the actual error message you got. Edit the question, add them and the question may be reopened. – ypercubeᵀᴹ Oct 06 '15 at 09:33
  • @Jen i posted an answer . Is that what are you looking for ? – Ghassen Oct 06 '15 at 09:38
  • @gas thanks for your help. I think your answer is right, but when I execute, I can't receive any result :( – Jen Oct 06 '15 at 09:40
  • @ypercube var query = (from u in Users where u.UserId.Contains((from f in Friends where f.User1Id==1 && Status=='1' select f.User2Id).Union(from f in Friends where f.User2Id==1 && Status=='1' select f.User1Id)) select u ); – Jen Oct 06 '15 at 09:45
  • @ypercube the Sql query above executed successfully. but when I translate to Linq, it error :( – Jen Oct 06 '15 at 09:46
  • 1
    You should **[edit that info into the question](http://stackoverflow.com/posts/32965978/edit)** ! – ypercubeᵀᴹ Oct 06 '15 at 09:48

1 Answers1

1
  internal class Program
    {
        private static void Main(string[] args)
        {
            List<User> users = new List<User>();
            users.Add(new User() { id = 1, name = "1" });
            users.Add(new User() { id = 2, name = "2" });
            users.Add(new User() { id = 3, name = "3" });

            List<Friend> friends = new List<Friend>();
            friends.Add(new Friend() { User1Id = 2, User2Id = 1 });
            friends.Add(new Friend() { User1Id = 1, User2Id = 3 });
            friends.Add(new Friend() { User1Id = 2, User2Id = 3 });

              var query = users.Where(q => (from f1 in friends where`f1.User1Id == 1 select f1.User2Id).Union(from f1 in friends where f1.User2Id ==` 1 select f1.User1Id).Contains(q.id));
        }
    }

    public class User
    {
        public int id { get; set; }

        public string name { get; set; }
    }

    public class Friend
    {


        public int User1Id { get; set; }

        public int User2Id { get; set; }
    }
Ghassen
  • 1,039
  • 12
  • 27