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?

asked Jun 02 '16 at 21:40

Peter%20Wake's gravatar image

Peter Wake
5123

edited Jun 02 '16 at 23:02


One Answer:

Hello Peter Wake,

Sorry for delay.

I think you will have to use Session.Query.Store<>() for the behavior you want to achieve. This is general way which will work no metter how many elements fooSet contains, expecially if you have millions of objects in Bar.

If fooSet had several objects it might be a query with complex condition in WHERE clause. It would be kind of improvement but this is be a special case.

answered Jun 08 '16 at 08:53

Alexey%20Kulakov's gravatar image

Alexey Kulakov
48215

Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA