LINQ初学者，表联接查询文档版本 [英] LINQ beginner, table joining query for document version

问题描述

我有一个Document表和一个Version表.两者具有相同的信息.每当创建文档时，都会将其插入到文档表中.每当编辑该文档时，都会在版本"表中添加一行.

I have a Document table and a Version table. Both have identical information. Whenever a document is created it is inserted into the document table. Whenever that document is then edited a line is added to the Versions table.

它具有一个字段dateApproved，在批准之前将一直为null.我目前正在MVC 4批准页面上工作，我以前从未接触过linq，因此需要在linq中完成.

It has a field dateApproved which will be null until it is approved. I am currently working on a MVC 4 Approval page and I have never touched linq before and it needs to be done in linq.

我如何才能将这两个表的文档/版本联接在一起，并且仅显示dateApproved为null的项目?

How can I join these two tables Document/Version and only show items where dateApproved is null?

from v in Versions
select v

from v in Document
select v

编辑#1

用户添加一个全新的文档，然后将该文档添加到文档表中.此时，该文档需要批准. 文档"表中的dateApproved字段为空.假设该文档获得批准，然后用户进行了更改，并且由于该文档已经存在，因此在版本"表中添加了一行.现在，两个表中都存在该文档，原始文档的修订版0带有dateApproved，版本表的修订版1带有dateApproved为null.

A user adds a brand new document, this document is then added to the document table. At this point the document needs approval. The dateApproved field in the Documents table is null. Lets say this document gets approved, then the user makes a change and since this document already exists a line is added to the Versions table. Now this document exists in both tables the original document having a revision 0 with a dateApproved, and the Versions table have a revision 1 with a dateApproved as null.

我需要的是dateApproved为null的文档(如果此null位于Documents表或Versions表中).批准后，我们​​将行保留在版本"表中，并使用批准的版本更新文档"表中的行.

What I need is the documents where the dateApproved is null weather this null is in the Documents table or the Versions table. After it is approved we leave the line in the Version table, and update the line in the Documents table with the approved version.

主要主键是DocumentID.

The key primary key is DocumentID.

EDIT#2

感谢Peter Kiss，它现在显示所有需要批准的文件.我正在遇到另一个打h.它返回3个结果是正确的. 2个文件是全新文件，其中1个已被编辑.

Thanks to Peter Kiss it is now displaying all the files that need to be approved. There is one more hiccup I am running into. It is returning 3 results which is correct. 2 files are brand new and 1 has been edited.

2个全新文件从文件表中正确显示了信息.编辑后的文件正在显示来自文档表的信息，但是我需要来自修订表的信息.如果项目在版本表中存在，是否可以使它返回版本表信息，如果版本表中不存​​在该项目，则是否可以返回文档表信息(即全新).

The 2 brand new files it displays the information correctly as from the documents table. The edited file it is displaying the information from the documents table but I need the info from the revisions table. Is it possible to make it return the version table information if the item exists in the version table and return the document table information if it does not exist in the version table (ie brand new).

希望我正确地解释了.

推荐答案

var documents = 
    ctx.Documents
         .Where(x => x.dateApproved == null 
                     || x.Versions.Any
                           (y => y.dateApproved == null));

documents变量将仅包含具有未批准版本的那些文档.然后，当您遍历此集合时，当前文档可以通过名为Versions的导航属性来到达其Versions(全部).在该集合中，您可以过滤未批准的版本.

The documents variable will contain only those documents which are having unapproved versions. Then when you iterating through this collection the current document can reach it's Versions (all) through a navigation property called Versions. In that collection you can filter the unapproved versions.

只有在您的上下文中正确设置了映射(也称为外键)的情况下，所有这些操作才会发生.

All this only happens when the mapping is set up correctly (aka foreign keys) in your context.

class DocumentToApprove
{
    public string Name { get; set; }
    /* other stuff */
    public int version { get; set; }
}

var documents = ctx.Documents
          .Where(x => x.dateApproved == null 
                      || x.Versions.Any(y => y.dateApproved == null));

var toApprove = from d in documents
            let v = d.Versions.FirstOrDefault(y => y.Approved == null)
        select new DocumentToApprove
                   {    Name = d.Name
                                ,
                                /* other stuff */
                                Version = v == null ? 1 : v.VersionNumber
                            };

这篇关于LINQ初学者，表联接查询文档版本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！