How to make this query with lambda expression in Entity Framework?

Question

This is my SQL query:

select 
    m.Name, s.Time, t.TheaterNumber
from   
    Movies m
join 
    MovieSeanceTheaters mst on mst.MovieId = m.MovieID
join 
    Theaters t on t.ID = mst.TheaterId
join 
    Seances s on mst.SeanceId = s.ID

This is my attempt at a Linq query:

var result = (from m in _context.Movies
              join mst in _context.MovieSeanceTheaters on m.ID equals mst.MovieId
              join t in _context.Theaters on mst.TheaterId equals t.ID
              join s in _context.Seances on mst.TheaterId equals s.ID
              select new { Film = m.Name, Salon = t.Name, Seans = s.Time }
             ).ToList();

I made this attempt, but I want to make with lambda for instance:

var result = movieManager.GetAll().Where(x => x.MovieSeanceTheaters)....

I couldn't do that.

What could you not do? Also, your attempt has a different starting point. And then, you shouldn't use join at all. Use navigation properties. — Gert Arnold
– Gert Arnold, Commented Nov 16, 2019 at 13:30

Alexander Petrov · Accepted Answer · 2019-11-16 13:42:31Z

1

If I understand you correctly, you want to rewrite your query from query syntax to method syntax?

Here we are!

var result = _context.Movies
    .Join(_context.MovieSeanceTheaters,
        m => m.MovieID,
        mst => mst.MovieID,
        (m, mst) => new
        {
            m = m,
            mst = mst
        })
    .Join(_context.Theaters,
        temp0 => temp0.mst.TheaterID,
        t => t.ID,
        (temp0, t) =>
            new
            {
                temp0 = temp0,
                t = t
            })
    .Join(_context.Seances,
        temp1 => temp1.temp0.mst.TheaterID,
        s => s.ID,
        (temp1, s) =>
            new
            {
                Film = temp1.temp0.m.Name,
                Salon = temp1.t.TheaterNumber,
                Seans = s.Time
            });

Looks ugly, doesn't it?
Most often, the method syntax is more compact and convenient. But in this case, leave it as is.

answered Nov 16, 2019 at 13:42

Alexander Petrov

14.4k2 gold badges23 silver badges56 bronze badges

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

2 Comments

Alexander Petrov Over a year ago

@clleker - yes, EF join for us, if navigation properties are available. Do you have any?

clleker Over a year ago

I use codefirst my project so I want to make lambda ,I don't know which table to start with

Collectives™ on Stack Overflow

How to make this query with lambda expression in Entity Framework?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related