C# Entity Framework count all instances of an object

Question

I'm trying to count all instances of Movie_Instance that contain a foreign key of a movie in the Movie table.

Here is my code:

var query = entities.Movie.Join(entities.Movie_Instance, 
                                movie2 => movie.ID_Movie,        
                                movie_instance => movie_instance.FK_ID_Movie,
                                (movie2, movie_instance) => new { Movi = movie, Movie_Instanc = movie_instance })
                    .Where(data => data.Movi.ID_Movie == data .Movie_Instanc.FK_ID_Movie 
                                   && data .Movi.ID_Movie == movie.ID_Movie)
                    .SelectMany(e => entities.Movie_Instance).Count();

What I get when I run this is an error:

System.NotSupportedException: 'Unable to create a constant value of type 'DBFilmy.Movie'. Only primitive types or enumeration types are supported in this context

[EDIT]

Movie and Movie_Instance ]1

Movie_instance contains instances of Movie. It's connected using foreign key (FK_ID_Movie -> ID_Movie) I have multiple instances of Movie and each of them is stored in Movie_Instance. I'd like to count how many instances of movie I have (movie is an object of Movie type)

Here is where I get movie:

var x = entities.Movie
                .Where(c => c.Title.Contains(_title)
                            && c.Director.Contains(_director)
                            && c.Category.Contains(_category));

foreach (var movie in x)
{
   ...
}

marc_s · Accepted Answer · 2017-12-03 09:56:15Z

1

I have found a working solution:

var query = entities.Movie.Join(entities.Movie_Instance,
                            s => s.ID_Movie,
                            c => c.FK_ID_Movie,
                            (s, c) => new {s, c}).Where(sc => sc.c.FK_ID_Movie == movie.ID_Movie).Select(sc => sc.s)
                        .Count();

edited Dec 3, 2017 at 9:56

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 2, 2017 at 12:01

user6094857

Sign up to request clarification or add additional context in comments.

Comments

Gert Arnold · Accepted Answer · 2017-12-02 20:52:14Z

The exception typically occurs when you try to join a local (in-memory) list of objects with a DbSet in a context. It tells you (in rather cryptic language) that the local objects can't be translated to primitive values in a SQL statement.

But looking at your code I don't see any local list. Both entities.Movie and entities.Movie_Instance are DbSets (unless you're doing something unexpected in your context) so I don't really understand the origin of the exception.

All that would only qualify as comment if it wasn't for a couple of concerns in your code.

The first part of the Where clause is totally redundant. The join already matches movie.ID_Movie and movie_instance.FK_ID_Movie, so why do it again?
The second part of the Where clause, data.Movi.ID_Movie == movie.ID_Movie, is also redundant: data.Movi and movie are the same entities.
You don't need the join at all because you've already got a navigation property that contains the join: movie.Movie_Instance.

When you use the navigation property your code can be as simple as this:

var query = entities.Movie
    .SelectMany(e => entities.Movie_Instance).Count();

You'll see that the generated SQL contains a JOIN. EF does all the work for you.

Collectives™ on Stack Overflow

C# Entity Framework count all instances of an object

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related