Asp.net core webapi实体框架如何查询区分和不在

Question

我有以下sql查询，并希望在entityframework linq中编写。有人能帮我写这个吗?基本上我想从表中唯一的标签名称，也没有为给定的用户添加。

期望的sql查询:

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
)

当前linq查询:

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

Answer 1

可以使用Except for "not in">

var answer = list1.Except(list2);

例如，在你的代码中，你可以这样写:

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);

第二种方法:

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();;

第三种方法;

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();

Answer 2

也可以使用Contains()方法实现:

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();