Asp Forum - LINQ queries within queries - microsoft.public.dotnet.framework

peter.mcclymont@gmail.com

10/13/2008 2:12:00 AM

First off I could not find any LINQ groups with more than a few
members. Is this an OK place for this?

I have a rather complicated query to turn into LINQ. The SQL query is
this (I have simplified it)

Select * from table where rowid in (
select rowid from table where name='c' and value='3' and rowid in
(
select rowid from table where name='b' and value='2'))

So yeah, it is very dynamic in the fact that I want to be able to add
more subqueries, or less in some cases. Depends on what the software
is doing at the time.

I tried to turn it into code,

private IQueryable<long> CreateKeySubQuery(string name, string value,
IQueryable<long> previousKeyQuery)
{
if (previousKeyQuery != null)
{
return from d in dataContext.DataStores
where d.Name == name && d.Value == value &&
previousKeyQuery.Contains(d.RowId)
select d.RowId;
}

else
{
return from d in dataContext.DataStores
where d.Name == name && d.Value == value
select d.RowId;
}
}

So I would call the method above multiple times to build up the
subqueries, thus passing in the previous subquery (previousKeyQuery)
to build upon. It also passes in the name and value that I want to
include in the current part of the subquery.

I then do this,

IQueryable<DataStore> dataStoreEntriesQuery = from t
in dataContext.Table
where
keySubQuery.Contains(d.RowId)
select
t;

Where keySubQuery is the completed subquery from calling
CreateKeySubQuery multiple times. When I try to run the query above I
get an exception,

Queries with local collections are not supported

I don't understand why?

Anybody got any ideas, or perhaps a different suggestion how to turn
my query into LINQ?

43 Answers

Frans Bouma [C# MVP]

10/13/2008 7:59:00 AM

bob laughland wrote:
> First off I could not find any LINQ groups with more than a few
> members. Is this an OK place for this?
>
> I have a rather complicated query to turn into LINQ. The SQL query is
> this (I have simplified it)
>
> Select * from table where rowid in (
> select rowid from table where name='c' and value='3' and rowid in
> (
> select rowid from table where name='b' and value='2'))
>
> So yeah, it is very dynamic in the fact that I want to be able to add
> more subqueries, or less in some cases. Depends on what the software
> is doing at the time.
>
> I tried to turn it into code,
>
> private IQueryable<long> CreateKeySubQuery(string name, string value,
> IQueryable<long> previousKeyQuery)
> {
> if (previousKeyQuery != null)
> {
> return from d in dataContext.DataStores
> where d.Name == name && d.Value == value &&
> previousKeyQuery.Contains(d.RowId)
> select d.RowId;
> }
>
> else
> {
> return from d in dataContext.DataStores
> where d.Name == name && d.Value == value
> select d.RowId;
> }
> }
>
> So I would call the method above multiple times to build up the
> subqueries, thus passing in the previous subquery (previousKeyQuery)
> to build upon. It also passes in the name and value that I want to
> include in the current part of the subquery.
>
> I then do this,
>
> IQueryable<DataStore> dataStoreEntriesQuery = from t
> in dataContext.Table
> where
> keySubQuery.Contains(d.RowId)
> select
> t;
>
> Where keySubQuery is the completed subquery from calling
> CreateKeySubQuery multiple times. When I try to run the query above I
> get an exception,
>
> Queries with local collections are not supported
>
> I don't understand why?
>
> Anybody got any ideas, or perhaps a different suggestion how to turn
> my query into LINQ?

Linq to Sql I pressume?

This is a limitation of linq to sql's linq provider, if I'm not
mistaken. The main thing is that 'keySubQuery' is a full linq query, and
it has to match a property of that resultset with a member in the
Contains call, which could lead to mismatches.

I do recall seeing similar errors when I was testing my own linq
provider and checked whether linq to sql produced different/better results.

It might be they've fixed it in SP1 for vs.net 2008, as they seem to
have fixed some other glitches in linq to sql as well.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.l...
My .NET blog: http://weblogs.asp....
Microsoft MVP (C#)
------------------------------------------------------------------------

Naked Gonad

11/24/2008 2:22:00 PM

Eli Grubman wrote:
> On Mon, 24 Nov 2008 12:48:55 +0000, Naked Gonad
> <bodron57@tiscali.co.uk> wrote:
>
>> Eli Grubman wrote:
>>> On Mon, 24 Nov 2008 11:57:49 +0000, Naked Gonad
>>> <bodron57@tiscali.co.uk> wrote:
>>>
>>>> Eli Grubman wrote:
>>>>> On Mon, 24 Nov 2008 11:24:49 +0000, Naked Gonad
>>>>> <bodron57@tiscali.co.uk> wrote:
>>>>>
>>>>>> Eli Grubman wrote:
>>>>>>> On Mon, 24 Nov 2008 09:14:28 +0000, Naked Gonad
>>>>>>> <bodron57@tiscali.co.uk> wrote:
>>>>>>>
>>>>>>>> Eli Grubman wrote:
>>>>>>>>> On Tue, 18 Nov 2008 09:47:25 +0000, Naked Gonad
>>>>>>>>> <bodron57@tiscali.co.uk> wrote:
>>>>>>>>>
>>>>>>>>>> Eli Grubman wrote:
>>>>>>>>>>> On Mon, 17 Nov 2008 20:00:20 +0000, Naked Gonad
>>>>>>>>>>> <bodron57@tiscali.co.uk> wrote:
>>>>>>>>>>>
>>>>>>>>>>>> Peter Hucker wrote:
>>>>>>>>>>>>> On Sun, 16 Nov 2008 18:45:02 -0000, Naked Gonad <bodron57@tiscali.co.uk> wrote:
>>>>>>>>>>>>>
>>>>>>>>>>>>>> Peter Hucker wrote:
>>>>>>>>>>>>>>> On Sat, 15 Nov 2008 19:58:51 -0000, Naked Gonad <bodron57@tiscali.co.uk> wrote:
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> Peter Hucker wrote:
>>>>>>>>>>>>>>>>> On Fri, 14 Nov 2008 18:39:04 -0000, Naked Gonad <bodron57@tiscali.co.uk> wrote:
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> Peter Hucker wrote:
>>>>>>>>>>>>>>>>>>> On Wed, 12 Nov 2008 19:21:39 -0000, Naked Gonad <bodron57@tiscali.co.uk> wrote:
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> Peter Hucker wrote:
>>>>>>>>>>>>>>>>>>>> So, would a bank account that has nothing in it, read 000.00 or would
>>>>>>>>>>>>>>>>>>>> it magically mean something else?
>>>>>>>>>>>>>>>>>>> It would read 0 (well maybe ?0.00), there's nothing magical about it.
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> Whatever, it still counts as nothing or 0.
>>>>>>>>>>>>>>>>> So you fail to distinguish between an empty glass and no glass?
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> I thought we were talking about counting 0 as nothing?
>>>>>>>>>>>>>>> Yes we were. An empty glass is not nothing. However the reading on beer level is "0".
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>> You've now changed it from counting to objects.
>>>>>>>>>>>>> Zero is important in both cases. If you're counting anything and miss out zero, going directly from -1 to 1, you've made an error. You could be counting how much money you owe me, which is changing as you give me ?1 coins. Going straight from -1 to +1 would do me out of a quid.
>>>>>>>>>>>>>
>>>>>>>>>>>> What on earth are you talking about!?
>>>>>>>>>>>> If I owed you ?5, I would count 1 2 3 4 5, where does 0 come into it?
>>>>>>>>>>> The 0 is what he'll get back.
>>>>>>>>>> He'll probably say "that's not the same as nothing".
>>>>>>>>> Nothing is the same as nothing.
>>>>>>>>>
>>>>>>>> Except Peter! - while we have nothing, he'll always have 0....er....erm.
>>>>>>> There is always room for improvement!
>>>>>>>
>>>>>> Two 0's?
>>>>> Two ho's?
>>>>>
>>>> Don't get bitchy!
>>> Meow.
>>>
>> Scratch n sniff?
>
> If you must.
>
After you!

Scott Marquardt

11/29/2008 8:59:00 PM

On Tue, 18 Nov 2008 19:36:08 -0000, Naked Gonad <bodron57@tiscali.co.uk> wrote:

> Peter Hucker wrote:
>> On Tue, 18 Nov 2008 09:47:25 -0000, Naked Gonad <bodron57@tiscali.co.uk> wrote:
>>
>>> Eli Grubman wrote:
>>>> On Mon, 17 Nov 2008 20:00:20 +0000, Naked Gonad
>>>> <bodron57@tiscali.co.uk> wrote:
>>>>
>>>>> Peter Hucker wrote:
>>>>>> On Sun, 16 Nov 2008 18:45:02 -0000, Naked Gonad <bodron57@tiscali.co.uk> wrote:
>>>>>>
>>>>>>> Peter Hucker wrote:
>>>>>>> You've now changed it from counting to objects.
>>>>>> Zero is important in both cases. If you're counting anything and miss out zero, going directly from -1 to 1, you've made an error. You could be counting how much money you owe me, which is changing as you give me ?1 coins. Going straight from -1 to +1 would do me out of a quid.
>>>>>>
>>>>> What on earth are you talking about!?
>>>>> If I owed you ?5, I would count 1 2 3 4 5, where does 0 come into it?
>>>> The 0 is what he'll get back.
>>> He'll probably say "that's not the same as nothing".
>>
>> It isn't. It's an opportunity to kick you in the teeth.
>>
> I think you've confused yourself.

What makes you think that?

--
http://www.petersp... http://www.insanevide... http://www.peters...

A guy goes to a psychiatrist.
"I'm in love with my horse," says the guy. "I desire my horse!"
"I see," says the shrink. "What kind of horse is it? Male or female?"
"Female of course! Do I look like some sort of pervert?"

Scott Marquardt

12/1/2008 6:43:00 PM

On Mon, 24 Nov 2008 06:50:50 -0000, Eli Grubman <eli.grubman@googlemail.com> wrote:

> On Tue, 18 Nov 2008 09:47:25 +0000, Naked Gonad
> <bodron57@tiscali.co.uk> wrote:
>
>>Eli Grubman wrote:
>>> On Mon, 17 Nov 2008 20:00:20 +0000, Naked Gonad
>>> <bodron57@tiscali.co.uk> wrote:
>>>
>>>> Peter Hucker wrote:
>>>>> On Sun, 16 Nov 2008 18:45:02 -0000, Naked Gonad <bodron57@tiscali.co.uk> wrote:
>>>>>
>>>>>> Peter Hucker wrote:
>>>>>>> On Sat, 15 Nov 2008 19:58:51 -0000, Naked Gonad <bodron57@tiscali.co.uk> wrote:
>>>>>>>
<snip>
>>>>>>> Yes we were. An empty glass is not nothing. However the reading on beer level is "0".
>>>>>>>
>>>>>> You've now changed it from counting to objects.
>>>>> Zero is important in both cases. If you're counting anything and miss out zero, going directly from -1 to 1, you've made an error. You could be counting how much money you owe me, which is changing as you give me ?1 coins. Going straight from -1 to +1 would do me out of a quid.
>>>>>
>>>> What on earth are you talking about!?
>>>> If I owed you ?5, I would count 1 2 3 4 5, where does 0 come into it?
>>>
>>> The 0 is what he'll get back.
>>
>>He'll probably say "that's not the same as nothing".
>
> Nothing is the same as nothing.

I studied Maths at University you bunch of losers.

--
http://www.petersp... http://www.insanevide... http://www.peters...

It is easier to get forgiveness than permission.

Eli Grubman

12/1/2008 7:47:00 PM

On Mon, 01 Dec 2008 18:42:51 -0000, "Peter Hucker" <none@spam.com>
wrote:

>On Mon, 24 Nov 2008 06:50:50 -0000, Eli Grubman <eli.grubman@googlemail.com> wrote:
>
>> On Tue, 18 Nov 2008 09:47:25 +0000, Naked Gonad
>> <bodron57@tiscali.co.uk> wrote:
>>
>>>Eli Grubman wrote:
>>>> On Mon, 17 Nov 2008 20:00:20 +0000, Naked Gonad
>>>> <bodron57@tiscali.co.uk> wrote:
>>>>
>>>>> Peter Hucker wrote:
>>>>>> On Sun, 16 Nov 2008 18:45:02 -0000, Naked Gonad <bodron57@tiscali.co.uk> wrote:
>>>>>>
>>>>>>> Peter Hucker wrote:
>>>>>>>> On Sat, 15 Nov 2008 19:58:51 -0000, Naked Gonad <bodron57@tiscali.co.uk> wrote:
>>>>>>>>
><snip>
>>>>>>>> Yes we were. An empty glass is not nothing. However the reading on beer level is "0".
>>>>>>>>
>>>>>>> You've now changed it from counting to objects.
>>>>>> Zero is important in both cases. If you're counting anything and miss out zero, going directly from -1 to 1, you've made an error. You could be counting how much money you owe me, which is changing as you give me ?1 coins. Going straight from -1 to +1 would do me out of a quid.
>>>>>>
>>>>> What on earth are you talking about!?
>>>>> If I owed you ?5, I would count 1 2 3 4 5, where does 0 come into it?
>>>>
>>>> The 0 is what he'll get back.
>>>
>>>He'll probably say "that's not the same as nothing".
>>
>> Nothing is the same as nothing.
>
>I studied Maths at University you bunch of losers.

Not very diligently, apparently. LOL

\"The Honest One\

12/1/2008 8:33:00 PM

"Eli Grubman" <eli.grubman@googlemail.com> wrote in message
news:4pf8j498bovjvc2mj3doeqmcbmk7plbcdq@4ax.com...
> On Mon, 01 Dec 2008 18:42:51 -0000, "Peter Hucker" <none@spam.com>
> wrote:
>
> >On Mon, 24 Nov 2008 06:50:50 -0000, Eli Grubman
<eli.grubman@googlemail.com> wrote:
> >
> >> On Tue, 18 Nov 2008 09:47:25 +0000, Naked Gonad
> >> <bodron57@tiscali.co.uk> wrote:
> >>
> >>>Eli Grubman wrote:
> >>>> On Mon, 17 Nov 2008 20:00:20 +0000, Naked Gonad
> >>>> <bodron57@tiscali.co.uk> wrote:
> >>>>
> >>>>> Peter Hucker wrote:
> >>>>>> On Sun, 16 Nov 2008 18:45:02 -0000, Naked Gonad
<bodron57@tiscali.co.uk> wrote:
> >>>>>>
> >>>>>>> Peter Hucker wrote:
> >>>>>>>> On Sat, 15 Nov 2008 19:58:51 -0000, Naked Gonad
<bodron57@tiscali.co.uk> wrote:
> >>>>>>>>
> ><snip>
> >>>>>>>> Yes we were. An empty glass is not nothing. However the
reading on beer level is "0".
> >>>>>>>>
> >>>>>>> You've now changed it from counting to objects.
> >>>>>> Zero is important in both cases. If you're counting anything
and miss out zero, going directly from -1 to 1, you've made an error.
You could be counting how much money you owe me, which is changing as
you give me ?1 coins. Going straight from -1 to +1 would do me out of a
quid.
> >>>>>>
> >>>>> What on earth are you talking about!?
> >>>>> If I owed you ?5, I would count 1 2 3 4 5, where does 0 come
into it?
> >>>>
> >>>> The 0 is what he'll get back.
> >>>
> >>>He'll probably say "that's not the same as nothing".
> >>
> >> Nothing is the same as nothing.
> >
> >I studied Maths at University you bunch of losers.
>
> Not very diligently, apparently. LOL

TRANSLATION:

Phucker took freshman Idiot math and then flunked-out...

HJ

Eli Grubman

12/1/2008 9:09:00 PM

On Mon, 1 Dec 2008 14:32:56 -0600, "John \"C\""
<honestjohn@centurytel.net> wrote:

>
>"Eli Grubman" <eli.grubman@googlemail.com> wrote in message
>news:4pf8j498bovjvc2mj3doeqmcbmk7plbcdq@4ax.com...
>> On Mon, 01 Dec 2008 18:42:51 -0000, "Peter Hucker" <none@spam.com>
>> wrote:
>>
>> >On Mon, 24 Nov 2008 06:50:50 -0000, Eli Grubman
><eli.grubman@googlemail.com> wrote:
>> >
>> >> On Tue, 18 Nov 2008 09:47:25 +0000, Naked Gonad
>> >> <bodron57@tiscali.co.uk> wrote:
>> >>
>> >>>Eli Grubman wrote:
>> >>>> On Mon, 17 Nov 2008 20:00:20 +0000, Naked Gonad
>> >>>> <bodron57@tiscali.co.uk> wrote:
>> >>>>
>> >>>>> Peter Hucker wrote:
>> >>>>>> On Sun, 16 Nov 2008 18:45:02 -0000, Naked Gonad
><bodron57@tiscali.co.uk> wrote:
>> >>>>>>
>> >>>>>>> Peter Hucker wrote:
>> >>>>>>>> On Sat, 15 Nov 2008 19:58:51 -0000, Naked Gonad
><bodron57@tiscali.co.uk> wrote:
>> >>>>>>>>
>> ><snip>
>> >>>>>>>> Yes we were. An empty glass is not nothing. However the
>reading on beer level is "0".
>> >>>>>>>>
>> >>>>>>> You've now changed it from counting to objects.
>> >>>>>> Zero is important in both cases. If you're counting anything
>and miss out zero, going directly from -1 to 1, you've made an error.
>You could be counting how much money you owe me, which is changing as
>you give me ?1 coins. Going straight from -1 to +1 would do me out of a
>quid.
>> >>>>>>
>> >>>>> What on earth are you talking about!?
>> >>>>> If I owed you ?5, I would count 1 2 3 4 5, where does 0 come
>into it?
>> >>>>
>> >>>> The 0 is what he'll get back.
>> >>>
>> >>>He'll probably say "that's not the same as nothing".
>> >>
>> >> Nothing is the same as nothing.
>> >
>> >I studied Maths at University you bunch of losers.
>>
>> Not very diligently, apparently. LOL
>
>TRANSLATION:
>
>Phucker took freshman Idiot math and then flunked-out...
>
>HJ

Is it one math and two maths? LOL

an old friend

12/1/2008 9:22:00 PM

On Mon, 01 Dec 2008 14:47:11 -0500, Eli Grubman
<eli.grubman@googlemail.com> wrote:

>On Mon, 01 Dec 2008 18:42:51 -0000, "Peter Hucker" <none@spam.com>
>wrote:
>
>>On Mon, 24 Nov 2008 06:50:50 -0000, Eli Grubman <eli.grubman@googlemail.com> wrote:
>>
>>> On Tue, 18 Nov 2008 09:47:25 +0000, Naked Gonad
>>> <bodron57@tiscali.co.uk> wrote:
>>>
>>>>Eli Grubman wrote:
>>>>> On Mon, 17 Nov 2008 20:00:20 +0000, Naked Gonad
>>>>> <bodron57@tiscali.co.uk> wrote:
>>>>>
>>>>>> Peter Hucker wrote:
>>>>>>> On Sun, 16 Nov 2008 18:45:02 -0000, Naked Gonad <bodron57@tiscali.co.uk> wrote:
>>>>>>>
>>>>>>>> Peter Hucker wrote:
>>>>>>>>> On Sat, 15 Nov 2008 19:58:51 -0000, Naked Gonad <bodron57@tiscali.co.uk> wrote:
>>>>>>>>>
>><snip>
>>>>>>>>> Yes we were. An empty glass is not nothing. However the reading on beer level is "0".
>>>>>>>>>
>>>>>>>> You've now changed it from counting to objects.
>>>>>>> Zero is important in both cases. If you're counting anything and miss out zero, going directly from -1 to 1, you've made an error. You could be counting how much money you owe me, which is changing as you give me ?1 coins. Going straight from -1 to +1 would do me out of a quid.
>>>>>>>
>>>>>> What on earth are you talking about!?
>>>>>> If I owed you ?5, I would count 1 2 3 4 5, where does 0 come into it?
>>>>>
>>>>> The 0 is what he'll get back.
>>>>
>>>>He'll probably say "that's not the same as nothing".
>>>
>>> Nothing is the same as nothing.
>>
>>I studied Maths at University you bunch of losers.
>
>Not very diligently, apparently. LOL

"one useless man is disgrace 2 become a law firm 3 or more become a congress"
adams

woger you are a Congress all in your own head

http://kb9rqz.bravejo...
altopia is never used by KB9RQZ
nor is Konstans@hotmail.com ever
btw i can be found at
17366 N River Rd
Chassel Mi

but the cowards asking lack the gut to act

an old friend

12/1/2008 9:23:00 PM

an old friend

12/1/2008 9:23:00 PM

On Mon, 01 Dec 2008 16:08:42 -0500, Eli Grubman
<eli.grubman@googlemail.com> wrote:

>On Mon, 1 Dec 2008 14:32:56 -0600, "John \"C\""
><honestjohn@centurytel.net> wrote:
>
>>
>>"Eli Grubman" <eli.grubman@googlemail.com> wrote in message
>>news:4pf8j498bovjvc2mj3doeqmcbmk7plbcdq@4ax.com...
>>> On Mon, 01 Dec 2008 18:42:51 -0000, "Peter Hucker" <none@spam.com>
>>> wrote:
>>>
>>> >On Mon, 24 Nov 2008 06:50:50 -0000, Eli Grubman
>><eli.grubman@googlemail.com> wrote:
>>> >
>>> >> On Tue, 18 Nov 2008 09:47:25 +0000, Naked Gonad
>>> >> <bodron57@tiscali.co.uk> wrote:
>>> >>
>>> >>>Eli Grubman wrote:
>>> >>>> On Mon, 17 Nov 2008 20:00:20 +0000, Naked Gonad
>>> >>>> <bodron57@tiscali.co.uk> wrote:
>>> >>>>
>>> >>>>> Peter Hucker wrote:
>>> >>>>>> On Sun, 16 Nov 2008 18:45:02 -0000, Naked Gonad
>><bodron57@tiscali.co.uk> wrote:
>>> >>>>>>
>>> >>>>>>> Peter Hucker wrote:
>>> >>>>>>>> On Sat, 15 Nov 2008 19:58:51 -0000, Naked Gonad
>><bodron57@tiscali.co.uk> wrote:
>>> >>>>>>>>
>>> ><snip>
>>> >>>>>>>> Yes we were. An empty glass is not nothing. However the
>>reading on beer level is "0".
>>> >>>>>>>>
>>> >>>>>>> You've now changed it from counting to objects.
>>> >>>>>> Zero is important in both cases. If you're counting anything
>>and miss out zero, going directly from -1 to 1, you've made an error.
>>You could be counting how much money you owe me, which is changing as
>>you give me ?1 coins. Going straight from -1 to +1 would do me out of a
>>quid.
>>> >>>>>>
>>> >>>>> What on earth are you talking about!?
>>> >>>>> If I owed you ?5, I would count 1 2 3 4 5, where does 0 come
>>into it?
>>> >>>>
>>> >>>> The 0 is what he'll get back.
>>> >>>
>>> >>>He'll probably say "that's not the same as nothing".
>>> >>
>>> >> Nothing is the same as nothing.
>>> >
>>> >I studied Maths at University you bunch of losers.
>>>
>>> Not very diligently, apparently. LOL
>>
>>TRANSLATION:
>>
>>Phucker took freshman Idiot math and then flunked-out...
>>
>>HJ
>
>Is it one math and two maths? LOL

"one useless man is disgrace 2 become a law firm 3 or more become a congress"
adams

woger you are a Congress all in your own head

http://kb9rqz.bravejo...
altopia is never used by KB9RQZ
nor is Konstans@hotmail.com ever
btw i can be found at
17366 N River Rd
Chassel Mi

but the cowards asking lack the gut to act

microsoft.public.dotnet.framework

LINQ queries within queries

peter.mcclymont@gmail.com

Frans Bouma [C# MVP]

Naked Gonad

Scott Marquardt

Scott Marquardt

Eli Grubman

\"The Honest One\

Eli Grubman

an old friend

an old friend

an old friend

x Login to ForumsZone