Dynamically build Entity Framework Select queries

Question

I have a query that looks a bit like this:

context.Users
.........
.Select(user => new UserDTO
{
    UserName = user.Name,
    DataList = context.Data.Where(x => x.UserId == user.Id).ToList(),
    Emails = context.Email.Where(x => x.UserId == user.Id).ToList(),
    BankAccounts = context.BankAccount.Where(x => x.UserId == user.Id).ToList(),
    .....
}

The problem with this is that this all creates a massive query, because all those subselects are seperate lists. The only reason these selects are there is because sometimes I need to filter on them. So I was wondering if it is possible to build them dynamically, like this:

var query = context.Users
    .........
    .Select(user => new UserDTO
    {
        UserName = user.Name,
        .....
    }

if (EmailFilter) query.Select.Add(Emails, context.Email.Where(x => x.UserId == user.Id).ToList());

if (AccountsFilter) query.Select.Add(BankAccounts, context.BankAccount.Where(x => x.UserId == user.Id).ToList());

Not exactly that of course but you get the idea hopefully.

Firstly, you should try using navigation properties. The above code will fire 3 queries to hydrate the DTO, whereas it can be done in a single query. Secondly, which properties do you need to filter on which ones do you need to display or send back to the user. You can easily add conditional Where clauses in the above piece of code if you don't need to display the navigation properties. — Amanvir Mundra
– Amanvir Mundra, Commented Apr 27, 2017 at 14:11

Ofir Winegarten · Accepted Answer · 2017-04-27 15:05:23Z

1

If you don't have navigation properties then I think you can use the flag itself inside the Where like this:

Select(user => new UserDTO
{
    UserName = user.Name,
    DataList = context.Data.Where(x => x.UserId == user.Id).ToList(),
    Emails = context.Email.Where(x => EmailFilter && x.UserId == user.Id).ToList(),
    BankAccounts = context.BankAccount.Where(x => AccountsFilter && x.UserId == user.Id).ToList(),
    .....
}

answered Apr 27, 2017 at 15:05

Ofir Winegarten

9,3852 gold badges23 silver badges27 bronze badges

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

1 Comment

user2657943 Over a year ago

Oh god I can't believe I didn't think of that, it's so easy!

hbulens · Accepted Answer · 2017-04-27 13:25:05Z

You can dynamically build your projections from outside the DbContext. If you look into expressions you will notice how you can create your select query before calling the EF methods.

For instance what I use is something like this:

internal class TenantFullProjector : IProjector<Tenant, TenantProjection>
{
    public Expression<Func<Tenant, TenantProjection>> GetProjection()
    {
        return (x) => new TenantProjection()
        {
            Code = x.Code,
            DatabaseId = x.DatabaseId,
            Id = x.Id,               
        };
    }
}

I have a IProjector interface which is used by my repository layer. What matters is that it returns an Expression that ultimately will map the Tenant domain model into a TenantProjection projection model. I can create as many different versions as I want as long as I implement the interface correctly. This example for instance maps all columns whereas other projectors might only select a subset of columns.

In the repository layer I will do something like this:

return query.Select(myProjectionExpression);

...where query equals ctx.Set().

You can do exactly the same thing for your where statements.

Alexander Petrov · Accepted Answer · 2017-04-27 13:31:40Z

0

Maybe something like this:

var query = context.Users
    .Select(user => new
    {
        UserName = user.Name,
        Emails = EmailFilter ? context.Email.Where(x => x.UserId == user.Id).ToList() : null,
        BankAccounts = AccountsFilter ? context.BankAccount.Where(x => x.UserId == user.Id).ToList() : null,
        // and so on
    });

Depending on the filter values you get value lists or null in the result properties.

answered Apr 27, 2017 at 13:31

Alexander Petrov

14.4k2 gold badges23 silver badges56 bronze badges

2 Comments

user2657943 Over a year ago

This doesn't work. I don't think Entity Framework understands ternary if's like that

Alexander Petrov Over a year ago

@user2657943 - This trick works in linq2sql. Need to check in EF.

Collectives™ on Stack Overflow

Dynamically build Entity Framework Select queries

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related