Entity Framework - Condition on one to many join (Lambda)

Question

Hi,

I have 2 entities: Customer & Account, where a customer can have multiple accounts. On the account, I have a "PlatformTypeId" field, which I need to condition on (multiple values), among other criterions. I'm using Lambda expressions, to build the query. Here's a snippet:

var customerQuery =
    from c in context.CustomerSet.Include("Accounts")
    select c;

if (criterions.UserTypes != null && criterions.UserTypes.Count() > 0)
{
    List<short> searchCriterionsUserTypes = 
        criterions.UserTypes.Select(i => (short)i).ToList();

    customerQuery = customerQuery
        .Where(LinqTools.BuildContainsExpression<Customer, short>(
            c => c.UserTypeId, searchCriterionsUserTypes));
}

// Other criterions, including the problematic platforms condition (below)
var customers = customerQuery.ToList();

I can't figure out how to build the accounts' platforms condition:

if (criterions.Platforms != null && criterions.Platforms.Count() > 0)
{
    List<short> searchCriterionsPlatforms =
        criterions.Platforms.Select(i => (short)i).ToList();

    customerQuery = customerQuery.Where(c => c.Accounts
        .Where(LinqTools.BuildContainsExpression<Account, short>(
            a => a.PlatformTypeId, searchCriterionsPlatforms)));
}

(The BuildContainsExpression is a method we use to build the expression for the multi-select)

I'm getting a compilation error:

The type arguments for method 'System.Linq.Enumerable.Where(System.Collections.Generic.IEnumerable, System.Func)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

Any idea how to fix this?

Thanks,

Nir.

Answer 1

I think this might fix it for you.

if (criterions.Platforms != null && criterions.Platforms.Count() > 0) 
{ 
    List<short> searchCriterionsPlatforms = 
        criterions.Platforms.Select(i => (short)i).ToList(); 

    customerQuery = customerQuery 
        .Where(c => c.Accounts  
                     .Any(a => searchCriterionsPlatforms 
                               .Contains(a.PlatformTypeId) 
                         )
              );        
} 

This will filter on customers who have any account that whose platfortypeid is contained in the searchCriterionsPlatforms list.

HTH

ps.

You can change:

customerQuery = customerQuery 
    .Where(LinqTools.BuildContainsExpression<Customer, short>( 
        c => c.UserTypeId, searchCriterionsUserTypes)); 

to:

customerQuery = customerQuery
                .Where(c => searchCriterionsUserTypes.Contains(c.UserTypeId));

IMO, that is much cleaner.