CODEWITHSUNDEEP
.netlinq
theta
theta

Reputation: 25601

Use query as quantifier

I have a table with two columns: "Source" and "Target" with around 500K rows and 6K unique items (of type Double) in both columns.

Now, I want to extract all "Source" items related to some sample item in "Target" column:

Dim samples = (From t in table Where t.Target = 77504944 Select t.Source).ToList()

and than output all table rows if any of these samples exist in "Source" column.

I first tried:

Dim query = From t in table _
    Where samples.All(Function(x) x.Contains(t.Source)) _
    Select t.Source, t.Target

but this raises "'Contains' is not a member of 'Double?'." which I guess is a result of linq deferred execution.

Then I tried:

Dim query = From t in table _
    Where samples.All(Function(x) x = t.Source) _
    Select t.Source, t.Target

which raises "OleDbException: Expression too complex in query expression", assuming because it translates all matched samples to separate conditional SQL statements, and it reaches some limit.

I think I miss something obvious and that this is indeed simple problem with simple linq query that I can't see at the moment. So, how can I use the first linq query (samples variable) as quantifier for the next one, in above example?

I really want to know if this problem is suitable for linq, in a manner that I'm learning linq. I can do such query quite easily in Python for example:

id = 77504944
samples = [x for x, y in table if y == id]
query = [(x, y) for x, y in table if x in samples or y in samples]

Upvotes: 1

Views: 48

Answers (1)

invernomuto
invernomuto

Reputation: 10211

Seems there is a limit in the Jet engine on how many ANDs you can have in a SQL statement.

This article can be useful.

Upvotes: 1

Related Questions