具有不同过滤器的实体框架 6 查询 [英] Entity Framework 6 query with Distinct filter

问题描述

我有问题.当我运行下面的代码时:

I have a problem. When I run the code below:

var data = context.TableX.Where(w => w.userId == 9999&& w.id == 9999) .Distinct().ToList();

这是生成的查询:

SELECT [Extent1].[id] AS [id], [Extent1].[name] AS [name], [Extent1].[companyId] AS [companyId], [Extent1].[userId] AS [userId] FROM [TableX] AS [Extent1] WHERE (9999 = [Extent1].[userId]) AND (9999= [Extent1].[id]) -- Executing at 01/06/2016 17:28:01 -03:00 -- Completed in 271 ms with result: SqlDataReader

我想知道您是否可以使不同"与查询一起运行，如下所示:

I wonder if you can make the "Distinct" to run with the query as follows:

SELECT DISTINCT id, name, companyId AS type FROM TableX WHERE id=9999 AND userId=9999

谢谢.

推荐答案

要获取你想要的查询，你需要先调用一个Select，然后再调用Distinct来获取您需要应用不同的列:

To obtain the query you want you need to call first a Select before call Distinctto get only the columns you need to apply a distinct:

var data = context.TableX.Where(w => w.userId == 9999&& w.id == 9999)
                         .Select(e=>new {e.id, e.name, e.companyId}) 
                         .Distinct()
                         .ToList();

更新 1

我很确定第一个查询应该可以工作，但无论如何另一个解决方案可能是通过以下方式应用组:

Update 1

I'm pretty sure the first query should work but anyways another solution could be applying a group by:

var data = context.TableX.Where(w => w.userId == 9999&& w.id == 9999)
                         .GroupBy(e=>new {e.id, e.name, e.companyId}) 
                         .Select(g=>new{g.Key.id, g.Key.name, g.Key.companyId})
                         .ToList();

更新 2

现在我在另一个上下文中测试了 LinqPad 中的第一个查询，但使用了相同的想法:

Update 2

Now I tested the first query in LinqPad in another context but using the same idea:

var query=Agencies.Where(a=>a.StatusId==1)
                  .Select(e=>new{e.StateId, e.AgencyName})
                  .Distinct()
                  .Dump();

这是生成的sql:

-- Region Parameters
DECLARE @p0 Int = 1
-- EndRegion
SELECT DISTINCT [t0].[stateId] AS [StateId], [t0].[agencyName] AS [AgencyName]
FROM [Agencies] AS [t0]
WHERE [t0].[statusId] = @p0

如您所见，它应该可以工作，我真的不知道您的情况发生了什么.

As you can see, it should work, I don't really know what is happening in your case.

对要通过 LinqPad 执行的第二个查询重复相同的过程:

Repeating the same process to the second query to be executed via LinqPad:

var query=Agencies.Where(a=>a.StatusId==1)
                  .GroupBy(e=>new{e.StateId, e.AgencyName})
                  .Select(g=>new{g.Key.StateId, g.Key.AgencyName})
                  .Dump();

这是sql代码:

-- Region Parameters

DECLARE @p0 Int = 1
-- EndRegion
SELECT [t0].[stateId] AS [StateId], [t0].[agencyName] AS [AgencyName]
FROM [Agencies] AS [t0]
WHERE [t0].[statusId] = @p0
GROUP BY [t0].[stateId], [t0].[agencyName]

这篇关于具有不同过滤器的实体框架 6 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！