title: C# lambda expression joining 4 tables situation
body: <p>I am joining 4 tables (Productos, ProductosFotos, Marca, Categoria) to find the product by CategoriaId. The ProductoFotos table contains 3 images but I only need to show 1 of them. What do I need to do and not repeat the result for each image.</p>
  
  <p>Here is my code:</p>
  
  <pre><code>var productoInCategoriaInDb = _productoRepository.GetAll()
              .Where(x =&gt; x.Publicado == true &amp;&amp; x.CategoriaId == id)
              .GroupJoin(
                  _productoFotoRepository.GetAll(), &lt;-- here take only one image
                  p =&gt; p.ProductoId,
                  pf =&gt; pf.ProductoId,
                  (p, pf) =&gt; new { p, pf })
              .SelectMany(
                  p =&gt; p.pf.DefaultIfEmpty(),
                  (p, pf) =&gt; new { p.p, pf })
              .GroupJoin(
                  _marcaRepository.GetAll(),
                  ppf =&gt; ppf.p.MarcaId,
                  pm =&gt; pm.MarcaId,
                  (ppf, pm) =&gt; new { ppf, pm })
              .SelectMany(
                  ppfpm =&gt; ppfpm.pm.DefaultIfEmpty(),
                  (ppfpm, pm) =&gt; new { ppfpm.ppf, pm })
              .GroupJoin(
                  _categoriaRepository.GetAll(),
                  ppfpm =&gt; ppfpm.ppf.p.CategoriaId,
                  pc =&gt; pc.CategoriaId,
                  (ppfpm, pc) =&gt; new { ppfpm, pc })
              .SelectMany(
                  ppfpmpc =&gt; ppfpmpc.pc.DefaultIfEmpty(),
                  (ppfpmpc, pc) =&gt; new ProductoBuscarViewModel
                  {
                      Producto = ppfpmpc.ppfpm.ppf.p,
                      ProductoFoto = ppfpmpc.ppfpm.ppf.pf,
                      Marca = ppfpmpc.ppfpm.pm,
                      Categoria = pc
                  }
              );
  </code></pre>
  
  <p><strong>Update</strong></p>
  
  <p><a href="https://i.stack.imgur.com/bAKHs.jpg" rel="nofollow noreferrer">Relational contraints diagram</a></p>