带linq和razor的内连接

Question

我有2个表，PostThr和PostEig。PostThr具有服务的作业日期，而PostEig具有该作业的可用worker插槽。

我想显示即将到来的工作的列表，并在每个工作下，可用的工人插槽。我怎么能这么做?

从我的理解，我应该写一个linq查询，但我不确定如何写查询。我可以分别为每个表编写查询，如下所示。在sql中，我会将其写为各种连接。然后，一旦我有了数据，我就可以在razor页面上的foreach循环中显示它们。

var today = DateTime.Now.Date;
var jobs = _context.PostThrs.Where(m => m.ThrDate > today
&& m.ThrText == "SERVICE DATE");
//zero is the FK...
var zero = "2102-01";
var slots = _context.PostEigs.Where(m => m.EigZero == zero
&& m.EigAgen == "OPEN");

2个表如下:

Table PostThr
ThrId | ZeroId | ThrDate | ThrText
Table PostEig
EigId | ZeroId | EigAgen | EigLoad

更新Master Zero Table

public class PostZero
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
[Display(Name = "0/")]
public string Zero { get; set; }
}

PostThr表

public class PostThr
{
[Key]
public int ThrId { get; set; }
[ForeignKey("PostZero")]
public string ThrZero { get; set; }
public int ThrDigit { get; set; }
[DataType(DataType.Date)]
public DateTime ThrDate { get; set; }
public string ThrTime { get; set; }
[Required]
public string ThrText { get; set; }
}

PostEig表

public class PostEig
{
[Key]
public int EigId { get; set; }
[ForeignKey("PostZero")]
public string EigZero { get; set; }
[Display(Name = "Number")]
public int EigDigit { get; set; }
[Required]
public string EigAgen { get; set; }
[Required]
public string EigRole { get; set; }
public string EigCont { get; set; }
public decimal EigLoad { get; set; }
public string EigNote { get; set; }
}

更新为VM

public class AgentClientIndexVM
{
public string Zero { get; set; }
public DateTime ThrDate { get; set; }
public string ThrTime { get; set; }
public string ThrText { get; set; }
public string EigAgen { get; set; }
public string EigRole { get; set; }
public decimal EigLoad { get; set; }
public string EigNote { get; set; }
}

Answer 1

如果我理解正确的话，表是通过PostZero连接的

var today = DateTime.Now.Date;
var jobs = 
from h in _context.PostThrs
join e in _context.PostEigs on h.ThrZero equals e.EigZero
where h.ThrDate > today && h.ThrText == "SERVICE DATE"
&& e.EigAgen == "OPEN"
select new AgentClientIndexVM
{
Zero = h.ThrZero,
ThrDate = h.ThrDate,
ThrTime = h.ThrTime,
ThrText = h.ThrText,
EigAgen = e.EigAgen,
EigRole = e.EigRole,
EigLoad = e.EigLoad,
EigNote = e.EigNote    
};