How to select data only with a unique email field?

Question

I need to select data from a table. The data has an Email field which is not unique. Now i only want to select the first item with an Email of its kind and ignore the others.

I tried:

var users = _context.Appointments.Where(p => (p.userId == userId))
            .Select(p => new MyUserModel
            {
                Id = p.Id,
                Email = p.User.Email
            });

return users.ToList();

I wanted to use Distinct(), but my elements are not unique, they have a different id.

How could I go about doing that?

Have you tried GroupBy with Count? https://stackoverflow.com/questions/7285714/linq-with-groupby-and-count — Jasen, Dec 08 '18 at 19:39
Just to clarify for myself - do you need to select the first record with definite email only? If yes, consider to group them by email and select the first element from every group. — Miamy, Dec 08 '18 at 19:41

Ousmane D. · Accepted Answer · 2018-12-08T20:15:08.053

1

However, if this field is repeated, then I do not need to select this element

You can group by the email and then perform a filter to retain the objects that don't repeat by email and then follow it with your current logic. Example:

var users = _context.Appointments
                .Where(p => p.userId == userId)
                .GroupBy(p => p.User.Email) 
                .Where(g => g.Count() == 1) // if there is only one object with this email, then retain it otherwise discard it
                .Select(g => g.First())
                .Select(p => new MyUserModel
                {
                    Id = p.Id,
                    ...
                    Email = p.User.Email
                    ...
                });

return users.ToList();

edited Dec 08 '18 at 20:15

answered Dec 08 '18 at 19:45

Ousmane D.

54,915
8
91
126

thanks, but after that I have the exception `System.NullReferenceException: 'Object reference not set to an instance of an object.'`. Before that, I could get all the meanings. Maybe something else is needed? – senjust Dec 08 '18 at 20:13
@senjust mhmm, try again with the edited code and let me know where the exception occurs. – Ousmane D. Dec 08 '18 at 20:17
I used the edited code. the exception I have in the line `return users.ToList();` is now users is empty... – senjust Dec 08 '18 at 20:24
I use IEnumerable, maybe the problem is this? – senjust Dec 08 '18 at 20:27
@senjust the `users` list will only be _empty_ if `_context.Appointments` returned no records or if every object from the source had a duplicate email elsewhere in the source which would then mean the `Where` clause in the pipeline above filters it out. I don't see any reason why the code I've suggested is returning an empty list apart from the aforementioned reasons, what I recommend is to debug your program and see what's going on. – Ousmane D. Dec 08 '18 at 20:32

How to select data only with a unique email field?

1 Answers1