CODEWITHSUNDEEP
c#linqlinq-to-entities
wingyip
wingyip

Reputation: 3536

Linq select parent/parents record with matching child items

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?

Upvotes: 1

Views: 840

Answers (1)

ilyes
ilyes

Reputation: 372

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));

Upvotes: 2

Related Questions