Entity Framework condition in select

Question

I have the following database code:

static IEnumerable<dynamic> GetData(bool withchildren) {
    using (var model = new testEntities()) {
        var res = default(IQueryable<dynamic>);
        if (withchildren) {
            res = model.UserSet
                .Where(u => u.name != "")
                .Select(u => new {
                    Name = u.name,
                    Email = u.email,
                    Groups = u.GroupSet.Select(g => new {
                        Name = g.name,
                        Id = g.Id
                    })
                });
        } else {
            res = model.UserSet
                .Where(u => u.name != "")
                .Select(u => new {
                    Name = u.name,
                    Email = u.email
                });
        }
        return res.ToList()
    }
}

I would like to shrink the code and write it like this:

static IEnumerable<dynamic> GetData(bool withchildren) {
    using (var model = new testEntities()) {
        var res = default(IQueryable<dynamic>);
        res = model.UserSet
            .Where(u => u.name != "")
            .Select(u => {
                dynamic item = new { 
                    Name = u.name,
                    Email = u.email
                };
                if(withchildren) {
                    item.Groups = u.GroupSet.Select(g => new {
                        Name = g.name,
                        Id = g.Id
                    });
                }
                return item;
            });
        return res.ToList();
    }
}

But Visual Studio complains, that it cannot convert the lambda expression into an expression tree.

My question is, is there a way to accomplish that with the Entity Framework and Linq? I really wouldn't want to use ADO.net directly.

Maybe there is even a better version to shrink it, than the code that I imagine.

Here is a related question with Linq-To-Objects.

EDIT

Before someone asks, I use dynamic in the example code to make it a bit easier and faster.

EDIT 2

My goal with this approach is, to only query the fields I need to improve performance. Check http://www.progware.org/Blog/post/Slow-Performance-Is-it-the-Entity-Framework-or-you.aspx.

At the moment we use something like

static IEnumerable<dynamic> GetData(bool withchildren) {
    using (var model = new testEntities()) {
        var res = default(IQueryable<dynamic>);
        res = model.UserSet
            .Where(u => u.name != "")
            .ToList();
        return res;
    }
}

And the performance is, according to Glimpse, horrible.

EDIT 3

Short side note, I made up some quick and dirty code. That is, why the foreach at the end was not needed. The actual code is not available at the moment.

What if you use the conditional operator Groups = !withchildren ? null : u.GroupSet.Select....? — juharr
– juharr, Commented Dec 30, 2014 at 14:40
Do you know for sure this is your performance problem? Do you need to retrieve the entire table here? Perhaps you can use paging or filter your data before retrieving them? — Rob Tillie
– Rob Tillie, Commented Dec 30, 2014 at 14:55
@RobTillie I used Glimpse to check for that. And yes, the database access is the bottle neck. For that purpose I googled a bit and found, that you can increase performance by just selecting the needed fields. — Knerd
– Knerd, Commented Dec 30, 2014 at 14:57

JLRishe · Accepted Answer · 2014-12-30 14:57:31Z

2

Is there any reason you couldn't use:

res = model.UserSet
    .Where(u => u.name != "")
    .Select(u =>  new { 
            Name = u.name,
            Email = u.email,
            Groups = withchildren 
                ? u.GroupSet.Select(g => new {
                     Name = g.name,
                     Id = g.Id
                  })
                : null;
            })
        };

or perhaps:

res = model.UserSet
    .Where(u => u.name != "")
    .ToList()                        // ToList() here to enumerate the items
    .Select(u => {
        dynamic item = new { 
            Name = u.name,
            Email = u.email
        };
        if(withchildren) {
            item.Groups = u.GroupSet.Select(g => new {
                Name = g.name,
                Id = g.Id
            });
        }
        return item;
    });

One approach that would allow you to eliminate some code would be:

var res = model.UserSet.Where(u => u.name != "");
if (withchildren) {
    res = res.Select(u => new {
             Name = u.name,
             Email = u.email,
             Groups = u.GroupSet.Select(g => new {
                Name = g.name,
                Id = g.Id
             })
          });
} else {
    res = res.Select(u => new {
            Name = u.name,
            Email = u.email
        });
}

edited Dec 30, 2014 at 14:57

answered Dec 30, 2014 at 14:41

JLRishe

102k19 gold badges138 silver badges171 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Knerd Over a year ago

The second part is not ok, because I want to reduce the execution time of the database queries and I want to shorten the queries generated by EF. The first option looks good :)

JLRishe Over a year ago

@Knerd I see. Makes sense. Just a question - isn't the foreach loop at the end of your method redundant? Can't you return res directly?

Knerd Over a year ago

Let me work through my code... Just a quick thing, the actual code is at home on a different computer, so I made some code quick and dirty up ;)

Victor · Accepted Answer · 2014-12-30 15:07:46Z

1

One of the most requested by community feature is support of multi-line expressions in EF, however so far, you can use only conditional operator "?:" as well as wrap result in one common type, so both your results will have to have "Groups" field.

Also there are an extensions to linq-providers such as https://www.nuget.org/packages/LinqKit/ , however they are based on own conventions so any developer should study it in depth before taking advance in applying and supporting code written upon that extensions.

edited Dec 30, 2014 at 15:07

answered Dec 30, 2014 at 14:42

Victor

6364 silver badges12 bronze badges

Collectives™ on Stack Overflow

Entity Framework condition in select

EDIT

EDIT 2

EDIT 3

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

EDIT

EDIT 2

EDIT 3

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related