LINQ to SQL in and not in

What is in and not in equals in LINQ to SQL?

For example

select * from table in ( ...)
and 
select * from table not in (..)

What is equa开发者_如何学运维l to the above statement in LINQ to SQL?

You use, where <list>.Contains( <item> )

var myProducts = from p in db.Products
                 where productList.Contains(p.ProductID)
                 select p;

Or you can have a list predefined as such:

int[] ids = {1, 2, 3};

var query = from item in context.items
            where ids.Contains( item.id )
            select item;

For the 'NOT' case, just add the '!' operator before the 'Contains' statement.

I'm confused by your question. in and not in operate on fields in the query, yet you're not specifying a field in your example query. So it should be something like:

select * from table where fieldname in ('val1', 'val2')

or

select * from table where fieldname not in (1, 2)

The equivalent of those queries in LINQ to SQL would be something like this:

List<string> validValues = new List<string>() { "val1", "val2"};
var qry = from item in dataContext.TableName
          where validValues.Contains(item.FieldName)
          select item;

and this:

List<int> validValues = new List<int>() { 1, 2};
var qry = from item in dataContext.TableName
          where !validValues.Contains(item.FieldName)
          select item;

Please Try This For SQL Not IN

var v = from cs in context.Sal_Customer
         join sag in context.Sal_SalesAgreement
         on cs.CustomerCode equals sag.CustomerCode
         where
          !(
               from cus in context.Sal_Customer
               join
               cfc in context.Sal_CollectionFromCustomers
               on cus.CustomerCode equals cfc.CustomerCode
               where cus.UnitCode == locationCode &&
                     cus.Status == Helper.Active &&
                     cfc.CollectionType == Helper.CollectionTypeDRF
               select cus.CustomerCode
           ).Contains(cs.CustomerCode) &&
           cs.UnitCode == locationCode &&
           cs.Status == customerStatus &&
           SqlFunctions.DateDiff("Month", sag.AgreementDate, drfaDate) < 36
           select new CustomerDisasterRecoveryDetails
           {
             CustomerCode = cs.CustomerCode,
             CustomerName = cs.CustomerName,
             AgreementDate = sag.AgreementDate,
             AgreementDuration = SqlFunctions.DateDiff("Month", sag.AgreementDate, drfaDate)
   };

Please Try This For SQL IN

context.Sal_PackageOrItemCapacity.Where(c => c.ProjectCode == projectCode && c.Status == Helper.Active && c.CapacityFor.Contains(isForItemOrPackage)).ToList();