i founf a half solution for my problem in that post: http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/095745fe-dcf0-4142-b684-b7e4a1ab59f0/
Let me try to explain and illustrate my problem.
public class ClassEntityA
{
public int Id { get; set; }
public string Text { get; set; }
}
public class ClassEntityB
{
public int Id { get; set; }
public string Text { get; set; }
public NullableEntityA { get; set; }
}
// I need to do the following query in Ado Entity. That query works in Linq To Sql, but not suported in Linq To Entity
list<int> list = new List<in>() { 1, 2, 3 };
var itens = DbContext.ClassEntityB.Where(c => (list.Contains(c.EntityA.Id) == false || c.EntityA == null);
// i Found a Half Solution to do that in Linq To Entity doing that:
// But Here i can do the second test (OR c.EntityA == null). How can i do That?
list<int> list = new List<in>() { 1, 2, 3 };
var itens = DbEntities.ClassEntityB.WhereNotIn(c => c.EntityA.Id, list.AsEnumerable<int>())
#region Where Not In
private static Expressionbool>>
GetWhereNotInExpression(
Expression> propertySelector,
IEnumerablevalues)
{
ParameterExpression p =
propertySelector.Parameters.Single();
if (!values.Any())
{
return e => true;
}
var unequals = values.Select(value =>
(Expression)Expression.NotEqual(
propertySelector.Body,
Expression.Constant(value, typeof(TValue))
)
);
var body = unequals.Aggregate(
(accumulate, unequal) => Expression.And(accumulate, unequal));
return Expression.Lambdabool>>(body, p);
}
public static IQueryable
WhereNotIn(this IQueryable source,
Expression> propertySelector,
params TValue[] values)
{
return source.Where(GetWhereNotInExpression(
propertySelector, values));
}
public static IQueryable
WhereNotIn(this IQueryable source,
Expression> propertySelector,
IEnumerablevalues)
{
return source.Where(GetWhereNotInExpression(
propertySelector, values));
}
#endregion
Can any of you help me?
i need to do a Query in the Ado that have a ("Where not in" or "Property == null) test.
thx.
Luciano Correia
Luciano CorreiaPosted Dec 1, 2009, 9:37 AM
The problem as not soled, because the Ado Data Service does not suporte "Union"
Piotr JustynaPosted Dec 1, 2009, 8:26 AM
I'll try to think how to optimize this, but since your problem is solved please mark it as helpful.
Luciano CorreiaPosted Dec 1, 2009, 8:15 AM
the Ado Data Service does not suporte "Union"
but thx alot man..
Piotr JustynaPosted Dec 1, 2009, 5:52 AM
I'm sorry for a late response. You're right, that complicates your problem a bit. But don't worry - the quickest solution I can think of is:
1) Get all B entities having A entity null
2) Get all A entities having A entity not null
3) Get all A entities which are not in your list querying a collection from point 2).
4) Merge collections from points 1 and 3 (using Concat method or List
Does it work now?
Cheers!
Luciano CorreiaPosted Nov 30, 2009, 12:37 PM
i got a "NulReferenceException" because when i try c => c.Emp.ID emp is Null
The second example, shows exactly what i need
Piotr JustynaPosted Nov 30, 2009, 10:54 AM
Could you paste the whole exception you are getting? I was convinced, that there should be no errors since I'm checking if the object is not null. Could you also paste your expression code?
Cheers!
Luciano CorreiaPosted Nov 30, 2009, 9:32 AM
very good idea, thx alot. but i got another problem:
In that example i got an Exception because the record "Cliente 5" has the Emp field as null.
So, any other idea?
Piotr JustynaPosted Nov 28, 2009, 6:19 PM
I get your point. It's rather difficult to manipulate the 'OR' operator, but don't worry since we have De Morgan's laws.
Using them you can instead of:
Where is true: Item is not in collection OR item is null
write this:
Where is not true: Item is in collection AND item is not null
This is a lot easier, because you can write your LINQ query like this (you will just have to add an expression 'WhereIn'):
Now you just have to nagate the obtained set of records:
Or to save some space:
method GetWhereNotInExpression. If you do so, you can with no problem access each value's properties and determine if they're null or not. This seems to be a better solution, because you have only one query in this case.The second solution is to avoid using generic type in 'values' collection in your
I hope this helps - if it does please mark it as helpful :)
Cheers!