13,095,218 members (78,151 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 15:59pm
Andy_L_J20.5K

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
 Karthik Bangalore 185 OriginalGriff 170 RickZeeland 95 Jochen Arndt 80 Graeme_Grant 70
 OriginalGriff 4,081 Graeme_Grant 2,222 ProgramFOX 2,047 ppolymorphe 1,731 Jochen Arndt 1,725