CODEWITHSUNDEEP
c#sqlentity-framework
user6094857
user6094857

Reputation:

C# Entity Framework count all instances of an object

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)
{
   ...
}

Upvotes: 0

Views: 400

Answers (2)

user6094857
user6094857

Reputation:

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();

Upvotes: 1

Gert Arnold
Gert Arnold

Reputation: 109261

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.

Upvotes: 0

Related Questions