LINQ AND & OR query
I am starting my journey with .NET and I need a little help.
I will describe my situation using an example of what I have and what I need to do, but I do not know how to do it.
So I have a class like this
public class Ban
{
public int ID { get; set; }
public string Nick { get; set; }
public string IP { get; set; }
public string GroupName { get; set; }
}
and variable bans which are IQueryable
Then in the signature method
public IEnumerable<Ban> FindBans(Ban filter);
I need to search for this bans variable;
As i'm looking now
public IEnumerable<Ban> FindBans(Ban filter)
{
var bans = GetBansQueryable();
if (!string.IsNullOrWhiteSpace(filter.GroupName))
{
bans = bans.Where(b => b.GroupName == filter.GroupName);
}
if (!string.IsNullOrWhiteSpace(filter.Nick))
{
bans = bans.Where(b => b.Nick == filter.Nick);
}
if (!string.IsNullOrWhiteSpace(filter.IP))
{
bans = bans.Where(b => b.IP == filter.IP);
}
return bans.AsEnumerable();
}
Which filters have AND. The SQL part of the query will look like this:
... WHERE group_name = 'abc' AND nick = 'def' AND ip = 'ghi';
What I need
... WHERE group_name = 'abc' AND (nick = 'def' OR ip = 'ghi');
The whole thing has to be dynamic (unless we pass the GroupName, filter it, etc.) I have no idea how I can achieve this other than that this dynamic is manual, for example
if (!string.IsNullOrWhiteSpace(filter.GroupName) &&
string.IsNullOrWhiteSpace(filter.Nick) &&
string.IsNullOrWhiteSpace(filter.IP))
{
bans = bans.Where(b => b.GroupName == filter.GroupName);
}
else if (!string.IsNullOrWhiteSpace(filter.GroupName) &&
!string.IsNullOrWhiteSpace(filter.Nick) &&
string.IsNullOrWhiteSpace(filter.IP))
{
bans = bans.Where(b => b.GroupName == filter.GroupName && b.Nick == filter.Nick);
}
else if (!string.IsNullOrWhiteSpace(filter.GroupName) &&
!string.IsNullOrWhiteSpace(filter.Nick) &&
!string.IsNullOrWhiteSpace(filter.IP))
{
bans = bans.Where(b => b.GroupName == filter.GroupName && (b.Nick == filter.Nick || b.IP == filter.IP));
}
etc ... and now add another variable to Ban.
source to share
I think you can simplify this whole limitation like this:
bans = bans.Where(b => ( string.IsNullOrWhiteSpace(filter.GroupName) || b.GroupName == filter.GroupName )
&&
( ( string.IsNullOrWhiteSpace(filter.Nick) || b.Nick == filter.Nick )
||
( string.IsNullOrWhiteSpace(filter.IP) || b.IP == filter.IP )
)
);
source to share
You should probably take a look at Scott Hansleman's post on dynamic sql, predicate builder and linqkit:
Weekly Source 48 - DynamicQueryable makes it easy to create custom LINQ expressions
Otherwise, there is a very nice blog post about using dynamic filter with Kendo UI Network and Web Api:
source to share
You can specify a case where both nick and ip are known:
public IEnumerable<Ban> FindBans(Ban filter)
{
var bans = GetBansQueryable();
if (!string.IsNullOrWhiteSpace(filter.GroupName))
{
bans = bans.Where(b => b.GroupName == filter.GroupName);
}
if (!string.IsNullOrWhiteSpace(filter.Nick) && !string.IsNullOrWhiteSpace(filter.IP))
{
bans = bans.Where(b => b.Nick == filter.Nick || b.IP == filter.IP);
}
else if (!string.IsNullOrWhiteSpace(filter.Nick))
{
// filter.IP is empty
bans = bans.Where(b => b.Nick == filter.Nick);
}
else if (!string.IsNullOrWhiteSpace(filter.IP))
{
// filter.Nick is empty
bans = bans.Where(b => b.IP == filter.IP);
}
return bans.AsEnumerable();
}
source to share