CODEWITHSUNDEEP
asp.netentity-framework
Pawan
Pawan

Reputation: 293

Entity Framework loading sub collection

I am trying to fill a Rights object in my PersonRight object using the following code:

var results = (from P in this.ObjectContext.Person
                join R in this.ObjectContext.Rights
                 on R.personid = p.personid
                select new PersonRight(){
                               Name = P.Name,
                           }).AsQueryable();



public class PersonRight
{
    public property Person { get; set; }
    public List<Rights> Rights { get; set; }
}

I would like to populate the Rights object with this query, how can this be done? I can not use entity framework created entity so I need to fill these database objects into a custom type.

Thank you

Upvotes: 1

Views: 948

Answers (3)

Slauma
Slauma

Reputation: 177133

You could try this:

var results = (from P in this.ObjectContext.Person
               join R in this.ObjectContext.Rights
               on P.personid equals R.personid
               into PR
               select new PersonRight()
               {
                   Name = P.Name,
                   Rights = PR
               }).AsQueryable();

public class PersonRight
{
    public string Name { get; set; }
    public IEnumerable<Rights> Rights { get; set; }
}

A few things to notice:

  • You must use equals in LINQ join and not ==.
  • On the left side of equals must be the outer variable, so on R.personid equals P.personid is wrong.
  • You must use the into keyword to create a group of all rights for a particular person. The variable PR represents an IEnumerable of all rights of the person.
  • I don't know how to get this working if Rights in your PersonRight class is a List<Rights>. Using PR.ToList() is not possible in LINQ to Entities because it will complain that it cannot translate ToList() to a store expression.
  • This all assumes that you don't have a navigation property on your Person class which points directly to the rights (like Person.Rights). If you have it you should leverage it in your query and don't use join.

Upvotes: 1

Chad
Chad

Reputation: 1562

var results = (from P in this.ObjectContext.Person                 
                select new PersonRight()
                           {Person = P,
                            Rights = p.Rights.ToList() }).ToList();

OR

var results = (from P in this.ObjectContext.Person                 
                select new PersonRight()
                           {Person = P,
                            Rights = this.ObjectContext.Rights.Where(r=>r.personid == p.personid ).ToList() }).ToList();

Upvotes: 0

František Žiačik
František Žiačik

Reputation: 7612

One possible solution (maybe not very effective?) is to simply get list of Persons and in creation of each PersonRights object, get corresponding Rights:

var results = 
    (from P in this.ObjectContext.Person
     select new PersonRight 
     {
        Name = P.Name,
        Rights = (from R in this.ObjectContext.Rights where R.personid == P.personid select R).ToList()
     }).AsQueryable();

Upvotes: 0

Related Questions