Build up IQueryable based on method parameters

Question

I have an API method similar to the below.

Is it possible to append the IQueryable so the final query has a list of items to execute a where statement or should I use the SqlQuery property and build up a raw SQL string.

    public IEnumerable<Message> GetMessagesFromApi(DateTime? dateFrom = null, DateTime? dateTo = null, int flag = -1, int messageType = 1, bool media =false)
    {
        if (dateFrom == null)
            dateFrom = DateTime.MinValue;

        if (dateTo == null)
            dateTo = DateTime.MaxValue;

        IQueryable<Message> query = null;


        query = db.Messages.Where(x => x.Created >= dateFrom && x.Created <= dateTo);


        if (flag >= 0)
        {
            //Append the where statement on dates to now include Flag
            query = query.Where(x => x.Flag == flag);
        }

       //Here it should execute against Where date >= and date <= and flag = 1
    }

Answer 1

Is it possible to append the IQueryable so the final query has a list of items to execute a where statement.

Yes. Your code should work fine. You just need to execute this if you want to build an IEnumerable<Message> for the result.

//Here it should execute against Where date >= and date <= and flag = 1
return query.ToList();

Note that you could use AsEnumerable() or even just return query directly (since IQueryable<T> implements IEnumerable<T>), but this will cause problems if a user enumerates your results more than once, as each enumeration will hit the DB.

I would personally write this as:

public IEnumerable<Message> GetMessagesFromApi(DateTime? dateFrom = null, DateTime? dateTo = null, int flag = -1, int messageType = 1, bool media =false)
{
    IQueryable<Message> query = db.Messages;

    if(dateFrom.HasValue)
        query = query.Where(x => x.Created >= dateFrom);
    if(dateTo.HasValue)
        query = query.Where(x => x.Created <= dateTo);
    if(flag >= 0)
        query = query.Where(x => x.Flag == flag);
    // others as needed, such as media...

    return query.ToList();
}