vb.net Linq to Entity Select Distinct

Linq to Entities Select Distinct

提问by Nick

I'm not sure what I'm missing but I've been thrashing at this one all afternoon.

我不确定我错过了什么，但我整个下午都在为这个而烦恼。

I have a Sql Server view of Companies which looks like this:

我有一个公司的 Sql Server 视图，如下所示：

CompanyId varchar(30) NOT NULL
CompanyName varchar(100) NOT NULL
CompanyPriority int NULL
ConfigItem int NOT NULL

With data that looks a bit like this:

数据看起来有点像这样：

00001 | Company One | 99 | 123
00001 | Company One | 99 | 456
00001 | Company One | 99 | 789
00002 | Company Two | 99 | 123
00002 | Company Two | 99 | 456

I'm trying to get a distinct list of companies. The sql query I want to exectute is

我正在尝试获得一份不同的公司名单。我要执行的 sql 查询是

select distinct CompanyId, CompanyName,CompanyPriority from vwCompany

which gives me exactly the results I want which would be

这给了我我想要的结果

00001 | Company One | 99
00002 | Company Two | 99

But for the life of me I can't find the LINQ query that results in this sql, or anything that produces the same results.

但是在我的一生中，我找不到导致此 sql 的 LINQ 查询，或任何产生相同结果的查询。

All of the questions I've found use grouping which works fine in my unit tests but fails to return distinct results when executed against an actual database.

我发现的所有问题都使用分组，这在我的单元测试中运行良好，但在针对实际数据库执行时无法返回不同的结果。

EDIT:

编辑：

So I've tried a few things based on the answers so far.

所以我已经根据到目前为止的答案尝试了一些东西。

Dim data = _miRepository.GetCompanies().
  Select(Function(c) New With {
           .companyId = c.CompanyId,
           .companyName = c.CompanyName,
           .companyPriority = c.CompanyPriority
         }
       ).Distinct().ToList()

generates the sql

生成sql

SELECT 
1 AS [C1], 
[Extent1].[CompanyId] AS [CompanyId], 
[Extent1].[CompanyName] AS [CompanyName], 
[Extent1].[CompanyPriority] AS [CompanyPriority]
FROM (SELECT 
      [vwCompany].[CompanyId] AS [CompanyId], 
      [vwCompany].[CompanyName] AS [CompanyName], 
      [vwCompany].[CompanyPriority] AS [CompanyPriority], 
      [vwCompany].[CiId] AS [CiId]
      FROM [dbo].[vwCompany] AS [vwCompany]) AS [Extent1]

which doesn't have the distinct operator in it at all :(

它根本没有不同的运算符:(

And yes, I'm doing this in VB.NET just to make it harder to find good examples :\

是的，我在 VB.NET 中这样做只是为了更难找到好的例子：\

EDIT 2:

编辑2：

I'm trying to get as close to Eric Js answer as I can in VB.

我试图在 VB 中尽可能接近 Eric Js 的答案。

Dim data = (From c In _miRepository.GetCompanies()
            Select New With {.companyId = c.CompanyId,
                             .companyName = c.CompanyName,
                             .companyPriority = c.CompanyPriority
                            }
                          ).Distinct().ToList()

gives me

给我

SELECT 
1 AS [C1], 
[Extent1].[CompanyId] AS [CompanyId], 
[Extent1].[CompanyName] AS [CompanyName], 
[Extent1].[CompanyPriority] AS [CompanyPriority]
FROM (SELECT 
      [vwCompany].[CompanyId] AS [CompanyId], 
      [vwCompany].[CompanyName] AS [CompanyName], 
      [vwCompany].[CompanyPriority] AS [CompanyPriority], 
      [vwCompany].[CiId] AS [CiId]
      FROM [dbo].[vwCompany] AS [vwCompany]) AS [Extent1]

Still no distinct keyword to be found :(

仍然没有找到明显的关键字:(

Maybe there's a subtle difference in VB.NET that I'm missing.

也许我遗漏了 VB.NET 中的细微差别。

EDIT 3:

编辑 3：

In order to progress with the rest of this application I've given up for the moment and created a new view (vwDistinctCompanies) using the sql statement at the start of the question.

为了继续使用此应用程序的其余部分，我暂时放弃并使用问题开头的 sql 语句创建了一个新视图 (vwDistinctCompanies)。

If anyone manages to get this working in VB.NET against a Sql view please let me know. Quite why this should be so difficult in LINQ I have no idea :(

如果有人设法在 VB.NET 中针对 Sql 视图执行此操作，请告诉我。为什么这在 LINQ 中如此困难我不知道:(

回答by Eric J.

Try using .Distinct() at the end of your query, e.g.

尝试在查询结束时使用 .Distinct()，例如

(from r in ctx.MyTable where SomeCondition select r).Distinct();

http://msdn.microsoft.com/en-us/library/system.linq.enumerable.distinct.aspx

http://msdn.microsoft.com/en-us/library/system.linq.enumerable.distinct.aspx

If needed, you can also provide an IEqualityComparer. However, to use the IEqualityComparer, the resulting enumerable must first be materialized using .ToEnumerable(). Doing this means the Distinct() operation is performed on the client rather than on the DB server.

如果需要，您还可以提供 IEqualityComparer。但是，要使用 IEqualityComparer，必须首先使用 . ToEnumerable()。这样做意味着 Distinct() 操作是在客户端而不是数据库服务器上执行的。

http://msdn.microsoft.com/en-us/library/bb338049.aspx

http://msdn.microsoft.com/en-us/library/bb338049.aspx

The IEqualityComparer allows you to control exactly which records are treated as equal and therefore not distinct from other records.

IEqualityComparer 允许您准确控制哪些记录被视为相等，因此与其他记录没有区别。

If you want to select only a subset of columns of your table, change

如果您只想选择表的一部分列，请更改

select r

to select either an anonymous type like this:

选择这样的匿名类型：

(from r in ctx.MyTable where SomeCondition
    select new 
    { 
        CompanyId = r.CompanyId, 
        CompanyName = r.CompanyName, 
        CompanyPriority = r.CompanyPriority
    }
).Distinct();

or if you need a strongly typed object (for example, because you are using strongly typed views in MVC:

或者如果您需要一个强类型对象（例如，因为您在 MVC 中使用强类型视图：

public class CompanyModel
{
    public int CompanyId { get; set; }
    public string CompanyName { get; set; }
    public int CompanyPriority { get; set; }
}

// Then in the Linq statement

(from r in ctx.MyTable where SomeCondition
    select new CompanyModel()
    {
        CompanyId = r.CompanyId, 
        CompanyName = r.CompanyName, 
        CompanyPriority = r.CompanyPriority
    }
).Distinct();

回答by Steven

-EDITED:-

-编辑：-

Ignore all my code that I mentioned earlier (everything after the end edit section). I tried further test. Plug the following VB code and tell me what results you got:

忽略我之前提到的所有代码（结束编辑部分之后的所有内容）。我尝试进一步测试。插入以下VB代码并告诉我你得到了什么结果：

(From c In ctx.Companies Select New With { _
    Key .companyId = c.CompanyId, _
    Key .companyName = c.CompanyName, _
    Key .companyPriority = c.CompanyPriority _
}).Distinct()

I tested them using LINQPad and I got the following SQL:

我使用 LINQPad 对它们进行了测试，得到以下 SQL：

SELECT DISTINCT [t0].[CompanyId] AS [companyId],
                [t0].[CompanyName] AS [companyName],
                [t0].[CompanyPriority] AS [companyPriority]
FROM [Companies] AS [t0]

-END EDIT-

-结束编辑-

I had this problem few days ago. This is what I ended up doing.

几天前我遇到了这个问题。这就是我最终要做的。

What you are looking for would require the GroupByclause as you mentioned in your post. Just using Distinctwill not work as how you would expect (As far as I know). The following lambda expression is what I did and right after is the SQL statement that is generated through the lambda code.

您正在寻找的内容需要GroupBy您在帖子中提到的条款。仅使用Distinct不会像您期望的那样工作（据我所知）。以下 lambda 表达式是我所做的，紧接着是通过 lambda 代码生成的 SQL 语句。

Lambda Code:

拉姆达代码：

Companies.GroupBy(c => new {
                           c.CompanyId,
                           c.CompanyName,
                           c.CompanyPriority
                       })
         .Select(p => new {
                          CompanyId = p.Key.CompanyId,
                          CompanyName = p.Key.CompanyName,
                          CompanyPriority = p.Key.CompanyPriority
                      })

SQL Code:

SQL 代码：

SELECT [t0].[CompanyId] AS [companyId],
       [t0].[CompanyName] AS [companyName],
       [t0].[CompanyPriority] AS [companyPriority]
FROM [Companies] AS [t0]
GROUP BY [t0].[CompanyId],
         [t0].[CompanyName],
         [t0].[CompanyPriority]

回答by Andrew Morton

Shouldn't that be two tables? One with

不应该是两张桌子吗？一个与

00001 | Company One | 99
00002 | Company Two | 99

And the other with

而另一个与

00001 | 123
00001 | 456
00001 | 789
00002 | 123
00002 | 456

which is more normalized and would make your query really easy?

哪个更规范化并且会让您的查询变得非常简单？

回答by Joel Etherton

Try:

尝试：

var results = (from company in context.Companies
    select new {
        CompanyId = company.CompanyId,
        CompanyName = company.CompanyName,
        CompanyPriority = company.CompanyPriority
    }).Distinct();

The key is to get the pieces of the query that CAN be unique together, then call the .Distinct()extension. If you leave the configID property in there, they will all be unique.

关键是将查询中可以唯一的部分放在一起，然后调用.Distinct()扩展。如果您将 configID 属性留在那里，它们都将是唯一的。

回答by Sergey Berezovskiy

var query = from c in context.vwCompany
            group c by new { 
                c.CompanyId, 
                c.CompanyName, 
                c.CompanyPriority } into g
            select g.Key;

Generated query (from SQL Server Profiler) will look like:

生成的查询（来自 SQL Server Profiler）将如下所示：

SELECT 
1 AS [C1], 
[Distinct1].[CompanyId] AS [CompanyId], 
[Distinct1].[CompanyName] AS [CompanyName]
[Distinct1].[CompanyPriority] AS [CompanyPriority]
FROM ( SELECT DISTINCT 
    [Extent1].[CompanyId] AS [CompanyId], 
    [Extent1].[CompanyName] AS [CompanyName]
    [Extent1].[CompanyPriority] AS [CompanyPriority]
    FROM [dbo].[vwCompany] AS [Extent1]
) AS [Distinct1]