Linq中这个TSQL语句的等价物是什么？ [英] What is the equivalent to this TSQL statement in Linq?

问题描述

我有一张桌子; 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?

推荐答案

键入我的头（可能有一些错误），应该是这样的：

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;

一个有用的工具： LinqPad [ ^ ]



如果需要，以下文章会有所帮助：

将SQL学习到LINQ（视觉表示） [ ^ ]

将SQL转换为LINQ，第1部分：基础知识（Bill Horst） [ ^ ]

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

稍作修改，这里是完整的代码片段：

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;

谢谢Sandeep，魔术！



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

Thanks Sandeep, magic!

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

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