c# - EF: Get list of objects where primary key is not referenced anywhere -

my current database has files table, referenced couple of other tables. trying achieve, retrieve list of files primary key not referenced @ all.

structure of models below.

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; }     ..... } 

what have been trying is:

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

but throws error of unable create constant value of type 'afile'. primitive types or enumeration types supported in context.

is there elegant way of handling this, can't pull out full list of fileid references afiles , bfiles costly.

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

try this

it result in following (sql server profiler)

select      [extent1].[fileid] [id],      [extent1].[name] [name],      [dbo].[file] [extent1]      not exists (select          1 [c1]         ( select distinct              [unionall1].[fileid] [c1]              (select                  [extent2].[fileid] [fileid]                  [dbo].[filesa] [extent2]             union                 select                  [extent3].[fileid] [fileid]                 [dbo].[filesb] [extent3]) [unionall1]         )  [distinct1]         [distinct1].[c1] = [extent1].[fileid]      )