Linq complex - Enumeration Performance

Question

Have got a method which returns IEnumerable<User> which I have been using Linq / Entity Framework / SQL Server to return results.

I came across a difficult conditional scenario, which was much more easily solved in C# iterating on the web server (at the end of a chain of linq statements, just before returning the data to the client):

public IEnumerable<User> ReturnUsersNotInRoles()
{
    IQueryable<User> z = (from users
                    //...many joins..conditions...
                    ).Distinct().Include(x => x.RoleUserLinks).ToList()


    IEnumerable<User> list = new List<User>();

    foreach (User user in z)
    {
        bool shouldReturnUser = true;

        foreach (var rul in user.RoleUserLinks)
        {
            if (rul.LinkStatusID == (byte)Enums.LinkStatus.Added)
                shouldReturnUser = false;
        }
        if (shouldReturnUser)
            list.Add(user);
    }

    return list;
}

Question: In C# is there a more performant / less memory overhead way of doing this?

Am only bringing back the entities I need from Linq. There is no N+1 scenario. Performance currently is excellent.

I realise that ideally I'd be writing this in SQL / Linq, as then SQL Server would do its magic and serve me the data quickly. However I'm balancing this with a potentially v.hard query to understand, and excellent performance currently with iterating, and the ease of understanding the C# way.

sinelaw · Accepted Answer · 2013-01-09T20:54:27.667

How about this:

public IEnumerable<User> ReturnUsersNotInRoles()
{
    var z = (from users
                    //...many joins..conditions...
                    ).Distinct().Include(x => x.RoleUserLinks);

    var addedLinkStatusID = (int)Enums.LinkStatus.Added;
    return z.Where(user => 
               false == user.RoleUserLinks.Any(link => link.LinkStatusID == addedLinkStatusID))
            .ToList();
}

This should run completely as a SQL query - you could make the first part (z) materialize by adding a .ToList() at the end of the line that defines it.

By the way, regarding your question "In C# is there a more performant / less memory overhead way of doing this?" - well, firstly you can add a break statement right after you set shouldReturnUser = false;.

Secondly, I prefer using the LINQ primitives whenever possible whether or not I'm working with a database:

When used correctly, implementation using LINQ methods will probably be as fast or faster than anything you can write.
More importantly, they promote functional, stateless programming over stateful, bug-prone programming.
Also, if you are working with a database you have the bonus of being able to decide whether or not you want the code to run as a SQL query - all you have to do is decide where to materialize.

Thank you sinelaw. Agreed with all your points. Especially thanks for seeing how to do this in linq - it is great to be able to express in a functional manner simply! — Dave Mateer, Jan 10 '13 at 11:15

score 0 · Answer 2 · answered Jan 09 '13 at 20:43

0

Your loop is equivalent to the following LINQ query - I find it easier to understand than the loop and it allows for complete execution on the server when combined with the first part of the query.

var linkStatusAdded = (Byte)Enums.LinkStatus.Added;

return z.Where(user => user.RoleUserLinks
                           .All(rul => rul.LinkStatusID != linkStatusAdded))
        .ToList();

answered Jan 09 '13 at 20:43

Daniel Brückner

59,031
16
99
143

Thanks Daniel - appreciated. – Dave Mateer Jan 10 '13 at 11:15

Linq complex - Enumeration Performance

2 Answers2