LINQ queries within queries

[bob laughland]

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?

 

[Frans Bouma [C# MVP]]

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.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------