如何在表中不存在的情况下执行此LINQ查询？

In our database, a file is "Processing" if the column "Pending" is set to false on the FileTable and that files FileID doesn't exist in the Transaction table.

在我们的数据库中,如果FileTable上的列“Pending”设置为false并且Transaction表中不存在文件FileID,则文件为“正在处理”。

How can I construct a LINQ query that basically says,

如何构造一个基本上说的LINQ查询,

where f.Pending == False && (f.ID !=exist in db.Transactions.FileID)

其中f.Pending == False &&(f.ID!=存在于db.Transactions.FileID中)

The portion in the parenthesis is what I'm not sure how to do. I could do that in a seperate query but I know there is a way to elegantly do this in one query.

括号中的部分是我不知道该怎么做的。我可以在单独的查询中执行此操作,但我知道有一种方法可以在一个查询中优雅地执行此操作。

Here is what I have so far:

这是我到目前为止:

 public IEnumerable<PlacementFile> getProcessingPlacementFiles()
        {
            using (LinqModelDataContext db = new LinqModelDataContext())
            {
                var placementFiles = from f in db.Placement_Files
                                     where (f.Pending == false && (f.ID does not exist in db.Transactions.FileID))
                                     orderby f.Import_Date descending
                                     select f;

                return placementFiles;

            }

Is this a case where I need to do a join on the FileTable and the Transactions table? Can someone guide me a little through this?

这是我需要在FileTable和Transactions表上进行连接的情况吗?有人能指导我一点吗?

2 个解决方案

#1

Use:

where !f.Pending && !db.Transactions.Any(trans => trans.ID == f.ID)

That should work at least logically - you'll have to see whether it generated the right SQL, of course :)

这应该至少在逻辑上起作用 - 你必须看看它是否生成了正确的SQL,当然:)

#2

This is a case where you want to use the Any() extension method, like so:

这是您想要使用Any()扩展方法的情况,如下所示:

public IEnumerable<PlacementFile> getProcessingPlacementFiles()
{
    using (LinqModelDataContext db = new LinqModelDataContext())
    {
         return  from f in db.Placement_Files
                 where (!f.Pending && !db.Transactions
                                         .Any( t => t.FileID == f.ID)
                 orderby f.Import_Date descending                        
                 select f;
    }
}

#1