LINQ to Entity - using anonymous object in a query

Question

I am trying to write LINQ2Entity query which will return only 3 columns from the table and then to set the result as a DataSource of a ComboBox.

The problem I have is this. First - to select all from the table I use a service like this:

IList<SoleColor> soles = SoleColorService.All().ToList();

Which is ready to be set as a DataSource for the ComboBox which waits data of this type. However because I want to choose anonymous object to store only 3 of the columns that the query result I modified my query like this :

IList<SoleColor> soles = SoleColorService.All()
                    .GroupBy(t => t.Sole.Code)
                    .Select(g => new
                    {
                        SoleCode = g.Key,
                        SoleName = g.Select(t => t.Sole.Name),
                        SoleId   = g.Select(t => t.SoleID)
                    });

Which leads to marking the .Selectas error with the following text "Cannot implicitly convert type System.Linq.IQueryable to System.Collections.Generic.IList". If I change IList<SoleColor> soles = to var soles = it's fine but then there's a problem with assigning anonymous type like a DataSource, but I think this can be fixed in the query so I get the right type there. Also at some point I have to add something like First() or FirstOrDafult() to y query, because I have many records with the same SoleCode and I want to keep only one for each unique SoleCode.

Daniel Hilgarth · Accepted Answer · 2013-03-22T09:14:09.683

First: How to only get one result per group I already showed you in your previous question. Combined with an anonymous type this would look like the following:

SoleColorService.All()
                .GroupBy(t => t.Sole.Code)
                .Select(g => g.First())
                .Select(x => new
                {
                    SoleCode = x.Sole.Code,
                    SoleName = x.Sole.Name),
                    SoleId   = x.SoleID)
                });

Second: If you need to return the result of this query in a strong typed manner from your method you can't use an anonymous type. You will have to create a named type and use that instead:

public class SoleModel
{
    private readonly string _code;
    private readonly string _name;
    private readonly int _id;

    public SoleModel(string code, string name, int id)
    {
        _code = code;
        _name = name;
        _id = id;
    }

    public string Code { get { return _code; } }
    public string Name { get { return _name; } }
    public int Id { get { return _id; } }
}

SoleColorService.All()
                .GroupBy(t => t.Sole.Code)
                .Select(g => g.First())
                .Select(x => new SoleModel(x.Sole.Code, x.Sole.Name x.SoleID));

Third: Assigning a list of anonymous types to a datasource should work.

var result = SoleColorService.All()
                             .GroupBy(t => t.Sole.Code)
                             .Select(g => g.First())
                             .Select(x => new
                             {
                                 SoleCode = x.Sole.Code,
                                 SoleName = x.Sole.Name),
                                 SoleId   = x.SoleID)
                             }); 
someControl.DataSource = result;

Thanks. I had to change the code from yesterday because of performance issue. It's supposed to load the form faster if in the `Select` are only 3 properties or at least that's why I'm doing this modification. For the unique records - I was trying to put `First()` at the very end of the query so obviously that was my mistake, didn't knew for this option `.Select(g => g.First())`. This is my second day working with LINQ2Entities or LINQ2SQL so I hope you'll excuse my not knowing - about the type - the DataSource is assigned with custom method that expects certain type that is why I need it. — Leron, Mar 22 '13 at 09:21
@Leron: So, did this answer your questions or is something still unclear? BTW: Selecting onle three columns most likely won't affect your performance much unless SoleColor has a huge amount of columns or columns with a huge amount of data. — Daniel Hilgarth, Mar 22 '13 at 09:25
Sorry I was trying what you've read. I accepted your answer. I'm not sure about the performance too but this is my task so... at least I hope to learn to work with Linq2Entities.Thanks — Leron, Mar 22 '13 at 09:33

LINQ to Entity - using anonymous object in a query

1 Answers1

Linked