LINQ to Entities query error

Question

I am encountered an error that I am not familier with. I tried to google with no success. I wrote the following query where I am having this error.

The entity or complex type 'MyWebProject.Models.UserDetail' cannot be constructed in a LINQ to Entities query.

The query:

UsersContext db = new UsersContext();
        var userdata = (from k in db.UserDetails
                        where k.UserId == WebSecurity.CurrentUserId
                        select new UserDetail()
                        {

                            FullName = k.FullName,
                            Email = k.Email,
                            About = k.About,
                            Link = k.Link,
                            UserSchool = new School()
                            {
                                SchoolId = k.UserSchool.SchoolId,
                                SchoolName = k.UserSchool.SchoolName
                            },
                            UserCourse = new Course()
                            {
                                CourseId=k.UserCourse.CourseId,
                                CourseName=k.UserCourse.CourseName
                            },
                            Country=k.Country
                        }).FirstOrDefault();

Class:

public class UserDetail
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public string FullName { get; set; }
    public string Link { get; set; }
    public bool? Verified { get; set; }

    public string Email { get; set; }

    public string About { get; set; }
    public School UserSchool { get; set; }
    public Course UserCourse { get; set; }

    public string Country { get; set; }

}
public class School
{
    public int SchoolId { get; set; }
    public string SchoolName { get; set; }
    public string Country { get; set; }
}
public class Course
{

    public int CourseId { get; set; }
    public string CourseName { get; set; }
    public School School { get; set; }
}

Any idea what went wrong??

Despertar · Accepted Answer · 2012-12-10T20:45:20.103

4

It looks like it is due to how you are creating the complex properties School and Course in the middle of the query. It would be better to select the User (remove the select transformation), then use navigation properties to access those objects instead of building them manually. The navigation are meant for this as long as you have the proper relations built with foreign keys.

UsersContext db = new UsersContext();

var userdata = (from k in db.UserDetails
    where k.UserId == WebSecurity.CurrentUserId})
    .FirstOrDefault();

// access navigation properties which will perform the joins on your behalf
// this also provides for lazy loading which would make it more effecient. (it wont load the school object until you need to access it)

userdata.School
userdata.Course

MSDN article about navigation properties: http://msdn.microsoft.com/en-us/library/vstudio/bb738520(v=vs.100).aspx

edited Dec 10 '12 at 20:45

answered Dec 10 '12 at 20:35

Despertar

21,627
11
81
79

ok..if I pass 'userdata' to view and try to display the school name like '@Html.TextBoxFor(m=>m.UserSchool.SchoolName)' . I dont get the school name. – Reza.Hoque Dec 10 '12 at 20:46
Check the UserDetail object to ensure it has the proper SchoolID so that it can link to the school table. Then does that associated school table have a name? In other words first insure that doing a raw query with a join would give you the expected result. Once you have verified the data is correct, set a break point before your controller renders the view and check the properties there as well. – Despertar Dec 10 '12 at 20:53
Could be an issue if he isn't using lazy loading. Updated my answer below to show how to explicitly load those objects rather than relying on lazy loading. – Rick B Dec 10 '12 at 20:57
@RickB That would only be an issue is if he was disposing of the database context before calling the School property which would throw an exception. That is a good suggestion though. – Despertar Dec 10 '12 at 21:09
if I do a raw query in database like 'select * from userdetails inner join Schools on UserDetails.UserSchool_SchoolId=Schools.SchoolId', it gives me the expected result. – Reza.Hoque Dec 10 '12 at 21:17
@kandroid If you are using code first, the problem may be that the relationship is not properly defined in your entity class. Try adding a UserSchoolId property to your UserDetail class. And make the UserSchool property virual. In addition to adding the UserSchoolId as a FK, you have to tell the model builder that you have a foriegn key in your table, shown here, http://stackoverflow.com/questions/5217441/ef4-code-first-how-to-add-a-relationship-without-adding-a-navigation-property – Despertar Dec 10 '12 at 21:32
Hi...adding the 'virtual' thing did the magic. :) Can you plz explain a little about that 'virtual'? – Reza.Hoque Dec 10 '12 at 21:36
Virtual enables lazy loading. You don't have to use it. The properties can be public, but then you need syntax like I have in my answer. It sounds like your DbContext wasn't set up right, which is what solved the issue. – Rick B Dec 10 '12 at 21:38
Here is a good explanation on how the virtual keyword affects NavigationProperties, http://stackoverflow.com/questions/7738722/entity-framework-4-1-virtual-properties. – Despertar Dec 10 '12 at 21:43

Rick B · Answer 2 · 2012-12-10T20:52:30.717

3

This should give you what you want. It will load your objects as part of the query (and not rely on lazy loading).

UsersContext db = new UsersContext();
var userdata = db.UserDetails.Include(x => x.UserSchool)
                             .Include(x => x.UserCourse)
                             .Include(x => x.Country)
                             .Where(x => x.UserId == WebSecurity.CurrentUserId)
                             .FirstOrDefault();

edited Dec 10 '12 at 20:52

answered Dec 10 '12 at 20:34

Rick B

1,156
10
11

I dont know for some reason I can not use your code. it says 'Error Cannot convert lambda expression to type 'string' because it is not a delegate type'. Am I doing any other mistakes ? – Reza.Hoque Dec 10 '12 at 21:00
@kandroid try adding `using System.Linq;` to your class – Rick B Dec 10 '12 at 21:18
it is already there. :) if i write a query in the database like 'select * from userdetails inner join Schools on UserDetails.UserSchool_SchoolId=Schools.SchoolId' it gives me the expected result. I am quite new in code first model. Do i have to enter anything here: public class UsersContext : DbContext { public UsersContext() : base("DefaultConnection") { } public DbSet UserProfiles { get; set; } public DbSet UserDetails { get; set; } } – Reza.Hoque Dec 10 '12 at 21:20

score 0 · Answer 3 · answered Dec 10 '12 at 21:13

I think it's because your entity has the same name of the object you're trying to create. Try renaming the object you want to return back. If you want to return the same type as your entity try the eager loading with .Include("relationshipname") feature.

score 0 · Answer 4 · edited May 23 '17 at 12:21

A great answer from @Yakimych is given below.

You cannot (and should not be able to) project onto a mapped entity. You can, however, project onto an annonymous type or onto a DTO:

public class ProductDTO
{
    public string Name { get; set; }
    // Other field you may need from the Product entity
}

And your method will return a List of DTO's.

public List<ProductDTO> GetProducts(int categoryID)
{
    return (from p in db.Products
            where p.CategoryID == categoryID
            select new ProductDTO { Name = p.Name }).ToList();
}

Mapped entities in EF basically represent database tables. If you project onto a mapped entity, what you basically do is partially load an entity, which is not a valid state. EF won't have any clue how to e.g. handle an update of such an entity in the future (the default behaviour would be probably overwriting the non-loaded fields with nulls or whatever you'll have in your object). This would be a dangerous operation, since you would risk losing some of your data in the DB, therefore it is not allowed to partially load entities (or project onto mapped entities) in EF.

For more details please go to the following link: The entity cannot be constructed in a LINQ to Entities query

LINQ to Entities query error

4 Answers4