Entity Framework include with where clause

Question

I am currently working on a cinema booking system for a school project and have run into a problem.

I have a Movie model which contains a list of show models(date and time for the viewing of the movie). I need to get all the movie objects that are shown on a specific date with a list containing only the Show objects which date is equal to the specific date. I have tried various ways to do it in entity but cant seem to get it to work.

Here is the Movie class:

[DataContract]
public class Movie
{
    [Key, Required, DataMember]
    public Guid Id { get; set; }

    [Required, DataMember]
    public string Name { get; set; }

    [Required, DataMember]
    public string Info { get; set; }

    [Required, DataMember]
    public DateTime Premiere { get; set; }

    [Required, DataMember]
    public MovieType Type { get; set; }

    [DataMember]
    public ICollection<Show> Shows { get; set; }

    [DataMember]
    public string IMDBID { get; set; }
}

public enum MovieType
{
    Movie2D = 0,
    Movie3D = 1,
    KidsMovie = 2
}

Here is the Show class:

[DataContract]
public class Show
{
    [Key, Required, DataMember]
    public Guid Id { get; set; }

    [Required, DataMember]
    public Guid MovieId { get; set; }

    [Required, DataMember]
    public Guid ScreenId { get; set; }

    [Required, DataMember]
    public DateTime DateTime { get; set; }

    [Required, DataMember]
    public ShowType Type { get; set; }

    [DataMember]
    public Screen Screen { get; set; }

    [DataMember]
    public Movie Movie { get; set; }
}

public enum ShowType
{
    Standard = 0,
    Premiere = 1,
}

Here is the GetMovies(DateTime date) method:

public List<Movie> GetMovies(DateTime date)
{
    using (EntityContext db = new EntityContext())
    {
        List<Movie> movieList = db.Movies
            .Include("Show")
            .Where(x => x.Shows.Where(x => x.DateTime.Date == date.Date)).ToList();

        return movieList;
    }
}

I know that this function isn't working but hope it would show what I am trying to do.

score 4 · Accepted Answer · edited May 23 '17 at 12:00

I think DateTime.Date is not supported in Linq to Entities. You could use DbFunctions.TruncateTime static method:

var justDate= date.Date;
var movieList=db.Movies.Include(x=>x.Shows)
                       .Where(x => x.Shows.Any(x => DbFunctions.TruncateTime(x.DateTime) == justDate))
                       .ToList();

Update:

After read @Jonathan's comment I did a little research and it's true using DbFunctions.TruncateTime could affect the performance. You can find a detailed explanation in this post.

Following the same idea of @JonathanAllen and @MattJohnson, you can avoid to use that function if you do a range query instead, truncating first the Time from date parameter:

var startDate= date.Date;
var endDate= startDate.AddDays(1);
var movieList=db.Movies.Include(x=>x.Shows)
                       .Where(x => x.Shows.Any(x =>x.DateTime >= startDate && x.DateTime < endDate)
                       .ToList();

That's going to be really, really slow. Your database can't use indexes if you apply a function to the column you are searching against. — Jonathan Allen, Apr 13 '16 at 21:18
Thanks.. It is working now. I have only shows tuples with a DateTime on 1st and 2nd december in the database, but i get all the movie tubles out whenever i choose any date in december. It should only get some movies out when i choose 1st or 2nd december — Michael Kirkegaard, Apr 14 '16 at 15:03

score 1 · Answer 2 · edited Apr 13 '16 at 20:53

1

You should be able to use Any

public List<Movie> GetMovies(DateTime date)
{
    using (EntityContext db = new EntityContext())
    {
        List<Movie> movieList = db.Movies.Include("Show")
            .Where(x => x.Shows.Any(x => x.DateTime.Date == date.Date));

        return movieList;
    }
}

edited Apr 13 '16 at 20:53

Craig W.

17,838
6
49
82

answered Apr 13 '16 at 18:49

QiMata

216
1
7

score 0 · Answer 3 · answered Mar 15 '17 at 12:17

0

My request good working with All() method.

model.ReferenceList = db.JournalCardReference.OrderBy(a => a.orderF)
 .Include(x => x.JournalCardField)
  .Where(x => x.JournalCardField
    .All(f => f.deleted == null || f.deleted != true)).ToList();

answered Mar 15 '17 at 12:17

Roberto Gata

243
3
4

Entity Framework include with where clause

3 Answers3

Update: