title: Linq lambda expression many to many table select
body: <p>I have three tables, which two of them are in many to many relationship.</p>
  
  <p>Picture:</p>
  
  <p><img src="https://i.stack.imgur.com/L0gVj.png" alt="enter image description here"></p>
  
  <p>This is the data in middle mm table:</p>
  
  <p><img src="https://i.stack.imgur.com/pT17H.png" alt="enter image description here"></p>
  
  <p><strong>Edit:</strong>
  Got until here, I get proper 4 rows back, but they are all the same result(I know I need 4 rows back, but there are different results)</p>
  
  <pre><code>    return this._mediaBugEntityDB.LotteryOffers
          .Find(lotteryOfferId).LotteryDrawDates
          .Join(this._mediaBugEntityDB.Lotteries, ldd =&gt; ldd.LotteryId, lot =&gt; lot.Id, (ldd, lot) =&gt;
              new Lottery
              {
                  Name = lot.Name,
                  CreatedBy = lot.CreatedBy,
                  ModifiedOn = lot.ModifiedOn
              }).AsQueryable();
  </code></pre>
  
  <p>My question is, how can I retrieve all the Lotteries via many to many table WHERE I have LotteryOfferId given only?</p>
  
  <p>What I want to achieve is to get data from lottery table by LotteryDrawDateId.</p>
  
  <p>First I use LotteryOfferId to get DrawDates from middle table, and by middle table I get drawDateIds to use them in LotteryDrawDate table. From that table I need to retreive Lottey table by LotteryId in LotteryDrawDate table.</p>
  
  <p>I gain this by normal SQL(LotteryOffersLotteryDrawDates is middle table in DB, not seen in model):</p>
  
  <blockquote>
    <p>select
    Name, Lotteries.CreatedBy, Lotteries.ModifiedOn, count(Lotteries.Id)
    as TotalDrawDates from Lotteries join LotteryDrawDates on Lotteries.Id
    = LotteryDrawDates.LotteryId join LotteryOffersLotteryDrawDates on LotteryDrawDates.Id = 
    LotteryOffersLotteryDrawDates.LotteryDrawDate_Id
    where LotteryOffersLotteryDrawDates.LotteryOffer_Id = 19 group by
    Name, Lotteries.CreatedBy, Lotteries.ModifiedOn</p>
  </blockquote>
  
  <p>But Linq is different story :P</p>
  
  <p>I would like to do this with lambda expressions.
  Thanks</p>