Entity SQL DateDiff function

Question

I I build select and where condition against an entity ObjectSet class using
ObjectQuery.

  ObjectQuery<DbDataRecord> query = context.TestEnt.Select("it.BuySell, "
                        + "it.DepoTerm").Where("Datediff(day,it.RunDateTime,'22-11-2012')=0" );

                var a = query.ToList();
                foreach (var tmp in a)
                {
                    Console.WriteLine(tmp["BuySell"].ToString());
                }

I would like to use datediff function in my where contition , How can I do it ?

i am not sure,but is not possible in entity framework (possible in Linq). but on question, are you using Linq.Dynamic class too? — Ali Foroughi
– Ali Foroughi, Commented Dec 11, 2011 at 7:34

Ladislav Mrnka · Accepted Answer · 2011-12-11 09:28:01Z

3

Try this:

var query = context.TestEnt
                   .Where("DiffDays(it.RunDateTime,'22-11-2012')=0" );
                   .Select("it.BuySell, it.DepoTerm")

It is possible that you will have to call CreateDataTime on your string passed data to make it work. Here is list of all supported date functions in ESQL.

You can also use Linq-to-entities:

var query = context.TestEnt
                   .Where(x => SqlFunctions.DateDiff("day", x.RunDateTime,'22-11-2012') == 0)
                   .Select(x => new { x.BuySell, x.DepoTerm });

edited Dec 11, 2011 at 9:28

answered Dec 11, 2011 at 9:20

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

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

1 Comment

Gregory Nozik Over a year ago

Unfortunately "DiffDays(it.RunDateTime,'22-11-2012')=0" ) does not work

Rob Lyndon · Accepted Answer · 2013-03-13 13:03:21Z

0

The ESQL function DiffDays does work, but it's fiddly to get it right. You have to cast the string to a DateTime; however, since the text is converted to SQL using the CreateQuery<> method, you need to cast to System.DateTime or else the query will fail at runtime.

Try this:

ObjectQuery<DbDataRecord> query = context.TestEnt.Select("it.BuySell, it.DepoTerm")
    .Where("DiffDays(it.[RunDateTime],cast('22-11-2012' as System.DateTime))=0");

var a = query.ToList();
foreach (var tmp in a)
{
    Console.WriteLine(tmp["BuySell"].ToString());
}

answered Mar 13, 2013 at 13:03

Rob Lyndon

12.7k5 gold badges56 silver badges79 bronze badges

Collectives™ on Stack Overflow

Entity SQL DateDiff function

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related