How to keep Linq2SQL OR between conditions?
Suppose we need to select two sets from the table: "Things"
var GradeA = db.Things.Where(t=> condition1);
var GradeB = db.Things.Where(t=> !condition1 && condition2);
var DesiredList = GradeA.union(GradeB);
alternatively, we need to write a single operator to avoid union
cost:
var DesiredList = db.Things.Where(t=> condtion1 || (!condition1 && condition2));
the problem in the query optimizer seems to truncate the expression only condition2 .
How to keep precedence between condition1 and condition2
A workaround for real life:
/// <summary>
/// Gets only first BookTag for each tag word, chooses the one of this user (if exists).
/// </summary>
/// <param name="book"></param>
/// <returns></returns>
public static IQueryable<BookTag> UniqueByTags(this IQueryable<BookTag> bookTags, User user)
{
return bookTags.GroupBy(BT => BT.TagId)
.Select(g => g.Any(bt => bt.UserId == user.Id) ?
new BookTag() { User = user, Tag = g.First().Tag, Book = bookTags.First().Book } :
new BookTag() {User = g.First().User, Tag = g.First().Tag, Book = bookTags.First().Book}
);
}
Edit:
An example of getting an autocomplete list:
- input:
str
- conclusion: things starting with
str
and things containingstr
(no duplicates)
Another example: Selection ThingTags
, which has 3 properties:
-
ThingID
-
UserID
-
TagID
we only want to select one ThingTag
for each TagID
provided that we select a value with an UserID
equal parameter if exists , otherwise select first ThingTag
for this TagID
>.
Still with me? I hope so :)
source to share
Is there any reason not to write this:
var DesiredList = db.Things.Where(t=> condition1 || condition2);
It is logically the same set of elements, after all. Since this is a simpler expression, it is more likely that the query generator will get it right. Having said that, I am surprised it is wrong. Do you have a complete example that you could provide?
source to share
It seems to me that you want to do XOR (Exclusive or), not a normal OR between your two conditions (in other words, you want elements that meet the requirements of only one OR the other ... not both).
I'm not sure about LINQ to SQL, but I know LINQ to Objects supports XOR ... so you can give it a try. Here's the syntax:
var DesiredList = db.Things.Where(t => condition1 ^ condition2);
source to share
To take the example literally, you could accomplish the combination in the question by building Expression
on the fly:
static IQueryable<T> WhereXElseY<T>(
this IQueryable<T> query,
Expression<Func<T, bool>> predicateA,
Expression<Func<T, bool>> predicateB)
{
var condA = predicateA.Body;
var param = predicateA.Parameters[0];
var body = Expression.OrElse(
condA,
Expression.AndAlso(
Expression.Not(condA),
Expression.Invoke(
predicateB, param)));
var lambda = Expression.Lambda<Func<T, bool>>(
body, param);
return query.Where(lambda);
}
However, while this might work with LINQ-to-SQL, it won't work with EF, as EF sadly hates Expression.Invoke
. But as John points out; if you are posting this to the database backend, the priority is irrelevant and you can also go with the logically equivalent condition1 || condition2
. You can combine expressions:
static IQueryable<T> WhereAny<T>(
this IQueryable<T> query,
params Expression<Func<T, bool>>[] predicates)
{
if (predicates == null) throw new ArgumentNullException("predicates");
if (predicates.Length == 0) return query.Where(x => false);
if (predicates.Length == 1) return query.Where(predicates[0]);
var param = predicates[0].Parameters[0];
var body = predicates[0].Body;
for (int i = 1; i < predicates.Length; i++)
{
body = Expression.OrElse(
body, Expression.Invoke(predicates[i], param));
}
var lambda = Expression.Lambda<Func<T, bool>>(body, param);
return query.Where(lambda);
}
If I missed this point please clarify ...
source to share
First: . I would really try your original code for the conditions, while its possible error in the query optimizer was most likely an error in the expression used and it really was not represented below:
var DesiredList = db.Things.Where (t => condition1 || (! condition1 && condition2));
The problem is in the query optimizer to trim the expression to condition2 only.
That should really give you the ones that match condition1, regardless of condition2 ... and those that don't match condition1 and match condition2. Instead, condition 2 is not equivalent, because that leaves records that match condition 1.
The JS version of just (condition1 || condition2) is equivalent to the above expression, because if u is a match condition 1, you already agree to condition2 and! condition2, so you already include condition2 for conditions condition1 and! condition1. If this is not what you did with the queries, then it is more clear that this is not a problem with the optimizer, but with the original expressions.
You only need complete expressions if you join 2 results with Concat instead of Union, as that would mean you get a result that matches in both expressions ... and then you will have duplicate results. But in contrast, the Where value is evaluated in the string, so you don't have such problems.
Second: From the example code, I think that what you are running into is less direct from what you will be doing in your question. You mentioned that you are getting the first tag, but what you are actually doing can be seen in this rewritten version:
public static IQueryable<BookTag> UniqueByTags(this IQueryable<BookTag> bookTags, User user)
{
return bookTags.GroupBy(BT => BT.TagId)
.Select(g => new BookTag() {
User = g.Any(bt => bt.UserId == user.Id) ? user : g.First().User,
Tag = g.First().Tag, Book = bookTags.First().Book
});
}
What the comment mentions looks more like:
public static IQueryable<BookTag> UniqueByTags(this IQueryable<BookTag> bookTags, User user)
{
return bookTags.GroupBy(BT => BT.TagId)
.Select(g => g.Any(bt => bt.UserId == user.Id) ?
g.First(bt=>bt.UserId == user.Id) :
g.First()
);
}
source to share