• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 18988
  • Last Modified:

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?
0
hijiki7777
Asked:
hijiki7777
  • 5
  • 5
  • 2
  • +1
3 Solutions
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 5
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now