Wheres and NOT IN

 As in Where's the NOT IN.  With all the capability added to the query, it seems like there should be a "Notn" on subsonic query.  I've tried to work around it with Inline query, but I think it was too much for the query parser.

I've also tried a SP with a strongly type class, but I come back with the right count, but all my values are 0.   Arggghhh  the pain of learning.   

NIX the NotIN comment.  I found it and I'm trying it now.

Please post your results.  I was not able to get it to work, as I posted in http://forums.subsonicproject.com/forums/p/3213/13319.aspx#13319.  I'm getting the error 'Must declare the scalar variable "@notIn1"'.  I had to implement an inefficient reverse IN query instead.

 Here's what I did ... who knows if it's efficient.  After working on it all afternoon, I'm glad it works:

1. Select  the list to be excluded
2. convert the collection to an array of objects
3. pass the array to the new collection selection as an array attached to the NotIN.

You may have to stumble through the variables ... it's not Northwind

 // Populate the Binding Sources for the related Tabs
// Retrieve Bindings for the LZone SubScription tab
// first the lookup

// Get the exclude list

OrionDB.LZLinkCollection nlzcol = new InlineQuery().
ExecuteAsCollection<OrionDB.LZLinkCollection>("SELECT LZoneNum from dbo.LZLinks WHERE Device = @DeviceID", DevItem);
BindingSource LZluBinding = new BindingSource();

//nlzcol contains the Zones already subscribed

object[] lzsubs = new object[nlzcol.Count];
int i = 0;
foreach (object o in nlzcol)
{
      lzsubs = nlzcol.LZoneNum;
      i++;
}
Orion.LZoneCollection lzc = Orion.DB.Select().From<Orion.LZone>().Where("LZoneNum").NotIn(lzsubs)
.ExecuteAsCollection<Orion.LZoneCollection>();

////This blows up look at it later
// List<LZLookup> lzlu = SPs.NotInLzonesForDevice(6)
// .ExecuteTypedList<LZLookup>();

LZluBinding.DataSource = lzc;
gvLZLookup.DataSource = LZluBinding;
//// Now The Data Table
gvLZSub.DataSource = LZoneBinding;

hth dan

I forgot about the query factory option, using that I got it to work.  The implementation of NOT_IN must be different than NotIn.  I was using an ArrayList for inList, but did not try an object array.

Doesn't work:

qry = new Query(MathcadValue.Schema).NOT_IN("Id", inList);

mvColl.LoadAndCloseReader(qry.ExecuteReader());  //errors wiith 'Must declare the scalar variable "@notIn1"'

Does work:

mvColl = DB.Select().From("MathcadValue").Where("Id").NotIn(inList).ExecuteAsCollection<MathcadValueCollection>();

Thanks