I have a scenario like this: I have a set of objects of class Foo: class Foo { int x; int y; }, let's call it fooSet. And I want to query database objects of type Bar, where Bar is something like class Bar { int? xx; int? yy; int? zz; } to find the set of Bar intersecting with a set Foo, where the Foo is derived from values in the Bar.

Presuming the set of Foo called fooSet is of moderate size...

session.Query.All<bar>().Where(bb => fooSet.Contains(new Foo(bb.xx ?? -1, bb.yy ?? -1)) doesn't work, which makes perfect sense. I didn't expect it to, but it shows the general intent of what I want to do.

I tried being tricky, using a Select... Imagine I declare a class called FooBar to contains a Bar and a derived Foo.

session.Query.All<bar>().Select(bb => new Foobar(bb, new Foo(bb.xx ?? -1, bb.yy ?? -1)).Where(foobar => fooSet.Contains(foobar.foo)).Select(fb => fb.bar)

but that doesn't work either, failed saying that it's unable to materialize objects of type Bar, even though Bar is the EntitySet in the database.

I figure I might make it work with a dual step process, or a subquery, but what is the "right" way? I don't want the Bar type to simply contain a Foo, even though it looks like a tempting solution in this simplified pseudocode example.

Also, in the real scenario, the database table for Bar objects will contain several million objects, and I'll have indexes on the contributing fields.

If this were plain SQL I'd probably use a temporary table to hold the Foo objects and then do a join against the Bar table. I can do this using session.Query.Store<> and it works, but is it the best way?