将SQL查询转换为LINQ #2

Question

我有一个包含两个表的数据库：User( idUser，firstName，lastName)和Document( idDoc，title，expirationDate，UserDoc)，UserDoc是idUser的外键

此外，我的C#代码中有两个类：

 public class DocumentUI
{
    public string Title { get; set; }
    public string Description { get; set; }
    public DateTime ExpirationDate { get; set; }
    public int UserDoc { get; set; }
    public UserUI User { get; set; }
}

和

 public class UserUI
{
    public int IdUser { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

}

我需要转换以下查询：

select a.*, (select u.lastName from Users as u where u.idUser = a.UserDoc),(select u.firstName from Users as u where u.idUser = a.UserDoc)

将文档转换为where () <= a.expirationDate

我试过了，这是很好的结果：

            var documents = (from d in DocumentDAO.GetDocument()
                             join k in UserDAO.GetUsers()
                             on d.UserDoc equals k.IdUser
                             where (DateTime.Now <= d.ExpirationDate)
                             select new DocumentUI
                             {
                                 Title = d.Title,
                                 Description = d.Description,
                                 DateOfAdd = d.DateOfAdd,
                                 ExpirationDate = d.ExpirationDate,
                                 UserDoc = d.UserDoc,
                                 User = new UserUI { FirstName = k.FirstName, LastName = k.LastName}

                             }).ToList();

但是在调试器中，我注意到它没有看到任何文档(我检查并有一个应该返回)。

我也试过这样做：

            var documents = (from d in DocumentDAO.GetDocument()
                             from k in UserDAO.GetUsers().Where(k =>k.IdUser == d.UserDoc
                             where (DateTime.Now <= d.ExpirationDate)
                             select new DocumentUI
                             {
                                 Title = d.Title,
                                 Description = d.Description,
                                 DateOfAdd = d.DateOfAdd,
                                 ExpirationDate = d.ExpirationDate,
                                 UserDoc = d.UserDoc,
                                 User = new UserUI { FirstName = k.FirstName, LastName = k.LastName}

                             }).ToList();

但它给了我同样的结果

第三条路：

            var documents = (from d in DocumentDAO.GetDocument()
                             from k in UserDAO.GetUsers()
                             where ((DateTime.Now <= d.ExpirationDate) && (k.IdUser == d.UserDoc))
                             select new DocumentUI
                             {
                                 Title = d.Title,
                                 Description = d.Description,
                                 DateOfAdd = d.DateOfAdd,
                                 ExpirationDate = d.ExpirationDate,
                                 UserDoc = d.UserDoc,
                                 User = new UserUI { FirstName = k.FirstName, LastName = k.LastName}

                             }).ToList();

仍然没有，但是如果我删除(k.IdUser == d.UserDoc)，它会向我展示这一文档和所有用户。

使用您的意见和想法更新--我写了以下文章：

 var now = DateTime.Now;
        var documents = DocumentDAO.GetDocument()
                    .Where(d => d.ExpirationDate > now)
                    .Select(d => new DocumentUI
             {
                 Title = d.Title,
                 Description = d.Description,
                 DateOfAdd = d.DateOfAdd,
                 ExpirationDate = d.ExpirationDate,
                 UserDoc = d.UserDoc,
                 User = new UserUI {
                     FirstName =UserDAO.GetUsers().First().FirstName,
                     LastName = UserDAO.GetUsers().First().LastName
                           }
             }).ToList(); 

但我仍然不检查UserDoc是否等于IdUser。我该把这个加在哪里呢？

Answer 1

vat now = DateTime.Now;
var result = DocumentDAO.GetDocument()
                        .Where(d => d.ExpirationDate > now)
                        .Select(d => new {
                                           doc = d,
                                           firstname = d.User.FirstName,
                                           lastname = d.User.LastName
                                       });

更新

为了返回DocumentUI列表，请用以下语句替换Select()语句

.Select(d => new DocumentUI
                 {
                     Title = d.Title,
                     Description = d.Description,
                     DateOfAdd = d.DateOfAdd,
                     ExpirationDate = d.ExpirationDate,
                     UserDoc = d.UserDoc,
                     User = new UserUI { 
                                   FirstName = d.User.FirstName, 
                                   LastName = d.user.LastName
                               }
                 }).ToList(); 

Answer 2

所以你应该有这样的东西：

var result = DocumentDAO.Where(d => d.expirationDate >= DateTime.Now).Select(d =>
                     new {
                       Document = d,
                       User = UserDAO.FirstOrDefault(u => u.idUser = d.UserDoc),
                       FirstName = User.firstName,
                       LastName = User.lastName,  
                     });

如果您使用EF作为ORM框架，并且在实体之间配置了适当的关系，则可能应该在Document中具有User属性。在这种情况下，这将更加容易：

var result = context.Documents.Where(d => d.expirationDate >= DateTime.Now).Select(d =>
                         new {
                           Document = d,
                           FirstName = d.User.firstName,
                           LastName = d.User.lastName,  
                         });