C#•4w ago

Efcore Dynamic Linq, AND and OR

Hi, a textbook dynamic filter for optional properties in a dto would look like something like this

c#
var query = context.MyEntity
            .AsQueryable();
if (dto.Tags != null && dto.Tags.Any())
    query = query.Where(x => x.Any(t => dto.(t.Name)));
    .
    .
    .
    .

c#
var query = context.MyEntity
            .AsQueryable();
if (dto.Tags != null && dto.Tags.Any())
    query = query.Where(x => x.Any(t => dto.(t.Name)));
    .
    .
    .
    .

above would result in an SQL query like SELECT * FROM table WHERE condition1 AND ... Condition N. I'd like to dynamically do SELECT * FROM table WHERE condition1 OR ... Condition N. looking online, here and there, mostly people refer to using Expressions and delegates. which is fine if that's the most sane way about (I used delegates few times btw Func, Action...), Extensions? just read about them. tldr of my understanding? it allows me to compile and invoke code at runtime (cool*2). here <=(short url: og link has NO SSL!) after explaining some of the concepts/solutions, it kinda recommends using LinqKit(never touched it before). Q1: are the discussed solutions optimal way around this? Q2: is linqkit bread and butter? should I just used it instead of writing my own predicate builder with Or and other abilities? (best to think twice before adding a packages, especially if I'm gonna use it in one method at least for now) .your help is much appreciated! 🙏

37 Replies

Unknown User•3w ago

Message Not Public

AlisterKBOP•3w ago

I share my full method here https://hastebin.com/share/onoxotapot.typescript this very line in my post

C#
 query.Where(x => x.Any(t => dto.(t.Name)));

C#
 query.Where(x => x.Any(t => dto.(t.Name)));

is actually the simplified of the line below for sake of shortening the post itself:

C#
 query = query.Where(bp => bp.TagsNavigation.Any(t => dto.Tags.Contains(t.Name)));

C#
 query = query.Where(bp => bp.TagsNavigation.Any(t => dto.Tags.Contains(t.Name)));

but my main question is mostly about implementing a dynamic OR kinda query.

Hastebin

Hastebin is a free web-based pastebin service for storing and sharing text and code snippets with anyone. Get started now.

blinkbat•3w ago

query.Where(x => x is y or z), no? not understanding your problem

AlisterKBOP•3w ago

this is my DTO which has optional properties for searching

C#
public record SearchBlogDTO
{
    public List<string>? Tags { get; set; }=null;
    public List<string>? Domain { get; set; }=null;
    public string? Author { get; set; }=null;
    public DateTime? CreatedBefore { get; set; }=null;
    public DateTime? CreatedAfter { get; set; }=null;
    public string? IncludesTitle { get; set; }=null;
    public string? IncludesTitleBody { get; set; } = null;
}

C#
public record SearchBlogDTO
{
    public List<string>? Tags { get; set; }=null;
    public List<string>? Domain { get; set; }=null;
    public string? Author { get; set; }=null;
    public DateTime? CreatedBefore { get; set; }=null;
    public DateTime? CreatedAfter { get; set; }=null;
    public string? IncludesTitle { get; set; }=null;
    public string? IncludesTitleBody { get; set; } = null;
}

this query works but results in a sql query like

 SELECT * FROM table WHERE condition AND condition

 SELECT * FROM table WHERE condition AND condition

I want to dynamically do

 SELECT * FROM table WHERE condition OR condition OR condition

 SELECT * FROM table WHERE condition OR condition OR condition

C#
var query = context.BlogPosts
            .AsQueryable();
// Filter by tags
if (dto.Tags != null && dto.Tags.Any())
{
    query = query.Where(some cindition);
}
// Filter by domains
if (dto.Domain != null && dto.Domain.Any())
{
    query = query.Where(some cindition);
}
// Filter by author
if (!string.IsNullOrEmpty(dto.Author))
{
    query = query.Where(some cindition);
}
var posts = await query.ToListAsync();

return Ok(posts .ToList());

C#
var query = context.BlogPosts
            .AsQueryable();
// Filter by tags
if (dto.Tags != null && dto.Tags.Any())
{
    query = query.Where(some cindition);
}
// Filter by domains
if (dto.Domain != null && dto.Domain.Any())
{
    query = query.Where(some cindition);
}
// Filter by author
if (!string.IsNullOrEmpty(dto.Author))
{
    query = query.Where(some cindition);
}
var posts = await query.ToListAsync();

return Ok(posts .ToList());

how can I do something like that?

blinkbat•3w ago

just like that keep chaining the wheres on, if you want to keep narrowing what is dto here?

AlisterKBOP•3w ago

C#
public record SearchBlogDTO
{
    public List<string>? Tags { get; set; }=null;
    public List<string>? Domain { get; set; }=null;
    public string? Author { get; set; }=null;
    public DateTime? CreatedBefore { get; set; }=null;
    public DateTime? CreatedAfter { get; set; }=null;
    public string? IncludesTitle { get; set; }=null;
    public string? IncludesTitleBody { get; set; } = null;
}

C#
public record SearchBlogDTO
{
    public List<string>? Tags { get; set; }=null;
    public List<string>? Domain { get; set; }=null;
    public string? Author { get; set; }=null;
    public DateTime? CreatedBefore { get; set; }=null;
    public DateTime? CreatedAfter { get; set; }=null;
    public string? IncludesTitle { get; set; }=null;
    public string? IncludesTitleBody { get; set; } = null;
}

blinkbat•3w ago

so you ingest an instance of this into the method above?

AlisterKBOP•3w ago

yes you're right and when narrowing search that works, but I wanna expand the search not WHERE but OR the link I posted in the original post talks about something like this

C#

public static class PredicateBuilder
{
  public static Expression<Func<T, bool>> True<T> ()  { return f => true;  }
  public static Expression<Func<T, bool>> False<T> () { return f => false; }
 
  public static Expression<Func<T, bool>> Or<T> (this Expression<Func<T, bool>> expr1,
                                                      Expression<Func<T, bool>> expr2)
  {
    var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
    return Expression.Lambda<Func<T, bool>>
          (Expression.OrElse (expr1.Body, invokedExpr), expr1.Parameters);
  }
 
  public static Expression<Func<T, bool>> And<T> (this Expression<Func<T, bool>> expr1,
                                                       Expression<Func<T, bool>> expr2)
  {
    var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
    return Expression.Lambda<Func<T, bool>>
          (Expression.AndAlso (expr1.Body, invokedExpr), expr1.Parameters);
  }
}

C#

public static class PredicateBuilder
{
  public static Expression<Func<T, bool>> True<T> ()  { return f => true;  }
  public static Expression<Func<T, bool>> False<T> () { return f => false; }
 
  public static Expression<Func<T, bool>> Or<T> (this Expression<Func<T, bool>> expr1,
                                                      Expression<Func<T, bool>> expr2)
  {
    var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
    return Expression.Lambda<Func<T, bool>>
          (Expression.OrElse (expr1.Body, invokedExpr), expr1.Parameters);
  }
 
  public static Expression<Func<T, bool>> And<T> (this Expression<Func<T, bool>> expr1,
                                                       Expression<Func<T, bool>> expr2)
  {
    var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
    return Expression.Lambda<Func<T, bool>>
          (Expression.AndAlso (expr1.Body, invokedExpr), expr1.Parameters);
  }
}

the post is long im just pasting a sinppet here

blinkbat•3w ago

then just... don't narrow it? that code ^ seems crazy

AlisterKBOP•3w ago

fair enough how do I chain ORs with linq ? dunamically

blinkbat•3w ago

query.Where(x => cond1(x) || cond2(x))

AlisterKBOP•3w ago

all my dto properties are optional

blinkbat•3w ago

basically, "accept either of these conditions passing". I don't see what the dto has to do with it... you project into the dto from the queried rows, no?

AlisterKBOP•3w ago

public record SearchBlogDTO { public List<string>? Tags { get; set; }=null; public List<string>? Domain { get; set; }=null; public string? Author { get; set; }=null; public DateTime? CreatedBefore { get; set; }=null; public DateTime? CreatedAfter { get; set; }=null; public string? IncludesTitle { get; set; }=null; public string? IncludesTitleBody { get; set; } = null; } the controller can receive dto{Tags[]} dto{Tags[], Author:""} and so on so if I donno beforehand which of the properties are or arent there me writing query.Where(x => cond1(x) || cond2(x)) becomes tricky this is the part I can't wrap my head around

blinkbat•3w ago

use those two examples and write the full method to the best of your ability comment the part you don't know bc i simply do not understand your issue

AlisterKBOP•3w ago

fair enough, I appreciate you entertaining the idea anyway 🙏

blinkbat•3w ago

private bool Cond1(Dto dto) => string.isNullOrWhitespace(dto.Author));

private bool Cond1(Dto dto) => string.isNullOrWhitespace(dto.Author));

for instance you can also just inline it you want to use a different where depending on the Author, for instance? just make different methods

AlisterKBOP•3w ago

yes that would be one way about it thanks a lot!

blinkbat•3w ago

i think you're overcomplicating it

AlisterKBOP•3w ago

i admit i am, but in a different project where i was doing raw sql, this was simpler to achieve and its bothering me that when it comes to OR i haven't been able to find the eqivalent with efcore, and the closest I have come is the use of Extension tree,

blinkbat•3w ago

...? but that's not true .Where(x => x is y or z) this accomplishes an or back to your example, if we have an Author, how does the clause need to change? also, you don't need to do = null for all of those, it's implied

AlisterKBOP•3w ago

you're right, but what if you donno if there is gonna be a third fourth and nth condition ?

blinkbat•3w ago

you check them? idgi x is y or z or foo or baz or bar or...

AlisterKBOP•3w ago

I will look into the solution you provided and will report back if it works

blinkbat•3w ago

query.Where(x =>
 AuthorIsLeftHanded(x)
 || AuthorIsFromBolivia(x)
 || AuthorHasChickens(x)
 || ...

query.Where(x =>
 AuthorIsLeftHanded(x)
 || AuthorIsFromBolivia(x)
 || AuthorHasChickens(x)
 || ...

you can do any arbitrary conditions you want, if EF cannot compile them it will tell you

AlisterKBOP•3w ago

i admit I know very little, so forgive me if I don't understand something here query = query.Where(i => i.name == dto.name || i.age == dto.age); what happens if dto.age is null ?

blinkbat•3w ago

then it will match records with null age

AlisterKBOP•3w ago

the behaviour I want is not to include age condition if its null

blinkbat•3w ago

if you want to exclude that, i => i.name == dto.name || (dto.age != null && i.age == dto.age) it's just a chain of boolean expressions

AlisterKBOP•3w ago

ok let me try! 🙂 if this works I will postpone learning Expression tree

blinkbat•3w ago

it works :kekw:

AlisterKBOP•3w ago

I appreciate it regardless! truly! thank you for entertaining my dumb question!

blinkbat•3w ago

have you done much work with lists and filtering outside of EF? if you're primarily a SQL dev, you'll want to pick up some general linq

AlisterKBOP•3w ago

i have gotten by, by using where, disticnt, groupby and select so far I have primarily used ado.net (horrible experience) at work and currently learning efcore rebuilding few projects done in ado.net ....

blinkbat•3w ago

sure, but do you also just write data manipulation in c# (outside of queries)? because linq is fundamental (or should be, anyway) ie, learn linq first. a lot of efcore is just linq.

AlisterKBOP•3w ago

thank YOU! sorry for the headache I gave you, and thanks for you patience!

blinkbat•3w ago

no worries

Gaming

Programming

Efcore Dynamic Linq, AND and OR

Did you find this page helpful?