Linq & List.Contains

[Andy]

Hi,

I'm trying to add a where clause to my query:

List<string> types = new List<string>();
types.Add( "A" );
types.Add( "B" );

query = query.Where( c => types.Contains( c.Type ) );

When executed, I get the following exception:
System.NotSupportedException : Queries with local collections are not
supported.

I found a bug filling on Connect that indicates this was fixed for
RTM.. what's going on?

Thanks
Andy

 

[Frans Bouma [C# MVP]]

Hi,

I'm trying to add a where clause to my query:

List<string> types = new List<string>();
types.Add( "A" );
types.Add( "B" );

query = query.Where( c => types.Contains( c.Type ) );

When executed, I get the following exception:
System.NotSupportedException : Queries with local collections are not
supported.

I found a bug filling on Connect that indicates this was fixed for
RTM.. what's going on?

try:
query = query.Where(c=>new List<string>(){"A", "B"}.Contains(c.Type));

Linq to Sql isn't always smart enough to convert a constant typed
object into a value-based filter.

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#)
------------------------------------------------------------------------

 

[Andy]

try:
query = query.Where(c=>new List<string>(){"A", "B"}.Contains(c.Type));

Linq to Sql isn't always smart enough to convert a constant typed
object into a value-based filter.

Hmm... well that works. Weird. I should have posted my actual
code... its like this:

List<string> types = new List<string>();
if ( conditionA ) {
types.Add( "A" );
}

if ( conditionB ) {
types.Add( "B" );
}

query = query.Where( c => types.Contains( c.Type ) );

So unfortunately I can't implement your recommendation.

 

[Jarlaxle]

i havent tried it but could you do...

query = query.Where( c => types.toList().Contains( c.Type ) );

 

[CSharper]

Hmm... well that works.  Weird.  I should have posted my actual
code... its like this:

List<string> types = new List<string>();
if ( conditionA ) {
    types.Add( "A" );

}

if ( conditionB ) {
    types.Add( "B" );

}

query = query.Where( c => types.Contains( c.Type ) );

So unfortunately I can't implement your recommendation.

Side question, what is the use of the query? How is it used?
Thanks.

 

[Andy]

Side question, what is the use of the query? How is it used?

Well, this particular query is used as a subquery. The purpose of
both is to return records based on user specified criteria. The
subquery searches a related table for matching records.

The database schema is this:

[Document] ( DocumentId, CreatedDate, CustPO, etc. )
[DocumentContact] ( DocumentContactId, DocumentId, ContactType,
FirstName, LastName, etc. )

ContactType indicates Sold To, Ship To. The user can search for any
documents where the Ship To has a first name of John. Or any
documents that have VT in the state for either the Sold to or Bill to.

 

[Andy]

i havent tried it but could you do...

query = query.Where( c => types.toList().Contains( c.Type ) );

Well I tried types.ToArray(), and that didn't work either.

 

[Frans Bouma [C# MVP]]

Hmm... well that works. Weird. I should have posted my actual
code... its like this:

List<string> types = new List<string>();
if ( conditionA ) {
types.Add( "A" );
}

if ( conditionB ) {
types.Add( "B" );
}

query = query.Where( c => types.Contains( c.Type ) );

So unfortunately I can't implement your recommendation.

I tried it with our linq provider (now in beta) and your initial query
works fine. It's also odd that it doesn't work in linq to sql: the new
List<string> ... is found by the 'funcletizer' routine in linq to sql's
engine which converts it into a compiled lambda and executes it. This
gives... a ConstantExpression with the actual object.

Your initial query has at the spot of the list parameter also a
ConstantExpression which represents the list object. Contains is a
difficult beast to implement (see my blog for details, I think it was
episode #12) though in this case, there's no real difference between
having a constant which is an IList and.... having a constant which is
an IList.

Perhaps you should post on the forums.microsoft.com linq forum, as
the linq to sql team is reading there and perhaps could help you
further. There are other cases where linq to sql gives up related to
Contains: it doesn't try to match source element's properties with
element to check (argument of Contains) in some occasions.

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#)
------------------------------------------------------------------------

 

[Andy]

I tried it with our linq provider (now in beta) and your initial query
works fine. It's also odd that it doesn't work in linq to sql: the new
List<string> ... is found by the 'funcletizer' routine in linq to sql's
engine which converts it into a compiled lambda and executes it. This
gives... a ConstantExpression with the actual object.

Your initial query has at the spot of the list parameter also a
ConstantExpression which represents the list object. Contains is a
difficult beast to implement (see my blog for details, I think it was
episode #12) though in this case, there's no real difference between
having a constant which is an IList and.... having a constant which is
an IList.

Perhaps you should post on the forums.microsoft.com linq forum, as
the linq to sql team is reading there and perhaps could help you
further. There are other cases where linq to sql gives up related to
Contains: it doesn't try to match source element's properties with
element to check (argument of Contains) in some occasions.

Ok, I'll try posting over there and see if I get any more ideas.

Thanks
Andy

 

[Vinit Modi]

This worked for me
public int getQty(List<int> lstNumbers, string scrip, string client)
{
int qty = 0;
var rows = from table in BsktQtyTbl.AsEnumerable()
where
table.Field<string>("Client") == client
&&
lstNumbers.Contains(table.Field<int>("Number"))

select new
{
qty = table.Field<int>(scrip)
};
foreach (var line in rows)
{
qty += line.qty;
}

return qty;
}

 

[Jon Skeet [C# MVP]]

This worked for me
public int getQty(List<int> lstNumbers, string scrip, string client)
        {
            int qty = 0;
            var rows = from table in BsktQtyTbl.AsEnumerable()
                       where
                       table.Field<string>("Client") == client
                       &&
lstNumbers.Contains(table.Field<int>("Number"))

                       select new
                       {
                           qty = table.Field<int>(scrip)
                       };
            foreach (var line in rows)
            {
                qty += line.qty;
            }

            return qty;
        }

Why are you bothering to create an anonymous type just for the sake of
one value? You can also get LINQ to sum the results for you. In other
words:

public int getQty(List<int> lstNumbers, string scrip, string client)
{
return (from table in BsktQtyTbl.AsEnumerable()
where table.Field<string>("Client") == client
&& lstNumbers.Contains(table.Field<int>("Number")))
.Sum(table => table.Field<int>(scrip));
}

Jon