Dapper Abfrage mit dynamischer Filterliste

Question

Dapper Abfrage mit dynamischer Filterliste

c# dapper sql

Frage

Ich habe ac # mvc App mit Dapper. Es gibt eine Listen-Tabellenseite, die mehrere optionale Filter (sowie Paging) enthält. Ein Benutzer kann (oder nicht) einen von mehreren (ungefähr 8 im Moment, aber könnte wachsen) Filter auswählen, jeder mit einem Dropdown für einen von Wert und Wert. So könnte beispielsweise ein Benutzer die Kategorie "Preis" auswählen und vom Wert "100" auf den Wert "200" filtern. Ich weiß jedoch nicht, wie viele Kategorien der Benutzer vorher filtert, und nicht alle Filterkategorien sind vom selben Typ (einige int, einige dezimal / doppelt, einige DateTime, obwohl sie alle als string in FilterRange ) .

Ich versuche, dafür eine (relativ) einfache und dennoch nachhaltige Dapper-Abfrage zu erstellen. Bisher habe ich das:

public List<PropertySale> GetSales(List<FilterRange> filterRanges, int skip = 0, int take = 0)
{
    var skipTake = " order by 1 ASC OFFSET @skip ROWS";
    if (take > 0)
        skipTake += " FETCH NEXT @take";

    var ranges = " WHERE 1 = 1 ";

    for(var i = 0; i < filterRanges.Count; i++)
    {
        ranges += " AND @filterRanges[i].columnName BETWEEN @filterRanges[i].fromValue AND @filterRanges[i].toValue ";
    }

    using (var conn = OpenConnection())
    {

        string query = @"Select * from  Sales " 
            + ranges
            + skipTake;

        return conn.Query<Sale>(query, new { filterRanges, skip, take }).AsList();
    }
}

I Fehlermeldung "... filterRanges kann nicht als Parameterwert verwendet werden".

Ist das in Dapper überhaupt möglich? Alle IEnumerable Beispiele, die ich sehe, sind where in _ die nicht zu dieser Situation passen. Jede Hilfe wird geschätzt.

nurdyguy

Beliebte Antwort

Sie können eine Liste dynamischer Spaltenwerte verwenden, aber Sie können dies auch nicht für den Spaltennamen tun, wenn Sie das Zeichenfolgenformat verwenden, das eine SQL-Injection verursachen kann.

Sie müssen die Spaltennamen aus der Liste validieren, um sicherzustellen, dass sie wirklich existieren, bevor Sie sie in einer SQL-Abfrage verwenden.

So können Sie die Liste der filterRanges dynamisch verwenden:

const string sqlTemplate = "SELECT /**select**/ FROM Sale /**where**/ /**orderby**/";

var sqlBuilder = new SqlBuilder();
var template = sqlBuilder.AddTemplate(sqlTemplate);

sqlBuilder.Select("*");

for (var i = 0; i < filterRanges.Count; i++)
{
    sqlBuilder.Where($"{filterRanges[i].ColumnName} = @columnValue", new { columnValue = filterRanges[i].FromValue });
}

using (var conn = OpenConnection())
{
    return conn.Query<Sale>(template.RawSql, template.Parameters).AsList();
}

Popa Andrei

Meer bekijken op Stack Overflow

nurdyguy · Accepted Answer · 6/20/2018 8:25:28 PM

Ich konnte dafür eine Lösung finden. Der Schlüssel war, die List in ein Dictionary zu konvertieren. Ich habe eine private Methode erstellt:

private Dictionary<string, object> CreateParametersDictionary(List<FilterRange> filters, int skip = 0, int take = 0)
{
    var dict = new Dictionary<string, object>()
    {
        { "@skip", skip },
        { "@take", take },
    };

    for (var i = 0; i < filters.Count; i++)
    {
        dict.Add($"column_{i}", filters[i].Filter.Description);

        // some logic here which determines how you parse
        // I used a switch, not shown here for brevity
        dict.Add($"@fromVal_{i}", int.Parse(filters[i].FromValue.Value));
        dict.Add($"@toVal_{i}", int.Parse(filters[i].ToValue.Value));                         
    }
    return dict;
}

Dann um meine Anfrage zu erstellen,

var ranges = " WHERE 1 = 1 ";
for(var i = 0; i < filterRanges.Count; i++)
    ranges += $" AND {filter[$"column_{i}"]} BETWEEN @fromVal_{i} AND @toVal_{i} ";

Besonderer Hinweis: Seien Sie hier sehr vorsichtig, da der Spaltenname kein Parameter ist und Sie sich selbst bis zur Injektion öffnen könnten (wie @Popa in seiner Antwort bemerkt hat). In meinem Fall stammen diese Werte aus einer Enum-Klasse und nicht aus dem Benutzer, also bin ich sicher.

Der Rest ist ziemlich geradlinig:

using (var conn = OpenConnection())
{
    string query = @"Select * from  Sales " 
        + ranges
        + skipTake;

    return conn.Query<Sale>(query, filter).AsList();
}

Dapper Abfrage mit dynamischer Filterliste

Frage

Fastest Dapper Plus Extensions

Akzeptierte Antwort

Beliebte Antwort

Performance

Expression Evaluator