Asp.net core webapi entityframework how to query distinct and not in-CodePudding

I have the following sql query and would like to write in entityframework linq. Can anyone help me how to write this. Basically i want the unique tag names from a table and also not already added for the given user.

Expected sql query:

SELECT DISTINCT(source.tagName) tagName FROM [dbo].[UserTag] source
WHERE source.tagName IS NOT NULL AND source.tagName NOT IN (
  SELECT tagName FROM [dbo].[UserTag] WHERE userid=87 AND tagName IS NOT NULL
)

Current linq query:

var list = await _context.UserTag
            .AsNoTracking()
            .Where(userTag => userTag.TagName != null && !userTag.Disabled)
            .Select(userTag => userTag.TagName)
            .OrderBy(tagName => tagName)
            .Distinct()
            .ToListAsync();

CodePudding user response：

You can use Except for "not in"

var answer = list1.Except(list2);

For example in your code, you can write something like this:

var blackList = await _context.UserTag
            .AsNoTracking()
            .Where(userTag => userTag.TagName != null && !userTag.Disabled && userTag.userid = 87)
            .Select(userTag => userTag.TagName)
            .Distinct()
            .ToListAsync();

var list = await _context.UserTag
            .AsNoTracking()
            .Where(userTag => userTag.TagName != null)
            .Select(userTag => userTag.TagName)
            .OrderBy(tagName => tagName)
            .Distinct()
            .ToListAsync();

var finalList = list.Except(blackList);

Second approach:

var blackList = _context.UserTag
            .AsNoTracking()
            .Where(userTag => userTag.TagName != null && !userTag.Disabled && userTag.userid = 87)
            .Select(userTag => userTag.TagName);

var list = _context.UserTag
            .AsNoTracking()
            .Where(userTag => userTag.TagName != null)
            .Select(userTag => userTag.TagName)
            .OrderBy(tagName => tagName)
            .Distinct();

var finalList = await (list.Except(blackList)).ToListAsync();;

Third approach;

var query =    
    from u in _context.UserTag   
    where !(from uu in _context.UserTag 
            where uu.TagName != null && uu.userid = 87)
            select uu.TagName)    
           .Contains(u.TagName)    
    select u.UserTag;

var finalList = query.OrderBy(tagName => tagName)
            .Distinct()
            .ToListAsync();

CodePudding user response：

Can be achieved using Contains() method as well:

var exceptionList = (from source in _context.UserTag
            where source.userid.Equals(87) && source.tagName!=null)
            order by source.tagName
            select source.tagname).Distint();

var result = from source in _context.UserTag
        where source.tagname != null && (!exceptionList.Contains(source.tagname != null))
        order by source.tagname
        select source.tagname).Distint();