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

Jon Skeet · Accepted Answer · 2009-10-26 13:41:23Z

9

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();
}

edited Oct 26, 2009 at 13:41

answered Oct 26, 2009 at 13:02

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Marc Over a year ago

Ok I hoped it's possible to do with complex where clauses but I do it so now. thx.

Marc Over a year ago

but how to handle it if i give both parameters?

Jon Skeet Over a year ago

@JustLoren: Fixed, thanks. @snarebold: It will work just fine with both parameters - if you specify two "where" clauses, it'll act as a logical "AND".

Marc Over a year ago

i can't make nullable string. my parameters are strings and i have to conver it. because they come from webservice and you input the date there by a textbox

Jon Skeet Over a year ago

Strings are always nullable - they're reference types.

Yogesh · Accepted Answer · 2009-10-26 13:20:03Z

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...

Either of the parameters are null
Both are null
Both are not null

answered Oct 26, 2009 at 13:20

Yogesh

14.6k6 gold badges47 silver badges72 bronze badges

2 Comments

daxsorbito Over a year ago

This is a pretty good solution but this cancels out the item in the result if the goTime is null (in the database) and passing a null value in goTime parameter (with a non-null startTime).

Yogesh Over a year ago

You are right. But as he did not mentioned that the fields are nullable in the database, I assumed they are not. :)

Marc · Accepted Answer · 2009-10-26 13:25:19Z

0

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.

answered Oct 26, 2009 at 13:25

Marc

2,8593 gold badges36 silver badges44 bronze badges

1 Comment

Jon Skeet Over a year ago

I suspect your strings are in the wrong format then - or at least that the parsing isn't propagated appropriately. I suggest you parse them before you use them in the query.

daxsorbito · Accepted Answer · 2009-10-26 13:37:43Z

0

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;
    }

answered Oct 26, 2009 at 13:37

daxsorbito

1,82021 silver badges24 bronze badges

2 Comments

Yogesh Over a year ago

This will fail if both the values are not null. He did'nt mentioned that he needs both parameters in the question, but as a comment to Jon's answer.

daxsorbito Over a year ago

You're right, I missed that one. I think you could be played around modifying the where clause, adding restriction if both are null.

Collectives™ on Stack Overflow

linq query with parameter which can be empty

4 Answers 4

5 Comments

2 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related