0

I am creating a utility using a line to sql query which will load data in a datagrid.

As the data set is in excess of 80,000 records, filtering is handled by the linq query to keep the filtering off the UI thread. I cannot filter the data grid using a filter in WPF directly as applying the filter is handled soon the UI thread, which locks the window for the 3 minutes that the filter takes to apply.

The filtering options are controlled by a number of switches (the function as checkboxes), returning bool if checked.

I want to use these switches to affect the line query, for example, if the option of completed items is checked, the equivalent change to the line query would be to add completed equals true as a where clause, however if they are not checked, I simply wish to ignore the option.

To clarify, if the check box to show completed items is not checked, I want to show all items whether completed, or not completed. If the check box to show completed items is checked, I want to show all items where completed is equal to true.

Currently, I am adding clauses to a query object called query in the form query = query.Where(r => r.completed == true), however these need to be wrapped in an if statement - I cannot simply take the value of the switch as I want to ignore the filtering if the switch is false rather than specifically add a condition which checks a certain property is set to false.

Is there anything provided as a part of linq for implementing a variable line query in this way, or is the if block the way to go about it?

Although this has been marked as a possible duplicate, I'm not certain that it is in that the solution to the other question is to use an if statement which is what I'm looking to avoid. That question was also asked over 8 years ago, and the .NET platform has changed a lot since then. I'm aware that I can use multiple if statements to handle this, but if there is an alternative which is more efficient and/or easier to read, I'd like to use it.

As code has been asked for, 

                IsBusy = true;
                String sqlConnect = connectionString;
                DbConnection connection = EFConnectionFactory.MakeConnection(sqlConnect);

                using (var db = new DbContext(connection)) {

                    var query = from l in db.Batch
                                join d in db.Document on l.id equals d.batch_id
                                group d by new { batchId = l.id, l.batchName, Added = l.added, Status = l.status, PaginationLocked = l.OKDate } into g
                                select new {
                                    BatchId = g.Key.batchId, BatchName = g.Key.batchName, CreatedDate = g.Min(t => t.added), g.Key.PaginationLocked, FinalisedDate = g.Max(t => t.OKDate), g
                                } into res
                                select new BatchChronologyDataModel {
                                    BatchId = res.BatchId,
                                    BatchName = res.BatchName,
                                    TotalDocuments = res.g.Count(),
                                    TotalOKayed = res.g.Where(s => s.documentOKayed != null).Count(),
                                    TotalExported = res.g.Where(s => s.editLocked != null).Count(),
                                    CreatedDate = res.g.Min(t => t.added),
                                    Status = res.g.Key.Status,
                                    PaginationLocked = res.PaginationLocked != null,
                                    FinalisedDate = res.g.Max(t => t.OKDate),
                                    DurationDays = DbFunctions.DiffDays(res.CreatedDate, res.FinalisedDate),
                                    BatchAgeDays = DbFunctions.DiffDays(res.CreatedDate, DateTime.Now),
                                    IsCompleted = res.g.Max(t => t.OKDate) != null,
                                };



                if (this.ShowLongCycleBatches) {
                    query = query.Where(r => r.DurationDays > 3);
                }

                if (this.Completed) {
                    query = query.Where(r => IsCompleted == true);
                }

The issue here is simply that I have multiple clauses which have to be applied in a certain order, i.e. all batched completed will remove all uncompleted items when it runs.

I wish to show the whole data set, and filter out anything which does not match the conditions if and only if an option is selected. I do not want to assume that not checking a box is equivalent to a false value.

As there are a lot of selectable options, I'd like to know if there is a way to avoid multiple if statements - I'm not saying that I have an issue with them, but I'd worked on solutions before where I've not spotted something useful and implemented a huge solution when there was a better answer.

Alex
  • 1,643
  • 1
  • 14
  • 32
  • Can you modify the SQL string query? The you can modify the where as a string like "parm1= 123 and parm2 = 456 and parm3 = 789" – jdweng Feb 05 '18 at 11:18
  • 2
    80K is a *tiny* dataset. There's nothing wrong with `query = query.Where(r => r.completed == true)` or `if (checkBox.Checked) { query = query.Where(r => r.completed == true);}` as long as you want an `AND` clause. Why the question? Have you tried something and run into a problem? – Panagiotis Kanavos Feb 05 '18 at 11:19
  • Simply put every clause into an `if`-statement. What´s the problem on this? – MakePeaceGreatAgain Feb 05 '18 at 11:19
  • 2
    Did you think about expression trees? Like https://stackoverflow.com/questions/6295926/how-build-lambda-expression-tree-with-multiple-conditions/6295969 or http://www.dotnettricks.com/learn/linq/understanding-expression-and-expression-trees – Stephu Feb 05 '18 at 11:19
  • Somehow you need to check the conditions. Why do you want to avoid if statements, is it for readability? If the conditions are simple, may be you can use the ? : operator to build the query. – Thangadurai Feb 05 '18 at 11:19
  • 1
    Possible duplicate of [LINQ to SQL Where Clause Optional Criteria](https://stackoverflow.com/questions/632434/linq-to-sql-where-clause-optional-criteria) – Thangadurai Feb 05 '18 at 11:25
  • The size of the dataset in this case only affects the front end filtering as it takes a long time to apply, hence why it must be filtered in the query. I do not wish too modify the SQL - linq should be sufficient. Expression trees in this case would be overkill I think. I don't want to void `if` statements as such, but if there is a more succinct linq way to do this, I'd rather use it. – Alex Feb 05 '18 at 11:39
  • you can also right like this. `query = query.Where(r => (paramCompleted == false || r.completed))`. here `paramCompleted` is your `switched flag` and if it is `false` then `completed condition is not apply` else `completed == true` applied – jignesh patel Feb 05 '18 at 11:44
  • @jigneshpatel so I will get all rows where the completed property is false. I don't want this. I want to return all rows where completed = true if and only if the check box is checked. If it isn't, I don't want to apply any filter on the completed property. – Alex Feb 05 '18 at 11:47
  • @Alex it will do same as you want. if `checkbox` is `not checked` then it will not apply completed filter else it will apply filter. – jignesh patel Feb 05 '18 at 11:48
  • @jigneshpatel that's a very bad idea. It can easily result in *bad* execution plans. Why pass a constant in the query when you *don't* intend to use it anywhere? *That's* what you avoid when you construct the `Where` clause dynamically. Such "dynamic" parameters only harm performance – Panagiotis Kanavos Feb 05 '18 at 11:49
  • @Alex you still haven't posted any code or explained what the problem is. There's nothing wrong with *not* providing a parameter if you don't intend to use it. This isn't a new or hard problem either. All commercial grids handle dynamic filters. Either add `Where` calls or construct an expression tree from the filters. – Panagiotis Kanavos Feb 05 '18 at 11:50
  • @PanagiotisKanavos I agree, I am happy to not apply the filter. And if there is no alternative, I will use if statements to check whether the box is marked, and add a where clause if it is. I just wanted to know if there is a better, less verbose, possibly more efficient way. – Alex Feb 05 '18 at 12:03
  • @Alex this looks more and more like a UI question. 1) You can have three-valued checkboxes. 2) if you need to apply rules to the conditions do so *before* you pass them to LINQ or the database. – Panagiotis Kanavos Feb 05 '18 at 12:09
  • 1
    @Alex You should probably decouple the UI from query builder and eg construct a `filters` object in the UI, apply any rules you need to it, then pass it to the query builder. This will allow you to test your rules separately from the UI or the query – Panagiotis Kanavos Feb 05 '18 at 12:11
  • @PanagiotisKanavos 1) In this case, they are not check boxes. They are switch components which have true or false. 2) So, you're suggesting that I add filters in my original line where I create the query object? I'm selecting properties from the BatchChronologyDataModel which don't actually exist as properties in the DB table, such as the BatchDurationDays property. Would this not require a separate linq expression for each property? Normally, I would use straight SQL but in this project I've tried EF and found it more of a problem than a help. – Alex Feb 05 '18 at 12:15
  • @PanagiotisKanavos I've moved the query construction into a factory class, and used the dynamic linq expression library to run a query generated on a string builder. It's a pretty quick way to apply the same functionality as expression trees, but it's much faster to implement and seems to work well thus far. – Alex Feb 09 '18 at 13:34

1 Answers1

0

The solution in this case which worked for me was to use the Dynamic Expression API to form up a query in the form of a string (akin to an SQL query), and run this directly on the query in the form query.Where("Status=hidden and BatchAgeDays>10");.

Although expression trees would be a usable solution, this form is less verbose and more readable, and overall a great deal less work to implement, and allows multiple clauses to be concatenated into a single call to Where.

https://github.com/kahanu/System.Linq.Dynamic/wiki/Dynamic-Expressions

Alex
  • 1,643
  • 1
  • 14
  • 32