EF:获取任何地方都没有引用主键的对象列表

本文关键字：引用 列表 对象 获取 任何地 EF | 更新日期: 2023-09-27 17:58:24

我当前的数据库有一个Files表，它被其他几个表引用。我试图实现的是检索一个根本没有引用主键的文件列表。

模型结构如下。

public class File
{ 
    public int FileId { get; set; }
    public ......
}
public class AFiles 
{
    public int AFilesId { get; set; }
    [ForeignKey("File")]
    public int FileId { get; set; }
    public virtual File File { get; set; }
    .....
}
public class BFiles 
{
    public int BFilesId { get; set; }
    [ForeignKey("File")]
    public int FileId { get; set; }
    public virtual File File { get; set; }
    .....
}

我一直在尝试的是：

return context.File.Where(x =>
            !context.AFiles.Any(y => y.FileId == x.FileId) &&
            !context.BFiles.Any(y => y.FileId == x.FileId) 
        ).Select(x => x.FileId).ToList();

但这会抛出Unable to create a constant value of type 'AFile'. Only primitive types or enumeration types are supported in this context. 的错误

有没有一种优雅的方法来处理这个问题，我不能从AFiles和BFiles中提取完整的FileId引用列表，因为这太昂贵了。

EF:获取任何地方都没有引用主键的对象列表

var ids = context.AFiles.Select(af => af.FileId)
                 .Union(context.BFiles.Select(bf => bf.FileId))
                 .Distinct();
var files = context.File.Where(f => !ids.Contains(f.FileId)).ToList();

试试这个

它将导致类似以下（SQL Server Profiler）的

SELECT 
    [Extent1].[FileId] AS [Id], 
    [Extent1].[Name] AS [Name], 
    FROM [dbo].[File] AS [Extent1]
    WHERE  NOT EXISTS (SELECT 
        1 AS [C1]
        FROM ( SELECT DISTINCT 
            [UnionAll1].[FileId] AS [C1]
            FROM  (SELECT 
                [Extent2].[FileId] AS [FileId] 
                FROM [dbo].[FilesA] AS [Extent2]
            UNION ALL
                SELECT 
                [Extent3].[FileId] AS [FileId]
                FROM [dbo].[FilesB] AS [Extent3]) AS [UnionAll1]
        )  AS [Distinct1]
        WHERE [Distinct1].[C1] = [Extent1].[FileId] 
    )

这里有一个查询，它应该完成您想要的任务。

List<int> ids;
using (var context = new Context())
{
    ids = (
        from f in context.Files
        from a in context.AFiles
        from b in context.BFiles
        where a.FileId != f.FileId && b.FileId != f.FileId
        select f.FileId).ToList();
}
public class File {
    [Key] public int FileId { get; set; }    
}
public class AFile {
    [Key] public int AFileId { get; set; }
    [ForeignKey("File")]
    public int FileId { get; set; }
    public virtual File File { get; set; }    
}
public class BFile {
    [Key] public int BFileId { get; set; }
    [ForeignKey("File")]
    public int FileId { get; set; }
    public virtual File File { get; set; }
}
public class Context : DbContext {
    public DbSet<File> Files { get; set; }
    public DbSet<AFile> AFiles { get; set; }
    public DbSet<BFile> BFiles { get; set; }
}

生成的SQL查询

SELECT
    [Filter1].[FileId1] AS [FileId]
    FROM   (SELECT [Extent1].[FileId] AS [FileId1]
        FROM  [dbo].[Files] AS [Extent1]
        CROSS JOIN [dbo].[AFiles] AS [Extent2]
        WHERE [Extent2].[FileId] <> [Extent1].[FileId] ) AS [Filter1]
    CROSS JOIN [dbo].[BFiles] AS [Extent3]
    WHERE [Extent3].[FileId] <> [Filter1].[FileId1]
相关文章：