CODEWITHSUNDEEP
c#linq-to-sqllambda
Sgraffite
Sgraffite

Reputation: 1928

Linq to SQL - How to compare against a collection in the where clause?

I'd like to compare against an IEnumerable collection in my where clause. Do I need to manually loop through the collection to pull out the column I want to compare against, or is there a generic way to handle this?

I want something like this:

public IEnumerable<Cookie> GetCookiesForUsers(IEnumerable<User> Users)
{
    var cookies = from c in db.Cookies
              join uc in db.UserCookies on c.CookieID equals uc.CookieID
              join u in db.Users on uc.UserID equals u.UserID
              where u.UserID.Equals(Users.UserID)
              select c;
    return cookies.ToList();
}

I'm used to using the lambda Linq to SQL syntax, but I decided to try the SQLesque syntax since I was using joins this time.

What is a good way to do this?

Upvotes: 1

Views: 656

Answers (1)

Michael Buen
Michael Buen

Reputation: 39393

Try this: http://blog.wekeroad.com/2008/02/27/creating-in-queries-with-linq-to-sql/

public IEnumerable<Cookie> GetCookiesForUsers(IEnumerable<User> Users)
{
    var cookies = from c in db.Cookies
              join uc in db.UserCookies on c.CookieID equals uc.CookieID
              join u in db.Users on uc.UserID equals u.UserID
              where Users.Contains(u.UserID)
              select c;
    return cookies.ToList();
}

Or perhaps:

public IEnumerable<Cookie> GetCookiesForUsers(IEnumerable<User> Users)
{
    var cookies = from c in db.Cookies
              join uc in db.UserCookies on c.CookieID equals uc.CookieID
              join u in db.Users on uc.UserID equals u.UserID
              where Users.Select(x => x.UserID).Contains(u.UserID)
              select c;
    return cookies.ToList();
}

Upvotes: 2

Related Questions