Select distinct column name from db using LINQ

Question

I have a table that has several columns including CarMake, CarModel, CarModelLoading etc. I want to select distinct CarMake and return them as list. I have tried the following:

public IEnumerable<EbimaCarMakesAndModel> GetAll()
        {
            //var list<ebima>
            return (IEnumerable<EbimaCarMakesAndModel>)context.EbimaCarMakesAndModels.Select(l => new { l.CarMake }).Distinct().ToList();
            //var data = (from dbo in context.EbimaCarMakesAndModels where dbo.Listed == "Y" select dbo.CarMake).Distinct().OrderBy(name => name).ToList();

            //return (IEnumerable<EbimaCarMakesAndModel>)data;
}

but whenever run the app, I get the error:

An unhandled exception occurred while processing the request. InvalidCastException: Unable to cast object of type 'System.Collections.Generic.List1[<>f__AnonymousType311[System.String]]' to type 'System.Collections.Generic.IEnumerable`1[motor_backend.Models.EbimaCarMakesAndModel]'. I have also tried the following but to no avail:

public IEnumerable<EbimaCarMakesAndModel> GetAll()
    {
        var res = context.EbimaCarMakesAndModels.GroupBy(x => new { x.CarMake }).
            Select(x => x.FirstOrDefault()).Where(v => v.Listed == "Y").ToList();
        return res;
     }

It gives me the error below:

FirstOrDefault()' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

all the solutions given here haven't answered my query so far

The error is pretty clear - the query returns an anonymous type, not a `EbimaCarMakesAndModel`. What is `EbimaCarMakesAndModel`? If it has a `CarMake` property you could write `.Select(l=> new EbimaCarMakesAndModel{CarMake=l}).Distinct()`. — Panagiotis Kanavos, Feb 19 '21 at 07:48
didn't quite understand the anonymous thing, i got a solution down below, thanks for pointing that out, — Nehemiah Cheburet, Feb 19 '21 at 09:04

score 1 · Accepted Answer · answered Feb 19 '21 at 07:56

1

Either declare your method to return an IE of string:

public IEnumerable<string> GetAllMakes()

And your query to not make an anonymous type but simply select the make:

return context.EbimaCarMakesAndModels.Select(l => l.CarMake).Distinct().ToList();

Or group by make and return some nominal entry from the group list (but that doesn’t really make sense to me)

return context.EbimaCarMakesAndModels.GroupBy(x => x.CarMake)
       .Select(x => x.FirstOrDefault()).ToList();

Your last query introduced some additional unmentioned constraint .Where(v => v.Listed == "Y") - perhaps consider doing this on EbimaCarMakesAndModels before you group

answered Feb 19 '21 at 07:56

Caius Jard

72,509
5
49
80

This has saved me big. It worked finally, thanks @Caius Jard – Nehemiah Cheburet Feb 19 '21 at 09:03
No probs! If you now click the grey check mark nex tto this answer to turn it green it will show up on the dashboard as answered so people know youre not still looking for an answer. You can upvote it too if it was helpful to you – Caius Jard Feb 19 '21 at 09:31

Select distinct column name from db using LINQ

1 Answers1