Linq中这个TSQL语句的等价物是什么？

问题描述

我有一张桌子; Material_Price 具有以下结构：



Material_ID，EffectiveDate，Price



在TSQL中我这样做：

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

linq查询中的等价物是什么？

What is the equivalent in a linq query?

推荐答案

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)[^]

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;

谢谢Sandeep，魔术！



我是希望可以用一个语句来完成。

Thanks Sandeep, magic!

I was hoping however that it could be done with a single statement.

这篇关于Linq中这个TSQL语句的等价物是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！