Linq To Sql vb.net help

Question

I have a table and I need to select the record based on an Id. I only want to return the record that has the Max(Date) on a date field. How do I do that?

In SQL Server I use this

SELECT * FROM dch
WHERE EffectiveDate = (SELECT MAX(EffectiveDate) FROM dch
WHERE ID = 99) AND ID = 99

How can I do this in Linq. Unfortunately this app is coded in VB.NET as we inherited it that way.

I was trying these

  Dim qry = (From dch In db.dch _
                  Where dch.Dealer.Equals(Dealer) _
                  Select dch.EffectiveDate).Max()

or

  Dim qry = (From dch In db.dch _
                  Where dch.Dealer.Equals(Dealer) _
                  Select ).Max(dch.EffectiveDate)

And obviously they do not work. Am I even close? Any ideas would be appreciated.

Thanks In Advance, Chris in San Diego

Answer 1

Please excuse any errors in my VB syntax, I'm a (mostly) C# programmer.

Dim item = db.dch.Where( Func(d) d.ID == 99 ) _
                 .OrderByDescending( Func(d) d.EffectiveDate )_
                 .Take(1)

It would actually be implemented as

select top 1 <...columns...>
from dch
where ID == 99
order by EffectiveDate desc

which I think is equivalent to your query.

Answer 2

I am not specifically answering your question. This is more one of those teach a man to fish type scenarios... but when learning Linq, bookmark this site and visit it often. 101 Linq VB Samples

Your specific answer is here under Max about halfway down.

That site will solve 80% of new LINQ questions going forward.

For the record, by your description I think this is what you want.

dim results = (from d in db.dch where d.ID == 99 select d.EffectiveDate).Max()

Granted, that will return the highest Effective date only.

Answer 3

In your second query, you have to pass a lambda expression as an argument to the Max extension method:

Dim qry = (From dch In db.dch _
          Where dch.Dealer.Equals(Dealer) _
          ).Max(Function (item) item.EffectiveDate)

Edit: Taking a second look at your question I realize that I don't really answer it, because you would need to do a second query to get the actual results. Also, the first query you tried should do exactly the same thing as my query (which is get the maximum EffectiveDate value).