Need to select multiple columns in Entity Framework, but need to get only distinct on single column

Question

I have a database table that has the following records. There are more fields than what I have displayed below, but I only want to return the Owner, Brand Name, ID#, and Type fields.

 Owner     Ingredient        BrandName    ID#       Type

  XXX       Methoprene       Precor        123-333    H
  XXX       Permethrin       Precor        123-333    H

I am trying to write an Entity Framework query to select only the distinct records in the Brand Name field, but still return the other columns in a list back to the controller to display in a partial view. The following is the code I have attempted, but I cannot seem to get the query written correctly:

               return db.Pesticides
                        .Where(c => c.Owner == Owner && c.BrandName == PesticidesBrand)
                        .GroupBy(c =>  c.Owner, c =>c.BrandName )
                        .Select(d => d.FirstOrDefault())
                        .ToList();

I realize the Select clause is not correct, but need help getting correct syntax to return the 4 specific fields. I would like the query to return the following record:

XXX  Precor    123-333  H

Thanks in advance....

See this answer.

Gert Arnold
– Gert Arnold

2018-03-09 20:23:32 +00:00
Commented Mar 9, 2018 at 20:23 — Gert Arnold
– Gert Arnold, Commented Mar 9, 2018 at 20:23

Igor · Accepted Answer · 2018-03-09 18:06:49Z

5

I believe this is what you are looking for.

var record = db.Pesticides
            .Where(c => c.Owner == Owner && c.BrandName == PesticidesBrand)
            .Select(c => new { c.Owner, c.BrandName, c.ID, c.Type })
            .FirstOrDefault();

If you want to return this from a result you need to project it to a known type.

PesticideModel record = db.Pesticides
            .Where(c => c.Owner == Owner && c.BrandName == PesticidesBrand)
            .Select(c => new PesticideModel{ Owner = c.Owner, BrandName = c.BrandName, ID = c.ID, Type = c.Type })
            .FirstOrDefault();

PesticideModel.cs

public class PesticideModel {
    public string Owner {get;set;}
    public string BrandName {get;set;}
    public string ID {get;set;}
    public string Type {get;set;}
}

If you wanted to return a list containing a single record do the following:

List<PesticideModel> record = db.Pesticides
            .Where(c => c.Owner == Owner && c.BrandName == PesticidesBrand)
            .Select(c => new PesticideModel{ Owner = c.Owner, BrandName = c.BrandName, ID = c.ID, Type = c.Type })
            .Take(1) // take 1st record
            .ToList();

edited Mar 9, 2018 at 18:06

answered Mar 9, 2018 at 15:32

Igor

62.5k10 gold badges111 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

stuartm Over a year ago

I am quite new with EF, so I am having a hard time with the correct syntax. Tells me that cannot implicitly convert type 'System.Collections.Generic.List<<anonymous type: string Owner, System.Collections.Generic.List<string> Items> > to 'System.Collections.Generic.List<string>

Igor Over a year ago

@stuartm - see update. I re-read your question and it seems you want the first record with a specific set of fields.

stuartm Over a year ago

That has gotten me much further along than before. Excuse my inexperience, but my controller is expecting a list to be returned. What do I need to do to return it as a list?

Igor Over a year ago

@stuartm - your question is asking for 1 record with 4 fields. "I would like the query to return the following record". Do you want to return this one record as a list or do you need additional records depending on what is returned from the query?

stuartm Over a year ago

return that one record as a list.

|

Ronald · Accepted Answer · 2018-03-09 15:55:34Z

0

return db.Pesticides
.Where(c => c.Owner == Owner && c.BrandName == PesticidesBrand)
.Select(d => new Pesticide() { Owner = d.Owner, BrandName = d.BrandName, ID = d.ID, Type = d.Type })
.Distinct()
.FirstOrDefault()
.ToList();

answered Mar 9, 2018 at 15:55

Ronald

1

2 Comments

Gert Arnold Over a year ago

It's always appreciated if you indicate which part of the posted solution is essential and briefly explain why. Code-only answers are of little value to future readers.

Flimtix Over a year ago

Why do you need Distinct if you only select the first element anyway? Also, FirstOrDefault could return a NULL value...

Collectives™ on Stack Overflow

Need to select multiple columns in Entity Framework, but need to get only distinct on single column

2 Answers 2

14 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

14 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related