LINQ - distinct query in linq - Asked By goldy gupta on 30-Aug-11 03:53 AM

heloo team I have a Query

select distinct distributorcenter from usermaster

II am binding dropdown.. i want this in linq at design time

How to right this query in linq

Reena Jain replied to goldy gupta on 30-Aug-11 04:01 AM

HI,

This sample uses Distinct to find the unique Category names.

public void Linq47()
{
  List<Product> products = GetProductList();
  
  var categoryNames = (
    from p in products
    select p.Category)
    .Distinct();
  
  Console.WriteLine("Category names:");
  foreach (var n in categoryNames)
  {
    Console.WriteLine(n);
  }
}
Hope this will help you

Ravi S replied to goldy gupta on 30-Aug-11 04:25 AM

Hi

Only way I would know how to do it now then would be to use a stored procedure and then add that SP to your DBML. It will create a simple function that you can then call through LINQ.

refer
http://msdn.microsoft.com/en-us/library/bb397906.aspx
http://forums.asp.net/t/1220691.aspx/1

goldy gupta replied to Reena Jain on 30-Aug-11 04:59 AM

Thanks for reply but i want at this place means at design time

<asp:DropDownList ID="DropDownList6" runat="server"

DataSourceID="LinqDataSource3" DataTextField="distributorcenter"

DataValueField="distributorcenter">

</asp:DropDownList>

<asp:LinqDataSource ID="LinqDataSource3" runat="server"

ContextTypeName="MasterEntryDataContext" Select=""

TableName="UserMasters">

</asp:LinqDataSource>

now tell me what will i write in select command

Anoop S replied to goldy gupta on 30-Aug-11 05:54 AM

You can refer to this article for more information on working with Distinct in LINQ.
http://blogs.msdn.com/charlie/archive/2006/11/19/linq-farm-group-and-distinct.aspx

Devil Scorpio replied to goldy gupta on 30-Aug-11 03:23 PM

Hi Goldy,

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