Get Specific object after a join using LINQ

Question

I am trying to retrieve a specific class related to a table in Entity Framework using linq for join and where conditions as follows:

                var result = (from a in db.Persons
                             join b in db.Person_IDs on a.PersonId equals b.PersonId
                             where b.FaceId == faceId
                             select new
                             {
                                 PersonId = a.PersonId,
                                 Name = a.Name,
                                 Address = a.Address,
                                 Picture = a.Picture,
                                 City = a.City,
                                 Estate = a.Estate,
                                 Phone = a.Phone,
                                 CellPhone = a.CellPhone,
                                 BlackList = a.BlackList
                             }
                             ).FirstOrDefault();

I would like the "result" object being returned as a Person object. In the above case, I need to create a new Person object and add the fields that came from the result.

Is it possible? I tried some ways and using some samples and researches but none of all alternatives worked for me.

Thanks!

UPDATE 1

All right, after some readings, the best way I found to do this was creating a DTO class for my Person object and returning this DTO class in my funcion as follows:

PersonDTO result = (from a in db.Persons
                             join b in db.Person_IDs on a.PersonId equals b.PersonId
                             where b.FaceId == faceId
                             select new PersonDTO
                             {
                                 PersonId = a.PersonId,
                                 Name = a.Name,
                                 Address = a.Address,
                                 Picture = a.Picture,
                                 City = a.City,
                                 Estate = a.Estate,
                                 Phone = a.Phone,
                                 CellPhone = a.CellPhone,
                                 BlackList = a.BlackList
                             }
                             ).FirstOrDefault();

            db.Dispose();

            return result;

All right, it worked fine, but one thing bothers me: why create another class identical to the EF class? Why can't EF class be used this way?

I am working with one table, but a program with, for example, 20 tables will force me to have 20 entity classes and 20 entity DTO classes!

As a beginner, I think this way of work a bit disorganized or nonsensical, making the traditional way (using data readers, commands and connections). Even being more bureaucratic, it don't have de need of "duplicated" objects.

Can somebody please provide this answer?

UPDATE 2

As requested: as I don't wat an anonymous type returning in my function, I tried returning the entity class (Person), but when I do this I get the following error in my application execution:

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

So the solution for this was create a DTO class (or a viewmodel class, whatever).

Answer 1

You should be able to do

var result = (from a in db.Persons 
    join b in db.Person_IDs on a.PersonId equals b.PersonId
    where b.FaceId == faceId
    select new Person
    { 
        PersonId = a.PersonId, 
        Name = a.Name, 
        Address = a.Address, 
        Picture = a.Picture, 
        City = a.City, 
        Estate = a.Estate, 
        Phone = a.Phone, 
        CellPhone = a.CellPhone, 
        BlackList = a.BlackList
    }).FirstOrDefault();

This should work as long as the Person class is accessible, it's location is imported, and it has public getters/setters with properties matching the above.

If you're still having issues, try including your class definition for person and any errors you might be seeing.

EDIT: Based on the error you are seeing, I'm guessing you're trying to select only SOME of the properties on this entity. EF actually won't let you do that. You can either select the whole entity (by not specifying the properties and just selecting a) or you can create a custom DTO that you can map to like I do above.

EF doesn't like incomplete mapping because it makes the state confusing for future model modifications. See this answer here. So if you're wanting to avoid loading the whole entity, go the custom DTO route.

Answer 2

you code is correct !! go for this solution as it has minimum modification just select person

 person result = (from a in db.Persons
                             join b in db.Person_IDs on a.PersonId equals 
                             b.PersonId
                             where b.FaceId == faceId
                             select new person
                             {
                                 PersonId = a.PersonId,
                                 Name = a.Name,
                                 Address = a.Address,
                                 Picture = a.Picture,
                                 City = a.City,
                                 Estate = a.Estate,
                                 Phone = a.Phone,
                                 CellPhone = a.CellPhone,
                                 BlackList = a.BlackList
                             }
                             ).FirstOrDefault();

Answer 3

If you have Person objects in your database, and you want Person objects, why are you going to the trouble of creating an anonymous type?

Why not just try

var result = (from a in db.Persons
              join b in db.Person_IDs on a.PersonId equals b.PersonId
              where b.FaceId == faceId
              select a).FirstOrDefault();