Advanced query in Dapper

Question

I have the following object structure in a c# object

new [] {
    new SomeType {
       Id = XXX,
       SomeSubType = new []{z,y,x,w}
    }, 
    .
    .
    .
}

I am trying to create something like the following sql query for Dapper.

SELECT *
FROM some_table, some_other_table
WHERE (X = XXX/*@Id*/ AND Y IN (z, y, x, w) /*@SomeSubType*/)
       OR (X = AND Y IN (....))
       OR (....)
       OR (....)
       ..... )

I could dynamically create the query, according to the object, and create a lot of parameters for each option - but that is a pain.

Can anyone think of a nicer way to do it? maybe a better query that makes sure both conditions are met?

May be you should read the flat list data (from your joins) and group them in C# — Shyju, Oct 26 '16 at 14:40
that is actually not a bad Idea, I think that is what I'll do. — gilmishal, Oct 26 '16 at 14:51
I'd say dynamically creating the query is the best bet, but depending on the types of X and Y you could combine them like `X + ' ' + Y IN @Combined` and then `@Combined` would be `stuff.SelectMany(i => i.SomSubType.Select(j => i.X + " " + j.Y))` But that could result in an inefficient query. — juharr, Oct 26 '16 at 14:52
That's an interesting idea, however - my ids are int and converting it to a string might affect performance in a bad way - as for creating a dynamic query - it might be more expensive than @shyju's option — gilmishal, Oct 26 '16 at 14:57

score 1 · Answer 1 · edited May 23 '17 at 12:01

1

I think what you are looking for is Dapper's SqlBuilder()?

https://github.com/StackExchange/dapper-dot-net/blob/master/Dapper.SqlBuilder/SqlBuilder.cs

How do I build a dynamic sql query with Dapper.SqlBuilder and OrWhere

edited May 23 '17 at 12:01

Community

1
1

answered Oct 26 '16 at 16:44

Zabbu

1,387
1
8
11

score 1 · Answer 2 · answered Aug 07 '20 at 23:19

If I understood correct you have a list of possible OR filters like this:

var filters = new SomeType[] {
    new SomeType { Id = ..., Subtypes = new []{z,y,x,w} }, 
    new SomeType { Id = ..., Subtypes = new []{z,y,x,w} }, 
    new SomeType { Id = ..., Subtypes = new []{z,y,x,w} }, ...
}

You can easily create that dynamic condition using DapperQueryBuilder:

var query = cn.QueryBuilder($@"
    SELECT *
    FROM some_table, some_other_table
    /**where**/");

// by default multiple filters are combined with AND
query.FiltersType = Filters.FiltersType.OR; 

foreach (var filter in filters)
    query.Where($"X = {filter.Id} AND Y IN {filter.Subtypes}");

var results = query.Query<YourPOCO>();

Disclaimer: I'm one of the authors of this library

Advanced query in Dapper

2 Answers2