Can SELECT and WHERE LINQ clause be combined?

Question

Here is what I have done to Select users into my model and then remove all the null records:

        model.Users = users
            .Select(u =>
        {
            var membershipUser = Membership.GetUser(u.UserName);
            return membershipUser != null
                ? new UserBriefModel
                {
                    Username = u.UserName,
                    Fullname = u.FullName,
                    Email = membershipUser.Email,
                    Roles = u.UserName.GetRoles()
                }
                : null;
        })
            .Where(u => u != null)
            .ToList();

Wondering if there is a way to combine the SELECT and WHERE clause.

I tried:

        model.Users = users
            .Select(u =>
            {
                var membershipUser = Membership.GetUser(u.UserName);
                if (membershipUser != null)
                    return new UserBriefModel
                    {
                        Username = u.UserName,
                        Fullname = u.FullName,
                        Email = membershipUser.Email,
                        Roles = u.UserName.GetRoles()
                    };
            })
            .ToList();

But the intellisense suggest a syntax error. Which forces me to add a return null statement:

        model.Users = users
            .Select(u =>
            {
                var membershipUser = Membership.GetUser(u.UserName);
                if (membershipUser != null)
                    return new UserBriefModel
                    {
                        Username = u.UserName,
                        Fullname = u.FullName,
                        Email = membershipUser.Email,
                        Roles = u.UserName.GetRoles()
                    };
                return null;
            })
            .ToList();

So what is the correct way to write this SELECT statement so only valid records are selected into my model?

Answer 1

Conceptually you actually have three operations here:

1. project the user name to a membership user
2. filter out null membership users
3. project the membership users to a model

That is how your query should be looking. Your very first query has already tried to combine steps 1 and 3 together, but you're struggling because step two really should be in the middle of the two, and the hoops that you need to jump through to get around that aren't pretty.

The query actually becomes simpler and readable (and becomes idiomatic LINQ code) when you represent all three operations individually.

model.Users = users
    .Select(user => new
    {
        user,
        membershipUser = Membership.GetUser(user.UserName)
    })
    .Where(pair => pair.membershipUser != null)
    .Select(pair => new UserBriefModel
    {
        Username = pair.user.UserName,
        Fullname = pair.user.FullName,
        Email = pair.membershipUser.Email,
        Roles = pair.user.UserName.GetRoles()
    })
    .ToList();

This is a query that can also be written more effectively in query syntax:

model.Users = from user in users
                let membershipUser = Membership.GetUser(user.UserName)
                where membershipUser != null
                select new UserBriefModel
                {
                    Username = user.UserName,
                    Fullname = user.FullName,
                    Email = membershipUser.Email,
                    Roles = user.UserName.GetRoles()
                };

As for the literal question of whether or not you can combine the projecting an filtering into a single LINQ operation, it is certainly possible. It would be an inappropriate solution to the problem, but the use of SelectMany can allow you to filter and project at the same time. This can be done by projecting the item to either a one item sequence containing the value that you want to project it to or an empty sequence based on the predicate.

model.Users = users
    .SelectMany(u =>
    {
        var membershipUser = Membership.GetUser(u.UserName);
        return membershipUser != null
            ? new[]{ new UserBriefModel
            {
                Username = u.UserName,
                Fullname = u.FullName,
                Email = membershipUser.Email,
                Roles = u.UserName.GetRoles()
            }}
            : Enumerable.Empty<UserBriefModel>();
    }).ToList();

Of course, every time you use this code, a kitten is killed. Don't kill kittens; use the earlier query instead.

Answer 2

I don't think this is possible, Select will map everything 1-1 as far as I know...if you're trying to filter you will need a Where.

edit edit: I no longer believe SelectMany can do it (As Servy has shown).