none
How to use NOT IN in LINQ ? RRS feed

  • Question

  • what would be the LINQ query for the following SQL statement

    Select * from UserDocuments ud where ud.UserDocumentID NOT IN
    (SELECT udc.UserDocumentID FROM UserDocumentConversions udc 
    WHERE udc.UserDocumentConversionStatusID = 2 OR udc.UserDocumentConversionStatusID = 3)

    var query = _repository.GetQuery<UserDocument>().Where(???????????)

    Note that: here is the signature for GetQuery() method

    IQueryable<TEntity> GetQuery<TEntity>()
    

    Friday, June 28, 2013 6:43 PM

Answers

  • var query = _repository.GetQuery<UserDocument>().Where(item => !_repository.GetQuery<UserDocumentConversion>().Where(ele => 
                    ele.udc.UserDocumentConversionStatusID = 2 
                    || ele.UserDocumentConversionStatusID = 3 ).Contains(item.UserDocumentID)

    first have to get list of items from userdocumentconversions and check that userdocumentid is not in this list.

    let me know if you need further help.


    Thanks & Regards
    Syed Amjad Sr. Silverlight/WPF Developer,
    yahoo : syedamjad6736@yahoo.com, skype : syedamjad.0786.
    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by lax4u Monday, July 1, 2013 9:16 PM
    Sunday, June 30, 2013 6:33 AM

All replies

  • Hello, you shoud use the Contains LINQ extension:

    // Example employees collection
    var employees = Enumerable.Range(1, 100).Select(item => new { EmployeeId = item, Name = item.ToString().PadLeft(10, '0') });
    
    // The EmployeeIds you dont want to take (NOT IN set)
    var notWanted = new[] { 1, 5, 10, 34, 25, 75 };
    
    // The query, you need to use the Contains LINQ method
    var query = employees.Where(employee => !notWanted.Contains(employee.EmployeeId));
    

    Hope this helps,

    Miguel.

    Sunday, June 30, 2013 6:03 AM
  • var query = _repository.GetQuery<UserDocument>().Where(item => !_repository.GetQuery<UserDocumentConversion>().Where(ele => 
                    ele.udc.UserDocumentConversionStatusID = 2 
                    || ele.UserDocumentConversionStatusID = 3 ).Contains(item.UserDocumentID)

    first have to get list of items from userdocumentconversions and check that userdocumentid is not in this list.

    let me know if you need further help.


    Thanks & Regards
    Syed Amjad Sr. Silverlight/WPF Developer,
    yahoo : syedamjad6736@yahoo.com, skype : syedamjad.0786.
    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by lax4u Monday, July 1, 2013 9:16 PM
    Sunday, June 30, 2013 6:33 AM