What is the equivalent to this TSQL statement in Linq?

Question

0.00/5 (No votes)

See more:

I have a Table; Material_Price with the following structure:

Material_ID, EffectiveDate, Price

In TSQL I do this:

SQL

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_J

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2013-02-16T19:14:00

Typed over my head (might have some error), should be something like:

C#

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;

A helpful tool: LinqPad[^]

Just in case needed, following articles would help:
Learn SQL to LINQ (Visual Representation)[^]
Converting SQL to LINQ, Part 1: The Basics (Bill Horst)[^]

Andy_L_J · Accepted Answer · 2013-02-16T20:36:00

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

C#

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.

What is the equivalent to this TSQL statement in Linq?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0