我正在努力了解如何在ASP.NET Web API v2项目中使用Entity Framework 6和SQL Server构造SQL查询。

我的表结构:Computers表是我的软件已清单的所有计算机的根表。它包含列ComputerID,该列被其他表用作外键。

我可以使用以下查询来检索一台计算机,其中包括我拥有的所有信息:

Computer computer = ComputersDbContext.Computers
                   ... more includes....
                    .Include("Computer_Win_Installed_Software")
                   ... more includes....
                    .Where(a => a.ComputerId == computerId)
                       .First(t => t.TenantId == tenantId);


如您所见,我还有另一个名为Computer_Win_Installed_Software的表,该表存储系统上安装的所有软件。看起来像这样:

IdentityKey | ComputerID (FK) | Softwarename     | EntryTimestamp
------------+-----------------+------------------+--------------
          1 |               1 | Some Software    | 1547241345
          2 |               1 | Another Software | 1547241345


EntryTimestamp是Unix时间戳,它对于每个清单运行都是唯一的,并且对该运行中发现的所有软件都相同。现在,如果系统再次进行盘点,则表将如下所示:

IdentityKey | ComputerID (FK) | Softwarename       | EntryTimestamp
------------+-----------------+--------------------+---------------
          1 |               1 | Some Software      |     1547241345
          2 |               1 | Another Software   |     1547241345
          3 |               1 | Some Software      |     1886454564
          4 |               1 | Another Software   |     1886454564
          5 |               1 | Even More Software |     1886454564


我想保留历史数据,所以我需要保留旧条目。

我的问题是我从上面的EF查询将在结果对象中返回所有这些条目。

如何更改查询,使其仅返回:

IdentityKey | ComputerID (FK) | Softwarename       | EntryTimestamp
------------+-----------------+--------------------+---------------
          3 |               1 | Some Software      |     1886454564
          4 |               1 | Another Software   |     1886454564
          5 |               1 | Even More Software |     1886454564


我考虑过使用2个查询:


第一个查询:我检查EntryTimestamp的最大值
第二个查询:是这样的:

Computer computer = ComputersDbContext.Computers
                        .Include("Computer_Win_Installed_Software")
                        .Where(a => a.ComputerId == computerId)
                        .Where(b => b.Computer_Win_Installed_Software.EntryTimestamp == EntryTimestamp)
                        .First(t => t.TenantId == tenantId);



但是,Intellisense立即对我尖叫:D

我还考虑过只选择列MAX()EntryTimestamp;但我什至不能在查询代码中使用b.Computer_Win_Installed_Software.EntryTimestamp

当我写:.Where(b => b.Computer_Win_Installed_Software时,它没有列出任何列作为可用选项。

我认为这是因为EF中的Computer_Win_Installed_Software类是ICollection<Computer_Win_Installed_Software>类型。对于其他所有与Computers表具有一对多关系的表,这也是同样的问题。
另一个表与Computers表具有1到1的关系,在这里我可以选择所有列。

我很困惑。

是的,我做了谷歌,但找不到任何帮助我的东西。去这里的正确方法是什么?

编辑:添加的模型

DBContext:

public class DbEntities : DbContext
{
    public virtual DbSet<Agent> Agents { get; set; }
    public virtual DbSet<Computer_Win_Installed_Software> ComputerWinInstalledSoftware { get; set; }
    public DbSet<Computer> Computers { get; set; }
}


EF计算机模型:

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace ProjectName
{
    using System;
    using System.Collections.Generic;

    public partial class Computer
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Computer()
        {
            this.Computer_Win_Installed_Software = new HashSet<Computer_Win_Installed_Software>();
        }

        public int ComputerId { get; set; }
        public int TenantId { get; set; }
        public virtual Agent Agent { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Computer_Win_Installed_Software> Computer_Win_Installed_Software { get; set; }

    }
}


适用于Computer_Win_Installed_Software的EF模型:

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace KysoWebApi
{
    using System;
    using System.Collections.Generic;

    public partial class Computer_Win_Installed_Software
    {
        public int ComputerId { get; set; }
        public string SoftwareName { get; set; }
        public Nullable<double> SoftwareVersion { get; set; }
        public Nullable<bool> IsServerSoftware { get; set; }
        public Nullable<int> SoftwareVendorId { get; set; }
        public string SoftwareIs32or64bits { get; set; }
        public int ComputerWinInstalledSoftwareEntryId { get; set; }
        public string EntryTimestamp { get; set; }

        public virtual Computer Computer { get; set; }
    }
}

最佳答案

因此,对于给定的ComputerICollection<Computer_Win_Installed_Software> Computer_Win_Installed_Software始终将包含所有EntryTimestampts的所有相关行。您不能(轻松地)filter包含的实体。

因此,只需返回过滤的实体:

var computer = ComputersDbContext.Computers
                .Where(a => a.ComputerId == computerId)
                .First(t => t.TenantId == tenantId);
var cwis = computer
            .Computer_Win_Installed_Software
                .Where(b => b.EntryTimestamp == EntryTimestamp);


当然,我不确定这是您实际要执行的操作,您可能会有XY Problem

您可以尝试不包括相关对象,而只是查询它们吗?

var computer = ComputersDbContext.Computers
                .Where(a => a.ComputerId == computerId)
                .First(t => t.TenantId == tenantId);
                .Where(b => b.Computer_Win_Installed_Software.EntryTimestamp == EntryTimestamp);

10-05 23:24