CODEWITHSUNDEEP
c#linqentity-frameworkiqueryable
Simon Francesco
Simon Francesco

Reputation: 1561

How can I condense the following Linq query into one IQueryable

I have an EntityFramework model that has User entity that has an EntityCollection of Organisations.

For a particular User I am trying to write a Linq Query to return the names of the organisations that the user belongs where that query will hit the db only once.

My problem is that I cannot see how to write this query without having to materialise the user first then query the users organisation collection.

I would like to try and write one query that hits the db once.

What I have so far:

var orgNames = context.Users
    .Where(u => u.LoweredUserName == userName.ToLower())
    //materialises user
    .FirstOrDefault()
    .Organisations
    //second hit to the db
    .Select(o => o.Name);

What I was psuedo aiming for but cannot see the wood for the trees:

orgNames = context.Users
    .Where(u => u.LoweredUserName == userName.ToLower())
    //don't materialise but leave as IQueryable
    .Take(1)
    //The problem: turn what the query sees as possibly multiple
    // (due to the Take method) EntityCollection<Organisation> into a List<String>
    .Select(u => u.Organisations.Select(o => o.Name));

I have looked at aggregates but I seem to be going in circles :)

Upvotes: 1

Views: 293

Answers (2)

Paul Creasey
Paul Creasey

Reputation: 28824

I'm assuming that Lowered User name is unique, otherwise the query would be fairly meaningless, so you can just use.

context.Users
       .Where(u => u.LoweredUserName == userName.ToLower())
       .Select(u => u.Organisations.Select(o => o.Name));

Upvotes: 0

Simon Francesco
Simon Francesco

Reputation: 1561

Doh! I think I can answer my own question by using SelectMany to condense the Collection of Collections into one collection like so:

orgNames = context.Users.Where(u => u.LoweredUserName == userName.ToLower())
                .Take(1)
                .SelectMany(u => u.Organisations)
                .Select(o => o.Name);

Upvotes: 2

Related Questions