C # UWP Windows 10 project.

It is necessary to implement a sample filter from the table in many ways (custom filter). Although we know the entire list of parameters, the problem is that it is unknown what options will be selected in advance, another problem is that, in some fields, a multiple filter is possible (IN, BETWEEN ...) in this case the parameter will be a List or an array.

How to implement this?

UPD: Now there is no filter, it just loads the table into the list

  public static List<Transaction> Transactions() { using (var db = new SQLiteConnection(DBInitializer.SQLITE_PLATFORM, DBInitializer.DB_PATH)) return db.Table<Transaction>().ToList(); }

UPD2: Request Example:

 SELECT * FROM Transaction WHERE `CreatedTime` >= @Value1 AND CreatedTime <= @Value2 AND `Tags` in {"Tag1", "Tag2", "Tag3"}...

At the end where the IN instruction is, we have a List or an array which somehow needs to be inserted there, the number of elements is not known in advance. Well, we must bear in mind that these parameters may not be.

About Linq - in principle, under the conditions you can go with if and for each re-filter the list (although this option resembles a костыль ), but what to do with the array?

  • What does the method code look like now? - George Polevoy
  • @GeorgePolevoy Added to question - SYL
  • Linq to sql will help you, give a specific example, you can answer more directly. - koks_rs
  • @koks_rs added a question - SYL

1 answer 1

  public static List<Transaction> Transactions(string start, string end, List<string> filterTags) { using (var db = new SQLiteConnection(DBInitializer.SQLITE_PLATFORM, DBInitializer.DB_PATH)) { var query = db.Table<Transaction>(); if(filterTags.Count> 0) query = query.Where(u => filterTags.Contains(u.Tags)); query = query.Where(u => u.CreatedTime > start); query = query.Where(u => u.CreatedTime < end); return query.ToList(); } }

In this example, you build a sql query and execute it only in query.ToList(); that is, linq will make only one query to the database instead of, as it seemed, three.

  • This may well work)) - SYL