Lets say I have a view called vwEmployees that has two fields (SupervisorID, EmployeeID) and contains the following data:

SupervisorID EmployeeID

1 100 2 100 3 101 4 101

I also have a GridView called gvEmployees and a LlblGenProDataSource called dsEmployees. In the gvEmployees_DataBinding event handler, I set:

dsEmployees.DataContainerType = "EntityCollection";
dsEmployees.EntityCollectionTypeName = "MyProj.LLBLGen.CollectionClasses.VwEmployeesCollection, MyProj.LLBLGen";
dsEmployees.Select();
gvEmployees.DataSource = dsEmployees;

The code above results in the GridView having 4 records, as expected. However, I need a way to SELECT DISTINCT EmployeeID FROM vwEmployees (or from dsEmployees itself) so that the resulting dsEmployees would only contain 2 records, for example:

SupervisorID EmployeeID

1 100 3 101

I don't care whether or not the SupervisorID column is included in dsEmployees. However, the issue is that I don't want to change the view's definition (such as removing the SupervisorID column from the schema) because this view is being used in other parts of my application where the SupervisorID column is used for filtering. Also, I'm trying to avoid creating a duplicate view but without the SupervisorID column.

It would be easy if I can loop through each item in dsEmployees and remove the entries with duplicate EmployeeIDs. But I can't seem to find a way to iterate through the dsEmployees.

I tried this code:

ArrayList arDistinctEmployeeIDs = new ArrayList();
foreach (VwEmployeesEntity entity in dsEmployees.EntityCollection)
{
      if (!arDistinctEmployeeIDs.Contains(entity.EmployeeID))
      {
            arDistinctEmployeeIDs.Add(entity.EmployeeID);
      }
      else
      {
            dsEmployees.EntityCollection.Remove(entity);
      }
}
gvEmployees.DataSource = dsEmployees;

...but it gave me an error...

Any help is greatly appeciated. Thanks.

I tried this code:

Code:

ArrayList arDistinctEmployeeIDs = new ArrayList(); foreach (VwEmployeesEntity entity in dsEmployees.EntityCollection) { if (!arDistinctEmployeeIDs.Contains(entity.EmployeeID)) { arDistinctEmployeeIDs.Add(entity.EmployeeID); } else { dsEmployees.EntityCollection.Remove(entity); } } gvEmployees.DataSource = dsEmployees;

...but it gave me an error...

Please post the error/exception, with stack trace if possible.

I need a way to SELECT DISTINCT EmployeeID FROM vwEmployees

You can use a TypedList of one field (EmployeeId), and bind to it. You can specify true for allowDuplicates when you fetch the typedList.

Also you can use a dynamicList, and bind to it directly.

Walaa wrote:

Please post the error/exception, with stack trace if possible.

Exception System.InvalidOperationException: Collection was modified; enumeration operation may not execute. at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource) at System.Collections.Generic.List`1.Enumerator.MoveNext() at GPS.WebApp.Modules.Security.Controls.UserSearch.gvUsers_DataBinding(Object sender, EventArgs e) in C:\VS 2005 Projects\GPS\WebApp\Modules\Security\Controls\UserSearch.ascx.cs:line 722 at System.Web.UI.Control.OnDataBinding(EventArgs e) at System.Web.UI.WebControls.DataBoundControl.PerformSelect() at System.Web.UI.WebControls.BaseDataBoundControl.DataBind() at System.Web.UI.WebControls.GridView.DataBind() at GPS.WebApp.Modules.Security.Controls.UserSearch.btnSearch_Click(Object sender, EventArgs e) in C:\VS 2005 Projects\GPS\WebApp\Modules\Security\Controls\UserSearch.ascx.cs:line 526 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Line 722 referenced above is at the beginning of the FOREACH loop in the code shown in the previous posts.

By the way, how come the LlblgenProDataSource object does not have the "SelectCommand" property?

Exception System.InvalidOperationException: Collection was modified; enumeration operation may not execute. at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource) at System.Collections.Generic.List`1.Enumerator.MoveNext() at GPS.WebApp.Modules.Security.Controls.UserSearch.gvUsers_DataBinding(Object sender, EventArgs e) in C:\VS 2005 Projects\GPS\WebApp\Modules\Security\Controls\UserSearch.ascx.cs:line 722 at System.Web.UI.Control.OnDataBinding(EventArgs e) at System.Web.UI.WebControls.DataBoundControl.PerformSelect() at System.Web.UI.WebControls.BaseDataBoundControl.DataBind() at System.Web.UI.WebControls.GridView.DataBind() at GPS.WebApp.Modules.Security.Controls.UserSearch.btnSearch_Click(Object sender, EventArgs e) in C:\VS 2005 Projects\GPS\WebApp\Modules\Security\Controls\UserSearch.ascx.cs:line 526 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Line 722 referenced above is at the beginning of the FOREACH loop in the code shown in the previous posts.

I guess this exception occurs after at least one entity has been removed from the collection, am I right? If so then its because the count of the entities inside the collection has been changed and the indexes should change too. That's why the foreach is complaining. Try to use the following code instead: ArrayList arDistinctEmployeeIDs = new ArrayList();

int count = dsEmployees.EntityCollection.Count;
for (int i = 0; i < Count; i++)
{
     VwEmployeesEntity entity = dsEmployees.EntityCollection[i];
     if (!arDistinctEmployeeIDs.Contains(entity.EmployeeID))
     {
            arDistinctEmployeeIDs.Add(entity.EmployeeID);
     }
     else
     {
            dsEmployees.EntityCollection.Remove(entity);
            count --;
            i--;
     }
}
gvEmployees.DataSource = dsEmployees;

By the way, how come the LlblgenProDataSource object does not have the "SelectCommand" property?

Because it formulates the SQL query automatically, otherwise you could have used SQLDataSource. You may disable LivePersistence for the datasource and handle the PerformSelect, PerformWork and PerformGetDbCount, to manually control the LLBLGenProDataSource database interactions, but then you will need to do it the LLBLGenPro way, not using select commands, but rather using LLBLgenPro objects.