获取从数据库返回的独特记录 [英] Get Distinct Records Returned From Database

问题描述

我有以下代码，它们将返回多行记录。但其中一个缺点是返回的记录可能会重复。例如，如果数据库返回Hello，则可能返回两个Hello。我可以在SELECT语句中更改存储过程添加Distinct但是有没有办法在C＃代码级别而不是存储过程中更改它？

以下是我的代码：

Hi, I have the following codes which will return multiple rows of records. But one of the drawback is that the records returned may be duplicated. For example, if the database return "Hello", it may return two "Hello". I can change the stored procedure adding a "Distinct" at the SELECT statement but is there any way of change it at the C# codes level instead of stored procedure?
Below are my codes:

public CustomDataCollectionDto GetCustomDataCollectionQuestion(CustomDataCollectionDto customDataCollectionDto, long versionId)
        {
            CustomDataCollectionQuestionDtoList questionList = new CustomDataCollectionQuestionDtoList();
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand selectCommand = db.GetStoredProcCommand("dbo.usp_CustomDataCollectionQuestionByVersionId_Select");
            db.AddInParameter(selectCommand, "p_CustomDataCollectionVersionId", DbType.Int64, versionId);
            db.AddInParameter(selectCommand, "p_CultureName", DbType.String, gsfParameterDto.Culture);

            using (IDataReader dataReader = db.ExecuteReader(selectCommand))
            {
                while (dataReader.Read())
                {
                    questionList.DtoList.Add(ParseDto<CustomDataCollectionQuestionDto>(dataReader));
                }

                customDataCollectionDto.CustomDataCollectionQuestionList = questionList;
            }

            return customDataCollectionDto;
        }

推荐答案

您正在使用DataReader执行选择。这对于提高效率非常有用，因为DataReader是一个线性，只读，仅向前的过程。缺点是，在查看结果之前，您无法判断是否存在重复记录。



所以。两种方式：



一：将给定的结果与已经找到的结果进行比较：

（我不知道CustomDataCollectionQuestionDtoList是什么，所以我'我猜这是存储过程的自动生成返回类型）

You're using DataReader to perform the select. This is great for efficiency as DataReader is a linear, readonly, forward only process. The drawback is that you can't tell if duplicate records exist until you look at the results.

So. Two ways:

One: Compare the given result with what has already been found:
(I have no idea what CustomDataCollectionQuestionDtoList is so I'll guess that it's the auto generated return type of the stored procedure)

using (IDataReader dataReader = db.ExecuteReader(selectCommand))
  {
    while (dataReader.Read())
    {
      var next = ParseDto<customdatacollectionquestiondto>(dataReader);
      if(!questionList.DtoList.Any(item=>item.a==next.a&&item.b==next.b)
        questionList.DtoList.Add(ParseDto<customdatacollectionquestiondto>(dataReader));
    }
 
    customDataCollectionDto.CustomDataCollectionQuestionList = questionList;
  }
</customdatacollectionquestiondto></customdatacollectionquestiondto>

这对于小型结果集非常有用，但对于较大的结果集来说却是一个真正的问题设置。



另一种方法是使用已完成集的内置功能。<​​br />
（再次：不知道CustomDataCollectionQuestionDtoList是什么可用的方法可能有所不同）

This is pretty good for small result sets but gets to be a real issue for larger sets.

The other method is to use the build in functionality of the completed set.
(again: no idea what CustomDataCollectionQuestionDtoList is so the available methods may differ)

using (IDataReader dataReader = db.ExecuteReader(selectCommand))
  {
    while (dataReader.Read())
    {
      var next = ParseDto<customdatacollectionquestiondto>(dataReader);
      if(!questionList.DtoList.Any(item=>item.a==next.a&&item.b==next.b)
        questionList.DtoList.Add(ParseDto<customdatacollectionquestiondto>(dataReader));
    }
 
    customDataCollectionDto.CustomDataCollectionQuestionList = questionList.Distinct();
  }
</customdatacollectionquestiondto></customdatacollectionquestiondto>

这对Linq对象非常有用（希望CustomDataCollectionQuestionDtoList至少是IEnumerable）但是你的大部分结果都是重复的，你必须要注意questionList将比需要的要大很多。



根据你的结果集大小来衡量选项重复的百分比。



希望有帮助



玩得开心^ _ ^

This works great for Linq objects (hopefully CustomDataCollectionQuestionDtoList is at least an IEnumerable) but is most of your results are duplicates you have to be aware that questionList is going to be a lot larger than is needs to be.

Weigh up the options depending on your result set size and the percent of duplicates.

Hope that helps

have fun ^_^

使用DataView ToTable函数。

Use the DataView ToTable function.

var dataTable = new DataTable();
dataTable.Load(dataReader );
var dataView = new DataView(dataTable);
var distinctDataTable  = dataView.ToTable(true, "Column1", "Column2" ...);

这篇关于获取从数据库返回的独特记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！