Linq select parent/parents record with matching child items

Question

Using ef core and trying to select parent items according to criteria in the child item.

If I was doing this in sql I would

 declare @UserName nvarchar(200) set @UserName = 'al65272' declare @ClientId int set @ClientId = 32 select u.* from Users u inner join ClientUsers cu on u.Id = cu.UserId where NormalizedUserName = upper(@UserName) and cu.ClientId=@ClientId

I thought I could do something like this:

var userByUserName = _applicationDbContext.Users.FirstOrDefault(x => x.NormalizedUserName == userName.ToUpper() && x.ClientUsers.Contains(new ClientUser {ClientId = client.Id, User = x}));

But obviously wrong as does not return anything.

Can anyone point me in the right direction?

In the provided SQL you address two tables AspNetUsers and ClientUser but in the LINQ you address Users. Could you edit your post with the proper data. — Azzy Elvul
– Azzy Elvul, Commented Jan 28, 2020 at 16:04

Johnathan Barclay · Accepted Answer · 2020-01-28 16:34:25Z

I think this would work for you :

var userByUserName = _applicationDbContext.Users.FirstOrDefault(x => x.NormalizedUserName == userName.ToUpper() && x.ClientUsers.Any(c => c.ClientId == client.Id));

Collectives™ on Stack Overflow

Linq select parent/parents record with matching child items

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related