Filter per parameter or all

Asked

Viewed 106 times

1

I make an appointment at the bank.

I would like to filter the column ID_LEAD by the filter value "if there is" int?(filter.code) or filter all over the world.

follows the example of how this in the bank to process

WHERE ---- @ID_LEAD ----
LE.ID_LEAD = ISNULL(@ID_LEAD,ID_LEAD)

I would like to do this in my consultation 

var lead = (from l in this.DB.BGC_LEAD
                                  where l.ID_LEAD == ).ToList();
c# sql linq

  • You want to validate if the codigo is null or if he is zero? (To bring all the records).

  • Good question! I got that question too.

  • I want to know if the filter.code and null, if it is null it will not be a filter, otherwise I want to filter by its value;

  • @Juniortorres That’s exactly what I did at my answer

  • @jbueno it returns all results passing null even the other records not being null in column ID_LEAD ?

  • Of course, that’s what the expression says.

Show 1 more comment

3 answers

2

Would this be:

var leadId = 0;

if(parametro == 1)
 leadId = 1
else
 leadId = 2;

var lead = (from l in this.DB.BGC_LEAD
                                  where l.ID_LEAD ==leadId).ToList();

1


Just check if there is a value in the variable that stores the filter and work on a boolean expression.

Example

If he goes nullable and null represent the absence of parameter (i.e., return all records):

var lead = (from l in this.DB.BGC_LEAD
            where filtro.codigo == null || l.ID_LEAD == filtro.codigo.Value)
            .ToList();

If he’s a int normal and 0 represent the absence of parameter:

var lead = (from l in this.DB.BGC_LEAD
            where filtro.codigo == 0 || l.ID_LEAD == filtro.codigo)
            .ToList();

1

First, in your SQL query you should do so: 

WHERE LE.ID_LEAD = 0 OR ID_LEAD = @ID_LEAD

On LINQ in the same way:

//pId é seu parâmetro (não nulo)
var lead = (from l in this.DB.BGC_LEAD
            where l.ID_LEAD == pId || pId == 0).ToList();

If the zero value is passed as parameter, then it will be true for all occurrences, if you pass something, you will only consider ID_LEAD equal to the parameter.

  • Friend, the filter. Code comes from the view, it can be null yes. so if it comes null, I don’t want to filter by it, in case it comes filled I want to filter by it

Browser other questions tagged c# sql linq

You are not signed in. Login or sign up in order to post.