如何用 LINQ 语法编写以下内容

Question

只是想知道如何用 LINQ 语法编写以下 SQL

select * from COESDetails
where COESNo in
(select distinct COESNo from COESDefects)

流畅或查询表达式，或两者兼而有之

COESDefects具有要COESDetails的导航属性

public class COESDefects
{
    public int Id { get; set; }
    public int COESNo { get; set; }
    public string Comments { get; set; }       
    public virtual COESDetails COESDetails { get; set; }
}
public class COESDetails
{
    public COESDetails()
    {           
        COESDetailsCOESDefects = new List<COESDefects>();         
    }
    public int COESNo { get; set; }
    public int Postcode { get; set; }      
    public virtual ICollection<COESDefects> COESDetailsCOESDefects { get; set; }            
}

谢谢

Answer 1

试试这个：

var query =
    from x in context.COESDetails
    join y in context.COESDefects on x.COESNo equals y.COESNo into ys
    where ys.Any()
    select x;

Answer 2

我觉得它会像这样简单：

yourDbContext.COESDetails.Where(detail => detail.COESDetailsCOESDefects.Any()).ToList();

如果我错了，请纠正我。

Answer 3

有时，如果查询很复杂，直接执行它会更容易，例如

IEnumerable<COESDetails> myResults = dbConext.ExecuteQuery<CCOESDetails>("select * from COESDetails where COESNo in (select distinct COESNo from COESDefects)")