Entity Core Framework Query is not correct?

Question

I am trying to perform this Query and this is the result that I get from that:

SELECT dbo.Show.ShowId, dbo.Show.Name, dbo.Cast.CastId, dbo.Cast.Name, dbo.Cast.Birthday
FROM dbo.Show
INNER JOIN dbo.Cast ON dbo.Cast.ShowId = dbo.Show.ShowId
WHERE dbo.Cast.ShowId=1;

This is the output of this query

I am trying to write this query However this does not work

        [HttpGet("{id}")]
        public async Task<IActionResult> GetShowCast(long? showId)
        {
            var query = from s in db.Set<Show>()
                        join c in db.Set<Cast>()
                            on s.ShowId equals c.ShowId
                        where c.CastId == showId
                        select new { c.Birthday, s.Name};
            return Json(query);
        }

The database is working because this does work.

        [HttpGet]
        public async Task<IActionResult> Index()
        {
            return Json(await db.shows.ToListAsync());
        }

What is wrong with my query?

What is wrong with my query? I expect to receive this from query

After updating my query i still don't receive a result

       [HttpGet("{id}")]
        public async Task<IActionResult> GetShowCast(long? showId)
        {
            var query = from s in db.Set<Show>()
                        join c in db.Set<Cast>()
                            on s.ShowId equals c.ShowId
                        where s.ShowId == showId
                        select new { c.Birthday, s.Name };
            return Json(query.ToList());
        }

When i debug: When debug this is see that var result is receiving nothing Count = 0

My models:

  [JsonProperty("id")]
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public long CastId { get; set; }

        [JsonProperty("name")]
        public string Name { get; set; }

        [JsonProperty("birthday")]
        public string? Birthday { get; set; }

        public long ShowId { get; set; }
        public ICollection<Show> Show { get; set; }

Model Show

      [JsonProperty("id")]
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public long ShowId { get; set; }
        [JsonProperty("name")]
        public string Name { get; set; }
        public ICollection<Cast> Casts { get; set; }

can you elaborate on the "not working" part? specifically, did you encounter error or given wrong output? also, what the query trying to do? — Bagus Tesa, Mar 15 '23 at 08:35
also, make sure the parameters are correct and of correct types. — Bagus Tesa, Mar 15 '23 at 08:36
You have a many-to-many relationship in your class model but it's one-to-many in the database. `Cast` should have a property `public Show Show { get; set; }` and you should use these *navigation properties* in your query. Remove the `join`. All answers so far lead you in the wrong direction. Your code can't work correctly as long as the class model and the database model have a mismatch. Fix that first. — Gert Arnold, Mar 15 '23 at 09:55
That said, shouldn't there actually be a many-to-many relationship in the database, i.e. a junction table between `Show` and `Cast`? It seems that `Cast` is in fact an actor and I hope for actors that they can play in more than one show. — Gert Arnold, Mar 15 '23 at 10:02

score 1 · Answer 1 · answered Mar 15 '23 at 10:10

There where multiple typo's that I made and GetShowCast(long? showId) should be GetShowCast(long showId)

The code bellow is working!

        [HttpGet("{id}")]
        public async Task<IActionResult> GetShowCast(long showId)
        {   
            var query = from s in db.Set<Show>()
                        join c in db.Set<Cast>()
                            on s.ShowId equals c.ShowId
                        where c.ShowId == 1
                        select new { c.Birthday, s.Name };

            var result = query.ToList();
            return Json(result);
        }

As hinted above, this should simply be `db.Set().Where(...).Select(c => new { c.Birthday, c.Show.Name }`. — Gert Arnold, Mar 15 '23 at 10:14

Qing Guo · Answer 2 · 2023-03-15T09:23:49.807

0

Try:

[HttpGet("{id}")]//remove it
        public async Task<IActionResult> GetShowCast(long? showId)
        {
            var query = from s in db.Set<Show>()
                        join c in db.Set<Cast>()
                            on s.ShowId equals c.ShowId
                        where s.ShowId == showId
                        select new { c.Birthday, s.Name };
            return Json(query);
        }

edited Mar 15 '23 at 09:23

answered Mar 15 '23 at 08:52

Qing Guo

6,041
1
2
10

When i debug: When debug this is see that `var result` is receiving nothing `Count = 0` – ozkan reyes Mar 15 '23 at 08:55
@ozkan reyes Is your CastId in your Cast table? Do you want to get "1979" and "under the dome" ? – Qing Guo Mar 15 '23 at 08:57
CastId is in my table indeed as you see in my SQL query. I want to receive all the columns in my table – ozkan reyes Mar 15 '23 at 09:01
1

@ozkan reyes I use your code, and I remove `[HttpGet("{id}")]` and I can get "1979" and "under the dome" .You cannot get them? – Qing Guo Mar 15 '23 at 09:20
[HttpGet("{id}")] is a rest needed for the rest api, however when i do `where c.ShowId == 1` the query works indeed – ozkan reyes Mar 15 '23 at 10:06
@ozkan reyes so the problem is about you cannot get showId ? – Qing Guo Mar 15 '23 at 10:11
Yes the cause was `long?` should have been `long` and the other feedback that I got – ozkan reyes Mar 15 '23 at 10:21
@ozkan reyes I just remove `[HttpGet("{id}")]`, and use GetShowCast(`long? showId`), the url is:https://localhost:7145/ControllerName/GetShowCast?showId=1 and I can get the result. You can try it. – Qing Guo Mar 15 '23 at 10:24

Entity Core Framework Query is not correct?

2 Answers2