Is it possible to use Select(l=> new{}) with SelectMany in EntityFramework

Question

Hi,

I am trying something that i not really sure but i want to ask here if it s possible.

Is it able to be done ?

public IQueryable<Info> GetInfo(int count, byte languageId)
        {
            return db.Info.SelectMany(i => i.LanguageInfo)
                              .Where(l => l.Language.id == languageId)
                              .Select(l => new Info {   AddDate = l.Info.AddDate,
                                                        Description = l.Description,
                                                        EntityKey = l.Info.EntityKey,
                                                        id = l.Info.id,
                                                        Title = l.Title,
                                                        ViewCount = l.Info.ViewCount }
                                                        )
                              .OrderByDescending(i => i.id)
                              .Take(count);
        }

When this method is executed i got an error

The entity or complex type 'GuideModel.Info' cannot be constructed in a LINQ to Entities query.

Does it mean "not possible" ?

Thank you

Answer 1

It is possible to use Select(l => new ...), but not with an Entity type. You need to use an anonymous type or a POCO type with a parameterless constructor. Entity types are "special" because of the way they interact with the ObjectContext. You can select them, but not new them up in a query.

Answer 2

The error essentially indicates that the Entity Framework doesn't know how to create an Info object, since it is not bound to a table object. (Put another way, the Select call on the IQueryable cannot be translated into equivalent SQL.) You could perform the Select projection on the client via:

public IQueryable<Info> GetInfo(int count, byte languageId)
{
    return db.Info.SelectMany(i => i.LanguageInfo)
                      .Where(l => l.Language.id == languageId)
                      .Take(count)
                      .AsEnumerable()
                      .Select(l => new Info {   AddDate = l.Info.AddDate,
                                                Description = l.Description,
                                                EntityKey = l.Info.EntityKey,
                                                id = l.Info.id,
                                                Title = l.Title,
                                                ViewCount = l.Info.ViewCount }
                                                )
                      .OrderByDescending(i => i.id);
}