12,074,449 members (60,226 online)
Rate this:
See more:
I have a Table; `Material_Price` with the following structure:

Material_ID, EffectiveDate, Price

In TSQL I do this:

```DECLARE @Id INT = 1

SELECT Price
FROM Material_Price
WHERE Material_ID = @Id
AND EffectiveDate = (SELECT (MAX)EffectiveDate
FROM Material_Price
WHERE Material_ID = @Id
AND EffectiveDate <= GetDate())
```

What is the equivalent in a linq query?
Posted 16-Feb-13 16:59pm
Andy_L_J20.2K

Rate this:

## Solution 1

Typed over my head (might have some error), should be something like:
```var effDate = (from mp in dataContext.Material_Price
where mp.Material_ID = @id and mp.EffectiveDate <= DateTime.Now
select mp.EffectiveDate).Max();

var query = from mp  in dataContext.Material_Price
where mp.Material_ID = @id and effDate.Contains(mp.EffectiveDate)
select mp.Price;```

Just in case needed, following articles would help:
Learn SQL to LINQ (Visual Representation)[^]
Converting SQL to LINQ, Part 1: The Basics (Bill Horst)[^]
Monjurul Habib 17-Feb-13 6:22am

5+
Rate this:

## Solution 2

With a few minor changes, here is the completed snippet:

```var effDate = (from mp in db.Material_Price
where mp.Material_ID == id && mp.EffectiveDate <= DateTime.Now
select mp.EffectiveDate).Max();

var query = from mp  in db.Material_Price
where mp.Material_ID == id
&& effDate.CompareTo(mp.EffectiveDate) == 0
select mp.Price;
```

Thanks Sandeep, magic!

I was hoping however that it could be done with a single statement.
v2
Monjurul Habib 17-Feb-13 6:22am

5+

Top Experts
Last 24hrsThis month
 OriginalGriff 472 Dave Kreskowiak 260 ProgramFOX 260 CPallini 210 CHill60 160
 Dave Kreskowiak 2,711 OriginalGriff 2,463 Richard MacCutchan 1,725 CPallini 1,507 CHill60 1,379