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](https://stackoverflow.com/a/491832/861716). — Gert Arnold, Mar 09 '18 at 20:23

Igor · Accepted Answer · 2018-03-09T18:06:49.123

4

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 09 '18 at 18:06

answered Mar 09 '18 at 15:32

Igor

60,821
10
100
175

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< Items> > to 'System.Collections.Generic.List – stuartm Mar 09 '18 at 16:00
@stuartm - see update. I re-read your question and it seems you want the first record with a specific set of fields. – Igor Mar 09 '18 at 16:04
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? – stuartm Mar 09 '18 at 16:42
@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? – Igor Mar 09 '18 at 16:45
return that one record as a list. – stuartm Mar 09 '18 at 16:52
@stuartm - see update below. If you have multiple returned records in the query that have different values you should use an OrderBy so you get an expected record and not the first one that happens to be at the top of the query result list. – Igor Mar 09 '18 at 16:57
@stuartm - if that solves your issue please consider marking an answer using the check-mark on the left side. – Igor Mar 09 '18 at 16:58
Now I am getting an error that I cannot convert System.Collections.Generic.List to Models.Pesticides – stuartm Mar 09 '18 at 17:26
@stuartm - I forgot to change the assignment type. Updated but you can also use `var`. – Igor Mar 09 '18 at 18:07
Same error:Cannot implicitly convert type 'System.Collections.Generic.List<>' to 'System.Collections.Generic.List' – stuartm Mar 09 '18 at 18:20
@stuartm - then you did not copy the complete code fragment (last one). It makes use of a *new* type named `PesticideModel`, see the class definition included. – Igor Mar 09 '18 at 18:21
I understand. I already have a model named "Pesticides" that already has those types, along with several others, defined – stuartm Mar 09 '18 at 18:34
@stuartm - your model is already associated with that DbContext type, you can't reuse to project data into from EF directly. That is why I have it in there. – Igor Mar 09 '18 at 18:43
I ended up creating a different model name which ended up being the answer – stuartm Mar 14 '18 at 22:37

score 0 · Answer 2 · answered Mar 09 '18 at 15:55

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 09 '18 at 15:55

Ronald

1

1

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. – Gert Arnold Mar 09 '18 at 20:19
Why do you need `Distinct` if you only select the first element anyway? Also, `FirstOrDefault` could return a NULL value... – Flimtix Mar 30 '22 at 12:43

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

2 Answers2