sql - LINQ to SQL 查询帮助 : Doing a distinct on two columns together?

Question

如何根据 FromLinkID 和 ToLinkID 一起获得 DISTINCT 结果的 SELECT ？

如果我有一个包含以下数据的 List，我怎样才能在 LINQ 中得到我想要的东西？

Answer 1

SELECT  DISTINCT FromLinkId, ToLinkId
FROM    mytable

要为每个 (FromLinkId, ToLinkId) 选择一条记录(具有最少 id 的记录)，假设 SQL Server:

SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY FromLinkId, ToLinkId ORDER BY id) AS rn
        FROM    mytable
        ) q
WHERE   rn = 1