LINQ database query using max

In the code I find the maximum date from a table by using
DateTime maxDate = Result.Select(md => md.Date).Max();

However I want to find the next largest date.
In SQL it will be
SELECT MAX([Date]) FROM dbo.FundPrice WHERE [Date] < '2008/02/04'

I am unfamilier with the Linq syntax. I cannot seem to put in a WHERE clause, so how do I do it?
hijiki7777Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
0
hijiki7777Author Commented:
I would say that these are excellent links that I missed during my own research, so thank you for that. But none seem to match the situation I need resolving.
If I had a couple of hours spare I may be able to work it out, but for now I do not.
0
Bob LearnedCommented:
What object are you working with LINQ on?

Bob
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

hijiki7777Author Commented:
SQL
0
Bob LearnedCommented:
LINQ is a syntax that generally works with business objects, so are you getting the data into a business objects, that you can work with (i.e. Employee), so that you can use the language-specific syntax for calculating Max, applying restriction operators, etc.

Bob
0
hijiki7777Author Commented:
DateTime maxDate = Result.Select(md => md.Date).Max();

I want to convert the above to do this SQL below, where the date is the maximum date. I have that date as a DateTime field in C# (ie maxDate as above), and I expect to use that field to obtain the next highest date.
SELECT MAX([Date]) FROM dbo.FundPrice WHERE [Date] < '2008/02/04'
To be clear, imagine the maximim date is '2008/02/04' and is in the maxDate field.
I want to populate a previousMaxDate field in a similar way as in the first line.
0
hijiki7777Author Commented:
So although this works;
DateTime maxDate = Result.Select(md => md.Date).Max();

And gets me the largest date, I cannot find the second largest date unless I put the SQL data into a business object?
0
Bob LearnedCommented:
What is 'Result' defined as?

Bob
0
hijiki7777Author Commented:
A query as follows

            // DB calls for FundPrice
            LipperPerformanceDataContext context = new LipperPerformanceDataContext();
            var Result = from c in context.FundPrices
                         where c.LipperID == investmentTrustLipperID
                         select c;

I suppose I could do a similar query, filtering out the rows with the maximum dates.
That would be one way of doing it, but it means another query.
0
Bob LearnedCommented:
Let's see if I understand your requirement.

Here is a possible example of using a WHERE condition with a lambda expression:

int maxDistance = (from location in travel.PlacesVisited
                             where location.Country != "USA"
                             select location).Max(loc => loc.Distance);

Reference:

Using LINQ to SQL (Part 1)
http://weblogs.asp.net/scottgu/archive/2006/05/14/Using-LINQ-with-ASP.NET-_2800_Part-1_2900_.aspx

Bob
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PraesidiumCommented:
So you could basically do
DateTime dt = Result.Max(p => p.Date);

There are several other approaches you could take with LINQ, but I think Bob has offered you the most straight forward...  Try that...
0
CarlVerretCommented:
Have you tried the following :

dim qry = from item in table _
               select distinct date_field take 2 _
               order by date_field desc

the first record retreived would be the max date, the second one would be the next "max" date.

Carl
0
CarlVerretCommented:
sorry, I forgot something in my last post :

    Dim qry = From item In db.FundPrice _
              Order By item.date Descending _
              Select item.date Distinct Take 2
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.