Filter joined table in where statement instead of subquery-CodePudding

This is my database models with EntityFramework for MySQL

public class User
    {
        public Guid id { get; set; }
        public string? firstName { get; set; }
        public string? lastName { get; set; }
        public string? email { get; set; }
        public string? displayName { get; set; }
        public virtual List<UserRequest>? userRequests { get; set; }
    }

public class UserRequest
    {
        public int id { get; set; }
        public string? action { get; set; }
        public string? domain { get; set; }
        public string? pathAccessing { get; set; }
        public string? applicationName { get; set; }
        public string? type { get; set; }
        public DateTime createdDate { get; set; }
        public Guid userId { get; set; }
        public User? user { get; set; }
    }

In this models, 1 user can have multiple userRequest. So I will like to query whether user access this domains before.

In SQL statement, this will be my statement

select * from User u 
inner join UserRequest ur on ur.userId = u.id
where ur.domains in ("http://localhost", "http://localhost.dev")

This is my statement in linq

var domainsList = new string[] {"http://localhost", "http://localhost.dev"};
     var result = _DBContext.User
                   .Include(q=>
                      q.userRequests             
                   ).Where((t)=> t.userRequests.any(u=>domainsList.Contains(u.domain)));

Generated SQL statement

SELECT `u`.`id`, `u`.`displayName`, `u`.`email`, `u`.`firstName`, `u`.`lastName`, `u1`.`id`, `u1`.`action`, `u1`.`applicationName`, `u1`.`createdDate`, `u1`.`domain`, `u1`.`pathAccessing`, `u1`.`type`, `u1`.`userID`
      FROM `user` AS `u`
      LEFT JOIN `userRequest` AS `u1` ON `u`.`id` = `u1`.`userID`
      WHERE EXISTS (
          SELECT 1
          FROM `userRequest` AS `u0`
          WHERE (`u`.`id` = `u0`.`userID`) AND `u0`.`domain` IN ('http://localhost', 'http://localhost.dev'))
      ORDER BY `u`.`id`

The generated sql statement is using subquery instead of my expected simple where expression. My database is not large so subquery is slower than join where expression in my case.

Is there a way I can rewrite my linq statement to have dotnet generate the SQL statement similar to what I expect? Or this is not possible in Linq?

Found a few similar question, but none is trying to achieve the end result I'm hoping for.

sorry for my bad english

CodePudding user response：

Remove include and use join. Please follow the sample query syntax

    var query = from u in User
            join ur in UserRequest
                on u.ID equals ur.userID
                where (new string[] {'exampleofdomainList'}).Contains(ur.domain)
            select new { u, ur}