LINQ - SELECT DISTINCT with NOT IN

Question

I'm have a SQL statement which I am trying to transform in a LINQ statement...

SELECT DISTINCT mc.*
     FROM ManufractorCategories mc 
WHERE mc.Active = 'true'
AND mc.Folder = 'false'
AND (mc.Id not in (SELECT Category_id FROM Manufractor_Category 
                        WHERE Manufractor_id = 3)); 

That's my last, not working LINQ statement

(IQueryable<object>)db.ManufractorCategories
                .Where(o => o.Active == active)
                .Where(o => o.Folder == folder)
                .Select(i => new { i.Id, i.Folder }).Except(db.Manufractor_Categories.Where(t => t.Manufractor_id == id).Select(t => new { t.Category_id })).Distinct();

I've tried the whole Sunday on that, but the Except statement won't work.

Thanks in advances for any help!

Answer 1

The Except method requires two sets of the same type - this means that you would have to select objects of type ManufractorCategory in the nested query as well as in the outer query - then it would select all categories that are in the first one and not in the second one.

An easier alternative is to use the Contains method to check whether the current ID is in a list of IDs that you want to filter. The following should work:

var q = 
  db.ManufractorCategories
    .Where(o => o.Active == active)
    .Where(o => o.Folder == folder)
    .Select(i => new { i.Id, i.Folder })
    .Where(o => 
       !db.Manufractor_Categories
          .Select(t => t.Manufractor_id)
          .Contains(o.Id)
    .Distinct();

And a simplified version using query syntax:

var q = 
  from o in db.ManufractorCategories
  where o.Active == active && o.Folder == folder &&
        db.Manufractor_Categories
          .Select(t => t.Manufractor_id)
          .Contains(o.Id)
  select new { i.Id, i.Folder };

Answer 2

The Except statement is going to get a list of objects with the Category_id property. However, you're query has a result that contains objects with the Id and Folder properties. The query will most likely be unable to see where these objects are equal, and so, the Except clause won't take effect.

Answer 3

Yeah, it worked! Thanks!!