Is there a way to compose a query that uses a condition of contains in two lists (AND not OR)? about blazartech.queryablevalues HOT 7 CLOSED

Hi @Tyler-V ! Does this work? I'm away from my computer.

public async Task<List<Package>> GetPackagesAsync(List<int> list1, List<string> list2)
{
    return await _context.Package
        .Where(p =>
            _context.AsQueryableValues(list1).Contains(p.p1) &&
            _context.AsQueryableValues(list2, true).Contains(p.p2))
        .ToListAsync();
}

I believe your original query should behave the way your are asking (AND)... Are p.p1 and p.p2 nullable?

from blazartech.queryablevalues.

Hey @yv989c we tried that and it still pulls more records than necessary because the two lists are independent of each other

i.e.
In the database,
1 / ABC
1 / XYZ

List list1 = { 1 }
List list2 = { "ABC" }

We would want packages that only contain 1 and ABC, but this is pulling back 1 and XYZ as well,

We are using something like this now to get desirable results, not sure if there is a better way

        var packages = await _context.Package
            .Where(p => _context
                .AsQueryableValues(accumulations.Select(a => a.nm1), true)
                .Contains(p.p1 + "_" + p.p2))
            .ToListAsync();

[NotMapped] public string nm1 => p1 + "_" + p2;

from blazartech.queryablevalues.

Does either p.p1 or p.p2 allow nulls?

from blazartech.queryablevalues.

No nulls will exist in either set

from blazartech.queryablevalues.

I'm confused because of the word "set"... not sure if you are referring to the input list or your entity's properties. Would you please clarify? My question is about the two properties on your Package entity. Do you know if the underlying table allow nulls in those fields?

Also, does this give you a different result?

public async Task<List<Package>> GetPackagesAsync(List<int> list1, List<string> list2)
{
    return await _context.Package
        .Where(p =>
            _context.AsQueryableValues(list1).Where(i => i == p.p1).Any() &&
            _context.AsQueryableValues(list2, true).Where(i => i == p.p2).Any())
        .ToListAsync();
}

from blazartech.queryablevalues.

Hey @Tyler-V , do you have any news about this?

I wasn't able to reproduce your issue on my end. In my test your original query works as expected (get Package entities where p1 and p2 is present in both list1 and list2).

I'm wondering if the problem is related to:

• Database null semantics
• The size of your lists

Would you mind sharing the following?

• Version of EF you are using
• The approximate number of elements in each list
• SQL generated by your original query
• The SQL data type and nullability used by p1 and p2

Thanks.

from blazartech.queryablevalues.

Closing due to inactivity.

from blazartech.queryablevalues.