Change select conditions according to column value

Question

I want to know if it is possible to choose what column I want to compare Datetime value depending on another column's value, for example:

My model looks like this:

public class ServicosFinanceiro
{
    [Key]
    public int IdservFin { get; set; }
    public int? ParcelaAtual { get; set; }
    public DateTime? DataVencto1 { get; set; }
    public DateTime? DataVencto2 { get; set; }
    public DateTime? DataVencto3 { get; set; }
    public DateTime? DataVencto4 { get; set; }
}

I want to select with linq to sql the rows that are in the datetime range of DataVencto1 or DataVencto2 or DataVencto3 or DataVencto4 according to the ParcelaAtual value.

If ParcelaAtual = 1 i need to compare the date of DataVencto1 column. If ParcelaAtual = 2 i need to compare the date of DataVencto2 column and so on..

Currently I'm selecting all the rows and doing a foreach to check what is ParcelaAtual value and according to it I select compare the date range of the respective DataVencto column and add it to another model object.

Is it possible to achieve that on the select statement using linq to sql?

score 0 · Accepted Answer · edited May 23 '17 at 11:57

Just do something like this

from s in ServicosFinanceiro
where (s.ParcelaAtual == 1 && s.DataVencto1 >= start && s.DataVencto1 <= end) ||
      (s.ParcelaAtual == 2 && s.DataVencto2 >= start && s.DataVencto2 <= end) ||
      (s.ParcelaAtual == 3 && s.DataVencto3 >= start && s.DataVencto3 <= end) ||
      (s.ParcelaAtual == 4 && s.DataVencto4 >= start && s.DataVencto4 <= end)
select s

That's similar to what I would do using plain T-SQL.

Observe that start and end are plain C# variables, representing the date period you need to query against.

This is probably better than generating a SQL CASE statement on the where clause (which should also be possible).

Of course, OR statements are not really recommended, perfomance-wise. If this is a concern, you should just make 4 LINQ statements and combine their results.

Edit Since you don't seem to be convinced, let me give you another option:

(
    from s in ServicosFinanceiro
    where (s.ParcelaAtual == 1 && s.DataVencto1 >= start && s.DataVencto1 <= end)
    select s
).Union(
    from s in ServicosFinanceiro
    where (s.ParcelaAtual == 2 && s.DataVencto2 >= start && s.DataVencto2 <= end)
    select s
).Union(
    from s in ServicosFinanceiro
    where (s.ParcelaAtual == 3 && s.DataVencto3 >= start && s.DataVencto3 <= end)
    select s
).Union(
    from s in ServicosFinanceiro
    where (s.ParcelaAtual == 4 && s.DataVencto4 >= start && s.DataVencto4 <= end)
    select s
)

This will generate a single UNION statement, composed of 4 subqueries. This is similar to what I said about combining 4 Linq queries - but we are doing that on the database server this way (and indexes over the DataVencto1 .. DataVencto2 will be considered by the SQL Server optimizer).

I was trying to do with CASE, but why is this way better then usign CASE? Thanks — Felipe Freitas, Dec 10 '13 at 13:50
Even if you had indexes over the `DataVencto*` columns, a `CASE` statement would never use them. Using `OR` statement is sometimes better (and I say sometimes because SQL-Server is not that good optimizing `OR` predicates either - it can end up doing a full scan too). — rsenna, Dec 10 '13 at 14:00

Change select conditions according to column value

1 Answers1