I need a trick to give a querry parameter the value of "everything"

Question

User283528319 posted

hi all,

I am pretty sure you also needed this in like the situation below

find all whites  _context.colors.where(e=>e.color=="white")

find all reds _context.colors.where(e=>e.color=="red")

but how about this 

find every color  _context.colors.where(e=>e.color=="????")

ofcourse there is no need to remind that, I need to protect this structure and I can't use _context.colors (without where) to get every color in that table.

thank you

Answer 1

User753101303 posted

Hi,

Not sure to see why you absolutely have to use the where clause.. You can "enrich" a query depending on what you need ie :

var query=_context.colors;
if(!String.IsNullOrEmpty(criteria)) query=query.Where(e=>e.color==criteria);

and keep on adding other criteria as needed. You could expose this through a method with an optional parameter and that would return all colors or the color you want..

Another approach is to build a where clause which include a test on the criteria variable.

Answer 2

User283528319 posted

thanks a lot but I don't need to add I just need to change 

beside yes you can solve this problem with simple if or case but they complicate the code and I want to simplify it.

Answer 3

User475983607 posted

Simply remove the where if you do not want to filter the results.  Maybe you want logic similar to the following?

string mycolor = string.empty;
var query _context.colors;

if(!string.IsNullOrEmpty(mycolor))
{
	query =  query.where(e => e.color == mycolor);
}

var results = query.ToList();

Answer 4

User753101303 posted

Or you prefer something such as:

context.colors.where(e=>criteria=="" || e.color==criteria) for example ?

Answer 5

User283528319 posted

I was asking for e=>criteria=="" 

does this find everything?

Answer 6

User283528319 posted

Simply remove the where if you do not want to filter the results.  Maybe you want logic similar to the following?

string mycolor = string.empty;
var query _context.colors;

if(!string.IsNullOrEmpty(mycolor))
{
	query =  query.where(e => e.color == mycolor);
}

var results = query.ToList();

this is what I use now, I just wanted to know if there is faster.

Answer 7

User753101303 posted

It finds everything if criteria is "" and nothing at all otherwise (which you could just try). If you want to use a single expression you have to check if the criteria should be ignored or if it matches a color (though you have many ways to write this).

You mean "faster to type" ? If using this multiple times you could hide this behind a method such as : IEnumable<Color> GetColors(string color==null)  or maybe make this reusable through your own extension method ?

Answer 8

User475983607 posted

this is what I use now, I just wanted to know if there is faster.

What does faster mean?  Faster than what exactly?

You can always move the boolean logic to the where predicate if you like similar to above.

string mycolor = string.empty;
bool nullOrEmptyCriteria = string.IsNullOrEmpty(mycolor);
query.where(e => e.color == mycolor || nullOrEmptyCriteria);

Answer 9

User283528319 posted

what about criteria=="*" or criteria =="%" can we use these wild cards or just criteria=="" is enough to find everything.

Answer 10

User475983607 posted

what about criteria=="*" or criteria =="%" can we use these wild cards or just criteria=="" is enough to find everything.

Your actual question is how to use wildcards in string filters?

using Microsoft.EntityFrameworkCore;

var colors = _context.colors.where(e => EF.Functions.Like(e.color, "%"));

Answer 11

User283528319 posted

thanks but "like" always give me fear.

isn't that too costly comparing "==" ?

Answer 12

User475983607 posted

thanks but "like" always give me fear.

isn't that too costly comparing "==" ?

I have no idea what you are asking...

In SQL, the LIKE operator is used with wildcards.   Do you want to use a wildcard filter or not?

Answer 13

User753101303 posted

What you are showing is just comparing a variable with a "%" or "*" character. Using like would be :

_context.colors.where(e=>e.color.Contains(criteria)) which would show all for "" but could introduce bugs for other values ("white" would find "AntiqueWhite" as well which is likely not what ou want). If you don't want to use like why do you suggest yourself to use "wildcards" ? 

So what if for now you are just using a working solution such as 

_dbcontext.where(e => String.IsNullOrEmpty(mycolor) || e.color == mycolor); // String.IsNullOrEmpty can be translated at least for SQL Server

Or hide this behind your own extension method so that you don't have this to write this over and over for example :
var query=_dbcontext.Filter(e=>e.color,mycolor);

And then you'll be able to change the underlying implementation if you change  your mind about how to handle this or add support as well for other types or values, the idea compared with where is that if the criteria is null (or maybe empty for a string)), it is just ignored.

Answer 14

User1520731567 posted

Hi fatihbarut,

I was asking for e=>criteria=="" 

does this find everything?

what about criteria=="*" or criteria =="%" can we use these wild cards or just criteria=="" is enough to find everything.

==""  can't select all data,it just filter specific data whoes data is ''.

* and % in it is not a Wildcard, and == is an exact search that does not achieve the goal of filtering all colors.

It doesn't make sense to filter all colors with Where clause.

If you must use Where() clause , the way I think of is to use Where() to query data that is not empty (provided there is no null data):

var list = _context.colors.Where(x=>x.color!="").ToList();

Best Regards.

Yuki Tao