CODEWITHSUNDEEP
sql.netentity-framework
CHS
CHS

Reputation: 965

Entity Framework: How to select specific fields from a singular Navigation Property?

I have the following entities:

public class Movie
{
    public int    MovieID      { get; set; }
    public int?   DirectorID   { get; set; }
    public string Title        { get; set; }

    public Person               Director   { get; set; }
    public ICollection<Person>  Actors     { get; set; }    
}

//Director Config:
//HasOptional(m => m.Director).WithMany().HasForeignKey(m => m.DirectorID);


public class Person
{
    public int    PersonID  { get; set; }
    public string FirstName { get; set; }
    public string LastName  { get; set; }
    //Other fields....
}

The Person entity has other fields than the 3 I've shown but those are the 3 I'm interested in. For the purposes of creating a web page to edit a movie entity I wish to return data like this:

var q = _ctx.Movies.Where(m => m.MovieID == movieID);
q = q.Include(m => m.Director);
return q.Select(m => new
{
    MovieID        = m.MovieID,
    Name           = m.Title,
    Director       = m.Director == null ? null : new { id= m.Director.PersonID, text=m.Director.LastName} 
}).Single();

The goal being to return an object with a Director property that is null if no Director has been set or that is an object with 2 properties just id and text (name) if a Director has been set. It would be overkill to return to the client the entire person entity for this scenario. Unfortunately the above query won't work.

Are there any options for doing something like this? Alternatives?

Thanks very much for any help.

Upvotes: 4

Views: 671

Answers (2)

Peter Hansen
Peter Hansen

Reputation: 8907

It fails bacause Entity Framework can't set the anonymous type to null if there is no director.

A fine workaround could be to add a flag to the type to indicate whether the movie has a director, and just set the other properties to some dummy values if thats not the case.

You can then check the flag, before accessing any of the actual values.

using (var ctx = new Context())
{
    var movie =
        (from m in ctx.Movies.Include(p => p.Director)
        where m.MovieID == 3
        let hasDirector = m.Director != null
        select new
        {
            MovieID = m.MovieID,
            Name = m.Title,
            Director = new
            {
                Id = hasDirector ? m.Director.PersonID : -1,
                Text = hasDirector ? m.Director.LastName : null,
                Exists = hasDirector
            }
        }).SingleOrDefault();

    if (movie != null)
    {
        Console.WriteLine("Movie name: " + movie.Name);

        if (movie.Director.Exists)
        {
            Console.WriteLine("Director id: " + movie.Director.Id.ToString());
            Console.WriteLine("Director text: " + movie.Director.Text);
        }
        else
        {
            Console.WriteLine("Movie has no director..");
        }
    }
}

Upvotes: 2

vittore
vittore

Reputation: 17579

You can create an ignored property DirectorExists and fill it in the way you specified.

Use CreateSourceQuery to create a query and check if Director exists and set DirectorExists property.

If your entities are derived from EntityObject 

 Movie m;
 ....

 m.DirectorExists = m.DirectorReference.CreateSourceQuery().Any();

In case you have POCO you can load your movie with include the way you are doing it and then just set Director to null prior to sending object to the client.

UPDATED: In case you have proxy enabled code above might work as well, but I don't have project like that to try it.

You may also consider actually changing your data model in order to include DirectoExists field in the Movie table and update it when Directors are set for Movie. This way you won't need to do Include when loading Movie objects.

Upvotes: 0

Related Questions