Linq where clause

  • Thread starter Thread starter EdisonCPP
  • Start date Start date
E

EdisonCPP

How can I check against a list of ids in a list or array:

List<long> ChildrenIDs = GetChildIDs();

var Items = from items in db.DBItems
//where CategoryID == any of of ChildrenIDs values
select items;

Thanks...

Steven
 
I tried

where ChildrenIDs.Contains(items.CategoryID)

but I get this error:

LINQ to Entities does not recognize the method 'Boolean Contains(Int64)'
method, and this method cannot be translated into a store expression.
 
when using linq against objects, the where expression should be a bool
lambda expression. in your case, List<> has a method you can use:

var Items = from items in db.DBItems
where ChildrenIDs.Contains(CategoryID)
select items;

-- bruce (sqlwork.com)
 
Did you read the post you replied to?

I tried that and got this:

LINQ to Entities does not recognize the method 'Boolean Contains(Int64)'
method, and this method cannot be translated into a store expression.
 
Hi Steven,

Thanks for your post. My name is Hongye Sun [MSFT] and it is my pleasure to
work with you on this issue.

We have successfully reproduce the issue in our lab. After performing
research on this issue, we found that Entity Framework does not currently
support collection-valued parameters. To bypass this restriction, we worked
out one workaround for you.

Building a containing expression
First, please copy the following utility code into your project.
---------------------------------
static Expression<Func<TElement, bool>> BuildContainsExpression<TElement,
TValue>(
Expression<Func<TElement, TValue>> valueSelector, IEnumerable<TValue>
values)
{
if (null == valueSelector) { throw new
ArgumentNullException("valueSelector"); }
if (null == values) { throw new ArgumentNullException("values"); }

ParameterExpression p = valueSelector.Parameters.Single();
if (!values.Any())
{
return e => false;
}

var equals = values.Select(value =>
(Expression)Expression.Equal(valueSelector.Body, Expression.Constant(value,
typeof(TValue))));
var body = equals.Aggregate<Expression>((accumulate, equal) =>
Expression.Or(accumulate, equal));
return Expression.Lambda<Func<TElement, bool>>(body, p);
}
---------------------------------
Then, change your original code:
var Items = from items in db.DBItems
where ChildrenIDs.Contains(items.CategoryID)
select items;
Click to expand...
Into the following code:
---------------------------------
var Items = db.Items.Where((BuildContainsExpression<Item, int>(item =>
item.CategoryID, ChildrenIDs));
---------------------------------

For your convenience, we also translate the utility code into VB.net. Hope
it helps.
---------------------------------
Public Shared Function BuildContainsExpression(Of TElement, TValue)( _
ByVal valueSelector As Expression(Of Func(Of TElement, TValue)), _
ByVal values As IEnumerable(Of TValue) _
) As Expression(Of Func(Of TElement, Boolean))

' validate arguments
If IsNothing(valueSelector) Then Throw New
ArgumentNullException("valueSelector")
If IsNothing(values) Then Throw New ArgumentNullException("values")

Dim p As ParameterExpression = valueSelector.Parameters.Single()
If Not values.Any Then
Return _
Function(e) False
End If

Dim equals = values.Select( _
Function(v) _
Expression.Equal(valueSelector.Body, Expression.Constant(v,
GetType(TValue))) _
)

Dim body = equals.Aggregate( _
Function(accumulate, equal) _
Expression.Or(accumulate, equal) _
)

Return Expression.Lambda(Of Func(Of TElement, Boolean))(body, p)
End Function
---------------------------------

We have successfully fixed this issue by using this workaround in our lab.
Please have a try on your side and let us know if it works for you. Thanks.

Regards,
Hongye Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Thanks Hongye,

I just got it to work, here was the method I used:

------------------------
string sWhere = "";
foreach (long id in ChildrenIDs)
{
if (sWhere.Length > 0)
sWhere += " OR ";
sWhere += ("it.CatalogCategories.CategoryID==" + id.ToString());
}

var Items = db.CatalogItems.Where(sWhere).Select(items => new {
items.ItemID, items.ItemName, CategoryID =
items.CatalogCategories.CategoryID, items.Price, items.SalePrice,
items.OriginalPrice });
------------------------

I tried this method earlier, but I was missing the namespace "it" and I kept
getting errors no knowing what CategoryID was.

Thanks for your reponse again!
Steven


"Hongye Sun [MSFT]" said:
Hi Steven,

Thanks for your post. My name is Hongye Sun [MSFT] and it is my pleasure
to
work with you on this issue.

We have successfully reproduce the issue in our lab. After performing
research on this issue, we found that Entity Framework does not currently
support collection-valued parameters. To bypass this restriction, we
worked
out one workaround for you.

Building a containing expression
First, please copy the following utility code into your project.
---------------------------------
static Expression<Func<TElement, bool>> BuildContainsExpression<TElement,
TValue>(
Expression<Func<TElement, TValue>> valueSelector, IEnumerable<TValue>
values)
{
if (null == valueSelector) { throw new
ArgumentNullException("valueSelector"); }
if (null == values) { throw new ArgumentNullException("values"); }

ParameterExpression p = valueSelector.Parameters.Single();
if (!values.Any())
{
return e => false;
}

var equals = values.Select(value =>
(Expression)Expression.Equal(valueSelector.Body,
Expression.Constant(value,
typeof(TValue))));
var body = equals.Aggregate<Expression>((accumulate, equal) =>
Expression.Or(accumulate, equal));
return Expression.Lambda<Func<TElement, bool>>(body, p);
}
---------------------------------
Then, change your original code:
var Items = from items in db.DBItems
where ChildrenIDs.Contains(items.CategoryID)
select items;
Click to expand...
Into the following code:
---------------------------------
var Items = db.Items.Where((BuildContainsExpression<Item, int>(item =>
item.CategoryID, ChildrenIDs));
---------------------------------

For your convenience, we also translate the utility code into VB.net. Hope
it helps.
---------------------------------
Public Shared Function BuildContainsExpression(Of TElement, TValue)( _
ByVal valueSelector As Expression(Of Func(Of TElement, TValue)), _
ByVal values As IEnumerable(Of TValue) _
) As Expression(Of Func(Of TElement, Boolean))

' validate arguments
If IsNothing(valueSelector) Then Throw New
ArgumentNullException("valueSelector")
If IsNothing(values) Then Throw New ArgumentNullException("values")

Dim p As ParameterExpression = valueSelector.Parameters.Single()
If Not values.Any Then
Return _
Function(e) False
End If

Dim equals = values.Select( _
Function(v) _
Expression.Equal(valueSelector.Body, Expression.Constant(v,
GetType(TValue))) _
)

Dim body = equals.Aggregate( _
Function(accumulate, equal) _
Expression.Or(accumulate, equal) _
)

Return Expression.Lambda(Of Func(Of TElement, Boolean))(body, p)
End Function
---------------------------------

We have successfully fixed this issue by using this workaround in our lab.
Please have a try on your side and let us know if it works for you.
Thanks.

Regards,
Hongye Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support
Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================

This posting is provided "AS IS" with no warranties, and confers no
rights.
Click to expand...
 
Thank you too for your quick response. I'm delighted to hear the problem is
resolved. I have been very impressed with your high level of technical
proficiency; it has been a pleasure to work with you on this issue.

Thanks again for using Microsoft Newsgroup Service.

Regards,
Hongye Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
 
This posting is provided "AS IS" with no warranties, and confers no rights.
 
sorry, thought you where using linq to ienumerable, not linq to sql.

in linq to sql you need to use an expression builder like Hongye Sun
provided. doing the filter in the foreach, is less efficient as the rows
are filtered after being returned from the database rather than be done
on the database side. removing the where from the linq query will return
all rows from the database (if its small no issue).

-- bruce (sqlwork.com)
 
Back
Top