none
How do I create a T-SQL "IN" statement with LINQ? RRS feed

  • Question

  • Hello all,

    I'm currently working on an ASP.Net MVC application using VS 2008 with EF 1.0

    I obtain an IEnumerable<string> of employee numbers such as: 00100008, 01824257, etc...

    I would like to create a LINQ query that would be equivalent to this T-SQL statement:
    SELECT * FROM User WHERE UserCode IN ("00100008", "01824257", etc...)

    As far as I know, hence why I'm here, I can't seem to figure out how to make an "IN" clause.


    In my data access layer, I have the following IQueryable query:

    public IQueryable<User> GetUserList()
    {
       return _context.User;
    }
    

    In my Business Logic layer, I first obtain my IEnumerable<string> which I plan to use in my "IN" clause.

    IEnumerable<string> empNumberList = //get list of emp#
    

    Then, I try the following:

    var result = _userRepository.GetUserList()
             .Select(u => empNumberList.Contains(u.UserCode));
    
    Which fails because it doesn't like the Contains (because I'm inside an IQueryable query).

    The following example obviously does not work but kinda illustrates what I'm trying to acheive:
    bool result = _userRepository.GetUserList()
               .Any(u => u.UserCode.IN(string.Join(", ", empNumberList));
    

    Which ideally means...Let me know (by returning true or false) if the numbers in the IEnumerable<string> empNumberList already exist in my UserCode field.

    How would one acheive this?

    Sincerely
    Thanks

    Friday, October 29, 2010 12:24 PM

Answers

  • Here is the solution I found to solve my problem (if anyone cares)

     

    var result = _userRepository.GetUserList()
                                         .Select(u => u.UserCode)
                                         .ToList();

     

    bool exist = result.Any(uc => empNumberList.Contains(uc));

    Where empNumberList is my IEnumerable<string> obtained earlier...

    • Marked as answer by Vlince Friday, October 29, 2010 5:31 PM
    Friday, October 29, 2010 5:30 PM

All replies

  • Here is the solution I found to solve my problem (if anyone cares)

     

    var result = _userRepository.GetUserList()
                                         .Select(u => u.UserCode)
                                         .ToList();

     

    bool exist = result.Any(uc => empNumberList.Contains(uc));

    Where empNumberList is my IEnumerable<string> obtained earlier...

    • Marked as answer by Vlince Friday, October 29, 2010 5:31 PM
    Friday, October 29, 2010 5:30 PM
  • Thanks for sharing! That helped a lot!

    Have a nice day!

    Wednesday, November 3, 2010 2:19 AM