Filter objects in a LINQ expression

I have a LINQ expression associating two object classes, two "tables", where I created a Join expression to associate some objects. My code is like this:

list = (from x in relacaoProdutosFilial
       join y in _listaDeVendas on new { x.CodigoEmpresa } equals new { y.CodigoEmpresa }
       where y.RelacaoProdutos.Contains(x.CodigoProduto)
       select new DetalhamentoVendaRealizada
{
<code>
}

I know the WHERE expression is incorrect, but the reasoning is this, I need to filter the records that are within the RelacaoProdutos list that is in class Y, which have the items CodeProduto that are in class X.

Can I make this filter in a LINQ expression?

CodePudding user response：

Ok, so you have a table containing RelacaoProdutosFilial (whatever that may be). Every RelacaoProdutoFilial from this table has properties CodigoEmpresa (Company Code) and CodigoProduto (Product Code)

You also have a table ListasDeVendas (sales list?). Every ListaDeVendas in this table has properties CodigoEmpresa and RelacaoProdutos (Product list)

There seems to be a relation between these two tables. Alas you forgot to give us the relation, and because you also forgot to tell us the relevant fields, especially the primary key and the foreign key, I'll have to assume some things.

To me it seems that there is a one-to-many relation between RelacaoProdutosFilial and ListaDeVendas: every RelacaoProdutoFilial has zero or more ListaDeVendas, and every ListaDeVenda belongs to exactly one RelacaoProdutoFilial, namely the one that the foreign key CodigoEmpresa refers to.

Further more you give us some code that doesn't do what you want, and you ask us to give you some working code:

I need to filter the records that are within the RelacaoProdutos list that is in class Y, which have the items CodigoProduto that are in class X.

I think, you want all RelacaoProdutosFilial, each RelacaoProdutoFilial with their ListasDeVendas. You don't want all ListasDeVendas of this RelacaoProdutoFilial, only those ListasDeVendas that have at least one RelacaoProduto that equals the RelacaoProdutoFilial's CodigoProduto.

In other words, suppose you have RelacaoProdutoFilial [10] has CodigoProduto [34] and several ListasDeVendas:

ListaDeVendas [20], with RelacaoProdutos {30, 34, 36, 34}
ListaDeVendas [21], with RelacaoProdutos {31, 34},
ListaDeVendas [22], with RelacaoProdutos {30, 36}

I need to filter the records that are within the RelacaoProdutos list that is in class Y, which have the items CodigoProduto that are in class X.

So as output you want RelacaoProdutoFilial [10] with three ListasDeVendas:

ListaDeVendas [20], with RelacaoProdutos {34, 34}
ListaDeVendas [21], with RelacaoProdutos {34},
ListaDeVendas [22], with and empty list of RelacaoProdutos

My, it took some time to find out what you want. Consider next time you ask a question to precisely define your requirements. After all, you must have them somewhere, so why not tell them? It prevents us from assuming the wrong things

var result = RelacaoProdutosFilials.GroupJoin(ListasDeVendas,

    relacaoProdutoFilials => relacaoProdutoFilials.CodigoEmpresa, // take the primary key
    listaDeVendas => listaDeVendas.CodigoEmpresa,   // take the foreign key

    // parameter resultSelector: from every relacaoProdutoFilial, and all its
    // listaDeVendas make one new:
    (relacaoProdutoFilial, listasDeVendasOfThisRelacaoProdutoFilial) => new
    {
         // select ony the relacaoProdutoFilial that you plan to use:
         Id = relacaoProdutoFilial.CodigoEmpresa,
         Name = relacaoProdutoFilial.Name,
         ProductCode = relacaoProdutoFilial.CodigoProduto,
         ...

         // you don't want all listaDeVendas Of This RelacaoProdutoFilial,
         // only those that have at least one RelacaoProduto that equals the
         // RelacaoProdutoFilial's CodigoProduto
         ListasDeVendas = listasDeVendasOfThisRelacaoProdutoFilial
            .Where (listaDeVendas => listaDeVendas.RelacaoProdutos
                   .Contains(relacaoProdutoFilial.CodigoProduto))
            .Select(listaDeVendas => new
            {
                // Select only the properties of this ListaDevenda that you plan to use
                Id = listaDeVendas.Id,
                Date = listaDeVendas.Date,

                // no need to Select the foreign key, you already know the value
                // CodigoEmpresa = listaDeVendas.CodigoEmpresa,

                // no need to select the product code, you already know the value
                // ProductCode = listaDeVendas.RelacaoProdutos,
          })
          .ToList(),
     });