Asp.netcorewebapientityframework如何查詢distinct和notin-有解無憂

我有以下 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();

uj5u.com熱心網友回復：

您可以使用除了“不在”

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

uj5u.com熱心網友回復：

也可以使用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();

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/335659.html

標籤：C＃实体框架链接到实体

上一篇：如何在物體框架中將多個物體合二為一（將復雜物體映射為單表）

下一篇：Last和LastOrDefault的EF行為