EF core set take extension optional

Question

I am trying to prevent my methods returning all rows from large table if user did not pass filter to method. Now I doing something like this

public IQueryable<Doc> grids (string filter)
{
     int all = int.MaxValue;
     if (string.IsNullOrEmpty(filter))
         all = 5;
     return rampa.Doc.Where(x => x.Napomena.Contains(filter)).Take(all);
}

Is there a nicer way to avoid take extension conditionally. I do not want to burden SQL server whit unnecessarily top clue.

`Is there a nicer way to avoid take extension conditionally.` What is your contrition actually for take? Make this clear. — TanvirArjel, Feb 16 '19 at 15:25
@TanvirArjel I do not know does top(int.mx) in sql query is expensive unnecessarily. Does sql execute in same speed queries whit top(max) and without it. — adopilot, Feb 16 '19 at 15:28
In many cases it's better to use paging (Skip/Take) so then it wouldn't matter if there's a filter or not. What's on the receiving end of the data? — Gert Arnold, Feb 16 '19 at 16:22

score 1 · Answer 1 · answered Feb 16 '19 at 15:18

1

Since you return an IQueryable, you can build your query conditionnaly :

public IQueryable<Doc> grids (string filter)
{
    IQueryable<Doc> items = rampa.Doc;
    if (string.IsNullOrEmpty(filter))
        items = items.Take(5);
    else 
        items = items.Where(x => x.Napomena.Contains(filter));
    return items;
}

answered Feb 16 '19 at 15:18

spidyx

1,067
9
16

If I understand ef well, this is not going to query full table from sql if there is no filter ?. Because I do not want to pull large data from sql to app – adopilot Feb 16 '19 at 15:23
In fact, the IQueryable is just representing the query. You are not returning the items here. It will execute the query against the SQL server when you are using the return value in a `foreach` or call ToList() for exemple – spidyx Feb 16 '19 at 15:47
Maybe you will want to check the generated query. If you are in a AspNetCore application, it will appear in the logs. If not, you can see this post : https://stackoverflow.com/questions/37527783/get-sql-code-from-an-ef-core-query – spidyx Feb 16 '19 at 16:01
Hi @adopilot, do you have any more questions ? I may have not directly respond to your comment, so as you say, this will query only five items when there is no filter. – spidyx Feb 22 '19 at 12:41

score 0 · Accepted Answer · answered Feb 16 '19 at 15:34

0

One of basic requirement of using Take() is you have to use OrderBy with it. Then to avoid top(int.max) write your query as follow:

public IQueryable<Doc> grids (string filter)
{ 
     IQueryable<Doc> query =  rampa.Doc;

     if (string.IsNullOrEmpty(filter))
     {
         query.Where(x => x.Napomena.Contains(filter)).Orderby(x => x.OrderByColumn).Take(5);
     }

     return query;
}

answered Feb 16 '19 at 15:34

TanvirArjel

30,049
14
78
114

2

Not quite. EF6 requires OrderBy when using `Skip`, not `Take`. EF core allows both without ordering. Which, by the way, doesn't exclude that ordering nearly always makes sense when presenting data in any UI. – Gert Arnold Feb 16 '19 at 16:26
Does sql server support take without ordering? May be not. that means EF Core putting some default ordering. – TanvirArjel Feb 16 '19 at 16:33
3

Without `OrderBy()`, SQL Server just returns TOP(5) from a query result of which the order is determined by physical storage or whichever indexes happen to come into play. The SQL won't contain an `ORDER BY`. – Gert Arnold Feb 16 '19 at 16:38
Okay! Thanks for letting me know this. – TanvirArjel Feb 16 '19 at 16:39
1

In SQL you can never rely on some natural order. The query optimizer can decide to use a different strategy at any time, when the number of records in a table changes. Without ORDER BY you can get the right order 999 times and the 1000th time, boooom! – Olivier Jacot-Descombes Feb 16 '19 at 17:09
1

@OlivierJacot-Descombes That means its always better to specifying the Order! – TanvirArjel Feb 16 '19 at 17:10

Olivier Jacot-Descombes · Answer 3 · 2019-02-16T16:14:10.417

From the perspective of the user the behavior must be understandable. He will not understand why he is getting only 5 records without filter but 25 when he enters a filter. A nice way to solve the problem, is to offer him options in a combo-box or with radio buttons etc., where he can choose the maximum number of records returned. (E.g. 10, 30 or 100). You would always have .Where(filter).Take(maxRecords) where maxRecords has been selected by the user.

Any case, the Take(max) should be performed, whether the user enters a filter or not, to never get into the situation where a filter returns more records than no filter. This can happen, if for instance the user enters a "e" as filter, then probably a high percentage of the entries will be returned.

Also make the result reproducible and comprehensible. Therefore I also suggest to apply an order before .Take(max).

with filter

result = source
    .Where(filter)
    .OrderBy(order)
    .Take(max);

or, with no filter

result = source
    .OrderBy(order)
    .Take(max);

I am considering virtual scoring of my data also as part of solution but I am little short whit time right now to properly handle server side conditional ordering and others. In first release I am just going to info user that he is seeing only top last x rows because of missing filters — adopilot, Feb 16 '19 at 15:40

EF core set take extension optional

3 Answers3