조건부 Linq 쿼리

조건부 Linq 쿼리

---

우리는 로그 뷰어를 만들고 있습니다. 사용에는 사용자, 심각도 등을 기준으로 필터링 할 수있는 옵션이 있습니다. SQL 날에는 쿼리 문자열에 추가했지만 Linq를 사용하여 수행하고 싶습니다. 조건부로 where 절을 추가하려면 어떻게해야합니까?

---

특정 기준이 통과 된 경우에만 필터링하려면 다음과 같이하십시오.

var logs = from log in context.Logs
           select log;

if (filterBySeverity)
    logs = logs.Where(p => p.Severity == severity);

if (filterByUser)
    logs = logs.Where(p => p.User == user);

이렇게하면 식 트리가 원하는대로 정확하게 될 수 있습니다. 이렇게하면 생성 된 SQL이 정확히 필요한 것입니다.

---

목록 / 배열을 기준으로 필터링해야하는 경우 다음을 사용하십시오.

    public List<Data> GetData(List<string> Numbers, List<string> Letters)
    {
        if (Numbers == null)
            Numbers = new List<string>();

        if (Letters == null)
            Letters = new List<string>();

        var q = from d in database.table
                where (Numbers.Count == 0 || Numbers.Contains(d.Number))
                where (Letters.Count == 0 || Letters.Contains(d.Letter))
                select new Data
                {
                    Number = d.Number,
                    Letter = d.Letter,
                };
        return q.ToList();

    }

---

Daren과 비슷한 대답을 사용했지만 IQueryable 인터페이스를 사용하여 끝냈습니다.

IQueryable<Log> matches = m_Locator.Logs;

// Users filter
if (usersFilter)
    matches = matches.Where(l => l.UserName == comboBoxUsers.Text);

 // Severity filter
 if (severityFilter)
     matches = matches.Where(l => l.Severity == comboBoxSeverity.Text);

 Logs = (from log in matches
         orderby log.EventTime descending
         select log).ToList();

데이터베이스에 도달하기 전에 쿼리를 작성합니다. 이 명령은 마지막에 .ToList ()까지 실행되지 않습니다.

---

When it comes to conditional linq, I am very fond of the filters and pipes pattern.
http://blog.wekeroad.com/mvc-storefront/mvcstore-part-3/

Basically you create an extension method for each filter case that takes in the IQueryable and a parameter.

public static IQueryable<Type> HasID(this IQueryable<Type> query, long? id)
{
    return id.HasValue ? query.Where(o => i.ID.Equals(id.Value)) : query;
}

---

Another option would be to use something like the PredicateBuilder discussed here. It allows you to write code like the following:

var newKids  = Product.ContainsInDescription ("BlackBerry", "iPhone");

var classics = Product.ContainsInDescription ("Nokia", "Ericsson")
                  .And (Product.IsSelling());

var query = from p in Data.Products.Where (newKids.Or (classics))
            select p;

Note that I've only got this to work with Linq 2 SQL. EntityFramework does not implement Expression.Invoke, which is required for this method to work. I have a question regarding this issue here.

---

I solved this with an extension method to allow LINQ to be conditionally enabled in the middle of a fluent expression. This removes the need to break up the expression with if statements.

.If() extension method:

public static IQueryable<TSource> If<TSource>(
        this IQueryable<TSource> source,
        bool condition,
        Func<IQueryable<TSource>, IQueryable<TSource>> branch)
    {
        return condition ? source : branch(source);
    }

This allows you to do this:

return context.Logs
     .If(filterBySeverity, q => q.Where(p => p.Severity == severity))
     .If(filterByUser, q => q.Where(p => p.User == user))
     .ToList();

Here's also an IEnumerable<T> version which will handle most other LINQ expressions:

public static IEnumerable<TSource> If<TSource>(
    this IEnumerable<TSource> source,
    bool condition,
    Func<IEnumerable<TSource>, IEnumerable<TSource>> branch)
    {
        return condition ? source : branch(source);
    }

---

Doing this:

bool lastNameSearch = true/false; // depending if they want to search by last name,

having this in the where statement:

where (lastNameSearch && name.LastNameSearch == "smith")

means that when the final query is created, if lastNameSearch is false the query will completely omit any SQL for the last name search.

---

It isn't the prettiest thing but you can use a lambda expression and pass your conditions optionally. In TSQL I do a lot of the following to make parameters optional:

WHERE Field = @FieldVar OR @FieldVar IS NULL

You could duplicate the same style with a the following lambda (an example of checking authentication):

MyDataContext db = new MyDataContext();

void RunQuery(string param1, string param2, int? param3){

Func checkUser = user =>

((param1.Length > 0)? user.Param1 == param1 : 1 == 1) &&

((param2.Length > 0)? user.Param2 == param2 : 1 == 1) &&

((param3 != null)? user.Param3 == param3 : 1 == 1);

User foundUser = db.Users.SingleOrDefault(checkUser);

}

---

I had a similar requirement recently and eventually found this in he MSDN. CSharp Samples for Visual Studio 2008

The classes included in the DynamicQuery sample of the download allow you to create dynamic queries at runtime in the following format:

var query =
db.Customers.
Where("City = @0 and Orders.Count >= @1", "London", 10).
OrderBy("CompanyName").
Select("new(CompanyName as Name, Phone)");

Using this you can build a query string dynamically at runtime and pass it into the Where() method:

string dynamicQueryString = "City = \"London\" and Order.Count >= 10"; 
var q = from c in db.Customers.Where(queryString, null)
        orderby c.CompanyName
        select c;

---

You can create and use this extension method

public static IQueryable<TSource> WhereIf<TSource>(this IQueryable<TSource> source, bool isToExecute, Expression<Func<TSource, bool>> predicate)
{
    return isToExecute ? source.Where(predicate) : source;
}

---

Just use C#'s && operator:

var items = dc.Users.Where(l => l.Date == DateTime.Today && l.Severity == "Critical")

Edit: Ah, need to read more carefully. You wanted to know how to conditionally add additional clauses. In that case, I have no idea. :) What I'd probably do is just prepare several queries, and execute the right one, depending on what I ended up needing.

---

You could use an external method:

var results =
    from rec in GetSomeRecs()
    where ConditionalCheck(rec)
    select rec;

...

bool ConditionalCheck( typeofRec input ) {
    ...
}

This would work, but can't be broken down into expression trees, which means Linq to SQL would run the check code against every record.

Alternatively:

var results =
    from rec in GetSomeRecs()
    where 
        (!filterBySeverity || rec.Severity == severity) &&
        (!filterByUser|| rec.User == user)
    select rec;

That might work in expression trees, meaning Linq to SQL would be optimised.

---

Well, what I thought was you could put the filter conditions into a generic list of Predicates:

    var list = new List<string> { "me", "you", "meyou", "mow" };

    var predicates = new List<Predicate<string>>();

    predicates.Add(i => i.Contains("me"));
    predicates.Add(i => i.EndsWith("w"));

    var results = new List<string>();

    foreach (var p in predicates)
        results.AddRange(from i in list where p.Invoke(i) select i);               

That results in a list containing "me", "meyou", and "mow".

You could optimize that by doing the foreach with the predicates in a totally different function that ORs all the predicates.

참고URL : https://stackoverflow.com/questions/11194/conditional-linq-queries