Linq Where Clause ‘AND’ ‘OR’

just wanna know how to use Linq if i have this code.

private IQueryable<viewTable> BaseQuery(Models.TestDataContext db)
{
   IQueryable<viewTable> query = db.viewTables;

   foreach (string row in gridCompany.GridView.GetSelectedFieldValues("CompanyCode"))
        query = query.Where(t => t.Comp_Code == row.ToString());

   return query;
}

This code return query
SELECT [t0].[Column]
FROM [dbo].[viewTable] AS [t0]
WHERE ([t0].[CompanyCode] = 'ALPHA') **AND** ([t0].[CompanyCode] = 'BETA')
ORDER BY [t0].[EditDate] DESC

How to achieve query result to
SELECT [t0].[Column]
FROM [dbo].[viewTable] AS [t0]
WHERE ([t0].[CompanyCode] = 'ALPHA') **OR** ([t0].[CompanyCode] = 'BETA')
ORDER BY [t0].[EditDate] DESC

Thanks in advance.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

If I understand your requirement correctly then you would like to use the IN clause rather than the OR operator

SELECT [t0].[Column]
FROM [dbo].[viewTable] AS [t0]
WHERE ([t0].[CompanyCode] IN ('ALPHA','BETA')
ORDER BY [t0].[EditDate] DESC

This can be achieved with the following Linq2Entities query:
(I assumed that you are using ASPxGridView.)

private IQueryable<viewTable> BaseQuery(Models.TestDataContext db)
{
    var companyCodes = gridCompany.GridView.GetSelectedFieldValues("CompanyCode")
        .Select(row => row.ToString())
        .ToList();

    return db.viewTables.Where(t => companyCodes.Contains(t.Comp_Code));
}

Method 2

Using LINQKit you can create extension methods for generically testing collections as filters for LINQ to databases:

public static class LinqKitExt { // using LINQKit
#region Predicates
    // searchTerms - IEnumerable<TSearch> where one must match for a row
    // testFne(row,searchTerm) - test one of searchTerms against a row
    // r => searchTerms.All(s => testFne(r,s))
    public static Expression<Func<T, bool>> AnyIs<T, TSearch>(this IEnumerable<TSearch> searchTerms, Expression<Func<T, TSearch, bool>> testFne) {
        var pred = PredicateBuilder.New<T>();
        foreach (var s in searchTerms)
            pred = pred.Or(r => testFne.Invoke(r, s));

        return (Expression<Func<T, bool>>)pred.Expand();
    }
#endregion

#region Filters
#region row matches any of searchTerms
    // searchTerms - IEnumerable<TSearch> where one must match for a row
    // testFne(row,searchTerm) - test one of searchTerms against a row
    // dbq.Where(r => searchTerms.Any(s => testFne(r,s)))
    public static IQueryable<T> WhereAny<T,TSearch>(this IQueryable<T> dbq, IEnumerable<TSearch> searchTerms, Expression<Func<T, TSearch, bool>> testFne) =>
        dbq.Where(searchTerms.AnyIs(testFne));
    public static IQueryable<T> WhereAny<T,TSearch>(this IQueryable<T> dbq, Expression<Func<T, TSearch, bool>> testFne, IEnumerable<TSearch> searchTerms) =>
        dbq.Where(searchTerms.AnyIs(testFne));
    public static IQueryable<T> WhereAny<T,TSearch>(this IQueryable<T> dbq, Expression<Func<T, TSearch, bool>> testFne, params TSearch[] searchTerms) =>
        dbq.Where(searchTerms.AnyIs(testFne));
#endregion
#endregion
}

Of course, there are natural corresponding methods for AllAre / SplitContains / WhereAll / WhereSplitContains, etc.

With these extensions available, your query becomes:

IQueryable<viewTable> query = db.viewTables.WhereAny(gridCompany.GridView.GetSelectedFieldValues("CompanyCode"), (v,s) => v == s);

Note: For situations where Contains works, that is probably a better choice.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x