c# - 如何使用 Entity Framework 和 Lambda 表达式编写下面的 mysql 查询？

Question

SELECT
`Distinct1`.`UserId`, 
`Distinct1`.`FirstName`, 
`Distinct1`.`LastName`, 
`Distinct1`.`EmailAddress`, 

FROM ( SELECT DISTINCT 
`Extent2`.`UserId`, 
`Extent2`.`FirstName`, 
`Extent2`.`LastName`, 
`Extent2`.`EmailAddress`, 

    FROM `AssistantTo` AS `Extent1` 
    INNER JOIN `User` AS `Extent2` ON `Extent1`.`AssistantId` = `Extent2`.`UserId`
    INNER JOIN `CustomTagUser` as `Extent3` ON `Extent3`.`UserId` = `Extent2`.`UserId`
 WHERE `Extent1`.`OwnerId` = 274 AND `Extent3`.`CustomTagId` = 114
 ) AS `Distinct1`

这是我的表结构:

我尝试使用以下查询，但它给了我错误。

 var assistants =
                    dbContext.AssistantsTo
                        .Include(x => x.Assistant)
                        .Include(x => x.Assistant.CustomTagUser)
                        .Where(at =>
                            at.OwnerId == currentUser.UserId
                            && (at.Assistant.CustomTagUser.Count(y => y.CustomTagId == filter) > 0)
                            )
                        .Select(at => at.Assistant)
                        .Distinct()
                        .ToList();

错误:{“‘where 子句’中存在未知列‘Extent1.AssistantId’”}

基本上我对提供过滤器有疑问

`Extent3`.`CustomTagId` = 114

我认为我们可以使用any()，但我在处理大数据和mysql时使用any()的经验很差。

模型类

public class AssistantTo
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int AssistantToId { get; set; }
        public int AssistantId { get; set; }
        [ForeignKey("AssistantId")]
        public virtual User Assistant { get; set; }
        public int OwnerId { get; set; }
        [ForeignKey("OwnerId")]
        public virtual User Owner { get; set; }
    }

    public class CustomTagUser
    {
        [Key]
        public int CustomTagUserId { get; set; }
        public int CustomTagId { get; set; }
        public int UserId { get; set; }
        public DateTime CreatedOn { get; set; }
        [ForeignKey("CustomTagId")]
        public virtual CustomTags CustomTags { get; set; }
        [ForeignKey("UserId")]
        public virtual User User { get; set; }
    }


[Table("User")]
    public class User
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int UserId { get; set; }

        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string EmailAddress { get; set; }
        public string Password { get; set; }
        .....   
        public ICollection<CustomTagUser> CustomTagUser { get; set; }   
    }
}

Basically I have main problem with applying this part in EF

AND Extent3.CustomTagId = 114

Answer 1

var assistants =
                    dbContext.AssistantsTo
                        .Include(x => x.Assistant)
                        .Include(x => x.Assistant.CustomTagUser)
                        .Where(at =>
                            at.OwnerId == currentUser.UserId && 
                            (                               
                                at.Assistant.CustomTagUser.Select(x => x.CustomTagId).Contains(filter)
                            )
                        .Select(at => at.Assistant)
                        .Distinct()
                        .ToList();      

试试这个...