Solved

LINQ to SQL

Posted on 2013-06-06
7
742 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
ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
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

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

803 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