c# linq to sql join problem

Question

i am trying to do

using (UserManagementDataContext context = new UserManagementDataContext())
            {
                var users = from u in context.Users
                            where u.UserEMailAdresses.EMailAddress == "[email protected]"
                            select u;
                return users.Count();
            }

however, when i get to:

using (UserManagementDataContext context = new UserManagementDataContext())
            {
                var users = from u in context.Users
                            where u.UserEMailAdresses.

i do not get offered the EMailAddress name, but rather some neutral default-looking list of options in intelisense.

what am i doing wrong?

table Users

ID  bigint
NameTitle   nvarchar(64)
NameFirst   nvarchar(64)
NameMiddle  nvarchar(64)
NameLast    nvarchar(64)
NameSuffix  nvarchar(64)
Status  bigint
IsActive    bit

table UserEMailAddresses

ID  bigint
UserID  bigint
EMailAddress    nvarchar(256)
IsPrimary   bit
IsActive    bit

obviously, 1 user can have many addresses and so Users.ID and UserEMailAddresses.UserID have a relationship between them: 1 to MANY.

Answer 1

UserEMailAdresses is a collection of email addresses, so it doesn't make sense to call EMailAddress on it.

You have to check if there is an email address in the collection that matches the one you're looking for :

var users = from u in context.Users
            where u.UserEMailAdresses.Any(e => e.EMailAddress == "[email protected]")
            select u;