Solved

LINQ to SQL

Posted on 2013-06-06
7
731 Views
Last Modified: 2013-06-06
Hello Experts,
 i need to write LINQ to SQL:  (C#)

select MAX(CreateDate) from Ecommerce.dbo.EcomRegisterOpenCloseLookup where EcomTransactionTypeID = 9
and createdate < dateadd(HOUR,-1,@maxendDate)

Thanks
Mani
0
Comment
Question by:mani_sai
  • 5
  • 2
7 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 39225832
i assume maxendDate is string so i used DateTime.Parse().
if it's DateTime then use lookup.createdate < maxendDate.AddHours(-1).
var maxCreatedDate = (from lookup in Ecommerce.dbo.EcomRegisterOpenCloseLookup 
where lookup.EcomTransactionTypeID = 9 and lookup.createdate < DateTime.Parse(maxendDate.ToString()).AddHours(-1)).Max();

Open in new window

0
 
LVL 8

Author Comment

by:mani_sai
ID: 39225855
Thanks.

I am getting error:

LINQ to Entities does not recognize the method 'System.DateTime AddHours(Double)' method, and this method cannot be translated into a store expression.

-------------------------------------

 private DateTime GetStartDate(DateTime endDate)
        {
           var getStartDate = (from ecom in _ecommerceDataConnection.EcomRegisterOpenCloseLookups
                                where ecom.EcomTransactionTypeID == 9 &&
                                ecom.CreateDate < DateTime.Parse(endDate.ToString()).AddHours(-1)
                                select (ecom.CreateDate)).Max();

           return getStartDate;

        }

--------------------------------



any idea?


Thanks
0
 
LVL 42

Accepted Solution

by:
sedgwick earned 500 total points
ID: 39225885
change this to:
 private DateTime GetStartDate(DateTime endDate)
        {
           var getStartDate = (from ecom in _ecommerceDataConnection.EcomRegisterOpenCloseLookups
                                where ecom.EcomTransactionTypeID == 9 &&
                                ecom.CreateDate < EntityFunctions.AddHours(endDate, -1).Value
                                select (ecom.CreateDate)).Max(); 

           return getStartDate;

        }

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 8

Author Comment

by:mani_sai
ID: 39225889
I tried something like this:

var getStartDate = (from ecom in _ecommerceDataConnection.EcomRegisterOpenCloseLookups
                                where ecom.EcomTransactionTypeID == 9 &&
                                ecom.CreateDate < endDate.AddHours(Convert.ToDouble(-1))
                                select (ecom.CreateDate)).Max();

still the same error...

LINQ to Entities does not recognize the method 'System.DateTime AddHours(Double)' method, and this method cannot be translated into a store expression.

Thanks
0
 
LVL 8

Author Comment

by:mani_sai
ID: 39225897
Hello sedgwick,
Thanks

What namespace to use for EntityFunctions?

Thanks
0
 
LVL 8

Author Comment

by:mani_sai
ID: 39225902
got it.

System.Data.Objects.

Thanks
0
 
LVL 8

Author Closing Comment

by:mani_sai
ID: 39225919
Awesome. It worked. Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ISS migration from IIS 6 to IIS 8.5 rendering problems 4 50
Get Reply from SSIS to intranet site when finished 3 33
Not showing JavaScript in the list 5 35
Server Error 11 47
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now