从id列表中获取缺失的id

Question

我正试图从实体框架核心的另一个id列表中检索数据库中缺失的id列表。

有办法在一行中接听这个电话吗?

public static async Task<IEnumerable<TKey>> GetMissingIds<T, TKey>(
this IQueryable<T> db, IEnumerable<TKey> ids)
where T : BaseEntity<TKey>
{
var existingIds = await db
.AsNoTracking()
.Where(entity => ids.Contains(entity.Id))
.Select(entity => entity.Id)
.ToListAsync();

return ids.Except(existingIds);
}

Answer 1

EF Core只支持Contains和本地集合(除了少数例外)，因此没有有效的方法来检索数据库中不存在的id。

无论如何有第三方扩展可以做linq2db。EntityFrameworkCore(注意我是创建者之一)。

使用这个扩展你可以加入本地集合LINQ查询:

public static Task<IEnumerable<TKey>> GetMissingIds<T, TKey>(
this IQueryable<T> query, IEnumerable<TKey> ids, CabcellationToken cancellationToken = default)
where T : BaseEntity<TKey>
{
// we need context to retrieve options and mapping information from EF Core
var context = LinqToDBForEFTools.GetCurrentContext(query) ?? throw new InvalidOperationException();
// create linq2db connection
using var db = context.CreateLinqToDbConnection();
var resultQuery =
from id in ids.AsQueryable(db) // transform Ids to queryable
join e in query on id equals e.Id into gj
from e in gj.DefaultIfEmpty()
where e == null
select id;
// there can be collision with EF Core async extensions, so use ToListAsyncLinqToDB
return resultQuery.ToListAsyncLinqToDB(cancellationToken);
}

这是生成查询的示例:

SELECT
[id].[item]
FROM
(VALUES
(10248), (10249), (10250), (10251), (10252), (10253), (10254),
(10255), (10256), (10257), (10023)
) [id]([item])
LEFT JOIN (
SELECT
[e].[OrderID] as [e]
FROM
[Orders] [e]
) [t1] ON [id].[item] = [t1].[e]
WHERE
[t1].[e] IS NULL

Answer 2

List<int> allIds = Enumerable.Range(1, 10).ToList();
List<int> ids = new List<int> { 1, 3, 5, 7, 9 };
using (var dbContext = new MyDbContext())
{
List<int> missingIds = dbContext.MyEntities
.Where(e => allIds.Contains(e.Id) && !ids.Contains(e.Id))
.Select(e => e.Id)
.ToList();
Console.WriteLine(string.Join(", ", missingIds));
}