从LINQ查询获取不同的行 [英] Getting Distinct Rows from a LINQ Query
问题描述
我已经发布了一个问题，为什么Entity建模者在这里坚持使用一个唯一的密钥; http://social.msdn.microsoft.com/Forums/en-GB/adodotnetentityframework/thread/bde61fbf-2038-4ed3-a1f6-c5cfa2d3ae42
但是我对一个查询有困难这提示了这个问题。
基本上我有以下SQL Server存储过程
;
ALTER PROCEDURE [dbo]。[STGP_GetProductInteractionsBySearchText]
- 在这里添加存储过程的参数
@ProductID int，
@SearchText varchar（100）=''
AS
BEGIN
SELECT iProductID
，iInteractionSeverityID
，sInteractionSeverityText
，FormattedInteractionMessage
，sOtherDrugInteractionText
，iClinicalSetID
，iInteractionGroupID1
，iInteractionGroupID2
，iInteractionMessageID
，iInteractionMessageStyleID
，sInteractionText
，sInteractionMessageStyleText
， sInteractionMessageText
，iOtherDrugProductID
，sOtherDrugDisplayName
，iOtherDrugClinicalSetID
FROM STGV_ProductInteractions
WHERE（iProductID = @ProductID）
AND（sOtherDrugDisplayName LIKE'％'+ @ SearchText +'％'）
结束
现在，如果我将产品ID 1015729和"paracetamol"一词传递给我，那么我得到20个结果返回;
| 1015729 | 3 | 通常避免组合。仅在特殊情况下使用组合，采取任何必要措施以降低风险。 | 增加出血风险 | ASPIRIN / SODIUM SALICYLATE | 2315 | 141 | 26 | 19 | 3 | NSAIDS |
这是因为有20种产品提供相同的互动。
然而，我的用户不需要看到所有这些。他们只想看到
| 3 | 通常避免组合。仅在特殊情况下使用组合，采取任何必要措施降低风险。 | 增加出血风险 | ASPIRIN / SODIUM SALICYLATE |
这将删除为实体提供唯一密钥的productId建模者。
所以我的网页服务返回用户想要的集合如下;
公共列表< ProductInteractionSummary> GetProductInteractionsSummary（int ProductId，String searchText）
{
List< ProductInteractionSummary> list = null;
STGMDDFFormularyEntities ctx = null;
尝试
{
ctx = new STGMDDFFormularyEntities（）;
var qry = from c in ctx.STGP_GetProductInteractionsBySearchText（ProductId，searchText）
select new ProductInteractionSummary（）
{
InteractionSeverityId =（int）i.iInteractionSeverityID，
ExistingDrugInteractionText = i.sOtherDrugInteractionText，
FormattedInteractionMessage = i.FormattedInteractionMessage，
InteractionSeverityText = i.sInteractionSeverityText
};
list = qry.ToList< ProductInteractionSummary>（）;
}
catch（System.Exception ex）
{
throw ex;
}
返回清单;
}
这会返回一个只包含我需要的属性的列表。但它仍然有20条记录。
我尝试了各种形式的区别
;
例如围绕括号中的linq查询并在之后放置Distinct
var qry =（from c in ctx.STGP_GetProductInteractionsBySearchText（ProductId，searchText）
select new ProductInteractionSummary（）
{
InteractionSeverityId =（int）i.iInteractionSeverityID，
ExistingDrugInteractionText = i.sOtherDrugInteractionText，
FormattedInteractionMessage = i.FormattedInteractionMessage，
InteractionSeverityText = i.sInteractionSeverityText
}）。Distinct（）;分配到列表时
list =（List< ProductInteractionSummary>）qry.ToList< ProductInteractionSummary>（）。Distinct（）;
它们都返回相同的20条记录 - 因此当分配给网格时，我得到20行;当我只需要2
那么你如何使用Distinct，而不是针对一列 - 而是整个行中的一个列。
I不能在SQL端执行，因为那时我要么得到相同的20行，要么它们现在是不同的值，这将组成一个唯一的密钥。
我不确定我是否可以在主键中使用文本字段 - 但这需要我通过修改Entity建模器后面的XML来手动设置密钥。我还需要更改存储过程以返回我想要的字段。
在qry之后将其分配给列表
将其分配给列表后
有几个实现目标的方法：
1）使用与自定义相等比较器的区别
Distinct isn’ t doing your want，因为它正在使用Object .Equals t o比较ProductInteractionSummary的每个实例
有一个Distinct的重载接受一个IEqualityComparer< T>这可以非常简单地实现你的目标
2）在模型中创建ProductInteractionSummary作为复杂类型，并使存储过程具体化
（类型只需要一个主键，如果它们是一个完整的实体类型（允许创建/更新/删除））
步骤将是：
- 更新存储过程以进行区分，只需选择您想要的列
- 将复杂类型（如现有的ProductInteractionSummary）添加到模型
（在设计师的表面;‘右键点击 - >添加 - >复杂类型’）
- 为存储过程创建函数导入并将返回类型设置为复杂类型
（在设计师的表面上;‘右键点击 - > ;添加 - >功能导入…’）
- 创建函数导入后，您将在派生的上下文类型上有一个方法来调用函数
选项2的优点是Distinct在数据库中而不是在内存中处理
I have already posted a question as to why the Entity modeller insists on a unique key here; http://social.msdn.microsoft.com/Forums/en-GB/adodotnetentityframework/thread/bde61fbf-2038-4ed3-a1f6-c5cfa2d3ae42
However I am having difficulty with one query which prompted that question.
Basically I have the following SQL Server Stored Procedure
ALTER PROCEDURE [dbo].[STGP_GetProductInteractionsBySearchText]
-- Add the parameters for the stored procedure here
@ProductID int,
@SearchText varchar(100) = ''
AS
BEGIN
SELECT iProductID
, iInteractionSeverityID
, sInteractionSeverityText
, FormattedInteractionMessage
, sOtherDrugInteractionText
, iClinicalSetID
, iInteractionGroupID1
, iInteractionGroupID2
, iInteractionMessageID
, iInteractionMessageStyleID
, sInteractionText
, sInteractionMessageStyleText
, sInteractionMessageText
, iOtherDrugProductID
, sOtherDrugDisplayName
, iOtherDrugClinicalSetID
FROM STGV_ProductInteractions
WHERE (iProductID = @ProductID)
AND (sOtherDrugDisplayName LIKE '%' + @SearchText + '%')
END
Now if I pass it the product ID 1015729 and the word 'paracetamol' then I get 20 results back;
| 1015729 | 3 | Usually avoid combination. Use combination only under special circumstances, taking any necessary action to reduce risk. | INCREASED RISK OF BLEEDING | ASPIRIN/SODIUM SALICYLATE | 2315 | 141 | 26 | 19 | 3 | NSAIDS |
This is because there are 20 products giving the same interaction.
However my users do not need to see all of this. They only want to see
| 3 | Usually avoid combination. Use combination only under special circumstances, taking any necessary action to reduce risk. | INCREASED RISK OF BLEEDING | ASPIRIN/SODIUM SALICYLATE |
This removes the productId which provides a unique key for the entity modeller.
So my web service which returns the set the user wants is as follows;
public List<ProductInteractionSummary> GetProductInteractionsSummary(int ProductId, String searchText)
{
List<ProductInteractionSummary> list = null;
STGMDDFFormularyEntities ctx = null;
try
{
ctx = new STGMDDFFormularyEntities();
var qry = from i in ctx.STGP_GetProductInteractionsBySearchText(ProductId, searchText)
select new ProductInteractionSummary()
{
InteractionSeverityId = (int)i.iInteractionSeverityID,
ExistingDrugInteractionText = i.sOtherDrugInteractionText,
FormattedInteractionMessage = i.FormattedInteractionMessage,
InteractionSeverityText = i.sInteractionSeverityText
};
list = qry.ToList<ProductInteractionSummary>();
}
catch (System.Exception ex)
{
throw ex;
}
return list;
}
this returns a list with just the properties I require. However it still has 20 records.
I have tried various forms of Distinct
Such as surrounding the linq query in brackets and putting Distinct afterwards
var qry = (from i in ctx.STGP_GetProductInteractionsBySearchText(ProductId, searchText)
select new ProductInteractionSummary()
{
InteractionSeverityId = (int)i.iInteractionSeverityID,
ExistingDrugInteractionText = i.sOtherDrugInteractionText,
FormattedInteractionMessage = i.FormattedInteractionMessage,
InteractionSeverityText = i.sInteractionSeverityText
}).Distinct();
when assigning to the list
list = (List<ProductInteractionSummary>)qry.ToList<ProductInteractionSummary>().Distinct();
They all return the same 20 records - so when assigned to a grid I get 20 rows; when I only need 2
So how can you use Distinct, not against one column - but one which is distinct across the whole row.
I can't do it at the SQL end because then I either get the same 20 rows or their is now distinct value which will make up a unique key.
I'm not sure if I can use text fields in the primary key - but this would require me to manually set the key by modifying the XML which is behind the Entity modeller. I would also need to alter the Stored Procedure to return back just the fields I want.
Placing the distinct after the qry when assigning it to the list
After assigning it to the list
There are a couple of ways to achieve what you are after:
1) Use Distinct with a custom equality comparer
Distinct isn’t doing what you want because it is using Object.Equals to compare each instance of ProductInteractionSummary
There is an overload of Distinct that accepts an IEqualityComparer<T> which would be very simple to implement for what you are after
2) Create ProductInteractionSummary as a complex type in your model and have the stored procedure materialize to it
(Types only need to have a primary key if they are a full entity type (to allow create/update/delete))
Steps would be:
- Update the stored procedure to do the Distinct and just select the columns you want
- Add a complex type (like your existing ProductInteractionSummary) to the model
(On the designer surface; ‘Right Click -> Add -> Complex Type’)- Create a function import for the stored procedure and set the return type to be the complex type
(On the designer surface; ‘Right Click -> Add -> Function Import…’)
- After creating the Function Import you will have a method on your derived context type to call the function
The advantage of option 2 is that the Distinct is processed in the database rather than in memory
这篇关于从LINQ查询获取不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
