linq query with parameter which can be empty

Question

I have 2 important date fields in db.

startTime and goTime

I like to create custom query in which one parameter may be empty, see my example

public List<Type> GetAll( DateTime startTime, DateTime goTime )
{
List<Type> getResultBetween =

   (from i in DB.TABLENAME
    where i.startTime >= startTime && i.goTime == ANYTHING
    select i).ToList();
    return getResultBetween;
}

So goal is now that I get all up to the given startTime even the goTime is not defined. It should also work if I define the goTime and let the Starttime empty. Resault should give me all till the gotime.

Thank you

Answer 1

Try something like this, using nullable types and building up the query explicitly:

public List<Type> GetAll(DateTime? startTime, DateTime? goTime )
{
    IQueryable<Type> query = DB.TABLENAME;
    if (startTime != null)
    {
        query = query.Where(i => i.startTime >= startTime.Value);
    }
    if (goTime != null)
    {
        query = query.Where(i => i.goTime == goTime.Value);
    }
    return query.ToList();
}

Answer 2

Try this "hacked" where clause:

where (i.startTime >= (startTime ?? i.startTime)) && (i.goTime >= (goTime ?? i.goTime))

For this to work, startTime and goTime should be Nullable<DateTime> (or DateTime?).

This will work in all scenarios, i.e. when...

1. Either of the parameters are null
2. Both are null
3. Both are not null

Answer 3

Strange it doesn't work. for more information. it's a webservice but I hope this is not the problem.

my method looks like.

public List<FDPGeschaefte> AlleGeschaefteZwischenBestimmtemDatum(string StartDatum, string EndDatum)
{
    IQueryable<FDPGeschaefte> query = DB.FDPGeschaefte;
    if (StartDatum != null && EndDatum != null)
    {
        query = query.Where(i => i.SysCreated > DateTime.Parse(StartDatum) && i.SysCreated <= DateTime.Parse(EndDatum));
    }
    if (StartDatum != null)
    {
        query = query.Where(i => i.SysCreated >= DateTime.Parse(StartDatum));
    }
    if (EndDatum != null)
    {
        query = query.Where(i => i.SysCreated <= DateTime.Parse(EndDatum));
    }
    return query.ToList();
}

if i type just one parameter in the webservice. it throws an invalid datetime argument.

Answer 4

Hi you could try something like this.

public static List<Type> GetAll(DateTime? startTime, DateTime? goTime)
    {
        List<Type> getResultBetween =
            (from i in DB.TableName
             where (startTime.HasValue && i.StartTime >= startTime)
                   || (goTime.HasValue && i.GoTime >= goTime)
             select i).ToList();
        return getResultBetween;
    }