CODEWITHSUNDEEP
c#linqentity-frameworkdistinct
patrick
patrick

Reputation: 16949

Entity Framework select distinct name

How can I do this SQL query with Entity Framework? 

SELECT DISTINCT NAME FROM TestAddresses

Upvotes: 183

Views: 326566

Answers (9)

Sphero Venezuela
Sphero Venezuela

Reputation: 73

.Distinct() use default equality comparer for table, which might not be the one we want, something this could give us not the right answer, so we can use DistinctBy which uses a specific key.

DBContext.TestAddresses.Select(m => new {m.NAME, m.ID}).DistinctBy(m=>m.NAME);

if you want with multiple columns just use .DistinctBy(m=> new{m.NAME, m.ID})

Upvotes: -1

indiPy
indiPy

Reputation: 8062

Using lambda expression..

 var result = EFContext.TestAddresses.Select(m => m.Name).Distinct();

Another variation using where,

 var result = EFContext.TestAddresses
             .Where(a => a.age > 10)//if you have any condition
             .Select(m => m.name).Distinct();

Another variation using sql like syntax

 var result = (from recordset
              in EFContext.TestAddresses
              .where(a => a.city = 'NY')//if you have any condition
              .select new 
              {
                 recordset.name
              }).Distinct();

Upvotes: 357

overcomer
overcomer

Reputation: 2374

In order to avoid ORDER BY items must appear in the select list if SELECT DISTINCT error, the best should be

var results = (
    from ta in DBContext.TestAddresses
    select ta.Name
)
.Distinct()
.OrderBy( x => 1);

Upvotes: 2

Muhammad Asad
Muhammad Asad

Reputation: 1782

DBContext.TestAddresses.Select(m => m.NAME).Distinct();

if you have multiple column do like this:

DBContext.TestAddresses.Select(m => new {m.NAME, m.ID}).Distinct();

In this example no duplicate CategoryId and no CategoryName i hope this will help you

Upvotes: 30

Hossein Hajizadeh
Hossein Hajizadeh

Reputation: 1485

use Select().Distinct()

for example

DBContext db = new DBContext();
var data= db.User_Food_UserIntakeFood .Select( ).Distinct();

Upvotes: 3

Prajapati Gautam
Prajapati Gautam

Reputation: 31

Entity-Framework Select Distinct Name:

Suppose if you are want every first data of particular column of each group ;

 var data = objDb.TableName.GroupBy(dt => dt.ColumnName).Select(dt => new { dt.Key }).ToList();

            foreach (var item in data)
            {
                var data2= objDb.TableName.Where(dt=>dt.ColumnName==item.Key).Select(dt=>new {dt.SelectYourColumn}).Distinct().FirstOrDefault();

               //Eg.
                {
                       ListBox1.Items.Add(data2.ColumnName);                    
                }

            }

Upvotes: 1

Kim Tranjan
Kim Tranjan

Reputation: 4541

The way that @alliswell showed is completely valid, and there's another way! :)

var result = EFContext.TestAddresses
    .GroupBy(ta => ta.Name)
    .Select(ta => ta.Key);

I hope it'll be useful to someone.

Upvotes: 32

Abdul Khaliq
Abdul Khaliq

Reputation: 2285

Entity-Framework Select Distinct Name:

Suppose if you are using Views in which you are using multiple tables and you want to apply distinct in that case first you have to store value in variable & then you can apply Distinct on that variable like this one....

public List<Item_Img_Sal_VIEW> GetItemDescription(int ItemNo) 
        {
            var Result= db.Item_Img_Sal_VIEW.Where(p => p.ItemID == ItemNo).ToList();
            return Result.Distinct().ToList();
        }

Or you can try this Simple Example

Public Function GetUniqueLocation() As List(Of Integer)
          Return db.LoginUsers.Select(Function(p) p.LocID).Distinct().ToList()
End Function

Upvotes: 4

marc_s
marc_s

Reputation: 754220

Try this:

var results = (from ta in context.TestAddresses
               select ta.Name).Distinct();

This will give you an IEnumerable<string> - you can call .ToList() on it to get a List<string>.

Upvotes: 60

Related Questions