We help IT Professionals succeed at work.

Linq to sql Not in Clause

Stanton_Roux
Stanton_Roux asked
on
Hi There

I have a linq query that I created to join to entities.

I am trying to return using the Not in clause.

How do I do this in linq.

See code below
string _jsonSpecification = bus.getVehicleFeaturesNamesByCatID(catid);
            List<VehicleFeatureNames> _featuresnames = JsonConvert.DeserializeObject<List<VehicleFeatureNames>>(_jsonSpecification);
            
             string _jsonFeatures = bus.getAllVehicleFeatures();
            List<VehicleFeatures> _features = JsonConvert.DeserializeObject<List<VehicleFeatures>>(_jsonFeatures);

            var joined = from c in _featuresnames
                         from d in _features
                         where (d.SMC == smc)
                         where (c.VehicleFeatureNameID == (int)d.VehicleFeatureNameID)
                         where (c.VehicleFeatureCategoryID == Convert.ToInt32(catid))
//Problem here
                         where (c.VehicleFeatureNameID Not in (6,7,8,9)
                         select new { c.Title, d.FeatureValue };

Open in new window

Comment
Watch Question

Top Expert 2009

Commented:
Here's what I've implemented in one of my queries.

TableA
ColumnA (int)
ColumnB (int)

TableB
ColumnA (int)
ColumnB (int)

So if I have to run the query:
select * from Tableb
where ColumnA not in (select ColumnA from TableA)

My equivalent LINQ statement will look like:

from tableb in db.TableB
where
  !(from tablea in db.TableA
    select new {
      tablea.ColumnA
    }).Contains(new { tableb.ColumnA })
select new {
  tableb.ColumnA,
  tableb.ColumnB
}

You can translate this to your requirements.

Arun
Commented:
Hi,
do you still have problem.
you have to use a combination of ! and contains as pointed by Arun. In case if you need to pass more than one condition to contains then try out using let. for details you can check this article
http://introducinglinq.com/blogs/marcorusso/archive/2008/01/14/the-not-in-clause-in-linq-to-sql.aspx

if you are using EF then contains will not work then you have to use except. You can refer to this
http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/6b0c92d0-83eb-4cb5-beb9-0957dc4f5a90/

I hope that this helps you

Thanks and regards,
Venki