Silverlight, EF and LINQ

CyclingFoodmanPA posted on Thursday, September 10, 2009

I have a question and have been trying to figure out the syntax for a while.  I have a form where a user can select 2 dates (NYMEXDate and NYMEXDate1) can select a ProductId and 2 more dates (ForwardDate and ForwardDate1).

I am trying to figure out how to put this in a EF/LINQ query as follows

SELECT *
FROM NYMEXHistory nh
WHERE ((nh.NYMEXDate BETWEEN NYMEXDate AND NYMEXDate1)
AND (nh.NYMEXProductID = NYMEXProductID)
AND (nh.ForwardDate BETWEEN ForwardDate AND ForwardDate1))

The dates being passed in are SmartDates so I am thinking I have to convert them to a System.DateTime.  However, I am trying to find the Between statement in my EF syntax but cannot find it.

Any comments leading me in the right direction would be most appreciated.

Thank you in advance for your help.

CyclingFoodmanPA

 

 

JonnyBee replied on Thursday, September 10, 2009

Hi,

You must use > (GreaterThan) and < (LessThan) operators.

Hint - Check out this article on PredicateBuilder too - in LinqKit. Nice stuff from Joseph Albahari.
Allows you to write generic nested predicates too, like

public interface IValidFromTo
{
   DateTime? ValidFrom { get; }
   DateTime? ValidTo   { get; }
}

public static Expression<Func<TEntity, bool>> IsCurrent<TEntity>()
   where TEntity : IValidFromTo
{
   return e => (e.ValidFrom == null || e.ValidFrom <= DateTime.Now) &&
               (e.ValidTo   == null || e.ValidTo   >= DateTime.Now);
}

/jonnybee

CyclingFoodmanPA replied on Friday, September 11, 2009

JonnyBee,

Thanks for the input on building predicates.  That is helpfull and I am getting an understanding of it.  However, how do you build predicates for a number of conditions.  For example, I may be looking for a string that is contained in a string field and I may be looking for a particular product id also. 

In my initial example, I am looking for 2 date ranges (NYMEXDate, ForwardDate) and I may have a ProductId in there.  If ProductId = 0, then I want all records within the date range.  If ProductId != 0 then I want records within the date range AND with a ProductId = to a particular product.

I am getting there, but not quite there yet and I have a pretty complex where clause coming up.  I want to do a query on a table and there are a number of items that can be 0 or can have a number and I want to be able to selectively query my entity.

Thanks for your help,
CyclingFoodmanPA